东北大学数据库应用程序设计实践报告

1、课程编号：B0数据库应用程序设计实践报告姓名学号班级指导教师开设学期2016- 20 17第一学期 开设时间开设时间第13周第15周 报告日期报告日期2016/12/16评定成绩评定人评定日期东北大学软件学院1问题定义银行代收费系统给电力公司开发的一套缴费系统，方便用户通过网银支付电费。主要的用例图：图1银行代收费系统用例图根据用例图得出主要的业务需求：（1）抄表系统管理员把抄表记录录入系统，抄表记录包括当前电表数、抄表日期、抄表人等 信息，根据抄表记录，系统自动计算每个计费设备当月的应收电费。每个计费设备有唯 一编号。（2）查询用户随时查询欠费金额。一个用户名下可能多个计费设备，查询欠费时，

2、将所有计 费设备欠费总和输出。需要考虑设备的余额问题。如果余额大于欠费，则欠费为0,更新余额，修改receivable 中flag标志。（3）缴费在当月电费清单生成完毕后，用户可进行电费缴纳，缴纳金额可是任意金额。系统 将缴费金额存入设备余额中，再次查询则欠费应该减少。（4）冲正用户在缴费过程中如果给其他用户缴费了，在当日0点前可以冲正，即把钱收回，放入余额，向payfee表中添加一个负数金额、相同银行流水号的记录。并且修改设备 余额，此时查询欠费应该有改变。（5）对帐每个银行每日凌晨给电力公司的代缴费系统发送对账信息，代缴费系统记录对账结果，对账明细，对账异常信息进行存储。错误信息为100银

3、行没有此记录。101企业没有此流水号.102银行企业金额不等。CitjteXM.SendJPossesspayfeebankserialidtypecheckdatepaymoney银行记录telbankserialbanktotalcountnamename缴给banktotalmoney缴纳客户idcodebal;拥有idourtotalmoney产生设备产生flagourmoneyididsnum:bankserial -bankmoneycheckdate建表语句费用（实缴）费用（应收）yearmonthI ”basicfee2.数据库设计（1） ER图设计: 自己设计的ER图：-Cr

4、eate tablecreate table Bank经过老师修正统一的ER图:” Antic ter ni岭i紗mer-Pay RecordtnertilIT l.edgerAdlillAoneter RecordU国kliankRir-iri银行对账异常表银行银行对账总表抄表记录id number(4),name varchar2(20), code char(2)- Create/Recreate primary, unique and foreign key constraints alter table Bankadd constraint PK_BANK_ID primary ke

5、y (ID);alter table BANKadd constraint PK_BANK_CODE unique (CODE);- Create tablecreate table client(id number(4),name varchar2(20),address varchar2(80),tel varchar2(20)- Create/Recreate primary, unique and foreign key constraints alter table clientadd constraint PK_CLIENT_ID primary key (ID);- Create

6、 tablecreate table device(deviceid number(4), clientid number(4), type char(2), balance number(7,2)- Create/Recreate primary, unique and foreign key constraints alter table deviceadd constraint PK_DEVICE_DEVICEID primary key (DEVICEID); alter table deviceadd constraint FK_DEVICE_CLIENTID foreign key

7、 (CLIENTID) references client (ID);- Create tablecreate table electricity(id number(4),deviceid number(4),yearmonth char(6),snum number(10)- Create/Recreate primary, unique and foreign key constraints alter table electricityadd constraint PK_ELECTRICITY_ID primary key (ID);alter table electricityadd

8、 constraint FK_ELECTRICITY_DEVICEID foreign key (DEVICEID) references device (DEVICEID);- Create tablecreate table RECEIVABLES(id number(4), yearmonth char(6), deviceid number(4), basicfee number(7,2), flag char(1)- Create/Recreate primary, unique and foreign key constraints alter table RECEIVABLESa

9、dd constraint PK_RECEIVABLES_ID primary key (ID);alter table RECEIVABLESadd constraint FK_RECEIVABLES_DEVICEID foreign key (DEVICEID) references device (DEVICEID);- Create table create table PAYFEE(id number(4), deviceid number(4), paymoney number(7,2), paydate date, bankcode char(2), type char(4),

10、bankserial varchar2(20)- Create/Recreate primary, unique and foreign key constraints alter table PAYFEEadd constraint PK_PAYFEE_ID primary key (ID);alter table PAYFEEadd constraint FK_PAYFEE_DEVICEID foreign key (DEVICEID) references device (DEVICEID);alter table PAYFEEadd constraint FK_PAYFEE_BANKC

11、ODE foreign key (BANKCODE) references BANK (CODE);- Create tablecreate table BANKRECORD(id number(4), payfee number(7,2), bankcode char(2), bankserial varchar2(20)- Create/Recreate primary, unique and foreign key constraintsalter table BANKRECORDadd con stra int PK_BANKRECORD_ID primary key (ID);alt

12、er table BANKRECORDadd con strai nt FK_BANKRECORD_BANKCODE foreig n key (BANKCODE) refere nces BANK (CODE);-Create tablecreate table CHECKRESULT(idn umber(4),checkdate date,ban kcode char(2),ban ktotalcou nt n umber(4),ban ktotalm oney n umber(10,2), ourtotalcou nt n umber(4), ourtotalm oney n umber

13、(10,2) -Create/Recreate primary, uni que and foreig n key con stra intsalter table cHECKRESULTadd con stra int PK_CHECKRESULT_ID primary key (ID);alter table CHECKRESULTadd con strai nt FK_CHECKRESULT_BANKCODE foreig n key (BANKCODE) refere nces BANK (CODE);-Create tablecreate table check_excepti on

14、 (idn umber(4),checkdate date, ban kcodechar(2),ban kserial varchar2(20), bankmoney n umber(7,2), ourm oneyn umber(7,2),excepti on type char(3) -Create/Recreate primary, uni que and foreig n key con stra ints alter table check_excepti onadd con stra int PK_CHECKEXCEPTION_ID primary key (ID);alter ta

15、ble CHECK_EXCEPTIONadd con strai nt FK_CHECKEXCEPTION_BANKCODE foreig n key (BANKCODE) refere nces BANK (CODE);3. 数据库端的系统实现1.十条sql语句（1）查询出所有欠费用户。（为了使测试方便，修改添加了一些数据，见附录）selectonfrom device a join receivables b on = join client c where =0order by 1,3,4(2) 查询出拥有超过 2个设备的用户SELECEIientid, nameFROM( SELECT

16、tlientid , COUNT) CT FROMevice GROUPBYclientid ) join client on =clientidWHERCT 2(3) 统计电力企业某个月的总应收费用，实收费用select month, sum( paymoney)from (select to_char ( paydate , yyyymm ) as Month, paymoney from payfee pwhere to_char (paydate , yyyymm )= 201608)-group by month-实收费用select yearmonth , sum( basicfe

17、e ) as receivableMoney from receivablesgroup by yearmonthhaving yearmonth =201608-应收费用(4) 查询出所有欠费超过半年的用户with s as(select , count from receivables bwhere flag =0group byhaving count 1 -我将题目修改成超过一个月 )select , from device join s onorder by 1, 2掘 Eq. -vti甘i ak( sefleft lb-drVK?id CMJrt(bfrarnb-fla=：O 射存

18、呻 叶 brdev- r I 討f l 让 1 43 Ifl. h iaLi ct d f coiiut ftiirsi i* tee hits bA -F-ipr by b. d.*vi z ei dh“i ng “si (b d“ E1此磨榔#辰说Sb匚Hcl i A-ljl L X dw L ei ck-vj 匚frn dm C4 jcm t kvsct. itnle*ictid(Mr勒r tiy 2称q粘剧田写虹申Jmr nmcD1 氓B2 L21X32L2C4=0ft G 4L-禮-M J 行昶潴，Hj a 01$ 秒(5) 查询任意用户的欠费总额select clientid fr

19、om device awhere clientid,sumjoin receivables b =1on,flaggroup by clientid having flag =0” hiGSE AS sreja* -La i 订甘送辜，輕时.djs 彗点! SQL fiC- -dli efitid,s um | b.baictee IrEm dwce a join reEeivsbli b |i ly li nlid 3 flftgKii.VL 1. zl=0lp=MCUStULL -W 3.KlJiik1I500(6) 查询出某个月用电量最高的3名用户with s as (select su

20、m as sum_numfrom device ainner join electricity b on = where = 201608- 月份条件 group by)select s0. *from ( select ,from sorder by desc) s0where rownum = 3(7)查询出电力企业某个月哪天的缴费人数最多select day, numfrom( select count (id ) as num , to_char ( paydate , yyyymmdd ) as day from payfeewhere to_char ( paydate , yyy

21、ymm )= 201608group by to_char (paydate , yyyymmdd) order by count (bankserial ) desc)where rownum2;-查询8月份付款人数最多的一天(8) 按设备类型使用人数从高到低排序查询列出设备类型，使用人数。select , count (*) as numfrom devicegroup byorder by count (*) desc(9) 统计每个月各银行缴费人次，从高到低排序。select to_char (paydate ,yyyymm) yearmonth “ count num from ba

22、nk join payfee on =group by to_char (paydate , yyyymm),order by yearmonth , num desc;-增加了一条记录，修改了两条记录(10) 查询出电力企业所有新增用户(使用设备不足半年)select ,from client join device on =join electricity on =group by ,having count (yearmonth )BjJ-2血闻WlP-il亍11 閔 WJ】皿闻鬥I常创Iaiea mmim DO : Dl&fljl *E,1- 3 TJ I ALFZE!ftll.J|-

23、 ikMlWMAiskTE_LiE-ttiWi. -if- WW HITJ = fWl 曲MHIV iz :：mIK* 聞-b1. 4/|0 thenif dtype =01 then -居民 违约金跨年与不跨年违约金比例相同smoney:= smoney+basicfee * days;else |if days days2 then -其他，不跨年 smoney:= smoney+basicfee * days;else-其他，跨年smoney:= smoney+basicfee *( days - days2)+ basicfee *( days2);end if ; end if ;e

24、nd if ;end loop ;select balance into d_balance from device where deviceid =deviceno ;if (smoney fll 卩山上山环in Dncy -? sm.jtsyl:4 nd：主要创新点：1. 我将修改标志位flag和扣费的过程写在了此存储过程中。查询时如果设备余额大于欠费数，则用余额对设备进行缴费，更新flag=2 （第二天凌晨所有的flag=2更改为1）是为了标志是今天的扣费修改过程，方便冲正。2. 我新设置了一个表，bankrecord用来记录扣费记录，方便冲正的时候将设备金额变回来。3. 在计算跨年费用

25、时，我使用了select TO_CHARSYSDATEDDD） into days2 from dual ;首先判断当前时间是一年中的第几天，再根据老师的代码，设备欠费天数days作比较。如果daysdays2 ,说明存在跨年的欠费，否则不存在。2.缴费代码：(添加记录到payfee表中，并更改设备余额)paydate begindate ;create or replace procedure payfee1 (deviceno in number, paymoney in number, results out varchar ) isif paymoney0 thenselect tru

26、nc (sysdate ) into paydate from dual ;-截取到日 insert into payfee values , deviceno , paymoney, paydate , 19, 2001,;update device set balance =balance +paymoney where deviceid =deviceno ; results:=成功;elseresults:=缴费失败，缴费金额不能少于0;end if ;end payfee1 ;测试截图StijCfir，prcKPdure DiFFFlXF 前瞄木三訝- lizElI思SISJBiK卡

27、1已执行.棚寸n nj专缴费之后的payfee表：（增加了一条付费记录）鶴计东MllfCt 1, K 1TnET t C.CTJLCC1mid froiATRF I*L nmE-gcnpPATBDSEr ?AYBMTjEJJKjaH3_rm3MsTSEEIkliBfiHII11 2tdlz21K. ODlb3C0Lyj_njW/iggLL sAa目 迫L 璋口 _ select t trnwid frcni PAYFEE T where deuiteidfi回=野 &2:1T再次查询欠费金额：（欠费金额为0）hrEEE .t S7SD3 由?巧漱盘抒，耗叶（J佃屯?1BIE籀岀皆无即冷匿1 f

28、 ill1 r_b# 丿 fwMrn q,irTf kLii&l.n4 =：dLi曾sxIew* =:si*myj;snontyU.kdlj titiinInlecetG* VVlC .-L-pru*i-补。畀kS 3TSH1!* -ft 已执打*袪討n Tift M，缴费之后的设备表：（余额由3变成）设备6应收费用表：（flag=2用来标志是今天刚刚缴费的，方便冲正。）主要创新点：按照设备号进行缴费，向payfee中添加记录。 使用序列来控制流水号，和id。3.冲正代码create or replace procedure Reverse ( re_bankserial in %type ,

29、 results out varchar ) is devicenonumber;re_moneynumber;-冲正钱数d_balancenumber;-设备余额d_smoneynumber;-设备扣费前的余额re_bankcode char (2);re_datedate;paydatedate;cursor temp_cursor isselect ，“from payfee pwhere =re_bankserial and in (select bankserialfrom payfeegroup by bankserialhaving count (*)= 1);-没被冲正过的记录

30、beginopen temp_cursorfetch temp_cursor into deviceno , re_money, re_bankcode , paydate ;select trunc (sysdate ) into re_date from dual ;-截取到日 if temp_cursor %NOTFOUND-判断有无数据then results :=失败,没有找到此流水号;elsif paydate != re_date thenresults:=失败，不是本日记录。；elseinsert into payfeevalues , deviceno , 0- re_mon

31、ey, re_date , re_bankcode , 2000, re_bankserial );select balance into d_balance from device where deviceid =deviceno ;-取岀设备的余额if (d_balance re_money) then -设备余额大于充值钱数说明充值的钱数不够，flag不变update device set balance =balance - re_money where deviceid =deviceno ;results:=成功;else -设备余额小于充值钱数，说明充值之后够了，并且扣费成功了u

32、pdate receivables set flag = 0 where deviceid =deviceno and flag =2;select distinct money into d_smoney from devicerecord where deviceid =deviceno and chargedate =paydate ;update device set balance =balance +d_smoney- re_money where deviceid =deviceno ; results:=成功;end if ;end if ;end Reverse测试截图.易漁

33、雄 口 prCKr- RFVFRFXF 力gt；帀卫1脚则体|1BMS踽娜棣踝娱酸n1| E P -将刚刚为6的缴费冲正。 :riwulist),Ffrultl 二rSlTLEF晒StTLIVg：*栩-更已拠冇中耗时1 I.幽砂此时的payfee表：（增加了一条冲正记录）应收费用表：（flag标志为变成0）设备表：（回到缴费前3元状态）查询设备6的欠费情况：（回到缴费前的状态，仍然欠费）主要创新点:实验的主要思路是主要确定有一致的缴费记录，并且流水号记录只有一次，说明没有冲正过，然后进行冲正。冲正过程中，读取设备此时余额与缴费金额（或者说是冲正金额）作对比，根据“设备初始金额+缴费金额-扣费金

34、额=设备此时余额”，如果设备此时余额 缴费金额，这说明 执行了扣费过程，需要进行receivable 中标志位的更改，也需要修改 device表中的设 备余额，这时候用到了扣费记录表（自己建的）找到扣费金额。那么设备此时的余额+扣费金额-缴费金额=设备初始金额如果设备此时余额 缴费金额，这说明缴费之后 依然欠费，没有进行扣费。这时候只需要向payfee中添加记录，并修改设备金额；4. 对总账代码create or replace procedure checkmoney (check_bankcode in char, total_count in number,total_money in

35、bankcode1number, check_date in varchar , results out varchar ) is varchar (2);paydateldate;total_money1number;total_count1number;re_countnumber;cursor temp_cursor isselect bankcode , paydate , sum( paymoney), count (*) from payfee group by bankcode , paydatehaving bankcode =check_bankcode and to_cha

36、r (paydate ,yyyymmdd )=check_date ;beginselect count (*) into re_countfrom payfeewhere bankcode =check_bankcode and to_char (paydate , yyyymmdd )=check_date and paymoney |H til h h h- I j - k - I .-I IJ Ilk II 1-= :rlhArkfll n = cKr-k_4i t(re;s =A :refillJcul;世*曲-11Fl皿0Fl b blI*r tK.nl IE.Slriiug-二爭

37、q qj * 匹srsniA -U已払行.料寸J郑秒主要创新点：主要思路是用游标把payfee表中符合银行代码、日期的记录保存起 来，然后提取出来进行比较。失败则调用对明细账模块，将错误信息 存储。5.对明细帐代码：create or replace procedure check_detail (check_date in char) isBbankcode varchar (2);Bpayfeenumber;Bbankserialvarchar (20);Pbankserialvarchar (20);Ppayfeenumber;Ppaydatedate;cursor temp_curso

38、r isselect ,，“from bankrecord b full outer join payfee p on = where not in(select bankserial -找到冲正的记录和被冲正的记录from payfeewhere type =2000) and to_char , yyyymmdd )=check_date or to_char , yyyymmdd ) is null beginselect trunc (sysdate ) into Ppaydate from dual ;-截取到日 open temp_cursor ;loopfetch temp_cu

39、rsor into BBankcode , Bpayfee , Bbankserial , Pbankserial , Ppayfee ; exit when temp_cursor %notfound ;if (Bbankserial is null ) theninsert into check_exceptionvalues , ppaydate , Bbankcode, Pbankserial , Bpayfee , Ppayfee , 100);elsif (Pbankserial is null ) theninsert into check_exceptionvalues , P

40、paydate , Bbankcode, Bbankserial , Bpayfee , Ppayfee , 101);elsif ( Bpayfee != Ppayfee) theninsert into check_exceptionvalues , Ppaydate , Bbankcode, Bbankserial , Bpayfee , Ppayfee , 102); end if ;end loop ;end check_detail ;测试截图:对账失败时蚩- prweckj re CHBCKMOMEYXE 铀甲本训?cms首出|幫词iin据跟尊(Vilib亡日九 lid-taii

41、jriuyi(r-K*rt(_Tijuk-M il* 二、:ckjaclUbutocwd ttal_ccinit = A i3toiL_c*nDi.J从靶叱和U 二1, re;J.is =A :resiilt.31HiUr丄血g因-1FIB0卜t Dt al rri0：uyFl martfL2时B12诫亠兀r3I b.SlfkEL* QJ-311 AS空官皿止 -U已扳行.耗时J.閃3秒Checkexception 表中数据：100-银行无此流水号101-企业无此流水号102-银行与企业记录的钱数不等主要创新点:主要思路是将payfee表和银行记录表进行全连接，然后进行筛选。因为一开始我在构建

42、游标时使用了where paydate=check_date筛选条件，忽略了全连接之后企业没有此流水号记录，paydate为空的情况，导致了企业方无此流水号这种错误记录不 会出现。所以将条件改为to_char , yyyymmdd )= check_date or to_char , yyyymmdd ) is null ;4程序实现 调用存储过程代码： package database;import class procedure public static void main(String args) throws ParseException String driver = ;Strin

43、g url = jdbc:oracle:thin:localhost:49159:XE; 询);2. 缴费 );3. 冲正 );4. 对总帐 );5. 对明细账 );0. 退出 ); 请输入选项： );choice = ();switch (choice) case 1: 请输入客户号： ); int customerno = ();cs = (call QUERYFEE(,); (1, customerno);(2, ;();double smoney = (2);break; case 2:cs = (call payfee1(,); 请输入设备号： ); deviceno = (); 请输入缴费金额： ); payMoney = ();(1, deviceno);(2, payMoney);(