模拟银行ATM取钱存钱开户挂失等

--创建表空间create tablespace lsqdatafile 'C:\Users\Administrator\Desktop\数据库项目\data.dbf'size 100Mautoextend on;--创建用户create user xxxidentified by lsqdefault tablespace lsq;--用户授权grant dba to xxx;--用户信息表create table userInfo (customerID NUMBER(30) not null,customerName VARCHAR2(30) not null unique,Pid varchar(18) unique,telephone varchar(11) not null,address VARCHAR2(50),constraint PK_t_user primary key (customerID));--银行卡信息表create table cardInfo (cardID CHAR(30),savingID NUMBER(29) not null ,customerID NUMBER(30) not null,pass CHAR(6) not null , --密码curID VARCHAR2(10) not null, --币种openDate DATE not null, --开户日期openMoney NUMBER not null,--卡户金额balance NUMBER(8,2) not null , --余额IsReportLoss SMALLINT not null,--是否挂失constraint PK_t_card primary key (cardID),constraint cardInfo_savingID_fk foreign key(savingID) references deposit(savingID),constraint cardInfo_customerID_fk foreign key(customerID) references Userinfo(customerID));--交易信息表create table tradeInfo(cardID CHAR(30) not null,tradeDate DATE not null,tradeMoney NUMBER not null,tradeType CHAR(4) not null,remark CLOB,constraint tradeInfo_cardID_fk foreign key(cardID) references Cardinfo(cardID));--存款类型表create table deposit (savingID NUMBER(29) not null,savingName VARCHAR2(20) not null,descrip VARCHAR2(50) not null,constraint PK_DEPOSIT primary key (savingID));/*$$$$$$$$$$$$$加约束$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$*//* userInfo表的约束customerID 顾客编号 主键customerName 开户名 必填PID 身份证号 必填，只能是18位或15位，身份证号唯一约束telephone 联系电话 必填，格式为xxxx-xxxxxxxx或手机号11位address 居住地址 可选输入*/alter table userInfo add constraint userInfo_Pid_ck check((length(Pid)=15 or length(Pid)=18));alter table userInfo add constraint userInfo_telephone_ck check(regexp_like(telephone,'(^\d{3,4}-\d{8}$)|([0-9]{11})'));--ALTER TABLE userInfo--drop CONSTRAINT CK_telephone /*cardInfo表的约束cardID 卡号 必填，主健 , 银行的卡号规则和电话号码一样，一般前8位代表特殊含义，如某总行某支行等。假定该行要求其营业厅的卡号格式为：1010 3576 xxxx xxx开始curType 货币 必填，默认为RMBsavingType 存款种类 活期/定活两便/定期openDate 开户日期 必填，默认为系统当前日期openMoney 开户金额 必填，不低于1元balance 余额 必填，不低于1元,否则将销户pass 密码 必填，6位数字，默认为6个8IsReportLoss 是否挂失 必填，0/1值，默认为0未挂失customerID 顾客编号 必填，表示该卡对应的顾客编号，一位顾客可以办理多张卡*/alter table cardInfo modify(curID default 'RMB'); -- 默认为RMBalter table cardInfo modify(openDate default sysdate);-- 开户日期为系统时间alter table cardInfo add constraint cardInfo_openMoney_ck check(openMoney>1); --开户金额大于一alter table cardInfo add constraint cardInfo_balance_ck check(balance>1); -- 余额大于一alter table cardInfo add constraint cardInfo_cardID_ck check(regexp_like(cardID,'1010 3576 \d{4} \d{4}')); -- 卡号格式为：1010 3576 xxxx xxx开始alter table cardInfo add constraint cardInfo_pass_ck check(regexp_like(pass,'^[0-9]{6}$')); --6位数字，默认为6个8alter table cardInfo modify (pass default '888888');/* tradeInfo表的约束tradeType 必填，只能是存入/支取 cardID卡号必填，外健，可重复索引tradeMoney交易金额必填，大于0tradeDate交易日期必填，默认为系统当前日期remark备注可选输入，其他说明*/alter table tradeInfo add constraint tradeInfo_tradeType_ck check(tradeType in ('存入','支取'));alter table tradeInfo add constraint tradeInfo_tradeMoney_ck check(tradeMoney>0);alter table tradeInfo modify(tradeDate default sysdate);

**插入测试数据**--存款类型-- 向表中插入数据-- 用户信息表userInfoinsert into userInfo(customerID,customerName,Pid,Telephone,Address) values(01,'张三','420984199901564898','13387514907','武汉');insert into userInfo values(02,'李四','420984199901560000','11111111111','北京');-- 向deposit表插入数据insert into deposit(savingID,savingname,descrip) values('1','活期','可以随时取出,按日结算利息');insert into deposit(savingID,savingname,descrip) values('2','定期','利息高,但不到期不能取出');insert into deposit(savingID,savingname,descrip) values('3','活定两期','可随时取出,但利息较低');--向cradInfo插入信息insert into cardInfo values('1010 3576 1234 5678','1','01','888888','RMB',sysdate,'5000','5000','0');insert into cardInfo values('1010 3576 1212 1134','1','02','888888','RMB',sysdate,'5000','5000','0');/*张三的卡号（1010 3576 1234 5678）取款900元，李四的卡号（1010 3576 1212 1134）存款5000元，要求保存交易记录，以便客户查询和银行业务统计。说明：当存钱或取钱（如300元）时候，会往交易信息表（tradeInfo）中添加一条交易记录，同时应更新银行卡信息表（cardInfo）中的现有余额（如增加或减少500元）*//*--------------交易信息表插入交易记录--------------------------*/insert into tradeInfo values('1010 3576 1234 5678',sysdate,'900','支取','支取');insert into tradeInfo values('1010 3576 1212 1134',sysdate,'5000','存入','存入');/*-------------更新银行卡信息表中的现有余额-------------------*/update cardInfo set balance=(balance-900) where cardID='1010 3576 1234 5678';update cardInfo set balance=(balance+5000) where cardID='1010 3576 1212 1134';/*--------------交易信息表插入交易记录--------------------------*/insert into tradeInfo values('1010 3576 1234 5678',sysdate,'900','支取','支取');insert into tradeInfo values('1010 3576 1212 1134',sysdate,'5000','存入','存入');/*-------------更新银行卡信息表中的现有余额-------------------*/update cardInfo set balance=(balance-900) where cardID='1010 3576 1234 5678';update cardInfo set balance=(balance+5000) where cardID='1010 3576 1212 1134';/*--------------交易信息表插入交易记录--------------------------*/insert into tradeInfo values('1010 3576 1234 5678',sysdate,'2000','支取','支取');insert into tradeInfo values('1010 3576 1212 1134',sysdate,'500','存入','存入');/*-------------更新银行卡信息表中的现有余额-------------------*/update cardInfo set balance=(balance-2000) where cardID='1010 3576 1234 5678';update cardInfo set balance=(balance+500) where cardID='1010 3576 1212 1134';/*--------------交易信息表插入交易记录--------------------------*//*-------------更新银行卡信息表中的现有余额--报错-----------------*//*--------------交易信息表插入交易记录--------------------------*//*-------------更新银行卡信息表中的现有余额-------------------*//*--------检查测试数据是否正确---------*/SELECT * FROM cardInfo;SELECT * FROM tradeInfo;SELECT * FROM userInfo;--delete from tradeInfo;--delete from cardInfo;--delete from userInfo;commit;

**模拟常规业务**/*---------修改密码-----*/--1.张三（卡号为1010 3576 1234 5678）修改银行卡密码为123456--2.李四（卡号为1010 3576 1212 1134）修改银行卡密码为123123update 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;/*--------统计银行的资金流通余额和盈利结算------------------------------*/--统计说明:存款代表资金流入,取款代表资金.假定存款利率为千分之3,贷款利率为千分之8/*--单一货币RMB--*/-- 统计银行资金流通余额和盈利结算银行资金流通余额=总存入金额-总支取金额 盈利结算=总支取金额 * 0.008 – 总存入金额 * 0.003-- 银行资金流通余额:select * from tradeinfo;select sum(t.trademoney) summoney from tradeinfo t where t.tradetype='存入';select sum(t.trademoney) summoney from tradeinfo t where t.tradetype='支取'; select (select sum(t.trademoney) summoney from tradeinfo t where t.tradetype='存入')-(select sum(t.trademoney) summoney from tradeinfo t where t.tradetype='支取')银行资金流通余额 from dual;-- 盈利结算=总支取金额 * 0.008 – 总存入金额 * 0.003select sum(t.trademoney) from tradeInfo t where t.tradetype='支取';select sum(t.trademoney) from tradeInfo t where t.tradetype='存入';declareinMoney number;outMoney number;beginselect sum(t.trademoney) into inMoney from tradeInfo t where t.tradetype='支取';select sum(t.trademoney) into outMoney from tradeInfo t where t.tradetype='存入';dbms_output.put_line('盈利结算:'||(inMoney*0.008-outMoney*0.003));end;/*--------查询本周开户的卡号,显示该卡相关信息-----------------*/select * from cardInfo where to_char(sysdate-opendate)<7;/*---------查询本月交易金额最高的卡号----------------------*/select max(t.trademoney) from tradeinfo t;select distinct(t.cardid) from tradeinfo t where t.trademoney=(select max(t.trademoney) from tradeinfo t);/*---------查询挂失帐号的客户信息---------------------*/select customerID from cardInfo where IsReportLoss=1;select * from userInfo where customerID=(select customerID from cardInfo where IsReportLoss=1);/*------催款提醒：例如某种业务的需要，每个月末，如果发现用户帐上余额少于200元，将致电催款。---*/select u.customername,u.telephone from userInfo u join cardInfo c on u.customerid=c.customerid where to_char(sysdate,'dd')>=28;

利用视图实现数据查询--1.创建视图：为了向客户显示信息友好,查询各表要求字段全为中文字段名。create view vw_userInfo as select u.customerid 用户编号,u.customername 姓名,u.pid 身份证号,u.telephone 联系电话,u.address 地址 from userInfo u;create view vw_deposit asselect d.savingid 存款类型ID,d.savingname 存款类型名称,d.descrip 存款描述 from deposit d;create view vw_cardInfo asselect c.cardid 卡号,c.savingid 存款类型ID,c.customerid 用户编号,c.pass 密码,c.curid 币种,c.opendate 开户日期,c.openmoney 开户金额,c.balance 余额,c.isreportloss 是否挂失 from cardInfo c;create view vw_tradeInfo asselect t.cardid 卡号,t.tradedate 交易日期,t.trademoney 交易金额, t.tradetype 交易类型,t.remark 备注 from Tradeinfo t;--使用视图select * from vw_userInfo;select * from vw_deposit;select * from vw_cardInfo;select * from vw_tradeInfo;--2.创建视图：查询银行卡信息create view v_cardID as select c.cardid,c.customerid,c.opendate,c.balance,c.isreportloss from cardInfo c;--使用视图select * from v_cardID;--3.创建视图：查看交易信息--交易信息表视图create view v_tradeInfo2 asselect t.cardid 卡号,t.tradedate 交易日期,t.trademoney 交易金额, t.tradetype 交易类型,t.remark 备注 from Tradeinfo t;--使用视图--4.根据客户登录名（采用实名制访问银行系统）查询该客户帐户信息的视图create view v_customer as select u.customername,c.* from cardInfo c join userInfo u on c.customerid=u.customerid;drop view v_customer; --使用视图select * from v_customer;

用存储过程实现业务处理select * from tradeinfo;select * from cardinfo;/*--1.取钱或存钱的存储过程*/--调用存储过程取钱或存钱 张三取300，--现实中的取款机依靠读卡器读出张三的卡号,这里根据张三的名字查出考号来模拟create or replace procedure saveMoney(vName in char,vMoney in number,vtradeType in char) isvCard char(30);vbalance number;beginselect c.cardid into vCard from cardInfo c join userInfo u on c.customerid=u.customerid where u.customername=vName;select c.balance into vbalance from cardInfo c where c.cardid=vCard;if vtradeType='支取' thenif vbalance>=vMoney thenupdate cardInfo set balance=vBalance-vMoney where cardId=vCard;dbms_output.put_line('更新后余额:'||(vBalance+vMoney));insert into tradeInfo values(vCard,sysdate,vMoney,vtradeType,'');else dbms_output.put_line('余额不足!');end if;elseupdate cardInfo set balance=vBalance+vMoney where cardId=vCard;dbms_output.put_line('更新后余额:'||(vBalance+vMoney));insert into tradeInfo values(vCard,sysdate,vMoney,vtradeType,'');end if;commit;end saveMoney; declarebeginsaveMoney('马云','5000','存入');end;declare beginsavemoney('张三',100,'支取');end;--调用存储过程，李四存500declare beginsavemoney('李四',1000,'存入');end;/*--2.产生随机卡号的存储过程(dbms_random包来实现) --*/create or replace procedure randomNum(vNum out char)iscardID char(16);begincardID := round((dbms_random.value(10000000,99999999)),0);vNum := '1010 3576'||' '|| substr(cardID,1,4) ||' '|| substr(cardID,5,8);end;declarerandomCard char(23);beginrandomNum(randomCard);dbms_output.put_line(randomCard);end;/*--3.开户的存储过程--*/select * from userInfo;--客户编号用序列生产create sequence userInfo_seqincrement by 1start with 3maxvalue 99999nocachenocycle;-- 下一个序列select userInfo_seq.nextval from dual;-- 当前序列select userInfo_seq.currval from dual;create or replace procedure openAccount(vCustomername in varchar2,vPid in varchar2,vTelephone in varchar,vAddress in varchar2,vSavingID in number,vOpenMoney in number)israndomCard char(25);beginrandomNum(randomCard);dbms_output.put_line(randomCard);insert into userInfo(customerID,Customername,Pid,Telephone,Address) values(userInfo_seq.Nextval,vCustomername,vPid,vTelephone,vAddress);insert into Cardinfo(cardID,Savingid,Customerid,Openmoney,Balance,Isreportloss) values(randomCard,vSavingID,userInfo_seq.Currval,vOpenMoney,vOpenMoney,0);end;--调用存储过程重新开户-declarebeginopenAccount('王健林','420984177702564562','12547895623','杭州','2','10000');end;select * from cardInfo;select * from userinfo;select * from tradeinfo;/*--4.输入页数和每页显示的记录数，实现分页显示*/--第pageNo页：(pageNo – 1) * pageSize，pageSizeselect * from tradeinfo;select t.*,rownum v from (select * from tradeinfo) t;select * from (select t.*,rownum v from (select * from tradeinfo) t) where v>=0 and v<3;create or replace procedure vpage(pageNo in number,pageSize in number)iscursor vrows is select * from (select t.*,rownum v from (select * from tradeinfo) t)where v>(pageNo-1)*pageSize and v<=pageNO*pageSize;vrow vrows%Rowtype;beginopen vrows;loopfetch vrows into vrow; exit when vrows%notfound;dbms_output.put_line('卡号:'||vrow.cardID||'交易时间:'||vrow.tradeDate||'交易金额:'||vrow.tradeMoney||'交易类型:'||vrow.tradeType||'备注:'||vrow.remark);end loop;close vrows;end;--调用declarebeginvpage(1,4);end;/*---- 5.打印对账单 ----*/create or replace procedure getTradeInfoByID(vCardID in char)iscursor vrows is (select * from tradeinfo t where t.cardid=vCardID);vrow tradeinfo%Rowtype;beginopen vrows;loopfetch vrows into vrow;exit when vrows%notfound;dbms_output.put_line('卡号:'||vrow.cardID||'交易时间:'||vrow.tradedate||'交易金额:'||vrow.tradeMoney||'交易类型:'||vrow.tradeType);end loop;close vrows;end;--调用declarebegingetTradeInfoByID('1010 3576 1234 5678');end;/*--6.查询、统计在指定时间段内没有发生交易的账户信息*/-- 时间段内没有交的账户SQL语句select u.customerid,u.customername,u.pid,u.telephone,u.address from tradeInfo t join cardInfo c on t.cardid=c.cardid join userInfo u on u.customerid=c.customerid where t.cardid not in (select distinct t.cardid from tradeInfo t where t.tradedate between to_date('-4-1','yyyy-mm-dd') and to_date('-4-3','yyyy-mm-dd'));create or replace procedure timeNoInfo(vTime1 in date :=sysdate,vTime2 in date :=sysdate)iscursor vrows is(select distinct u.customerid,u.customername,u.pid,u.telephone,u.address from tradeInfo t join cardInfo c on t.cardid=c.cardid join userInfo u on u.customerid=c.customerid where t.cardid not in (select distinct t.cardid from tradeInfo t where t.tradedate between vTime1 and vTime2));vrow vrows%rowtype;beginopen vrows ;dbms_output.put_line(vTime1||'到'||vTime2||'没有交易的用户:');loopfetch vrows into vrow;exit when vrows%notfound;dbms_output.put_line('用户姓名:'||vrow.customername||'用户编号:'||vrow.customerID||'用户身份号:'||vrow.Pid||'用户电话:'||vrow.telephone||'用户地址:'||vrow.address);end loop;close vrows; end;--调用存储过程declarevTime1 date;vTime2 date;beginvTime1 := to_date('-4-1','yyyy-mm-dd');vTime2 :=to_date('-4-3','yyyy-mm-dd') ;timeNoInfo(vTime1,vTime2);end;/*--7.统计银行卡交易量和交易额*/create or replace procedure countTradeInfo(vNum1 out number,vAmount1 out number,vNum2 out number,vAmount2 out number,vDate1 date:=trunc(sysdate,'year'),vDate2 date:= sysdate,vAddress varchar:=null)isbeginif vAddress is null thenselect count(t.trademoney),sum(t.trademoney) into vNum1,vAmount1 from tradeinfo t where t.tradedate between vDate1 and vDate2 and t.tradetype='存入'; select count(t.trademoney),sum(t.trademoney) into vNum2,vAmount2 from tradeinfo t where t.tradedate between vDate1 and vDate2 and t.tradetype='支取';else select count(t.trademoney) ,sum(t.trademoney) into vNum1,vAmount1 from tradeInfo t join cardInfo c on t.cardid=c.cardid join userInfo u on c.customerid=u.customeridwhere t.tradedate between vDate1 and vDate2 and t.tradetype='存入' and u.address like vAddress;select count(t.trademoney),sum(t.trademoney) into vNum2,vAmount2 from tradeInfo t join cardInfo c on t.cardid=c.cardid join userInfo u on c.customerid=u.customeridwhere t.tradedate between vDate1 and vDate2 and t.tradetype='支取' and u.address like vAddress;end if;end;--调用declarevNum1 number;vAmount1 number;vNum2 number;vAmount2 number;vDate1 date;vDate2 date;beginvDate1 := to_date('-2-1','yyyy-mm-dd');vDate2 :=to_date('-6-1','yyyy-mm-dd') ;countTradeInfo(vNum1,vAmount1,vNum2,vAmount2,vDate1,vDate2);dbms_output.put_line('起始日期:'||to_char(vDate1,'yyyy-mm-dd')||'截止日期:'||to_char(vDate2,'yyyy-mm-dd'));dbms_output.put_line('存入笔数:'||vNum1||'存入金额:'||vAmount1);dbms_output.put_line('支取笔数:'||vNum2||'支取金额:'||vAmount2);end;