数据库课程设计 银行存取款管理
银行储蓄业务数据库分析
一、系统功能简介
本系统是银行用户的存取款系统。主要功能是管理各个用户存取款的相关数据。
二、本系统由三张表组成，具体如下：
表名属性(字段)名
储户表账号、身份证号、姓名、性别、身高、地址、存款余额
存款单表存款单号、金额、存款方式、账号、存款日期
取款单表取款单号、金额、取款方式、账号、取款日期
系统表基本信息
三、表间关系如下：
一个存款单或取款单属于一个用户，一个用户可以拥有多次存款单或取款单。用户通过存款操作与存款单建立联系，通过取款操作与取款单建立联系。
ER模型如下图：
四、数据准备
前期准备工作：
以system账户登录，创建新账户
create user dy identified by lyjn;
grant dba to dy;
connect dy/dy@orcl;
各个表的具体信息和创建表的代码如下：
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);
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 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));
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 drawslip
在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', 3422101, '刘忠田', '男',1.72 ,'北京市朝阳区', 345);
insert into luser (ACNUM, ID, NAME, SEX,HEIGHT, ADDRESS, BALANCE) values ('00002', 3422102, '周正义', '男',1.78, '天津市宝坻区', 123);
insert into luser (ACNUM, ID, NAME, SEX,HEIGHT, ADDRESS, BALANCE) values ('00003', 3422103, '陈光标', '男',1.80, '南京市新街口', 333);
insert into luser (ACNUM, ID, NAME, SEX,HEIGHT, ADDRESS, BALANCE) values ('00004', 3422104, '张茵', '女',1.65, '上海市黄浦区', 234);
insert into luser (ACNUM, ID, NAME, SEX,HEIGHT, ADDRESS, BALANCE) values ('00005', 3422105, '陈丽华', '女',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,'00001', 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'), '整存零取');
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, 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,'00004', 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'), '通知存款'); 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, 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-09-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, 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 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 TRIGGER OperatingDps
AFTER INSERT ON depositslip
FOR EACH ROW
DECLARE
n_ac VARCHAR2(5);
n_mo INT;
BEGIN
n_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 DELETE ON depositslip
FOR EACH ROW
DECLARE
o_ac VARCHAR2(5);
o_mo INT;
BEGIN
o_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 EACH ROW
DECLARE
n_ac VARCHAR2(5);
n_mo INT;
BEGIN
n_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 ROW
DECLARE
o_ac VARCHAR2(5);
o_mo INT;
BEGIN
o_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 ROW
DECLARE
n_ac VARCHAR2(5);
n_mo INT;
n_ba INT;
BEGIN
n_ac:=:new.acnum;
n_mo:=:new.money;
select balance into n_ba from luser where acnum=n_ac;
if n_mo>n_ba then
RAISE_APPLICATION_ERROR(-20000, '存款余额不足！请重新插入存款！'); end if;
END OperatingDw3;
测试(以刘忠田为例)：
查看刘忠田的余额数
select name,balance from luser where name='刘忠田'
1、插入存款时
插入一条存款记录
insert into depositslip (DPS_NUM, ACNUM, MONEY, DPS_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, 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 (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 varchar2) as
smoney 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 name;
create table new2 as select * from luser inner join new1 on https://www.wendangku.net/doc/2013185133.html,=new1.姓名;
存储过程
create or replace procedure luinf
is
cursor mycur is
select * from new2 where new2.balance>new2.总数;
v_record new2%rowtype;)
begin
if mycur%isopen=false then
open mycur;
end if;
DBMS_OUTPUT.PUT_LINE(rpad('账号',15,'-')||rpad('身份证',15,'-')||rpad('姓名',15,'-')||rpad('性别',15,'-')||
rpad('余额',15,'-')||'取款总数');
loop
fetch mycur into v_record;
exit when mycur%notfound;
DBMS_OUTPUT.PUT_LINE(rpad(v_record.acnum,15,'-')||rpad(v_https://www.wendangku.net/doc/2013185133.html,
,15,'-')||rpad(v_record.id,15,'-')||rpad
(v_record.sex,15,'-')||rpad(v_record.balance,15,'-')||v_record.总数);
end loop;
close mycur;
end luinf;
十、查询
1、查询余额最多的人共取过多少次款，共取了多少钱
select count(*),sum(money) from luser natural join drawslip where name
=(select name from luser where balance=(select max(balance) from luser)) 2、查询存款余额大于200（亿）的储户，男女分别有多少人
select sex,count(*)from luser where balance>200 group by sex
3、查询哪个储户的某次存款期限满16个月，显示姓名、当次存款金额和性别select name,money,sex from luser natural join depositslip where extract(month from sysdate)-extract(month from dps_time)>4
4、查询哪些储户的取款总数大于100
select name 姓名,sum(money) 取款数, '亿' 单位from luser inner join drawslip on luser.acnum=drawslip.acnum group by name
having sum(money)>100
5、查询存款时间长度超过4个月的存款信息
select name 姓名,dps_num 存款单号,dps_time 存款日期,extract(month from sysdate)-extract(month from dps_time) 存款时间长度,'月' 单位from luser natural join depositslip where balance>400 and extract(month from sysdate)-extract(month from dps_time)>=3