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

1、东北大学数据库应用程序设计实践报告 课程编号： b080 0 0 004 数据库应用程序设计实践报告 姓 名 学 号 班 级 指 导 教 师 开 设 学 期 0 0 6 6- -2 2 1 17 7 第一学 期 开 设 时 间 第 周 - - 第 第 5 15 周 报 告 日 期 201 /12 16 评 定 成 绩 评 定 人 评 定 日 期 东北大学软件学院 1. 问题定义 银行代收费系统给电力公司开发得一套缴费系统，方便用户通过网银支付电费. 主要得用例图: 图 1 银行代收费系统用例图 根据用例图得出主要得业务需求： （1）抄表 系统管理员把抄表记录录入系统,抄表记录包括当前电表数、抄

2、表日期、抄表人等信息,根据抄表记录，系统自动计算每个计费设备当月得应收电费。每个计费设备有唯一 编号。 （2）查询 用户随时查询欠费金额。一个用户名下可能多个计费设备，查询欠费时，将所有计费设备欠费总与输出。需要考虑设备得余额问题。如果余额大于欠费,则欠费为 0，更新余额,修改 reciable 中 fag 标志。 （3）缴费 在当月电费清单生成完毕后，用户可进行电费缴纳，缴纳金额可就是任意金额.系统将缴费金额存入设备余额中，再次查询则欠费应该减少。 （4）冲正 用户在缴费过程中如果给其她用户缴费了,在当日 0 点前可以冲正，即把钱收回,放入余额，向 pyfee 表中添加一个负数金额、相同银行

3、流水号得记录.并且修改设备余额，此时查询欠费应该有改变。 (5）对帐 每个银行每日凌晨给电力公司得代缴费系统发送对账信息,代缴费系统记录对账结果,对账明细，对账异常信息进行存储。错误信息为 10银行没有此记录。10企业没有此流水号、2 银行企业金额不等。 2 数据库设计 （1）er 图设计: 自己设计得 e图: 经过老师修正统一得 er 图： 客户teladdress name设备deviceid拥有费用（应收）产生费用（实缴）缴纳银行缴给idnameflag idyearmonth paydate bankserial type paymoney idcodeidtype balance 抄

4、表记录产生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 数据库端得系统实现 条 、十条 s l 语句 （ （1） ） 查询出所有欠费用户. （为了使测试方便, 修改添加了一些数据，见附录） 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 个设备得用户 selc clintid，m rom （slect lienti， count（） ct from dvi grop by intid） jin cliet on cint、id=cenid her ct 2 （ （3） ） 统计电力企业 某个月 得总应收费用，实收费用 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 -应收费用 （ （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） ） 查询任意用户得欠费总额 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） ） 查询出电力企业某个月哪天得缴费人数最多 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） ） 统计每个月各银行缴费人次，从高到低排序。 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） ） 查询出电力企业所有新增用户（使用设备不足半年). 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； -如果某个设备得抄表记录数小于 6,则其使用不足半年 附录： 添加修改得数据: 1、在ece 表下,加入数据如图： 2、在 reivable表中，加入四个设备两个月得应收记录如图： 3.在 paye 里加入201 设备 2609 得付款记录 4、向lectrici中插入数据，如下图,比较用户. 5.将ayfe 中得部分记录得 bakcoe 更改，订单日期也进行更改 6.增加记录到 bak 表中。 2 、事物存储过程 （） 查询 码 代码 1: （按设备号进行查询，在代码 2 中被调用) 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 -如果设备余额大于欠费 余额更新、欠费置 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：smoey

25、d_balane； nd f; e quryfee1； 代码 : （按用户号获得设备号，将设备号传入代码 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; 测试截图： 设备 6 得应收费用表: 查询设备 6 得欠费金额： 主要创新点： 1.我将修改标志位g 与扣费得过程写在了此存储过程中.查询时如果设备余额大于欠费数，则用余额对设备进行缴费,更新 flag=2（第二天凌晨所有得lag=更改为 1）就是为 了标志就是今天得扣费修改过程，方便冲正。 2.我新设置了一个表，bankrrd 用来记录扣费记录,方便冲正得时候将设备金额变回来。 3、在计算跨年费用时，我使用了 s

27、ele to_char(sysdate,"dd") no ays from dul； 首先判断当前时间就是一年中得第几天,再根据老师得代码，设备欠费天数 days 作比较. 如果 dayda2 ,说明存在跨年得欠费，否则不存在。 2. 缴费 代码：（添加记录到yf 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

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） 缴费之后得设备表：（余额由 3 变成8、) 设备 6 应收费用表：（flg

29、=用来标志就是今天刚刚缴费得,方便冲正。） 主要创新点: 按照设备号进行缴费,向 pafee 中添加记录。 使用序列来控制流水号，与。 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、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 -判断有无数据 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； 取出设备得余额 i（dbaancee_mon） then -设备余额大于充值钱数 说明充值得钱数不够，flag 不变 update device et baane=blancre_oey wher dvicid=dviceo

32、; reult ：=成功"； ele -设备余额小于充值钱数，说明充值之后够了，并且扣费成功了 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 表:(增加了一条冲正记录） 应收费用表:（lag 标志为变成 0) 设备表:（回到缴费前 3 元状态) 查询设备 6 得欠费情况:（回到缴费前得状态，仍然欠费 11、2) 主要创新点： 实验得主要思路就是主要确定有一致得缴费记录，并且流水号记录只有一次，说明没有 冲正过，然后进行冲正。 冲正过程中，读取设备此时余额与缴费金额(或者说就是冲正金额)作对比，根据"设备初始金额+缴费金额-扣费金额设备此时余额',如果设备此时余额缴费金额，这说明执行了扣费过程，需要进行 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 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：=对账失败，执行对账明细.; ceck_deal （chec_dat)； ed if； end chemone； 测试截图 对账成功（： pyee 表中数据： 对账失败,调用对账明细： 主要创新点： 主要思路就是用游标把 payfe 表中符合银行代码、日期得记录保存 起来，然后提取出来进行比较。失败则调用

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 -找到冲正得记录 与 被冲正得记录 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 表中数据：100-银行无此流水号 101企业无此流水号 2银行与企业记录得钱数不等 主要创新点: 主要思路就是将 payf表与银行记录表进行全连接,然后进行筛选。 因为一开始我在构建游标时使用了 whee paydate=chekdate 筛选条件，忽略了全连接之后企业没有此流水号记录，padate 为空得情况,导致了企业方无此流水号这种错误记录不会出现。所以将条件改为 to_chr（、padt，yyymmdd&qu

41、ot;)=check_date or t_char(、paydate,"yyyymmd"） s nll； 4. 程序实现 调用存储过程代码: 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 对应得就是我得 orcl在我得电脑上使用得端口,xe 就是服务名 ;'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