数据库课程设计--银行存取款管理

1、银行储蓄业务数据库分析一、系统功能简介本系统是银行用户的存取款系统。主要功能是管理各个用户存取款的相关数据。二、本系统由三张表组成，具体如下：表名属性(字段)名储户表账号、身份证号、姓名、性别、身高、地址、存款余额存款单表存款单号、金额、存款方式、账号、存款日期取款单表取款单号、金额、取款方式、账号、取款日期系统表基本信息三、表间关系如下：一个存款单或取款单属于一个用户，一个用户可以拥有多次存款单或取款单。用户通过存款操作与存款单建立联系，通过取款操作与取款单建立联系。ER模型如下图：金额存款单号存款余额地址地址 身高性别储户姓名身份证号账号 存款单取款单金额存款方式取款单号金额取款方式存款取

2、款存款日期取款日期账号账号存款方式存款方式 ER图，如下：四、 数据准备前期准备工作：以system账户登录，创建新账户create user dy identified by lyjn;grant dba to dy;connect dy/dyorcl;各个表的具体信息和创建表的代码如下：储户表(luser)字段(属性)含义字段(属性)名字段(属性)类型账号acnumVARCHAR(5)身份证号idNUMBER姓名nameVARCHAR2(20)性别sexVARCHAR2(2)身高heightNUMBER(3,2)地址addressVARCHAR2(20)存款余额(亿)Balance(bil

3、lion)INT创建储户表的代码如下：CREATE TABLE luser(acnum VARCHAR2(5) NOT NULL ,id NUMBER NOT NULL ,name VARCHAR2(20) NOT NULL ,sex VARCHAR2(2) CHECK(sex IN (男,女) ) , height NUMBER(3,2) NOT NULL,address VARCHAR2(20) NOT NULL ,balance INT NOT NULL );ALTER TABLE luser添加主键约束ADD CONSTRAINT XPKluser PRIMARY KEY (acnum

4、);存款单表(depositslip)字段(属性)含义字段(属性)名字段(属性)类型存款单号dps_numNUMBER账号acnumVARCHAR2(5)存款时间dps_timeTIMESTAMP金额(亿)moneyINT存款方式dps_wayVARCHAR2(10)创建存款单表的代码如下：CREATE TABLE depositslip(dps_num NUMBER NOT NULL ,money INT CHECK(money=0) ,dps_way VARCHAR2(10) NOT NULL ,acnum VARCHAR2(5) NOT NULL ,dps_time TIMESTAMP

5、NULL );ALTER TABLE depositslip 添加主键约束ADD CONSTRAINT XPKdepositslip PRIMARY KEY (dps_num,acnum);ALTER TABLE depositslip在depositslip表中的acnum字段中添加外键约束ADD (CONSTRAINT R_5 FOREIGN KEY (acnum) REFERENCES luser(acnum);取款单表(drawslip)字段(属性)含义字段(属性)名字段(属性)类型取款单号dw_numNUMBER账号acnumVARCHAR2(5)取款时间dw_timeTIMESTA

6、MP金额(亿)MoneyINT取款方式dw_wayVARCHAR2(10)创建取款单表的代码如下：CREATE TABLE drawslip(dw_num NUMBER NOT NULL ,money INT CHECK(money=0),dw_way VARCHAR2(10) NOT NULL ,acnum VARCHAR2(5) NOT NULL ,dw_time TIMESTAMP NULL );ALTER TABLE drawslip 添加主键约束ADD CONSTRAINT XPKdrawslip PRIMARY KEY (dw_num,acnum);ALTER TABLE draw

7、slip 在drawslip表中的acnum字段中添加外键约束ADD (CONSTRAINT R_6 FOREIGN KEY (acnum) REFERENCES luser(acnum);五、插入(insert)数据1、插入储户表(luser)数据insert into luser (ACNUM, ID, NAME, SEX,HEIGHT, ADDRESS, BALANCE)values (00001, , 刘忠田, 男,1.72 ,北京市朝阳区, 345);insert into luser (ACNUM, ID, NAME, SEX,HEIGHT, ADDRESS, BALANCE)va

8、lues (00002, , 周正义, 男,1.78, 天津市宝坻区, 123);insert into luser (ACNUM, ID, NAME, SEX,HEIGHT, ADDRESS, BALANCE)values (00003, , 陈光标, 男,1.80, 南京市新街口, 333);insert into luser (ACNUM, ID, NAME, SEX,HEIGHT, ADDRESS, BALANCE)values (00004, , 张茵, 女,1.65, 上海市黄浦区, 234);insert into luser (ACNUM, ID, NAME, SEX,HEIG

9、HT, ADDRESS, BALANCE)values (00005, , 陈丽华, 女,1.70, 香港市旺角区, 111);2、插入存款单表(depositslip)数据insert into depositslip (DPS_NUM, ACNUM, MONEY, DPS_TIME, DPS_WAY)values (98001,00001, 120,to_date(08-08-2009,dd-mm-yyyy), 整存整取);insert into depositslip (DPS_NUM, ACNUM, MONEY, DPS_TIME, DPS_WAY)values (98002,0000

10、1, 300,to_date(10-08-2009,dd-mm-yyyy), 整存整取);insert into depositslip (DPS_NUM, ACNUM, MONEY, DPS_TIME, DPS_WAY)values (98003,00002, 100,to_date(12-08-2009,dd-mm-yyyy), 整存零取);insert into depositslip (DPS_NUM, ACNUM, MONEY, DPS_TIME, DPS_WAY)values (98004,00002, 150,to_date(12-09-2009,dd-mm-yyyy), 整存零

11、取);insert into depositslip (DPS_NUM, ACNUM, MONEY, DPS_TIME, DPS_WAY)values (98005,00003, 200,to_date(15-08-2009,dd-mm-yyyy), 零存整取);insert into depositslip (DPS_NUM, ACNUM, MONEY, DPS_TIME, DPS_WAY)values (98006,00003, 100,to_date(14-09-2009,dd-mm-yyyy), 零存整取);insert into depositslip (DPS_NUM, ACNUM

12、, MONEY, DPS_TIME, DPS_WAY)values (98007,00003, 80,to_date(23-09-2009,dd-mm-yyyy), 零存整取);insert into depositslip (DPS_NUM, ACNUM, MONEY, DPS_TIME, DPS_WAY)values (98008,00004, 80,to_date(23-08-2009,dd-mm-yyyy), 零存零取);insert into depositslip (DPS_NUM, ACNUM, MONEY, DPS_TIME, DPS_WAY)values (98009,000

13、04, 180,to_date(24-09-2009,dd-mm-yyyy), 零存零取);insert into depositslip (DPS_NUM, ACNUM, MONEY, DPS_TIME, DPS_WAY)values (98010,00005, 190,to_date(24-08-2009,dd-mm-yyyy), 通知存款);insert into depositslip (DPS_NUM, ACNUM, MONEY, DPS_TIME, DPS_WAY)values (98011,00005, 210,to_date(30-09-2009,dd-mm-yyyy), 通知

14、存款);3、插入取款单表(drawslip)数据insert into drawslip (DW_NUM, ACNUM, MONEY, DW_TIME, DW_WAY)values (52001,00001, 75,to_date(08-08-2010,dd-mm-yyyy), 柜台);insert into drawslip (DW_NUM, ACNUM, MONEY, DW_TIME, DW_WAY)values (52002,00002, 27,to_date(08-09-2010,dd-mm-yyyy), 取款机);insert into drawslip (DW_NUM, ACNUM

15、, MONEY, DW_TIME, DW_WAY)values (52003,00002,100,to_date(12-10-2010,dd-mm-yyyy), 柜台);insert into drawslip (DW_NUM, ACNUM, MONEY, DW_TIME, DW_WAY)values (52004,00003,47,to_date(15-08-2010,dd-mm-yyyy), 取款机);insert into drawslip (DW_NUM, ACNUM, MONEY, DW_TIME, DW_WAY)values (52005,00004,26,to_date(12-0

16、9-2010,dd-mm-yyyy), 取款机);insert into drawslip (DW_NUM, ACNUM, MONEY, DW_TIME, DW_WAY)values (52006,00005,100,to_date(23-09-2010,dd-mm-yyyy), 柜台);insert into drawslip (DW_NUM, ACNUM, MONEY, DW_TIME, DW_WAY)values (52007,00005,150,to_date(10-10-2010,dd-mm-yyyy), 柜台);insert into drawslip (DW_NUM, ACNUM

17、, MONEY, DW_TIME, DW_WAY)values (52008,00005,39,to_date(31-10-2010,dd-mm-yyyy), 取款机);七、索引建立索引遵循的规律：1.建立在where子句经常引用的列上，2.经常需要排序的列上，3.连接属性列上等在存款单表的存款单号字段和账号字段建立组合索引CREATE UNIQUE INDEX XPKdepositslip ON depositslip(dps_num ASC,acnum ASC);在存款单表的账号字段建立索引CREATE INDEX XIF1depositslip ON depositslip(acnum

18、ASC);在取款单表的取款单号字段和账号字段建立组合索引CREATE UNIQUE INDEX XPKdrawslip ON drawslip(dw_num ASC,acnum ASC);在取款单表的账号字段建立索引CREATE INDEX XIF1drawslip ON drawslip(acnum ASC);在储户表的账户字段上建立索引，方便查询CREATE UNIQUE INDEX XPKluser ON luser(acnum ASC);八、触发器1、插入存款，对应储户表的相应储户的余额(balance)增加插入记录包含的金额数(money)CREATE OR REPLACE TRIG

19、GER OperatingDps AFTER INSERT ON depositslip FOR EACH ROWDECLAREn_ac VARCHAR2(5);n_mo INT;BEGINn_ac:=:new.acnum;n_mo:=:new.money;update luser set balance=balance+n_mo where luser.acnum=n_ac;END OperatingDps;2、删除存款，对应储户表的相应储户的余额(balance)减去所删除记录的金额数(money)CREATE OR REPLACE TRIGGER OperatingDps2 AFTER

20、DELETE ON depositslip FOR EACH ROWDECLAREo_ac VARCHAR2(5);o_mo INT;BEGINo_ac:=:old.acnum;o_mo:=:old.money;update luser set balance=balance-o_mo where luser.acnum=o_ac;END OperatingDps2;3、插入取款，对应储户表的相应储户的余额(balance)减少插入记录包含的金额数(money)CREATE OR REPLACE TRIGGER OperatingDw AFTER INSERT ON drawslip FOR

21、EACH ROWDECLAREn_ac VARCHAR2(5);n_mo INT;BEGINn_ac:=:new.acnum;n_mo:=:new.money;update luser set balance=balance-n_mo where luser.acnum=n_ac;END OperatingDw;4、删除取款，对应储户表的相应储户的余额(balance)加上所删除记录的金额数(money)CREATE OR REPLACE TRIGGER OperatingDw2 AFTER DELETE ON drawslip FOR EACH ROWDECLAREo_ac VARCHAR2

22、(5);o_mo INT;BEGINo_ac:=:old.acnum;o_mo:=:old.money;update luser set balance=balance+o_mo where luser.acnum=o_ac;END OperatingDw2;5、插入的取款金额(money)大于对应用户的余额(balance)时，系统报错CREATE OR REPLACE TRIGGER OperatingDw3 AFTER insert ON drawslip FOR EACH ROWDECLAREn_ac VARCHAR2(5);n_mo INT;n_ba INT;BEGINn_ac:=:

23、new.acnum;n_mo:=:new.money;select balance into n_ba from luser where acnum=n_ac;if n_mon_ba thenRAISE_APPLICATION_ERROR(-20000, 存款余额不足！请重新插入存款！);end if;END OperatingDw3;测试(以刘忠田为例)：查看刘忠田的余额数select name,balance from luser where name=刘忠田1、插入存款时插入一条存款记录insert into depositslip (DPS_NUM, ACNUM, MONEY, DPS

24、_TIME, DPS_WAY)values (98012,00001, 80,to_date(08-09-2009,dd-mm-yyyy), 整存整取);查看插入后，刘忠田余额数的变化select name,balance from luser where name=刘忠田2、删除存款时删除一条存款记录delete from depositslip where dps_num=98012查看删除后，刘忠田余额数的变化select name,balance from luser where name=刘忠田3、插入取款时插入一条取款记录insert into drawslip (DW_NUM,

25、ACNUM, MONEY, DW_TIME, DW_WAY)values (52007,00001, 80,to_date(08-11-2010,dd-mm-yyyy), 柜台);查看插入取款后，刘忠田余额数的变化select name,balance from luser where name=刘忠田4、删除取款时删除一条取款记录delete from drawslip where dw_num=52007查看删除后，刘忠田余额数的变化select name,balance from luser where name=刘忠田5、插入的取款数大于余额数时insert into drawslip

26、 (DW_NUM, ACNUM, MONEY, DW_TIME, DW_WAY)values (52007,00001, 700,to_date(08-12-2010,dd-mm-yyyy), 柜台); 700大于刘忠田的余额数系统会报错：ORA-20000: 存款余额不足！请重新插入存款！ ORA-06512: 在 DY.OPERATINGDW3, line 10 ORA-04088: 触发器 DY.OPERATINGDW3 执行过程中出错 九、存储过程创建过程dpspro，查询某位储户共取过多少钱create or replace procedure dpspro(dname varcha

27、r2) assmoney int;begin select sum(money) into smoney from drawslip natural join luser where name=dname; DBMS_OUTPUT.PUT_LINE(smoney); end dpspro;创建存储过程luinf，查询余额大于取款总数储户的相关信息先创建两张表create table new1 as select name 姓名,sum(money) 总数 from luser inner join drawslip on luser.acnum=drawslip.acnum group by

28、name;create table new2 as select * from luser inner join new1 on =new1.姓名;存储过程 create or replace procedure luinfis cursor mycur isselect * from new2 where new2.balancenew2.总数;v_record new2%rowtype;)beginif mycur%isopen=false thenopen mycur;end if;DBMS_OUTPUT.PUT_LINE(rpad(账号,15,-)|rpad(身份证,15,-)|rpad(姓名,15,-)|rpad(性别,15,-)|rpad(余额,15,-)|取款总数);loopfetch mycur into v_record;exit when mycur%notfound;DBMS_OUTPUT.PUT_LINE(rpad(v_record.acnum,15,-)|rp