東北大學(xué)數(shù)據(jù)庫應(yīng)用程序設(shè)計實踐報告

1、課程編號：B0數(shù)據(jù)庫應(yīng)用程序設(shè)計實踐報告姓名學(xué)號班級指導(dǎo)教師開設(shè)學(xué)期2016- 20 17第一學(xué)期 開設(shè)時間開設(shè)時間第13周第15周 報告日期報告日期2016/12/16評定成績評定人評定日期東北大學(xué)軟件學(xué)院1問題定義銀行代收費(fèi)系統(tǒng)給電力公司開發(fā)的一套繳費(fèi)系統(tǒng)，方便用戶通過網(wǎng)銀支付電費(fèi)。主要的用例圖：圖1銀行代收費(fèi)系統(tǒng)用例圖根據(jù)用例圖得出主要的業(yè)務(wù)需求：（1）抄表系統(tǒng)管理員把抄表記錄錄入系統(tǒng)，抄表記錄包括當(dāng)前電表數(shù)、抄表日期、抄表人等 信息，根據(jù)抄表記錄，系統(tǒng)自動計算每個計費(fèi)設(shè)備當(dāng)月的應(yīng)收電費(fèi)。每個計費(fèi)設(shè)備有唯 一編號。（2）查詢用戶隨時查詢欠費(fèi)金額。一個用戶名下可能多個計費(fèi)設(shè)備，查詢欠費(fèi)時，

2、將所有計 費(fèi)設(shè)備欠費(fèi)總和輸出。需要考慮設(shè)備的余額問題。如果余額大于欠費(fèi)，則欠費(fèi)為0,更新余額，修改receivable 中flag標(biāo)志。（3）繳費(fèi)在當(dāng)月電費(fèi)清單生成完畢后，用戶可進(jìn)行電費(fèi)繳納，繳納金額可是任意金額。系統(tǒng) 將繳費(fèi)金額存入設(shè)備余額中，再次查詢則欠費(fèi)應(yīng)該減少。（4）沖正用戶在繳費(fèi)過程中如果給其他用戶繳費(fèi)了，在當(dāng)日0點(diǎn)前可以沖正，即把錢收回，放入余額，向payfee表中添加一個負(fù)數(shù)金額、相同銀行流水號的記錄。并且修改設(shè)備 余額，此時查詢欠費(fèi)應(yīng)該有改變。（5）對帳每個銀行每日凌晨給電力公司的代繳費(fèi)系統(tǒng)發(fā)送對賬信息，代繳費(fèi)系統(tǒng)記錄對賬結(jié)果，對賬明細(xì)，對賬異常信息進(jìn)行存儲。錯誤信息為100銀

3、行沒有此記錄。101企業(yè)沒有此流水號.102銀行企業(yè)金額不等。CitjteXM.SendJPossesspayfeebankserialidtypecheckdatepaymoney銀行記錄telbankserialbanktotalcountnamename繳給banktotalmoney繳納客戶idcodebal;擁有idourtotalmoney產(chǎn)生設(shè)備產(chǎn)生flagourmoneyididsnum:bankserial -bankmoneycheckdate建表語句費(fèi)用（實繳）費(fèi)用（應(yīng)收）yearmonthI ”basicfee2.數(shù)據(jù)庫設(shè)計（1） ER圖設(shè)計: 自己設(shè)計的ER圖：-Cr

4、eate tablecreate table Bank經(jīng)過老師修正統(tǒng)一的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. 數(shù)據(jù)庫端的系統(tǒng)實現(xiàn)1.十條sql語句（1）查詢出所有欠費(fèi)用戶。（為了使測試方便，修改添加了一些數(shù)據(jù)，見附錄）selectonfrom device a join receivables b on = join client c where =0order by 1,3,4(2) 查詢出擁有超過 2個設(shè)備的用戶SELECEIientid, nameFROM( SELECT

16、tlientid , COUNT) CT FROMevice GROUPBYclientid ) join client on =clientidWHERCT 2(3) 統(tǒng)計電力企業(yè)某個月的總應(yīng)收費(fèi)用，實收費(fèi)用select month, sum( paymoney)from (select to_char ( paydate , yyyymm ) as Month, paymoney from payfee pwhere to_char (paydate , yyyymm )= 201608)-group by month-實收費(fèi)用select yearmonth , sum( basicfe

17、e ) as receivableMoney from receivablesgroup by yearmonthhaving yearmonth =201608-應(yīng)收費(fèi)用(4) 查詢出所有欠費(fèi)超過半年的用戶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) 查詢?nèi)我庥脩舻那焚M(fèi)總額select clientid fr

19、om device awhere clientid,sumjoin receivables b =1on,flaggroup by clientid having flag =0” hiGSE AS sreja* -La i 訂甘送辜，輕時.djs 彗點(diǎn)! 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)查詢出電力企業(yè)某個月哪天的繳費(fèi)人數(shù)最多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月份付款人數(shù)最多的一天(8) 按設(shè)備類型使用人數(shù)從高到低排序查詢列出設(shè)備類型，使用人數(shù)。select , count (*) as numfrom devicegroup byorder by count (*) desc(9) 統(tǒng)計每個月各銀行繳費(fèi)人次，從高到低排序。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) 查詢出電力企業(yè)所有新增用戶(使用設(shè)備不足半年)select ,from client join device on =join electricity on =group by ,having count (yearmonth )BjJ-2血聞WlP-il亍11 閔 WJ】皿聞鬥I常創(chuàng)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 卩山上山環(huán)in Dncy -? sm.jtsyl:4 nd：主要創(chuàng)新點(diǎn)：1. 我將修改標(biāo)志位flag和扣費(fèi)的過程寫在了此存儲過程中。查詢時如果設(shè)備余額大于欠費(fèi)數(shù)，則用余額對設(shè)備進(jìn)行繳費(fèi)，更新flag=2 （第二天凌晨所有的flag=2更改為1）是為了標(biāo)志是今天的扣費(fèi)修改過程，方便沖正。2. 我新設(shè)置了一個表，bankrecord用來記錄扣費(fèi)記錄，方便沖正的時候?qū)⒃O(shè)備金額變回來。3. 在計算跨年費(fèi)用

25、時，我使用了select TO_CHARSYSDATEDDD） into days2 from dual ;首先判斷當(dāng)前時間是一年中的第幾天，再根據(jù)老師的代碼，設(shè)備欠費(fèi)天數(shù)days作比較。如果daysdays2 ,說明存在跨年的欠費(fèi)，否則不存在。2.繳費(fèi)代碼：(添加記錄到payfee表中，并更改設(shè)備余額)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:=繳費(fèi)失敗，繳費(fèi)金額不能少于0;end if ;end payfee1 ;測試截圖StijCfir，prcKPdure DiFFFlXF 前瞄木三訝- lizElI思SISJBiK卡

27、1已執(zhí)行.棚寸n nj專繳費(fèi)之后的payfee表：（增加了一條付費(fèi)記錄）鶴計東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再次查詢欠費(fèi)金額：（欠費(fèi)金額為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-補(bǔ)。畀kS 3TSH1!* -ft 已執(zhí)打*袪討n Tift M，繳費(fèi)之后的設(shè)備表：（余額由3變成）設(shè)備6應(yīng)收費(fèi)用表：（flag=2用來標(biāo)志是今天剛剛繳費(fèi)的，方便沖正。）主要創(chuàng)新點(diǎn)：按照設(shè)備號進(jìn)行繳費(fèi)，向payfee中添加記錄。 使用序列來控制流水號，和id。3.沖正代碼create or replace procedure Reverse ( re_bankserial in %type ,

29、 results out varchar ) is devicenonumber;re_moneynumber;-沖正錢數(shù)d_balancenumber;-設(shè)備余額d_smoneynumber;-設(shè)備扣費(fèi)前的余額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-判斷有無數(shù)據(jù)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 ;-取岀設(shè)備的余額if (d_balance re_money) then -設(shè)備余額大于充值錢數(shù)說明充值的錢數(shù)不夠，flag不變update device set balance =balance - re_money where deviceid =deviceno ;results:=成功;else -設(shè)備余額小于充值錢數(shù)，說明充值之后夠了，并且扣費(fèi)成功了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；帀衛(wèi)1腳則體|1BMS踽娜棣踝娛酸n1| E P -將剛剛為6的繳費(fèi)沖正。 :riwulist),Ffrultl 二rSlTLEF曬StTLIVg：*栩-更已拠冇中耗時1 I.幽砂此時的payfee表：（增加了一條沖正記錄）應(yīng)收費(fèi)用表：（flag標(biāo)志為變成0）設(shè)備表：（回到繳費(fèi)前3元狀態(tài)）查詢設(shè)備6的欠費(fèi)情況：（回到繳費(fèi)前的狀態(tài)，仍然欠費(fèi)）主要創(chuàng)新點(diǎn):實驗的主要思路是主要確定有一致的繳費(fèi)記錄，并且流水號記錄只有一次，說明沒有沖正過，然后進(jìn)行沖正。沖正過程中，讀取設(shè)備此時余額與繳費(fèi)金額（或者說是沖正金額）作對比，根據(jù)“設(shè)備初始金額+繳費(fèi)金額-扣費(fèi)金

34、額=設(shè)備此時余額”，如果設(shè)備此時余額 繳費(fèi)金額，這說明 執(zhí)行了扣費(fèi)過程，需要進(jìn)行receivable 中標(biāo)志位的更改，也需要修改 device表中的設(shè) 備余額，這時候用到了扣費(fèi)記錄表（自己建的）找到扣費(fèi)金額。那么設(shè)備此時的余額+扣費(fèi)金額-繳費(fèi)金額=設(shè)備初始金額如果設(shè)備此時余額 繳費(fèi)金額，這說明繳費(fèi)之后 依然欠費(fèi)，沒有進(jìn)行扣費(fèi)。這時候只需要向payfee中添加記錄，并修改設(shè)備金額；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鄭秒主要創(chuàng)新點(diǎn)：主要思路是用游標(biāo)把payfee表中符合銀行代碼、日期的記錄保存起 來，然后提取出來進(jìn)行比較。失敗則調(diào)用對明細(xì)賬模塊，將錯誤信息 存儲。5.對明細(xì)帳代碼：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 -找到?jīng)_正的記錄和被沖正的記錄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 鈾甲本訓(xùn)?cms首出|幫詞iin據(jù)跟尊(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 表中數(shù)據(jù)：100-銀行無此流水號101-企業(yè)無此流水號102-銀行與企業(yè)記錄的錢數(shù)不等主要創(chuàng)新點(diǎn):主要思路是將payfee表和銀行記錄表進(jìn)行全連接，然后進(jìn)行篩選。因為一開始我在構(gòu)建

42、游標(biāo)時使用了where paydate=check_date篩選條件，忽略了全連接之后企業(yè)沒有此流水號記錄，paydate為空的情況，導(dǎo)致了企業(yè)方無此流水號這種錯誤記錄不 會出現(xiàn)。所以將條件改為to_char , yyyymmdd )= check_date or to_char , yyyymmdd ) is null ;4程序?qū)崿F(xiàn) 調(diào)用存儲過程代碼： 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. 繳費(fèi) );3. 沖正 );4. 對總帳 );5. 對明細(xì)賬 );0. 退出 ); 請輸入選項： );choice = ();switch (choice) case 1: 請輸入客戶號： ); int customerno = ();cs = (call QUERYFEE(,); (1, customerno);(2, ;();double smoney = (2);break; case 2:cs = (call payfee1(,); 請輸入設(shè)備號： ); deviceno = (); 請輸入繳費(fèi)金額： ); payMoney = ();(1, deviceno);(2, payMoney);(