東北大學數據庫應用程序設計實踐報告

1、本文格式為word版，下載可任意編輯東北大學數據庫應用程序設計實踐報告 課程編號： b080 0 0 004 數據庫應用程序設計實踐報告 姓 名 學 號 班 級 指 導 教 師 開 設 學 期 0 0 6 6- -2 2 1 17 7 第一學 期 開 設 時 間 第 周 - - 第 第 5 15 周 報 告 日 期 201 /12 16 評 定 成 績 評 定 人 評 定 日 期 東北高校軟件學院 1. 問題定義 銀行代收費系統(tǒng)給電力公司開發(fā)得一套繳費系統(tǒng)，便利用戶通過網銀支付電費. 主要得用例圖: 圖 1 銀行代收費系統(tǒng)用例圖 依據用例圖得出主要得業(yè)務需求： （1）抄表 系統(tǒng)管理員把抄表記錄

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

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

4、deidtype balance 抄表記錄產生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/ee

5、ae pmary, unqe and 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_c

6、ientid rimar key （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）

7、; ceate table create 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 tabl

8、e ceate able rceivables ( 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）；

9、- rete al crate table 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

10、) eferec devic (deviceid）； 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

11、ke （i）; ater tale 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 t

12、e table checresul 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/eca

13、te primay， nque nd 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 數據庫端得系統(tǒng)實現 條 、十條 s l 語句 （ （1） ） 查詢出全部欠費用戶. （為了使測試便利, 修改添加了一些數據，見附錄） seec 、clinid，c、name，a、evicid,、

14、ermont from deice 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 個設備得用戶 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è) 某個月 得總應收費用，實收費用 set month,sum(paymoney) from( sele

15、c to_char（adate,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 -應收費用 （ （4） ） 查詢出全部欠費超過半年得用戶 with a（ select b、devicd ，cunt（、dicei) frm receivale her lg grp by b、dvicid avin count（、dev

16、icei）1 -我將題目修改成超過一個月 ） selec dice、cliend，devic、eveid frm dvie on o device、dvid=s、deicid ord by 1，2 （ （5） ） 查詢任意用戶得欠費總額 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，

17、a、intid 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è)某個月哪天得繳費人數最多 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 月份付款人數最多得一天 （ （8） ） 按設備類型使用人數從高到低排序查詢列出設備類型, 使用人數。 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，bnk、nm， cunt（payf

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

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

21、vieno 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； begi smoey：0； oen tem

22、p_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 dual； eet o_h（syate,ddd） in

23、t 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 devicei=device; f（smoney=d_balance

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

25、 : （按用戶號獲得設備號，將設備號傳入代碼 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 en te_ursorntfound； queryfee1（

26、devceno,d_smoney）； soney：=ony+_smoney； nd loop； en queryfee; 測試截圖： 設備 6 得應收費用表: 查詢設備 6 得欠費金額： 主要創(chuàng)新點： 1.我將修改標志位g 與扣費得過程寫在了此存儲過程中.查詢時假如設備余額大于欠費數，則用余額對設備進行繳費,更新 flag=2（其次天凌晨全部得lag=更改為 1）就是為 了標志就是今日得扣費修改過程，便利沖正。 2.我新設置了一個表，bankrrd 用來記錄扣費記錄,便利沖正得時候將設備金額變回來。 3、在計算跨年費用時，我使用了 sele to_char(sysdate,dd) no ays

27、 from dul； 首先推斷當前時間就是一年中得第幾天,再依據老師得代碼，設備欠費天數 days 作比較. 假如 dayda2 ,說明存在跨年得欠費，否則不存在。 2. 繳費 代碼：（添加記錄到y(tǒng)f e 表中，并更改設備余額） 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； 截取到日 it into payfe vlues（pasequce、next，d

28、eiceno，paymne，aydate，19，001，baksral、nxval)； update evice se balance=balancepaymoney here dice=deviceo; reults：=勝利； es eult：=繳費失敗,繳費金額不能少于 0； ed if； end payfee1； 測試截圖 繳費之后得 payf表：(增加了一條付費記錄) 再次查詢欠費金額:（欠費金額為 0） 繳費之后得設備表：（余額由 3 變成8、) 設備 6 應收費用表：（flg=用來標志就是今日剛剛繳費得,便利沖正。） 主要創(chuàng)新點: 根據設備號進行繳費,向 pafee 中添加記錄。

29、使用序列來掌握流水號，與。 3. 沖正 代碼 cate r repc prodre rere （ ebanksil i pafee、banriaype，ress ut archa) is deviceno nmber； re_mney umbe; -沖正錢數 d_baane mber； -設備余額 d_sone nmb； -設備扣費前得余額 re_bacode char（2）； re_ate dae; aydate ate; rsor temp_curo is seect p、devicid,、aymne,p、bnkoe，、paydate rom payfee p where p、nkseri

30、a_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 -推斷有很多據 e rslts :=失敗,沒有找到此流水號； sif adate！r_ae then rults:=失敗，不就是本日記錄。; ele ert into a

31、f vaues(payequence、extval，deceno,0rmoney，reate，re_bankcod，200，reankseril）; slect alane int _bace from devce here devieid=dvceo； 取出設備得余額 i（dbaancee_mon） then -設備余額大于充值錢數 說明充值得錢數不夠，flag 不變 update device et baane=blancre_oey wher dvicid=dviceo; reult ：=勝利； ele -設備余額小于充值錢數，說明充值之后夠了，并且扣費勝利了 update receiv

32、ables 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 ； 測試截圖 將剛剛為 6 得繳費沖正. 此時得 pfee 表:(增加了一條沖正記錄） 應收費用表:（lag 標志為變成 0) 設備表:（回到繳費前 3

33、元狀態(tài)) 查詢設備 6 得欠費狀況:（回到繳費前得狀態(tài)，仍舊欠費 11、2) 主要創(chuàng)新點： 試驗得主要思路就是主要確定有全都得繳費記錄，并且流水號記錄只有一次，說明沒有 沖正過，然后進行沖正。 沖正過程中，讀取設備此時余額與繳費金額(或者說就是沖正金額)作對比，依據設備初始金額+繳費金額-扣費金額設備此時余額,假如設備此時余額繳費金額，這說明執(zhí)行了扣費過程，需要進行 reevable 中標志位得更改，也需要修改eic表中得設備余額，這時候用到了扣費記錄表(自己建得)找到扣費金額。那么 設備此時得余額+扣費金額-繳費金額=設備初始金額 、 假如設備此時余額繳費金額 ,這說明繳費之后依舊欠費，沒有

34、進行扣費.這時候只需要向 payfe 中添加記錄，并修改設備金額； 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 ume; e_cunt number； curor te_cso is select bnkcod,paydate，sum（amy)，cont(） from

35、 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 ankcode1,ydae1,totl_mney1，otal_coun1； ttal_coun1:=totalcont1-eot2; if talcoun1=tal

36、_cunt and totaloney=total_money en results：=對賬勝利； ese els：=對賬失敗，執(zhí)行對賬明細.; ceck_deal （chec_dat)； ed if； end chemone； 測試截圖 對賬勝利（： pyee 表中數據： 對賬失敗,調用對賬明細： 主要創(chuàng)新點： 主要思路就是用游標把 payfe 表中符合銀行代碼、日期得記錄保存 起來，然后提取出來進行比較。失敗則調用對明細賬模塊,將錯誤信息存儲. 5.對明細帳 代碼: crete or repace procedue check_dil（hck_dat ar) is bbankco varc

37、har（）； 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 wher p、bakeial no i （slt banseial -找到沖正得記錄 與 被沖正得記錄 rm pyfe whre ype=00) and to_chr(、paydat

38、,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_cursor%ntound; if(bbankeria s ull) th nsert int hc_exption values(eckid、etvl，pydate,bbankode，pbakseil,bayf,pp

39、ayee,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,bbakseril,bpayfee，ppayfee，102); end if； end loop； end checkdetal； 測試截圖： 對賬失敗時 checeception 表中數據：100-銀行無此流水號

40、101企業(yè)無此流水號 2銀行與企業(yè)記錄得錢數不等 主要創(chuàng)新點: 主要思路就是將 payf表與銀行記錄表進行全連接,然后進行篩選。 由于一開頭我在構建游標時使用了 whee paydate=chekdate 篩選條件，忽視了全連接之后企業(yè)沒有此流水號記錄，padate 為空得狀況,導致了企業(yè)方無此流水號這種錯誤記錄不會消失。所以將條件改為 to_chr（、padt，yyymmdd)=check_date or t_char(、paydate,yyyymmd） s nll； 4. 程序實現 調用存儲過程代碼: pace datbase； imrt ja、l、calllesttemnt； impor

41、 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； public clas rocdre itpcxeesrap swrt ）gr gnirts（iam iov citt cilbup ;iearo、cbdj、lcr = rei gits ;x：5194：tolal:it：earo:cj = lr gnrts 連接字符串，4919 對應得就是我得 orcl在我得電腦上使用得端口,xe 就是服務名 ;rh = reu gnirs string psswod = r; ry cls、forne（rver)； ap ，resu ,lr（noitcenoctg、reganmreird noitenoc itcennswo