东北大学数据库实验报告(3)

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; endif; commit;

end checkcharge;

第三部分【冲正】 交易描述

冲正交易为缴费交易的逆向操作，但不能简单的把原记录删除，应该在缴费表中增加一条相当于原记录负值的记录，同时应收费用表中缴费操作产生的数据恢复为未缴费的初始状态。

判断是否能找到原银行流水，客户号，实收金额的收费记录，没找到，返回冲正错误的提示信息。如果找到修改应收费用表中的应收违约金，附加费用1，附加费用2，实收违约金，收费标志几个字段，使这几个字段的值恢复为未缴费前的状态；用户交费表中增加一条记录，其中操作类型为“冲正”，银行流水改为银行交费时产生的原流水号。

createorreplaceprocedure checkpay(banktransfer_id innumber,resultoutvarchar2,balance outnumber)is num number(4); Terror varchar2(20); Tclientno number(4); Tpayday date;

10

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

selectcount(banktransfer_id)into num from chargelog where banktransferid=banktransfer_id ; if num=0then

Terror:='冲正错误'; result:=Terror; endif;

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; insertinto paylog

(id,clientno,paymoney,payday,paytype)values(seq_pay.nextval,Tclientno,-Trbalance,sysdate,'1');

update monthlyneedpay set paystatus=0where

to_char(payday,'YYYY-MM-DD')=to_char(Tpayday,'YYYY-MM-DD')and clientno=Tclientno; result:='冲正成功'; balance:=Trbalance;

end checkpay;

第四部分【对总账】

11

交易描述

每天凌晨银行会发送所有前一天的总交易金额，总交易笔数，及所有交易金额等明细信息，供企业方核对。银行传来的信息包括：银行代码，总笔数，总金额，对帐日期。

根据银行代码，对帐日期在相关表中查找所有满足当前银行，日期的缴费记录笔数和金额，同银行传来数据做比较，如果总笔数，总金额都相同，可以认为此次对总账成功，否则调用对明细账模块。其中冲正交易和被冲正交易的笔数和金额不计入在内。不需要返回给银行对总账是否成功的信息。 createorreplaceprocedure CheckTotalProcedure(bank_id innumber,resultoutvarchar2)is

ourtotalcount number(4); ourtotalmoney number(7,2); banktotalcount number(4); banktotalmoney number(7,2); begin

selectcount(*),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');

selectcount(*),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

insertinto checktotal values(seq_checktotal.nextval, bank_id, banktotalcount, banktotalmoney, ourtotalcount, ourtotalmoney,sysdate,'1'); result:='true'; else

insertinto checktotal values(seq_checktotal.nextval, bank_id, banktotalcount, banktotalmoney, ourtotalcount, ourtotalmoney,sysdate,'0'); result:='false'; endif; commit;

12

end CheckTotalProcedure;

第五部分【对明细账】 交易描述

总账不平需要逐笔核对明细账。判断账务不平的原因，并记录到对账异常表中，由其他应用程序来处理。 根据银行传来的银行代码，银行交易流水号，交易日期时间，客户号，交费金额等信息，在业务系统表中查找相应的记录，对账不平的可能性有企业方无此流水号的缴费信息，银行无此流水号的缴费信息，金额不等，把所有对账不平的信息及原因记录到对账异常表。

createorreplaceprocedure checkdetailmoney(bank_id innumber,resultoutvarchar2)is

ourtotalcount number(4); ourtotalmoney number(7,2); banktotalcount number(4); banktotalmoney number(7,2); cursor recordcursor is

select*from banktransferrecord

where to_char(transferdate,'yyyy-mm-dd')=to_char(sysdate-1,'yyyy-mm-dd') and bankid=bank_id;

Cursor filecursor is

Select*from banktransferfile

where 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

selectcount(*),sum(b.transfermoney)

13

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');

selectcount(*),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

insertinto checktotal values(seq_checktotal.nextval, bank_id, banktotalcount, banktotalmoney, ourtotalcount, ourtotalmoney,sysdate,'1'); result:='true'; else

insertinto checktotal values(seq_checktotal.nextval, bank_id, banktotalcount, banktotalmoney, ourtotalcount, ourtotalmoney,sysdate,'0'); result:='false'; open recordcursor;

fetch recordcursor into rc; while recordcursor% …… 此处隐藏：3573字，全部文档内容请下载后查看。喜欢就下载吧 ……