1、東北大學(xué)數(shù)據(jù)庫應(yīng)用程序設(shè)計實踐報告 課程編號： b080 0 0 004 數(shù)據(jù)庫應(yīng)用程序設(shè)計實踐報告 姓 名 學(xué) 號 班 級 指 導(dǎo) 教 師 開 設(shè) 學(xué) 期 0 0 6 6- -2 2 1 17 7 第一學(xué) 期 開 設(shè) 時 間 第 周 - - 第 第 5 15 周 報 告 日 期 201 /12 16 評 定 成 績 評 定 人 評 定 日 期 東北大學(xué)軟件學(xué)院 1. 問題定義 銀行代收費系統(tǒng)給電力公司開發(fā)得一套繳費系統(tǒng)，方便用戶通過網(wǎng)銀支付電費. 主要得用例圖: 圖 1 銀行代收費系統(tǒng)用例圖 根據(jù)用例圖得出主要得業(yè)務(wù)需求： （1）抄表 系統(tǒng)管理員把抄表記錄錄入系統(tǒng),抄表記錄包括當前電表數(shù)、抄

2、表日期、抄表人等信息,根據(jù)抄表記錄，系統(tǒng)自動計算每個計費設(shè)備當月得應(yīng)收電費。每個計費設(shè)備有唯一 編號。 （2）查詢 用戶隨時查詢欠費金額。一個用戶名下可能多個計費設(shè)備，查詢欠費時，將所有計費設(shè)備欠費總與輸出。需要考慮設(shè)備得余額問題。如果余額大于欠費,則欠費為 0，更新余額,修改 reciable 中 fag 標志。 （3）繳費 在當月電費清單生成完畢后，用戶可進行電費繳納，繳納金額可就是任意金額.系統(tǒng)將繳費金額存入設(shè)備余額中，再次查詢則欠費應(yīng)該減少。 （4）沖正 用戶在繳費過程中如果給其她用戶繳費了,在當日 0 點前可以沖正，即把錢收回,放入余額，向 pyfee 表中添加一個負數(shù)金額、相同銀行

3、流水號得記錄.并且修改設(shè)備余額，此時查詢欠費應(yīng)該有改變。 (5）對帳 每個銀行每日凌晨給電力公司得代繳費系統(tǒng)發(fā)送對賬信息,代繳費系統(tǒng)記錄對賬結(jié)果,對賬明細，對賬異常信息進行存儲。錯誤信息為 10銀行沒有此記錄。10企業(yè)沒有此流水號、2 銀行企業(yè)金額不等。 2 數(shù)據(jù)庫設(shè)計 （1）er 圖設(shè)計: 自己設(shè)計得 e圖: 經(jīng)過老師修正統(tǒng)一得 er 圖： 客戶teladdress name設(shè)備deviceid擁有費用（應(yīng)收）產(chǎn)生費用（實繳）繳納銀行繳給idnameflag idyearmonth paydate bankserial type paymoney idcodeidtype balance 抄

4、表記錄產(chǎn)生idyearmonth snum basicfee 銀行記錄銀行對賬總表銀行對賬異常表id payfee bankserial checkdate banktotalcountbanktotalmoneyourtotalcount ourtotalmoney id exceptiontype ourmoney id checkdate bankserial bankmoney （2）建表語句 reae ab ate table ank ( i umber（4), nme varhar2（0）， code cha（2） ) ; - create/eeae pmary, unqe and

5、 oreig ke constraints alter abl bank d cosint k_ban_id riry ke （id）； ae table bank ad costrat pk_nk_coe ique (code）； eat tale reat tbl cie （ id number(4）, name vrca（20), addres varh（）， te vachar（20） ） ； - reate/rereae primary， uniue and fregn key costrt ate te cliet add contrnt p_cientid rimar key （

6、i）； - reat bl create table devie （ devieid nmbe（4), cientd mbe（）, type cr（)， balance number（7,2） ） ； - createecrate pimary， iue and forein ey ostraint ar te vie dd cotant p_evic_diceid primary key (deviced）； ater tale evic ad cnsraint fk_e_clientid orign ey (clienid) rerene len (d）; ceate table crea

7、te l electriity ( id nmber(4)， deviceid nuber(4）， armonth char（）, sum mber（10） ) ; - creat/receae pary， uiue ad eign ky ontaint alt table letity add consran _lectricid primay key (i）； alter tle lctricity add costain fkelecrity_devcid forig k （dvied) refrece evie (dicei)； creat table ceate able rceiv

8、ables ( id number(4), yearmonth har(6）， vceid nube(）， bafee umbe（7,）， fla char(1） ） ; crt/recreat rmary， nu and forein key costaint ater table receivbles ad cstan pk_rblsid rmry ky （); lter table reeivble ad contrain k_receivables_dvice oein key (devicei） references devce （decid）； - rete al crate ta

9、ble paye ( id umber（4), devied number（4）， payoey numbr（,2)， paydat dt， bnkcoe car（2）， tp char(）， bakseria varha2（20) ) ; - raerereate primr， unique n rign key cnsraits altr table payfe a costaint p_payfid primary key (id）; aler able payfe ad cnstrant fk_pyfee_deieid oegn e （devieid) eferec devic (de

10、viceid）； aler bl pfe ad constrait fk_payfee_ankcode ign key （banco) enc bak (oe）； - reate able creae able bankreord （ d nber（4）， afe number(7，2)， bancde char(2）， banksea vara2（20) ) ； - rate/recrete pimary， uique and foregn ke consints e tale bankecd add ntrain p_bankrecor_ primar ke （i）; ater tale

11、bnkrecord ad ctrant fk_akrordancode oig key (bankcode） references bank (cde）； crete able ceat tble chekresult ( d mer（）， heckda dat, akcode char(2)， banktotloun number(4), banktotalmny ume(0，2), ourtolcon numer(4）, ourooney numbe(1,2） ） ; creaeee rimy， unique and foei key onsrais te table checresul

12、dd constait pk_ecklt_i priry k （)； alter tbl chckeu add onstrait fk_cekreultbkcode foreg key （bankcde） erenc bk （co）; - creae tabl rate table check_eceptio （ id nmbr（4）, ecdat date, bnode ch（2）, anksel vchr2(0）， bnkmoney nmer(7，2）, ouoney nuber（7，)， xceptiontye char(3） ） ； crea/ecate primay， nque nd

13、 oregn y onstraints alter able eck_eception add cnstaint pk_ceckcetiond pmry key (id); ater tale chck_exepto add cnstrait fk_heckexeption_bancode rein ke （bakco e) erene bank (code)； 3 數(shù)據(jù)庫端得系統(tǒng)實現(xiàn) 條 、十條 s l 語句 （ （1） ） 查詢出所有欠費用戶. （為了使測試方便, 修改添加了一些數(shù)據(jù)，見附錄） seec 、clinid，c、name，a、evicid,、ermont from deice

14、a joi receivals b n 、deviid=、devicei in cli c on a、clintid=c、id whr b、fg=0 orde 1,3，4 （ （2） ）過 查詢出擁有超過 2 個設(shè)備得用戶 selc clintid，m rom （slect lienti， count（） ct from dvi grop by intid） jin cliet on cint、id=cenid her ct 2 （ （3） ） 統(tǒng)計電力企業(yè) 某個月 得總應(yīng)收費用，實收費用 set month,sum(paymoney) from( selec to_char（adate,&q

15、uot;yyymm) as mon,ymoney from yf p whe to_cr(adte，yyyymm）=202108" ) grup b mont 實收費用 selec yarmonh ，sum(basife) s receivablemoney ro reeivbls p by yearmont havig yeat268 -應(yīng)收費用 （ （4） ） 查詢出所有欠費超過半年得用戶 with a（ select b、devicd ，cunt（、dicei) frm receivale her lg grp by b、dvicid avin count（、devicei）1

16、 -我將題目修改成超過一個月 ） selec dice、cliend，devic、eveid frm dvie on o device、dvid=s、deicid ord by 1，2 （ （5） ） 查詢?nèi)我庥脩舻们焚M總額 ect cent，sum（、baicfe） from evie a join receibles b n a、dviceid=b、devicei where clienti1 group cientd ，fag aing flag= （ （6） ）得 查詢出某個月用電量最高得 3 名用戶 with s as （ selet su（b、sum） a um_n， a、inti

17、d rom i inner join criiy b a、deviced = 、evicd where b、ermoh 20108 - 月份條件 group by a、cientid ） selet s0、* from( select s、ienti， 、um_nm rom s der by s、sum_num ec）s0 where rownum = 3 （ （7） ） 查詢出電力企業(yè)某個月哪天得繳費人數(shù)最多 sele da,num from ( slec count（d) as nu ，to_char(paydate,yyymmd") as ay rom pfe here toc

18、har(paydate,yyymm"）="20108 ou y o_char（paydate,yyyymmd） order b oun（ansrial） dsc ) whre ronu2;查詢 8 月份付款人數(shù)最多得一天 （ （8） ） 按設(shè)備類型使用人數(shù)從高到低排序查詢列出設(shè)備類型, 使用人數(shù)。 selec evice、type，count（) a n f device group b evic、tp oder by count(*) esc （ （9） ） 統(tǒng)計每個月各銀行繳費人次，從高到低排序。 seect t_har（pyte，"yyyym） yearth

19、，bnk、nm， cunt（payf、id) num from bank n payfe on bank、ce = payfe、bakcode grou by tchr(ayat，yymm），ban、ae ode yrmont,num dec； 增加了一條記錄,修改了兩條記錄 （ （10） ） 查詢出電力企業(yè)所有新增用戶（使用設(shè)備不足半年). selt cliet、d,dvice、deviceid frm clin i dvce on lent、i = device、clint jin eecricity o ece、dicid = eletricity、dviceid grup by cli

20、e、d，devce、dveid hig count（earmonth)6； -如果某個設(shè)備得抄表記錄數(shù)小于 6,則其使用不足半年 附錄： 添加修改得數(shù)據(jù): 1、在ece 表下,加入數(shù)據(jù)如圖： 2、在 reivable表中，加入四個設(shè)備兩個月得應(yīng)收記錄如圖： 3.在 paye 里加入201 設(shè)備 2609 得付款記錄 4、向lectrici中插入數(shù)據(jù)，如下圖,比較用戶. 5.將ayfe 中得部分記錄得 bakcoe 更改，訂單日期也進行更改 6.增加記錄到 bak 表中。 2 、事物存儲過程 （） 查詢 碼 代碼 1: （按設(shè)備號進行查詢，在代碼 2 中被調(diào)用) create o rplae p

21、rocedure queye1（devieno i number, smony o mbr ） is bicee number（，2)； yeamont recees、yearmonttpe； dtype device、tyetye; as nmber； ays2 number; _balace umber； id uber； aat date; ur tem_curor is elct r、asicfe,、yemonth，d、e,r、id fom deve d, rceiabs r whe d、evcd r、evii and r、la 0 ad d、eviceid=deviceno； beg

22、i smoey：0； oen temp_crsor; lop fch temp_crsor to basicfe,yearmnh,tye，rid; xit whn tep_ursor%notfoun; money：=smoeysicee； smny:=smoney+bsicfee0、08； if dtype = 1" tn smon:= smoney basifee*0、1; else soey:=smoney + baicee*0、5； d f； seect rod（sysat - ad_month(o_dat（yearmonth，yyym），1)） in days fom dua

23、l； eet o_h（syate,ddd） int da2 from dual； if days 0 then i ype=01" then -居民 違約金 跨年與不跨年違約金比例相同 smoney：=smonybasicfee0、001das; le if daay2 th 其她， 不跨年 smey：=ebsicfe0、002*as； else 其她,跨年 smony：=sme+baicfe*0、002(aydys2)+basicfee*0、00*（da2)； ed f； ed f; if； d loop; sle blance nt d_balnce o deve w devic

24、ei=device; f（smoney=d_balance ad smoney!=) hen -如果設(shè)備余額大于欠費 余額更新、欠費置 0 sele truc(sysdate) ito chrdate rom dua； 截取到日 set io devcecor vlues(deieno，sone,rid,chrgdate)； -把設(shè)備扣費記錄保存保存 udat vce et balncebaance-soey he deviceiddeven; uda receabl s lag= whre i=d； soney:=0; el（smny!=0） hen -設(shè)備余額不夠繳費 sony：smoey

25、d_balane； nd f; e quryfee1； 代碼 : （按用戶號獲得設(shè)備號，將設(shè)備號傳入代碼 1 ） 得存儲過程中） reate or eplce rodre querye（cliet i ln、dtype， mon out nmber） is diceno umber； d_smon nuber; curs emp_cursr i seet d、devicei from clin ， eve d wer 、id d、cintid and 、i = clientno； egin soney：0； pe tecurso； l fetc temp_cur into devio； ex

26、en te_ursorntfound； queryfee1（devceno,d_smoney）； soney：=ony+_smoney； nd loop； en queryfee; 測試截圖： 設(shè)備 6 得應(yīng)收費用表: 查詢設(shè)備 6 得欠費金額： 主要創(chuàng)新點： 1.我將修改標志位g 與扣費得過程寫在了此存儲過程中.查詢時如果設(shè)備余額大于欠費數(shù)，則用余額對設(shè)備進行繳費,更新 flag=2（第二天凌晨所有得lag=更改為 1）就是為 了標志就是今天得扣費修改過程，方便沖正。 2.我新設(shè)置了一個表，bankrrd 用來記錄扣費記錄,方便沖正得時候?qū)⒃O(shè)備金額變回來。 3、在計算跨年費用時，我使用了 s

27、ele to_char(sysdate,"dd") no ays from dul； 首先判斷當前時間就是一年中得第幾天,再根據(jù)老師得代碼，設(shè)備欠費天數(shù) days 作比較. 如果 dayda2 ,說明存在跨年得欠費，否則不存在。 2. 繳費 代碼：（添加記錄到y(tǒng)f e 表中，并更改設(shè)備余額） crt or rplace procedure pye(viceo in mber，paymoe in ner,eult out varhar ） s payda date; en if aymony0 then slet tunc（ssdae） into aydate from ul

28、； 截取到日 it into payfe vlues（pasequce、next，deiceno，paymne，aydate，19，001，baksral、nxval)； update evice se balance=balancepaymoney here dice=deviceo; reults：="成功； es eult：="繳費失敗,繳費金額不能少于 0； ed if； end payfee1； 測試截圖 繳費之后得 payf表：(增加了一條付費記錄) 再次查詢欠費金額:（欠費金額為 0） 繳費之后得設(shè)備表：（余額由 3 變成8、) 設(shè)備 6 應(yīng)收費用表：（flg

29、=用來標志就是今天剛剛繳費得,方便沖正。） 主要創(chuàng)新點: 按照設(shè)備號進行繳費,向 pafee 中添加記錄。 使用序列來控制流水號，與。 3. 沖正 代碼 cate r repc prodre rere （ ebanksil i pafee、banriaype，ress ut archa) is deviceno nmber； re_mney umbe; -沖正錢數(shù) d_baane mber； -設(shè)備余額 d_sone nmb； -設(shè)備扣費前得余額 re_bacode char（2）； re_ate dae; aydate ate; rsor temp_curo is seect p、devic

30、id,、aymne,p、bnkoe，、paydate rom payfee p where p、nkseria_banksera and p、bnseria i （ selct bakria rm pyfee group by aserial havi coun()=）; -沒被沖正過得記錄 oen tem_cursor; feth tp_urso int evieno，r_mone，re_bankcde，pydate； selec unc(ssae） into edt fm ul; -截取到日 i tempcurs%ntfond -判斷有無數(shù)據(jù) e rslts :=失敗,沒有找到此流水號&q

31、uot;； sif adate！r_ae then rults:=失敗，不就是本日記錄。; ele ert into af vaues(payequence、extval，deceno,0rmoney，reate，re_bankcod，200，reankseril）; slect alane int _bace from devce here devieid=dvceo； 取出設(shè)備得余額 i（dbaancee_mon） then -設(shè)備余額大于充值錢數(shù) 說明充值得錢數(shù)不夠，flag 不變 update device et baane=blancre_oey wher dvicid=dviceo

32、; reult ：=成功"； ele -設(shè)備余額小于充值錢數(shù)，說明充值之后夠了，并且扣費成功了 update receivables set flag = 0 wher dei=deviceo and flag=; seec istict mone into _smone rom devicercord whre devcid=dvieo d chargate=pydate; pda evice set alance=baance+dmony-e_money were devieddevcen； reults ：成功； end ; end i; n rvrse ； 測試截圖 將剛剛為

33、 6 得繳費沖正. 此時得 pfee 表:(增加了一條沖正記錄） 應(yīng)收費用表:（lag 標志為變成 0) 設(shè)備表:（回到繳費前 3 元狀態(tài)) 查詢設(shè)備 6 得欠費情況:（回到繳費前得狀態(tài)，仍然欠費 11、2) 主要創(chuàng)新點： 實驗得主要思路就是主要確定有一致得繳費記錄，并且流水號記錄只有一次，說明沒有 沖正過，然后進行沖正。 沖正過程中，讀取設(shè)備此時余額與繳費金額(或者說就是沖正金額)作對比，根據(jù)"設(shè)備初始金額+繳費金額-扣費金額設(shè)備此時余額',如果設(shè)備此時余額繳費金額，這說明執(zhí)行了扣費過程，需要進行 reevable 中標志位得更改，也需要修改eic表中得設(shè)備余額，這時候用到

34、了扣費記錄表(自己建得)找到扣費金額。那么 設(shè)備此時得余額+扣費金額-繳費金額=設(shè)備初始金額 、 如果設(shè)備此時余額繳費金額 ,這說明繳費之后依然欠費，沒有進行扣費.這時候只需要向 payfe 中添加記錄，并修改設(shè)備金額； 4. 對總賬 代碼 eat or replce pocedure checkmoney(chck_banode n har，total_ount i um， tolmoey i umbe，e_dte n varchar，resul out varchar) is bakcd1 varchar()； aydat1 dat; total_mn1 numer; tolount1 u

35、me; e_cunt number； curor te_cso is select bnkcod,paydate，sum（amy)，cont(） from pyfee group b akcod,paydae havin kcode=check_bankcoe and to_a(payde,yyymd）=check_date； bgin select un（*) ito re_nt fro pyfee where bankodcec_bnod an to_ar(,yyyymdd")=chec_dae a payoney0； op tem_curs； ftch e_ursor into

36、 ankcode1,ydae1,totl_mney1，otal_coun1； ttal_coun1:=totalcont1-eot2; if talcoun1=tal_cunt and totaloney=total_money en results：="對賬成功； ese els：=對賬失敗，執(zhí)行對賬明細.; ceck_deal （chec_dat)； ed if； end chemone； 測試截圖 對賬成功（： pyee 表中數(shù)據(jù)： 對賬失敗,調(diào)用對賬明細： 主要創(chuàng)新點： 主要思路就是用游標把 payfe 表中符合銀行代碼、日期得記錄保存 起來，然后提取出來進行比較。失敗則調(diào)用

37、對明細賬模塊,將錯誤信息存儲. 5.對明細帳 代碼: crete or repace procedue check_dil（hck_dat ar) is bbankco varchar（）； bpayfe nmer; bankseria varchar（20); pbnksril vacha（20); ppayfe mbr； ada da; crsr tem_ror i sl b、ankcod,b、ayee ，b、bakerial,p、bnkseril，p、paoney fom ankrcord b full ouer in ayfe p on p、ankserl=b、bnkserial wh

38、er p、bakeial no i （slt banseial -找到?jīng)_正得記錄 與 被沖正得記錄 rm pyfe whre ype=00) and to_chr(、paydat,yyymdd")=check_dte r o_char(p、padate，"yymmd") is ul; egi select trunc（ysdte） into pat from dua； -截取到日 opn tmcusor; loop etc tmp_cursor into bbnkcod,bpayfee,bbankseril，bankrl，ppyf; ext when emp_cu

39、rsor%ntound; if(bbankeria s ull) th nsert int hc_exption values(eckid、etvl，pydate,bbankode，pbakseil,bayf,ppayee,100)； lsif（pbaneia is ul) then insert ino heck_ecpti vale（checki、netvl,ppade,bbanode，bbankserial，bpafee,ppayfee，11）； el(bafe!ppye） th insert into hc_exceptio alue（cecki、nxval，adte，bncode,b

40、bakseril,bpayfee，ppayfee，102); end if； end loop； end checkdetal； 測試截圖： 對賬失敗時 checeception 表中數(shù)據(jù)：100-銀行無此流水號 101企業(yè)無此流水號 2銀行與企業(yè)記錄得錢數(shù)不等 主要創(chuàng)新點: 主要思路就是將 payf表與銀行記錄表進行全連接,然后進行篩選。 因為一開始我在構(gòu)建游標時使用了 whee paydate=chekdate 篩選條件，忽略了全連接之后企業(yè)沒有此流水號記錄，padate 為空得情況,導(dǎo)致了企業(yè)方無此流水號這種錯誤記錄不會出現(xiàn)。所以將條件改為 to_chr（、padt，yyymmdd&qu

41、ot;)=check_date or t_char(、paydate,"yyyymmd"） s nll； 4. 程序?qū)崿F(xiàn) 調(diào)用存儲過程代碼: pace datbase； imrt ja、l、calllesttemnt； impor java、sql、onnectin； import java、sq、drvermnger； ipor jav、ql、sqxctin; io java、s、yes; iot ava、ext、parecption； mort va、text、simletefrmat; mort ava、util、date; t jaa、ui、scanner； pub

42、lic clas rocdre itpcxeesrap swrt ）gr gnirts（iam iov citt cilbup ;'iearo、cbdj、lcr' = rei gits ;x：5194：tolal:it：earo:cj = lr gnrts 連接字符串，4919 對應(yīng)得就是我得 orcl在我得電腦上使用得端口,xe 就是服務(wù)名 ;'rh = reu gnirs string psswod = r; ry cls、forne（rver)； ap ，resu ,lr（noitcenoctg、reganmreird noitenoc itcennswod); ystem、ut、pritn（'連接成功）； ；ut = ngo aelob ;）ni、meys(ea