SQL银行模拟语句整合

SQL银行模拟语句整合

--创建表空间
create tablespace bankspace datafile 'E:\bank\bank.dbf' size 10m autoextend on;
--创建用户bankuser
create user bankuser identified by bankuser default tablespace bankspace;
--
drop table tradeinfo;
drop table userInfo;
drop table cardInfo;
drop table Deposit;
--为用户授权
grant connect,resource to bankuser;
--创建用户信息表
create table userInfo --用户信息表
(
customerID number not null,
customerName varchar2(8),
PID varchar2(18) not null,
telephone varchar2(20) not null,
address varchar(50)
);
--创建银行卡信息表
create table cardinfo(
cardid char(19) NOT NULL,
curid varchar(10)NOT NULL,--币种
savingid number NOT NULL,
openDate date NOT NULL,
openMoney number NOT NULL,
balance number NOT NULL,
pass char(6)NOT NULL,
isreportloss number(1)NOT NULL,--判断卡是否挂失
customerID number NOT NULL
);
select * from userInfo;
--创建交易信息表
create table tradeInfo 
(
tradeDate DATE NOT NULL,
tradeType Char(4) NOT NULL,
cardID  CHAR(19) NOT NULL,
tradeMoney NUMBER NOT NULL,
remark LONG
);
select * from tradeInfo;
--创建存款类型表
create table deposit (
savingID NUMBER NOT NULL,
savingName varchar(20)NOT NULL,
descrip varchar(50)
);
/*为deposit表添加约束*/
alter table deposit add constraint pa_savingID
primary key(savingid);
/*为userInfo添加约束*/
--主键
alter table userinfo add constraint pk_customerID
primary key(customerID);
--check约束，身份证号长度
alter table userinfo add constraint ck_pid
check(length(pid)=18 or length(pid)=15);
--unique唯一约束，身份证号唯一
alter table userinfo add constraint uq_pid unique(pid);
--check约束，电话号码
alter table userinfo add constraint ck_telephone
check(regexp_like(telephone,'(^\d{3,4}-\d{7,8}$)|(^\d{11}$)'));
--查看创建的约束
select * from user_constraints where table_name='DEPOSIT';
select * from user_constraints where table_name='USERINFO';
/*为cardInfo添加约束*/
--主键
alter table cardinfo add constraint pk_cardid
primary key(cardid);
--check约束，卡号
alter table cardinfo add constraint ck_cardID
check(regexp_like(cardid,'1010 3576 \d{4} \d{4}'));
--修改表中字段
alter table cardInfo modify (curid varchar(10) default 'RMB');
alter table cardInfo modify (opendate varchar(10) default sysdate);
--预存金额大于等于1
alter table cardinfo add constraint ck_openmoney
check(openmoney>=1);
--check 约束 ，预存金额大于等于1
alter table cardinfo add constraint ck_balance
check(balance>=1);
--密码检查约束，密码必须是六位数字
alter table cardinfo add constraint ck_pass
check(regexp_like(pass,'^[0-9]{6}$'));
--密码默认6个8
alter table cardinfo modify (pass char(6) default '888888');
--是否挂失默认值0
alter table cardinfo modify (isreportloss number(1) default 0);
--外键约束
alter table cardinfo add constraint fk_customerid
foreign key(customerid)references userInfo(customerid);
--外键约束
alter table cardinfo add constraint fk_savingid
foreign key(savingid)references deposit(savingid);
/*为tradeinfo 表添加约束*/
--交易类型(存入、支取)
alter table tradeinfo add constraint ck_tradetype
check(tradetype in ('存入','支取'));
--外键
alter table tradeinfo add constraint fk_cardID
foreign key(cardid)references cardInfo(cardId);
--交易金额大于0
alter table tradeinfo add constraint ck_tradeMoney
check(tradeMoney>0);
--交易时间
alter table tradeinfo modify (tradeDate date default sysdate);
/***************************************************************************************************/
/*                                        插入测试数据                                              */
/***************************************************************************************************/
/*
 ==========================
 测试数据
 ==========================
 */
--存款类型
INSERT INTO deposit (savingID,savingName,descrip) VALUES (1,'活期','按存款日结算利息');
INSERT INTO deposit (savingID,savingName,descrip) VALUES (2,'定期一年','存款期是1年');
INSERT INTO deposit (savingID,savingName,descrip) VALUES (3,'定期二年','存款期是2年');
INSERT INTO deposit (savingID,savingName,descrip) VALUES (4,'定期三年','存款期是3年');
INSERT INTO deposit (savingID,savingName) VALUES (5,'定活两便');
INSERT INTO deposit (savingID,savingName) VALUES (6,'通知');
INSERT INTO deposit (savingID,savingName,descrip) VALUES (7,'零存整取一年','存款期是1年');
INSERT INTO deposit (savingID,savingName,descrip) VALUES (8,'零存整取二年','存款期是2年');
INSERT INTO deposit (savingID,savingName,descrip) VALUES (9,'零存整取三年','存款期是3年');
INSERT INTO deposit (savingID,savingName,descrip) VALUES (10,'存本取息五年','按月支取利息');
SELECT * FROM DEPOSIT;
SELECT * FROM userinfo;
SELECT * FROM cardinfo;
SELECT * FROM tradeinfo;
INSERT INTO userInfo(customerID,customerName,PID,telephone,address )
     VALUES(1,'张三','123456789012345','010-67898978','北京海淀');
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
     VALUES('1010 3576 1234 5678',1,1000,1000,1);
INSERT INTO userInfo(customerID,customerName,PID,telephone)
     VALUES(2,'李四','321245678912345678','0478-44443333');
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
     VALUES('1010 3576 1212 1134',2,1,1,2);
INSERT INTO userInfo(customerID,customerName,PID,telephone)
     VALUES(3,'王五','567891234532124670','010-44443333');
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
     VALUES('1010 3576 1212 1130',2,1601,1601,3);
INSERT INTO userInfo(customerID,customerName,PID,telephone)
     VALUES(4,'丁六','567891321242345618','0752-43345543');
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
     VALUES('1010 3576 1212 1004',2,1,1,4);
/*
张三的卡号（1010 3576 1234 5678）取款900元，李四的卡号（1010 3576 1212 1134）存款5000元，要求保存交易记录，以便客户查询和银行业务统计。
说明：当存钱或取钱（如300元）时候，会往交易信息表（tradeInfo）中添加一条交易记录，
      同时应更新银行卡信息表（cardInfo）中的现有余额（如增加或减少500元）
*/
/*--------------交易信息表插入交易记录--------------------------*/
INSERT INTO tradeInfo(tradeType,cardID,tradeMoney) 
      VALUES('支取','1010 3576 1234 5678',900)  ;
/*-------------更新银行卡信息表中的现有余额-------------------*/
UPDATE cardInfo SET balance=balance-900 WHERE cardID='1010 3576 1234 5678';
/*--------------交易信息表插入交易记录--------------------------*/
INSERT INTO tradeInfo(tradeType,cardID,tradeMoney) 
      VALUES('存入','1010 3576 1212 1130',300)  ;
/*-------------更新银行卡信息表中的现有余额-------------------*/
UPDATE cardInfo SET balance=balance+300 WHERE cardID='1010 3576 1212 1130';
/*--------------交易信息表插入交易记录--------------------------*/
INSERT INTO tradeInfo(tradeType,cardID,tradeMoney) 
      VALUES('存入','1010 3576 1212 1004',1000)  ;
/*-------------更新银行卡信息表中的现有余额-------------------*/
UPDATE cardInfo SET balance=balance+1000 WHERE cardID='1010 3576 1212 1004';
/*--------------交易信息表插入交易记录--------------------------*/
INSERT INTO tradeInfo(tradeType,cardID,tradeMoney) 
      VALUES('支取','1010 3576 1212 1130',1900)  ;
/*-------------更新银行卡信息表中的现有余额--报错-----------------*/
UPDATE cardInfo SET balance=balance-1900 WHERE cardID='1010 3576 1212 1130';
/*--------------交易信息表插入交易记录--------------------------*/
INSERT INTO tradeInfo(tradeType,cardID,tradeMoney) 
      VALUES('存入','1010 3576 1212 1134',5000)   ;
      
      --INSERT INTO tradeInfo(tradeType,cardID,tradeMoney,Tradedate) 
      --VALUES('存入','1010 3576 1212 1134',5000,to_date('2014-06-01','yyyy-mm-dd'))   ;
/*-------------更新银行卡信息表中的现有余额-------------------*/
UPDATE cardInfo SET balance=balance+5000 WHERE cardID='1010 3576 1212 1134';
commit;
/*--------检查测试数据是否正确---------*/
SELECT * FROM cardInfo;
SELECT * FROM tradeInfo;
SELECT * FROM userInfo;
--删除数据
delete from tradeInfo;
delete from cardInfo;
delete from userInfo;
delete from DEPOSIT;
/*
取本周第一天
select trunc (sysdate,'DAY') from dual;
*/
/*---------修改密码-----*/
--1.张三（卡号为1010 3576 1234 5678）修改银行卡密码为123456
--2.李四（卡号为1010 3576 1212 1134）修改银行卡密码为123123
update cardInfo set pass='123456' WHERE cardID='1010 3576 1234 5678' ;
update cardInfo set pass='123123' WHERE cardID='1010 3576 1212 1134' ;
--查询账户信息
SELECT * FROM cardInfo;
/*---------挂失帐号---------*/
--李四（卡号为1010 3576 1212 1134）因银行卡丢失，申请挂失
update cardInfo set IsReportLoss=1 WHERE cardID='1010 3576 1212 1134' ;
SELECT * FROM cardInfo;
--查看修改密码和挂失结果
SELECT cardid 卡号,curID 货币,savingName 储蓄种类,opendate 开户日期,openmoney 开户金额,balance 余额,pass 密码,
    case IsReportLoss WHEN 1 THEN '挂失'  WHEN 0 THEN '未挂失' ELSE NULL end 是否挂失, 
    customerName 客户姓名
FROM CardInfo, Deposit, UserInfo
WHERE CardInfo.savingID=Deposit.savingID and CardInfo.customerID = UserInfo.customerID;
/*--------统计银行的资金流通余额和盈利结算------------------------------*/
--统计说明:存款代表资金流入,取款代表资金.假定存款利率为千分之3,贷款利率为千分之8
/*--单一货币RMB--*/
DECLARE 
    v_inMoney number;
    v_outMoney number;
    v_profit number;
begin
    --SELECT * FROM tradeInfo 
    SELECT sum(tradeMoney) into v_inMoney FROM tradeInfo WHERE (tradeType='存入');
    SELECT sum(tradeMoney) into v_outMoney FROM tradeInfo WHERE (tradeType='支取');
    dbms_output.put_line('银行流通余额总计为:'||to_char(v_inMoney-v_outMoney)||'RMB');
    v_profit:=v_outMoney*0.008-v_inMoney*0.003;
    dbms_output.put_line('盈利结算为:'||to_char(v_profit)||'RMB');
end;
/*--------查询本周开户的卡号,显示该卡相关信息-----------------*/
SELECT c.cardID 卡号,u.customerName 姓名,c.curID 货币,d.savingName 存款类型,c.openDate 开户日期,c.openMoney 开户金额,c.balance 存款余额,
       CASE c.IsReportLoss WHEN 0 THEN '正常账户'
                           WHEN 1 THEN '挂失账户'
                           ELSE NULL
       END 账户状态
FROM cardInfo c INNER JOIN userInfo u ON (c.customerID = u.customerID)
INNER JOIN Deposit d ON (c.savingID = d.savingID )
WHERE  openDate between trunc(sysdate,'DAY') and trunc(sysdate,'DAY')+6
/*---------查询本月交易金额最高的卡号----------------------*/
SELECT * FROM tradeInfo;
SELECT DISTINCT cardID 
FROM tradeInfo 
WHERE  tradeMoney=
    (SELECT Max(tradeMoney) FROM tradeInfo
     WHERE to_char(tradeDate,'yyyy-mm')=to_char(sysdate,'yyyy-mm'));
   
/*---------查询挂失帐号的客户信息---------------------*/
SELECT customerName as 客户姓名,telephone as 联系电话 FROM userInfo 
    WHERE customerID IN (SELECT customerID FROM cardInfo WHERE IsReportLoss=1);
    
    
/*------催款提醒：例如某种业务的需要，每个月末，如果发现用户帐上余额少于200元，将致电催款。---*/
SELECT customerName as 客户姓名,telephone as 联系电话,balance as 存款余额 
FROM userInfo INNER JOIN cardInfo ON  userInfo.customerID=cardInfo.customerID 
WHERE balance<200;
/*视图查询*/
--1.创建视图：为了向客户显示信息友好,查询各表要求字段全为中文字段名。
create or replace VIEW vw_userInfo  --客户信息表视图
  AS 
    select customerID as 客户编号,customerName as 开户名, PID as 身份证号,
        telephone as 电话号码,address as 居住地址  from userInfo;
--使用视图
SELECT * FROM vw_userInfo;
--2.创建视图：查询银行卡信息
create or replace VIEW vw_cardInfo  --银行卡信息表视图
  AS 
    select c.cardID as 卡号,u.customerName as 客户,c.curID as 货币种类, d.savingName as 存款类型,c.openDate as 开户日期,
       c.balance as 余额,c.pass 密码,
       case c.IsReportLoss when 0 then '正常'
                           when 1 then '挂失'
       end as 是否挂失
    from cardInfo c, deposit d,userinfo u
    where c.savingID=d.savingID and c.customerID=u.customerID;
--使用视图
SELECT * FROM vw_cardInfo;
SELECT 客户,余额 FROM vw_cardInfo where 客户='张三';
--3.创建视图：查看交易信息
create VIEW vw_tradeInfo  --交易信息表视图
  AS 
    select tradeDate as 交易日期,tradeType as 交易类型, cardID as 卡号,tradeMoney as 交易金额,
      remark as 备注  from tradeInfo ;
--使用视图
SELECT * FROM vw_tradeInfo;
--4.根据客户登录名（采用实名制访问银行系统）查询该客户帐户信息的视图
/*trim去掉前后空格*/
create or replace VIEW vw_oneUserInfo  
  AS 
     select customerID as 客户编号,customerName as 开户名, PID as 身份证号,
        telephone as 电话号码,address as 居住地址  
     from userInfo
     where UPPER(TRIM(customerName)) in (select UPPER(TRIM(username)) from user_users);
select * from user_users;
--使用视图
select * from vw_oneUserInfo;
/*
存储过程
*/
select * from tradeinfo;
select * from cardinfo;
/*--1.取钱或存钱的存储过程*/
create or replace procedure usp_takeMoney 
  (v_card char, --卡号
  v_m number,  --存取金额
  v_type char,  --存取类型
  v_inputPass char default NULL) --密码
as
    v1 number(1);  --临时变量
    v_mybalance  number; --余额
begin
    dbms_output.put_line('交易正进行,请稍后......');
    if (v_type='支取') then
       SELECT 1 into v1 FROM cardInfo WHERE cardID=v_card and pass=v_inputPass;
    end if;
    SELECT balance into v_mybalance 
    FROM cardInfo 
    WHERE cardID=v_card;
    if (v_type='支取') then
       if (v_mybalance>=v_m+1) then
           update cardInfo set balance=balance-v_m WHERE cardID=v_Card;
       else
            dbms_output.put_line('卡号'||v_card||'  余额：'||to_char(v_mybalance));
            raise_application_error(-20000,'交易失败！余额不足！');
        end if;
    else
         update cardInfo set balance=balance+v_m WHERE cardID=v_card;
    end if;
    dbms_output.put_line('交易成功！交易金额：'||to_char(v_m));
    SELECT balance into v_mybalance FROM cardInfo WHERE cardID=v_card;
    dbms_output.put_line('卡号'||v_card||'  余额：'||to_char(v_mybalance)); 
    INSERT INTO tradeInfo(tradeType,cardID,tradeMoney) VALUES(v_type,v_card,v_m);
    commit;
exception
   when no_data_found then
      raise_application_error(-20001,'卡号或密码错误!');
end;
--调用存储过程取钱或存钱 张三取300，
 --现实中的取款机依靠读卡器读出张三的卡号,这里根据张三的名字查出考号来模拟
 
 DECLARE
   emp_20000 EXCEPTION;
   PRAGMA EXCEPTION_INIT(emp_20000, -20000);
   emp_20001 EXCEPTION;
   PRAGMA EXCEPTION_INIT(emp_20001, -20001);
   v_card char(19);
BEGIN
    select cardID into v_card 
    from cardInfo Inner Join userInfo ON cardInfo.customerID=userInfo.customerID 
    where customerName='张三';
    usp_takeMoney(v_card,300 ,'支取','123456');
EXCEPTION
   WHEN emp_20000 THEN
      DBMS_OUTPUT.PUT_LINE('交易失败！余额不足！');
      rollback;
   WHEN emp_20001 THEN
      DBMS_OUTPUT.PUT_LINE('密码错误!');
      rollback;
   WHEN no_data_found THEN
      DBMS_OUTPUT.PUT_LINE('用户名不存在!');      
      rollback;
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('出现了其他异常错误');
      rollback;
END;
--调用存储过程，李四存500
 
DECLARE
   emp_20000 EXCEPTION;
   PRAGMA EXCEPTION_INIT(emp_20000, -20000);
   emp_20001 EXCEPTION;
   PRAGMA EXCEPTION_INIT(emp_20001, -20001);
   v_card char(19);
BEGIN
    select cardID into v_card 
    from cardInfo Inner Join userInfo ON cardInfo.customerID=userInfo.customerID 
    where customerName='李四';
    usp_takeMoney(v_card,500 ,'存入');
EXCEPTION
   WHEN emp_20000 THEN
      DBMS_OUTPUT.PUT_LINE('交易失败！余额不足！');
      rollback;
   WHEN emp_20001 THEN
      DBMS_OUTPUT.PUT_LINE('密码错误!');
      rollback;
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('出现了其他异常错误');
      rollback;
END;
select * from vw_cardInfo;
select * from vw_tradeInfo;
/*--2.产生随机卡号的存储过程(dbms_random包来实现) --*/
create or replace procedure usp_randCardID(v_randCardID OUT char)
AS
    v_r number(8);
BEGIN
  v_r:=round(dbms_random.value(10000000,99999999));--产生这个范围(10000000,99999999)的随机数
  v_randCardID:='1010 3576 '||substr(v_r,1,4)||' '||substr(v_r,5,8);--四位一空格
end;
--测试产生随机卡号
DECLARE 
    v_mycardID char(19) ;
BEGIN
    usp_randCardID(v_mycardID);
    dbms_output.put_line('产生的随机卡号为：'||v_mycardID);
END;
/*--3.开户的存储过程--*/
select * from userInfo;
CREATE SEQUENCE seq_customerID
        START WITH 10 
        INCREMENT BY 1
        NOMAXVALUE 
        NOCYCLE
        CACHE 30;
create or replace procedure usp_openAccount
( v_customerName char,
  v_PID char,
  v_telephone char,
  v_openMoney number,
  v_savingName char,
  v_address varchar default '' )
AS
   v_mycardID char(19);
   v_cur_customerID int;
   v_savingID int ;
   v1 int;
 begin
   --调用产生随机卡号的存储过程获得随机卡号
   usp_randCardID (v_mycardID);
   SELECT count(*) into v1 FROM cardInfo WHERE cardID=v_mycardID;
   while (v1<>0) loop
      usp_randCardID (v_mycardID);
      SELECT count(*) into v1 FROM cardInfo WHERE cardID=v_mycardID;            
   end loop;
   dbms_output.put_line('尊敬的客户,开户成功!系统为您产生的随机卡号为:'||v_mycardID);
   dbms_output.put_line('开户日期'||to_char(sysdate,'yyyy-mm-dd')||'  开户金额:'||to_char(v_openMoney));
   select count(*) into v1 from userInfo where PID=v_PID;
   if v1=0 then
         INSERT INTO userInfo(customerID,customerName,PID,telephone,address )
          VALUES(seq_customerID.nextval,v_customerName,v_PID,v_telephone,v_address) ;   
   end if;  
   SELECT savingID into v_savingID FROM deposit WHERE savingName =v_savingName;
   select customerID into v_cur_customerID from userInfo where PID=v_PID;
   INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
         VALUES(v_mycardID,v_savingID,v_openMoney,v_openMoney,v_cur_customerID);
exception
    when no_data_found then
        raise_application_error(-20000,'存款类型不正确,请重新输入!');
    when others then
        raise_application_error(-20001,'其他错误,请重新输入!');
end;
--调用存储过程重新开户
begin
    usp_openAccount ('王老五','334456889012678','2222-63598978',1000,'活期','河南新乡');
    commit;
end;
--EXEC usp_openAccount('赵小二','213445678912342222','0760-44446666',1,'定期');
select * from vw_userInfo;
select * from vw_cardInfo;
select * from vw_tradeInfo;
GO
/*--4.输入页数和每页显示的记录数，实现分页显示*/
--DROP PROCEDURE usp_pagingDisplay
SELECT tradeDate 交易日期,tradeType 交易类型,cardID 卡号,trademoney 交易金额 
FROM (SELECT t.*,rownum rn FROM (SELECT * FROM tradeInfo ) t)
WHERE rn>=4 and rn<=6;
CREATE OR REPLACE PROCEDURE usp_pagingDisplay
( v_page number:= 1, 
  v_records number:= 10)
AS
  v_rec1 number;
  v_rec2 number;
  v_statement varchar2(200);
  TYPE cursor_type IS REF CURSOR;
  --声明一个游标变量
  c1 CURSOR_TYPE;
  v_trade tradeinfo%rowtype;
begin
  v_rec1:= (v_page-1)*v_records+1;
  v_rec2:= v_page*v_records;
  v_statement:='SELECT  tradeDate,tradeType,cardID,trademoney,REMARK ';
  --SQL语句拼接
  v_statement:=v_statement||'FROM (SELECT t.*,rownum rn FROM (SELECT * FROM tradeInfo ) t) ';
  v_statement:=v_statement||'WHERE rn>='||v_rec1||' and rn<='||v_rec2;
  --dbms_output.put_line(v_statement);
  dbms_output.put_line('交易日期   交易类型             卡号           交易金额 ');
  dbms_output.put_line('---------------------------------------------------------');
  open c1 for v_statement;
  LOOP
      FETCH c1 INTO v_trade ;
      EXIT WHEN c1%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(to_char(v_trade.tradeDate,'yyyy-mm-dd')||'     '||v_trade.tradeType||'     '||v_trade.cardID||'     '||v_trade.trademoney);
  END LOOP;
  CLOSE c1;
END;
BEGIN
 usp_pagingDisplay(2,2);
END;
/*---- 5.打印对账单 ----*/
--drop proc usp_CheckSheet
CREATE OR REPLACE PROCEDURE usp_CheckSheet(
  v_cardID varchar2,
  v_date1 date:=NULL,
  v_date2 date:=NULL)
AS
    v_custName varchar2(20);
    v_curName varchar2(20);
    v_savingName varchar2(20);
    v_openDate date;
  TYPE cursor_type IS REF CURSOR;
  --声明一个游标变量
   c1 CURSOR_TYPE;
   v_trade tradeinfo%rowtype;
   v_sqlStr varchar2(2000);
BEGIN
    SELECT c.curID, u.customerName,d.savingName ,c.openDate
  INTO v_curName,v_custName,v_savingName, v_openDate
    FROM cardInfo c inner join  userInfo u on c.customerID=u.customerID
                  inner join deposit d on c.savingID = d.savingID
    WHERE  cardID = v_cardID; --and u.customerName = user_name()
    dbms_output.put_line('卡号：' || v_cardID);
    dbms_output.put_line('姓名:'  || v_custName);
    dbms_output.put_line('货币:'  || v_curName);
    dbms_output.put_line('存款类型：'  || v_savingName);
    dbms_output.put_line('开户日期：'  || to_char(v_openDate,'yyyy"年"mm"月"dd"日"'));
    dbms_output.put_line(' ');
    dbms_output.put_line('--------------------------------------------------------------------');
  dbms_output.put_line('交易日   '||'   类型  '||'   交易金额  '||'   备注');
  v_sqlStr:='SELECT *
        FROM tradeInfo
        WHERE cardID='''||v_cardID||'''';
  IF v_date2 IS NOT NULL THEN
      v_sqlStr:=v_sqlStr||' AND tradeDate <=to_date('''||to_char(v_date2,'yyyy-mm-dd')||' 23:59:59'||''',''yyyy-mm-dd hh24:mi:ss'')';
  END IF;
  IF v_date1 IS NOT NULL THEN
      v_sqlStr:=v_sqlStr||' AND tradeDate >=to_date('''||to_char(v_date1,'yyyy-mm-dd')||' 00:00:00'||''',''yyyy-mm-dd hh24:mi:ss'')';       
  END IF;
  v_sqlStr:=v_sqlStr||' ORDER BY tradeDate';
  --dbms_output.put_line(v_sqlStr);
  open c1 for v_sqlStr;
  LOOP
      FETCH c1 INTO v_trade ;
      EXIT WHEN c1%NOTFOUND;  
      DBMS_OUTPUT.PUT_LINE(to_char(v_trade.tradeDate,'yyyy-mm-dd')||'     '||v_trade.tradeType||'     '||v_trade.tradeMoney||'     '||v_trade.remark);
  END LOOP;
  CLOSE c1;
END;
--调用
begin
  -- Test statements here
  usp_CheckSheet('1010 3576 1212 1130',to_date('2012-12-01','yyyy-mm-dd'),to_date('2013-11-01','yyyy-mm-dd'));
end;
/*--6.查询、统计在指定时间段内没有发生交易的账户信息*/
--drop proc usp_getWithoutTrade
create or replace procedure usp_getWithoutTrade(
  v_Num out number ,
  v_Amount out number ,
  v_date1 date := NULL,
  v_date2 date := NULL)
AS
 v_sd date;
 v_ed date;
TYPE cursor_type IS REF CURSOR;
c1 CURSOR_TYPE;
v_cur userInfo%rowtype;
BEGIN
  IF v_date1 IS NULL THEN
   v_sd:=trunc(sysdate,'month');
  END IF;
  IF v_date2 IS NULL THEN
     v_ed := sysdate;
  END IF;
  dbms_output.put_line('客户号   '||'  客户姓名'||'    身份证号'||'   电话'||'   地址');
  dbms_output.put_line('---------------------------------------------------------------');
  open c1 for SELECT distinct u.customerID,u.customerName,u.PID,u.telephone,address
  FROM userInfo u
  JOIN cardInfo c ON u.customerID = c.customerID
  WHERE c.cardID NOT IN (SELECT cardID FROM tradeInfo WHERE tradeDate Between  v_sd and v_ed);
  LOOP
      FETCH c1 INTO v_cur ;
      EXIT WHEN c1%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(v_cur.customerID||'     '||v_cur.customerName||'     '||v_cur.PID||'     '||v_cur.telephone||'   '||v_cur.address);
  END LOOP;
  CLOSE c1;
  SELECT COUNT(customerID),SUM(balance) into  v_Num,v_Amount
  FROM cardInfo
  WHERE cardID NOT IN (SELECT cardID FROM tradeInfo WHERE tradeDate Between v_sd and v_ed);
end;
--调用存储过程
DECLARE 
  v_NUM number;
  v_Amount number(18,2);
  v_date1 date;
  v_date2 date;
begin
  v_date1:= to_date('2009-1-1','yyyy-mm-dd');
  v_date2:= sysdate;
  usp_getWithoutTrade(v_NUM, v_Amount);--, @date1, @date2
  dbms_output.put_line('统计未发生交易的客户');
  dbms_output.put_line( '---------------------------------------');
  dbms_output.put_line( '客户人数：' || v_NUM || '  客户总余额：' || v_Amount);
end;
/*--7.统计银行卡交易量和交易额*/
--drop proc usp_getTradeInfo
create or replace procedure usp_getTradeInfo(
  v_Num1 out number,
  v_Amount1 out number,
  v_Num2 out number,
  v_Amount2  out number,
  v_date1 date:=trunc(sysdate,'year'),
  v_date2 date:=sysdate,
  v_address varchar:= NULL)
AS
BEGIN
 
  IF v_address IS NULL THEN
    SELECT COUNT(tradeMoney), SUM(tradeMoney) into v_num1,v_Amount1
    FROM tradeInfo
    WHERE tradeDate BETWEEN v_date1 AND v_date2 AND tradeType='存入';
    SELECT COUNT(tradeMoney), SUM(tradeMoney) into v_num2,v_Amount2
    FROM tradeInfo
    WHERE tradeDate BETWEEN v_date1 AND v_date2 AND tradeType='支取';
  ELSE
    SELECT  COUNT(tradeMoney), SUM(tradeMoney) into v_num1,v_Amount1
    FROM tradeInfo JOIN cardInfo ON tradeInfo.cardID = cardInfo.cardID
           JOIN userInfo ON cardInfo.customerID = userInfo.customerID
    WHERE tradeDate BETWEEN v_date1 AND v_date2 AND tradeType='存入'
        AND address Like '%'||v_address||'%';
    SELECT COUNT(tradeMoney), SUM(tradeMoney) into v_num2,v_Amount2
    FROM tradeInfo JOIN cardInfo ON tradeInfo.cardID = cardInfo.cardID
           JOIN userInfo ON cardInfo.customerID = userInfo.customerID
    WHERE tradeDate BETWEEN v_date1 AND v_date2 AND tradeType='支取'
        AND address Like '%'||v_address||'%';
  END IF;
  v_num1:=nvl(v_num1,0);
  v_num2:=nvl(v_num2,0);
  v_Amount1:=nvl(v_Amount1,0);
  v_Amount2:=nvl(v_Amount2,0);
end;
--调用
declare 
    v_CNT1 number;
    v_Total1 number(18,2);
    v_CNT2 number;
    v_Total2 number(18,2);
    v_date1 date;
    v_date2 date;
begin
    v_date1 := to_date('2009-1-1','yyyy-mm-dd');
    v_date2 := sysdate;
    usp_getTradeInfo (v_CNT1, v_Total1, v_CNT2, v_Total2, v_date1,v_date2,'北京海淀');--, '北京';
    dbms_output.put_line('统计银行卡交易量和交易额');
    dbms_output.put_line('');
    dbms_output.put_line('起始日期：' || to_char(v_date1,'yyyy-mm-dd') ||  '  截止日期：' || to_char(v_date2,'yyyy-mm-dd'));
    dbms_output.put_line('-----------------------------------------------------------');
    dbms_output.put_line('存入笔数：' || v_CNT1 || '  存入金额：' ||v_Total1);
    dbms_output.put_line( '支取笔数：' || v_CNT2|| '  支取金额：' ||v_Total2);
    dbms_output.put_line('-----------------------------------------------------------');
    dbms_output.put_line('发生笔数：' || (v_CNT1+v_CNT2)|| '  结余金额：' || (v_Total1-v_Total2));
end;
/*复杂的业务逻辑*/
--转帐的事务存储过程
--  drop proc usp_tradefer
create or replace procedure usp_tradefer (
    v_card1 varchar2,
    v_pwd varchar2,
    v_card2 varchar2,
    v_outmoney number)
AS
   v_date1 date:= sysdate;
   v_date2 date:= sysdate;
   
   emp_20000 EXCEPTION;
   PRAGMA EXCEPTION_INIT(emp_20000, -20000);
   emp_20001 EXCEPTION;
   PRAGMA EXCEPTION_INIT(emp_20001, -20001);
BEGIN
    commit;
    dbms_output.put_line('开始转账，请稍后......');
    usp_takeMoney(v_card1,v_outmoney ,'支取',v_pwd);
    usp_takeMoney(v_card2,v_outmoney ,'存入');
    commit;
    dbms_output.put_line('转账成功！');
    v_date2 := sysdate;
    dbms_output.put_line('打印转出账户对账单');
    dbms_output.put_line('-------------------');
    usp_CheckSheet(v_card1,v_date1,v_date2);
    dbms_output.put_line('打印转入账户对账单');
    dbms_output.put_line( '-------------------');
    usp_CheckSheet(v_card2,v_date1,v_date2);
EXCEPTION
   WHEN emp_20000 THEN
      DBMS_OUTPUT.PUT_LINE('交易失败！余额不足！转账失败！');
      rollback;
   WHEN emp_20001 THEN
      DBMS_OUTPUT.PUT_LINE('卡号或密码错误! 转账失败！');
      rollback;
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('出现了其他异常错误,转账失败！');
      rollback;
END;
--测试上述事务存储过程
--从李四的帐户转帐2000到张三的帐户
--同上一样,现实中的取款机依靠读卡器读出张三/李四的卡号,这里根据张三/李四的名字查出考号来模拟
DECLARE
   emp_20000 EXCEPTION;
   PRAGMA EXCEPTION_INIT(emp_20000, -20000);
   emp_20001 EXCEPTION;
   PRAGMA EXCEPTION_INIT(emp_20001, -20001);
   v_card1 char(19);
   v_card2 char(19);
BEGIN
    select cardID into v_card1 
    from cardInfo Inner Join userInfo ON cardInfo.customerID=userInfo.customerID 
    where customerName='李四';
    select cardID into v_card2 
    from cardInfo Inner Join userInfo ON cardInfo.customerID=userInfo.customerID 
    where customerName='张三';
    usp_tradefer(v_card1,'123123',v_card2,2000);
EXCEPTION
   WHEN emp_20000 THEN
      DBMS_OUTPUT.PUT_LINE('交易失败！余额不足！转账失败！');
   WHEN emp_20001 THEN
      DBMS_OUTPUT.PUT_LINE('密码错误!转账失败！');
   WHEN no_data_found THEN
      DBMS_OUTPUT.PUT_LINE('用户名不存在!转账失败！');      
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('出现了其他异常错误！');
END;
select * from vw_userInfo;
select * from vw_cardInfo;
select * from vw_tradeInfo;