东北大学数据库实验报告.docx

课程编号：B080109004数据库应用程序设计实践报告姓名Xx学号20班级软件1301指导教师毛克明开设学期2015-2016第二学期开设时间第1周第3周报告日期2015-3-23评定成绩评定人评定日期东北大学软件学院1关系数据库设计以下三个表：客户信息表: 客户号 (主码) NUMBER(4) 客户姓名 VARCHAR2(20) 客户类型 VARCHAR2(20) 地址 VARCHAR2(20) 余额 NUMBER(7,2)计费设备表 计费设备号(主码) NUMBER(4) 客户号 (外码)NUMBER(4) 设备类别（01，02）Characters(1)应收费用表 年月 Date 计费设备号(外码)NUMBER(4) 基本费用 NUMBER(7,2) 附加费用1 NUMBER(7,2) 附加费用2 NUMBER(7,2) 应收违约金 NUMBER(7,2) 实收违约金 NUMBER(7,2) 减免违约金 NUMBER(7,2) 收费标志（0未交费，1已交费）Characters(1)第一部分：1指出你所设计表的各种键值，在选择时不要考虑性能问题。1) 指出每张表是否存在主码，若存在，请指出具体的主码，并说明原因。CLIENT主码:CLIENTNODEVICE主码:DEVICENOMONTHLYNEDDPAY主码:ID 这些能够作为主码的字段的数据都是唯一的，因此能够进行唯一性标识，能够作为主码来使用。2) 指出每张表是否存在备用码（除了主码之外的所有候选码），若存在，请指出所有的备用码，并说明原因。Client和Device表不存在候选码，Montthlyneedpay 中的DeviceNo和logdate 可以作为一个候选码。 3) 指出各表中存在的外码和完整性约束，并说明原因。Client表中没有外码，但是clientno不能为空，因为它是主码。DEVICE:外码是CLIENTNO，Deviceno不能为null，因为它是主码 MONTHLYNEDDPAY:外码有DEVICENO和CLIENTNO。ID不能为空2 列出各表所有列和各列的域（数据类型和格式），并说明理由。CLIENT: CLIENTNO是主码DEVICE: DEVICENO是主码MONTHLYNEDDPAY: ID是主码第二部分：写出如下SQL语句：1 用DDL语言中的CREATE TABLE语句创建以上三张表，并确定指定了表的主码和备用码；客户信息表:create table CLIENT( CLIENTNO NUMBER(4) not null, CLIENTNAME VARCHAR2(20), ADDRESS VARCHAR2(20), BALANCE VARCHAR2(20)alter table CLIENT add constraint PK_CLIENT_CLIENTNO primary key (CLIENTNO) using index 计费设备表: create table DEVICE( DEVICENO NUMBER(4) not null, CLIENTNO NUMBER(4), TYPE CHAR(1) alter table DEVICE add constraint PK_DEVICE_DEVICENO primary key (DEVICENO) using index 应收费用表: create table MONTHLYNEEDPAY( ID NUMBER(10) not null, DEVICENO NUMBER(4), CLIENTNO NUMBER(4), LOGDATE DATE, SFROM NUMBER(10), STO NUMBER(10), BASICFEE NUMBER(7,2), ADDFEE1 NUMBER(7,2), ADDFEE2 NUMBER(7,2), LATEFEE NUMBER(7,2), NEEDPAY NUMBER(7,2), ACTUALPAY NUMBER(7,2), PAYDAY DATE, PAYSTATUS CHAR(1) alter table MONTHLYNEEDPAY add constraint PK_MONTHLYNEEDPAY_ID primary key (ID) using index2 利用INSERT语句向客户信息表中插入1条客户记录； insert into client(clientno,clientname,address,balance) values(1,张三,沈阳市和平区东北大学,123.09);3 利用INSERT语句向计费设备表中为该客户插入2条设备记录。Insert into device(deviceno,clientno,type) values(1,1001,1);Inser into device(deviceno,clientno,type) values(2,1002,1); 4 利用INSERT语句向应收费用表中为该客户插入2个月份的应收费信息。Insert into monthlyneedpay (id,deviceno,clientno,logdate,sfrom,sto,basicfee,addfee1,addfee2,latefee,needpay,actualpay,payday,paystatus)values(1,1,1001,to_date(2015-1-31,YYYY-MM-DD),120,150,10,1,2,0,14.7,0,to_date(2015-2-5,yyyy-mm-dd,),0)Insert into monthlyneedpay (id,deviceno,clientno,logdate,sfrom,sto,basicfee,addfee1,addfee2,latefee,needpay,actualpay,payday,paystatus)values(2,2,1001,to_date(2015-2-28,YYYY-MM-DD)150,180,10,1,2,0,14.7,0,to_date(2015-3-5,yyyy-mm-dd,),0)5 在不考虑附加费和违约金的情况下，给定一个客户号，查询该客户号下所有设备累计应收基本费用；Select sum(needpay)+sum(addfee1)+sum(addfee2)+sum(latefee) from monthlyneedpay where clientno=1001; 第三部分：（理解关系运算）1 已知关系表r和s如下：给出差运算r-s和s-r的结果；表 r ABCabcdafcbd表 s DEFbgadafR-SABCabccbdS-RDEFbga2 描述下面查询的结果，如果将UNION用EXCEPT替代，又会有什么样的查询结果？ ( SELECT A FROM r, s WHERE r.a = s.d ) UNION ( SELECT A FROM r, s WHERE r.c = s.d ); UNION 这条语句是查询数据库中r表的a字段的值和s表中的d字段的值，r表中c字段的值和s表中d字段的值相等的部分，取出相等的部分后，相同的结果只保留一个。将union换位EXPECT后 得到的结果是只存在表a中和d相等的部分，并且去掉重复行。2SQL请写出针对以下问题的SQL语句（每一问必需用一条SQL语句实现，但该SQL语句可以包含子查询）。1. 查询姓张的所有客户信息Select * from clinet where clientname like 张%;2. 查询客户号1001的客户拥有的计费设备个数。Select count(*) from device where clientno=1001;3. 计算客户号1001在2016年1月产生的附加费用1和附加费用2；Select addfee1,addfee2 from monthlyneedpay where to_char(logdate,yyyy)=2016 and to_char(logdate,mm)=1;4. 查询客户号1001在2016年的历史缴费记录；Select * from paylog where clientno=1001 and to_char(payday,yyyyy)=2016;5. 更新客户号1001在2016年1月份的收费标识为1；Update monthlyneedpay set paystatus=1 where to_char(payday,yyyy)=2016 andto_char(payday,mm)=1;6. 查询应收费用表，先按照客户号升序排序，再按照年份排序降序排序。Select * from monthlyneedpay order by clientno asc,payday desc;3Advanced SQL在该练习中，我们根据银行代收费系统的需求，完成以下高级SQL语句的编写：1. 查询前一年所有客户的欠费记录，按照客户编号升序排列。Select clientno,needpay,payday from monthlyneedpay where paystatus=0 and to_char(payday,yyyy)=2015 order by clientno asc;2. 查询当前年份欠费记录超过5条以上的用户。select*from(selectsum(casePAYSTATUSwhen0then1else0end)asQUANTITYfrommonthlyneedpaywhereto_char(LOGDATE,yyyy)=2016groupbyCLIENTNO)whereQUANTITY=5; 3计算客户号1001的客户，其名下所有设备的应收基本费用之和，附加费用1之和，附加费用2之和。Select count(needpay),count(addfee1),count(addfee2) from monthlyneedpay where clientno=1001;4.计算客户号1001在2016年1月份，计费设备号100的应收违约金。Select latefee from monthlyneedpay where clientno=1001 and deviceno=100 and to_char(payday,yyyy)=2016 and to_char(payday,mm)=1;5.计算银行代号为19的银行在20160130产生的缴费总次数和总金额，冲账的记录不记录总次数和总金额中。 select banktotalcount,banktotalmoney from checktotal where bank_id=19 and to_char(checkdate,yyyy)=2016 and to_char(checkdate,mm)=1 and to_char(checkdate,dd)=30;4Programming with Transactions and Procedure Process 编写存储过程完成以下问题(如果不熟悉存储过程的编写，也可以顺序执行多条SQL语句来实现功能需求)：第一部分【查询】：交易描述： 判断客户号是否存在，然后根据客户号取得客户姓名，地址，应收费用。其中应收费用的计算：该用户下所有计费设备，计算所有计费设备未交费的所有月份应收费用的和。计费设备应收费用的计算：根据计费设备号取得基本费用，附加费用1，附加费用2，应收违约金,实收违约金 ,减免违约金后，应收费用=基本费用+附加费用1+附加费用2+应收违约金-减免违约金，依次获取该计费设备收费标志为0的所有月份应收费用的和。修改应收费用表中的应收违约金，附加费用1，附加费用2几个字段，避免执行缴费操作后的重复计算。获取的客户姓名、地址、应收费用等数据返回。create or replace procedure checkclient(client_no in number,error out varchar2, outclientnanme out varchar2,outaddress out varchar2,outneedpay out number) isTclientnanme varchar2(20);Taddress varchar2(20);Tneedpay number(7,2);clientnum number(4);Taddfee1 number(7,2);Taddfee2 number(7,2);Terror varchar2(20);Tlatefee number(7,2);begin select count(*) into clientnum from client where clientno=client_no;if clientnum=0 then Terror:=没有此客户; error:=Terror; elseselect clientname,address into Tclientnanme,Taddress from client where clientno=client_no;select sum(needpay),sum(addfee1), sum(addfee2),sum(latefee) into Tneedpay ,Taddfee1,Taddfee2,Tlatefee from monthlyneedpay where clientno=client_no and paystatus=0;outclientnanme:=Tclientnanme;outaddress:=Taddress;outneedpay:=Tneedpay+Taddfee1+Taddfee2+Tlatefee;end if;end checkclient;第二部分【缴费】 交易描述： 判断交费金额是否正确，交费金额应该为基本费用，附加费用1，附加费用2，应收违约金的和再减去减免违约金之后的值。如果交费金额不正确，返回交费金额错误的提示信息。缴费金额正确，在应收费用表表中修改收费标志，实收违约金，其中实收违约金应该为应收违约金减去减免违约金。同时用户交费表中增加一条记录，其中操作类型为“存款”。如果客户存在违约金，应计算违约金（这部分可以在其他模块完成的情况下做，具体计算方法见附录） 返回包括姓名，金额，地址，明细费用等详细信息的发票数据。create or replace procedure checkcharge(client_no in number,LName out varchar2, balance out number,address out varchar2,error out varchar2 ) isTName varchar2(20);Taddress varchar2(20);Tpay number(7,2);Tclientnum number(4);Terror varchar2(20);Tneedpay number(7,2);Taddfee1 number(7,2);Taddfee2 number(7,2);Tlatefee number(7,2);Tcharge number(7,2);len number;TID number(4);Tbasicfee number(7,2);Cursor myCur is select * from monthlyneedpay where clientno=client_no; Tend Date;begin select count(clientno) into Tclientnum from client where clientno=client_no; if Tclientnum=0 then Terror:=不存在此用户; error:=Terror; end if; for cur_result in myCur loop begin Tend:=cur_result.logdate; TID:=cur_result.id; len:=trunc(sysdate)-trunc(Tend)-10; update monthlyneedpay set latefee=len*0.001 where id=TID and clientno=client_no; end; end loop; select sum(paymoney) into Tpay from paylog where clientno=client_no; select sum(needpay),sum(basicfee),sum(addfee1),sum(addfee2),sum(latefee) into Tneedpay,Tbasicfee,Taddfee1,Taddfee2,Tlatefee from monthlyneedpay where clientno=client_no; Tcharge:=Tneedpay+Tbasicfee+Taddfee1+Taddfee2+Tlatefee; if Tpay!=Tcharge then Terror:=金额错误; error:=Terror; else update monthlyneedpay set paystatus=1 where clientno=client_no; select clientname,address into Tname,Taddress from client where client_no=clientno; LName:=TName; address:=Taddress; balance:=Tcharge; end if; commit;end checkcharge;第三部分【冲正】 交易描述 冲正交易为缴费交易的逆向操作，但不能简单的把原记录删除，应该在缴费表中增加一条相当于原记录负值的记录，同时应收费用表中缴费操作产生的数据恢复为未缴费的初始状态。 判断是否能找到原银行流水，客户号，实收金额的收费记录，没找到，返回冲正错误的提示信息。如果找到修改应收费用表中的应收违约金，附加费用1，附加费用2，实收违约金，收费标志几个字段，使这几个字段的值恢复为未缴费前的状态；用户交费表中增加一条记录，其中操作类型为“冲正”，银行流水改为银行交费时产生的原流水号。create or replace procedure checkpay(banktransfer_id in number,result out varchar2,balance out number) isnum number(4);Terror varchar2(20);Tclientno number(4);Tpayday date;Tneedpay number(7,2);Taddfee1 number(7,2);Taddfee2 number(7,2);Tlatefee number(7,2);Trbalance number(7,2);Tbalance number(7,2);begin select count(banktransfer_id) into num from chargelog where banktransferid=banktransfer_id ; if num=0 then Terror:=冲正错误; result:=Terror; end if; select chargedate,clientno into Tpayday,Tclientno from chargelog where banktransferid=banktransfer_id; dbms_output.put_line(Tpayday); dbms_output.put_line(Tclientno); select needpay,addfee1,addfee2,latefee into Tneedpay,Taddfee1,Taddfee2,Tlatefee from monthlyneedpay where to_char(payday,YYYY-MM-DD)=to_char(Tpayday,YYYY-MM-DD)and clientno=Tclientno; select balance into Tbalance from client where clientno=Tclientno; Tbalance:=Tbalance+Tneedpay+Taddfee1+Taddfee2+Tlatefee; Trbalance:=Tneedpay+Taddfee1+Taddfee2+Tlatefee; update client set balance=Tbalance where clientno=Tclientno; insert into paylog (id,clientno,paymoney,payday,paytype)values(seq_pay.nextval,Tclientno,-Trbalance,sysdate,1); update monthlyneedpay set paystatus=0 where to_char(payday,YYYY-MM-DD)=to_char(Tpayday,YYYY-MM-DD) and clientno=Tclientno; result:=冲正成功; balance:=Trbalance; end checkpay;第四部分【对总账】 交易描述 每天凌晨银行会发送所有前一天的总交易金额，总交易笔数，及所有交易金额等明细信息，供企业方核对。银行传来的信息包括：银行代码，总笔数，总金额，对帐日期。 根据银行代码，对帐日期在相关表中查找所有满足当前银行，日期的缴费记录笔数和金额，同银行传来数据做比较，如果总笔数，总金额都相同，可以认为此次对总账成功，否则调用对明细账模块。其中冲正交易和被冲正交易的笔数和金额不计入在内。不需要返回给银行对总账是否成功的信息。create or replace procedure CheckTotalProcedure(bank_id in number, result out varchar2) isourtotalcount number(4);ourtotalmoney number(7,2);banktotalcount number(4);banktotalmoney number(7,2);begin select count(*), sum(b.transfermoney) into ourtotalcount, ourtotalmoney from banktransferrecord b where b.bankid = bank_id and TO_CHAR(b.transferdate,YYYY-MM) = TO_CHAR(sysdate-1, YYYY-MM); select count(*), sum(b.transfermoney) into banktotalcount, banktotalmoney from banktransferfile b where b.bankid = bank_id and TO_CHAR(b.transferdate,YYYY-MM) = TO_CHAR(sysdate-1, YYYY-MM); if ourtotalcount = banktotalcount and ourtotalmoney = banktotalmoney then insert into checktotal values(seq_checktotal.nextval, bank_id, banktotalcount, banktotalmoney, ourtotalcount, ourtotalmoney, sysdate, 1); result := true; else insert into checktotal values(seq_checktotal.nextval, bank_id, banktotalcount, banktotalmoney, ourtotalcount, ourtotalmoney, sysdate, 0); result := false; end if; commit; end CheckTotalProcedure;第五部分【对明细账】 交易描述 总账不平需要逐笔核对明细账。判断账务不平的原因，并记录到对账异常表中，由其他应用程序来处理。 根据银行传来的银行代码，银行交易流水号，交易日期时间，客户号，交费金额等信息，在业务系统表中查找相应的记录，对账不平的可能性有企业方无此流水号的缴费信息，银行无此流水号的缴费信息，金额不等，把所有对账不平的信息及原因记录到对账异常表。create or replace procedure checkdetailmoney(bank_id in number, result out varchar2) isourtotalcount number(4);ourtotalmoney number(7,2);banktotalcount number(4);banktotalmoney number(7,2);cursor recordcursor isselect * from banktransferrecord where to_char(transferdate,yyyy-mm-dd)=to_char(sysdate-1,yyyy-mm-dd)and bankid=bank_id;Cursor filecursor isSelect *from banktransferfilewhere to_char(transferdate,yyyy-mm-dd)=to_char(sysdate-1,yyyy-mm-dd)and bankid=bank_id;rc recordcursor%rowtype;fc filecursor%rowtype;num number(4);filemoney number(7,2);begin select count(*), sum(b.transfermoney) into ourtotalcount, ourtotalmoney from banktransferrecord b where b.bankid = bank_id and TO_CHAR(b.transferdate,YYYY-MM-DD) = TO_CHAR(sysdate-1, YYYY-MM-DD); select count(*), sum(b.transfermoney) into banktotalcount, banktotalmoney from banktransferfile b where b.bankid = bank_id and TO_CHAR(b.transferdate,YYYY-MM-dd) = TO_CHAR(sysdate-1, YYYY-MM-dd); if ourtotalcount = banktotalcount and ourtotalmoney = banktotalmoney then insert into checktotal values(seq_checktotal.nextval, bank_id, banktotalcount, banktotalmoney, ourtotalcount, ourtotalmoney, sysdate, 1); result := true; else insert into checktotal values(seq_checktotal.nextval, bank_id, banktotalcount, banktotalmoney, ourtotalcount, ourtotalmoney, sysdate, 0); result := false; open recordcursor; fetch recordcursor into rc; while recordcursor%found loop select count(*) into num from banktransferfile b where rc.banktransferid=b.banktransferid; if num=0 then dbms_output.put_line(rc.clientno); dbms_output.put_line(rc.banktransferid); dbms_output.put_line(rc.transfermoney); insert into checkerrordetail val