东北大学数据库应用程序设计实践报告.docx
《东北大学数据库应用程序设计实践报告.docx》由会员分享，可在线阅读，更多相关《东北大学数据库应用程序设计实践报告.docx（28页珍藏版）》请在冰点文库上搜索。
东北大学数据库应用程序设计实践报告
课程编号：
数据库应用程序设计实践报告
姓名
学号
班级
指导教师
开设学期
2016-2017第一学期
开设时间
第13周——第15周
报告日期
2016/12/16
评定成绩
评定人
评定日期
东北大学软件学院
1.问题定义
银行代收费系统给电力公司开发的一套缴费系统，方便用户通过网银支付电费。
主要的用例图：
图1银行代收费系统用例图
根据用例图得出主要的业务需求：
（1）抄表
系统管理员把抄表记录录入系统，抄表记录包括当前电表数、抄表日期、抄表人等信息，根据抄表记录，系统自动计算每个计费设备当月的应收电费。
每个计费设备有唯一编号。
（2）查询
用户随时查询欠费金额。
一个用户名下可能多个计费设备，查询欠费时，将所有计费设备欠费总和输出。
需要考虑设备的余额问题。
如果余额大于欠费，则欠费为0，更新余额，修改receivable中flag标志。
（3）缴费
在当月电费清单生成完毕后，用户可进行电费缴纳，缴纳金额可是任意金额。
系统将缴费金额存入设备余额中，再次查询则欠费应该减少。
（4）冲正
用户在缴费过程中如果给其他用户缴费了，在当日0点前可以冲正，即把钱收回，放入余额，向payfee表中添加一个负数金额、相同银行流水号的记录。
并且修改设备余额，此时查询欠费应该有改变。
（5）对帐
每个银行每日凌晨给电力公司的代缴费系统发送对账信息，代缴费系统记录对账结果，对账明细，对账异常信息进行存储。
错误信息为100银行没有此记录。
101企业没有此流水号.102银行企业金额不等。
2．数据库设计
（1）ER图设计：
自己设计的ER图：
经过老师修正统一的ER图：
（2）建表语句
--Createtable
createtableBank
（
idnumber（4）,
namevarchar2（20）,
codechar
（2）
）
;
--Create/Recreateprimary,uniqueandforeignkeyconstraints
altertableBank
addconstraintPK_BANK_IDprimarykey（ID）;
altertableBANK
addconstraintPK_BANK_CODEunique（CODE）;
--Createtable
createtableclient
（
idnumber（4）,
namevarchar2（20）,
addressvarchar2（80）,
telvarchar2（20）
）
;
--Create/Recreateprimary,uniqueandforeignkeyconstraints
altertableclient
addconstraintPK_CLIENT_IDprimarykey（ID）;
--Createtable
createtabledevice
（
deviceidnumber（4）,
clientidnumber（4）,
typechar
（2）,
balancenumber（7,2）
）
;
--Create/Recreateprimary,uniqueandforeignkeyconstraints
altertabledevice
addconstraintPK_DEVICE_DEVICEIDprimarykey（DEVICEID）;
altertabledevice
addconstraintFK_DEVICE_CLIENTIDforeignkey（CLIENTID）
referencesclient（ID）;
--Createtable
createtableelectricity
（
idnumber（4）,
deviceidnumber（4）,
yearmonthchar（6）,
snumnumber（10）
）
;
--Create/Recreateprimary,uniqueandforeignkeyconstraints
altertableelectricity
addconstraintPK_ELECTRICITY_IDprimarykey（ID）;
altertableelectricity
addconstraintFK_ELECTRICITY_DEVICEIDforeignkey（DEVICEID）
referencesdevice（DEVICEID）;
--Createtable
createtableRECEIVABLES
（
idnumber（4）,
yearmonthchar（6）,
deviceidnumber（4）,
basicfeenumber（7,2）,
flagchar
（1）
）
;
--Create/Recreateprimary,uniqueandforeignkeyconstraints
altertableRECEIVABLES
addconstraintPK_RECEIVABLES_IDprimarykey（ID）;
altertableRECEIVABLES
addconstraintFK_RECEIVABLES_DEVICEIDforeignkey（DEVICEID）
referencesdevice（DEVICEID）;
--Createtable
createtablePAYFEE
（
idnumber（4）,
deviceidnumber（4）,
paymoneynumber（7,2）,
paydatedate,
bankcodechar
（2）,
typechar（4）,
bankserialvarchar2（20）
）
;
--Create/Recreateprimary,uniqueandforeignkeyconstraints
altertablePAYFEE
addconstraintPK_PAYFEE_IDprimarykey（ID）;
altertablePAYFEE
addconstraintFK_PAYFEE_DEVICEIDforeignkey（DEVICEID）
referencesdevice（DEVICEID）;
altertablePAYFEE
addconstraintFK_PAYFEE_BANKCODEforeignkey（BANKCODE）
referencesBANK（CODE）;
--Createtable
createtableBANKRECORD
（
idnumber（4）,
payfeenumber（7,2）,
bankcodechar
（2）,
bankserialvarchar2（20）
）
;
--Create/Recreateprimary,uniqueandforeignkeyconstraints
altertableBANKRECORD
addconstraintPK_BANKRECORD_IDprimarykey（ID）;
altertableBANKRECORD
addconstraintFK_BANKRECORD_BANKCODEforeignkey（BANKCODE）
referencesBANK（CODE）;
--Createtable
createtableCHECKRESULT
（
idnumber（4）,
checkdatedate,
bankcodechar
（2）,
banktotalcountnumber（4）,
banktotalmoneynumber（10,2）,
ourtotalcountnumber（4）,
ourtotalmoneynumber（10,2）
）
;
--Create/Recreateprimary,uniqueandforeignkeyconstraints
altertablecHECKRESULT
addconstraintPK_CHECKRESULT_IDprimarykey（ID）;
altertableCHECKRESULT
addconstraintFK_CHECKRESULT_BANKCODEforeignkey（BANKCODE）
referencesBANK（CODE）;
--Createtable
createtablecheck_exception
（
idnumber（4）,
checkdatedate,
bankcodechar
（2）,
bankserialvarchar2（20）,
bankmoneynumber（7,2）,
ourmoneynumber（7,2）,
exceptiontypechar（3）
）
;
--Create/Recreateprimary,uniqueandforeignkeyconstraints
altertablecheck_exception
addconstraintPK_CHECKEXCEPTION_IDprimarykey（ID）;
altertableCHECK_EXCEPTION
addconstraintFK_CHECKEXCEPTION_BANKCODEforeignkey（BANKCODE）
referencesBANK（CODE）;
3．数据库端的系统实现
1.十条sql语句
（1）查询出所有欠费用户。
（为了使测试方便，修改添加了一些数据，见附录）
selecta.clientid,c.name,a.deviceid,b.yearmonth
fromdeviceajoinreceivablesbona.deviceid=b.deviceidjoinclientcona.clientid=c.id
whereb.flag=0
orderby1,3,4
（2）查询出拥有超过2个设备的用户
SELECTclientid,name
FROM（SELECTclientid,COUNT（*）CTFROMdeviceGROUPBYclientid）joinclientonclient.id=clientid
WHERECT>2
（3）统计电力企业某个月的总应收费用，实收费用
selectmonth,sum（paymoney）
from（
selectto_char（paydate,'yyyymm'）asMonth,paymoney
frompayfeep
whereto_char（paydate,'yyyymm'）='201608'
）
groupbymonth
--实收费用
selectyearmonth,sum（basicfee）asreceivableMoney
fromreceivables
groupbyyearmonth
havingyearmonth='201608'
--应收费用
（4）查询出所有欠费超过半年的用户
withsas（
selectb.deviceid,count（b.deviceid）
fromreceivablesb
whereflag=0
groupbyb.deviceid
havingcount（b.deviceid）>1--我将题目修改成超过一个月
）
selectdevice.clientid,device.deviceid
fromdevicejoinsondevice.deviceid=s.deviceid
orderby1,2
（5）查询任意用户的欠费总额
selectclientid,sum（b.basicfee）
fromdeviceajoinreceivablesbona.deviceid=b.deviceid
whereclientid=1
groupbyclientid,flag
havingflag=0
（6）查询出某个月用电量最高的3名用户
withsas（
selectsum（b.snum）assum_num,a.clientid
fromdevicea
innerjoinelectricitybona.deviceid=b.deviceid
whereb.yearmonth='201608'--月份条件
groupbya.clientid
）
selects0.*
from（
selects.clientid,s.sum_num
froms
orderbys.sum_numdesc）s0
whererownum<=3
（7）查询出电力企业某个月哪天的缴费人数最多
selectday,num
from
（selectcount（id）asnum,to_char（paydate,'yyyymmdd'）asday
frompayfee
whereto_char（paydate,'yyyymm'）='201608'
groupbyto_char（paydate,'yyyymmdd'）
orderbycount（bankserial）desc
）
whererownum<2;--查询8月份付款人数最多的一天
（8）按设备类型使用人数从高到低排序查询列出设备类型，使用人数。
selectdevice.type,count（*）asnum
fromdevice
groupbydevice.type
orderbycount（*）desc
（9）统计每个月各银行缴费人次，从高到低排序。
selectto_char（paydate,'yyyymm'）yearmonth,bank.name,count（payfee.id）num
frombankjoinpayfeeonbank.code=payfee.bankcode
groupbyto_char（paydate,'yyyymm'）,bank.name
orderbyyearmonth,numdesc;
--增加了一条记录，修改了两条记录
（10）查询出电力企业所有新增用户（使用设备不足半年）。
selectclient.id,device.deviceid
fromclientjoindeviceonclient.id=device.clientid
joinelectricityondevice.deviceid=electricity.deviceid
groupbyclient.id,device.deviceid
havingcount（yearmonth）<6;
--如果某个设备的抄表记录数小于6，则其使用不足半年
附录：
添加修改的数据：
1.在device表下，加入数据如图：
2.在receivables表中，加入四个设备两个月的应收记录如图：
3.在payfee里加入1201设备201609的付款记录
4.向electricity中插入数据，如下图，比较用户。
5.将payfee中的部分记录的bankcode更改，订单日期也进行更改
6.增加记录到bank表中。
2.事物存储过程
（1）查询
代码1：
（按设备号进行查询，在代码2中被调用）
createorreplaceprocedurequeryfee1（devicenoinnumber,smoneyoutnumber）is
basicfeenumber（7,2）;
yearmonthreceivables.yearmonth%type;
dtypedevice.type%type;
daysnumber;
days2number;
d_balancenumber;
ridnumber;
chargedatedate;
cursortemp_cursoris
selectr.basicfee,r.yearmonth,d.type,r.id
fromdeviced,receivablesr
whered.deviceid=r.deviceid
andr.flag=0
andd.deviceid=deviceno;
begin
smoney:
=0;
opentemp_cursor;
loop
fetchtemp_cursorintobasicfee,yearmonth,dtype,rid;
exitwhentemp_cursor%notfound;
smoney:
=smoney+basicfee;
smoney:
=smoney+basicfee*0.08;
ifdtype='01'then
smoney:
=smoney+basicfee*0.1;
else
smoney:
=smoney+basicfee*0.15;
endif;
selectround（sysdate-add_months（to_date（yearmonth,'yyyymm'）,1））
intodays
fromdual;
selectTO_CHAR（SYSDATE,'DDD'）intodays2fromdual;
ifdays>0then
ifdtype='01'then--居民违约金跨年与不跨年违约金比例相同
smoney:
=smoney+basicfee*0.001*days;
else
ifdayssmoney:
=smoney+basicfee*0.002*days;
else--其他，跨年
smoney:
=smoney+basicfee*0.002*（days-days2）+basicfee*0.003*（days2）;
endif;
endif;
endif;
endloop;
selectbalanceintod_balancefromdevicewheredeviceid=deviceno;
if（smoney<=d_balanceandsmoney!
=0）then--如果设备余额大于欠费余额更新、欠费置0
selecttrunc（sysdate）intochargedatefromdual;--截取到日
insertintodevicerecordvalues（deviceno,smoney,rid，chargedate）;--把设备扣费记录保存保存
updatedevicesetbalance=balance-smoneywheredeviceid=deviceno;
updatereceivablessetflag=2whereid=rid;
smoney:
=0;
elsif（smoney!
=0）then--设备余额不够缴费
smoney:
=smoney-d_balance;
endif;
endqueryfee1;
代码2：
（按用户号获得设备号，将设备号传入代码1的存储过程中）
createorreplaceprocedureQueryFee（clientnoinclient.id%type,smoneyoutnumber）is
devicenonumber;
d_smoneynumber;
cursortemp_cursoris
selectd.deviceid
fromclientc,deviced
wherec.id=d.clientid
andc.id=clientno;
begin
smoney:
=0;
opentemp_cursor;
loop
fetchtemp_cursorintodeviceno;
exitwhentemp_cursor%notfound;
queryfee1（deviceno,d_smoney）;
smoney:
=smoney+d_smoney;
endloop;
endQueryFee;
测试截图：
设备6的应收费用表：
查询设备6的欠费金额：
主要创新点：
1.我将修改标志位flag和扣费的过程写在了此存储过程中。
查询时如果设备余额大于欠费数，则用余额对设备进行缴费，更新flag=2（第二天凌晨所有的flag=2更改为1）是为了标志是今天的扣费修改过程，方便冲正。
2.我新设置了一个表，bankrecord用来记录扣费记录，方便冲正的时候将设备金额变回来。
3.在计算跨年费用时，我使用了selectTO_CHAR（SYSDATE,'DDD'）intodays2fromdual;
首先判断当前时间是一年中的第几天，再根据老师的代码，设备欠费天数days作比较。
如果days>days2,说明存在跨年的欠费，否则不存在。
2.缴费
代码：
（添加记录到payfee表中，并更改设备余额）
createorreplaceprocedurepayfee1（devicenoinnumber,paymoneyinnumber,resultsoutvarchar）is
paydatedate;
begin
ifpaymoney>0then
selecttrunc（sysdate）intopaydatefromdual;--截取到日
insertintopayfeevalues（paysequence.nextval,deviceno,paymoney,paydate,19,2001,bankserial.nextval）;
updatedevicesetbalance=balance+paymoneywheredeviceid=deviceno;
results:
='成功';
else
results:
='缴费失败，缴费金额不能少于0';
end
升级会员 