东北大学数据库应用程序设计实践报告Word文件下载.docx
《东北大学数据库应用程序设计实践报告Word文件下载.docx》由会员分享，可在线阅读，更多相关《东北大学数据库应用程序设计实践报告Word文件下载.docx（37页珍藏版）》请在冰豆网上搜索。
（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
addressvarchar2（80）,
telvarchar2（20）
altertableclient
addconstraintPK_CLIENT_IDprimarykey（ID）;
createtabledevice
deviceidnumber（4）,
clientidnumber（4）,
typechar
（2）,
balancenumber（7,2）
altertabledevice
addconstraintPK_DEVICE_DEVICEIDprimarykey（DEVICEID）;
addconstraintFK_DEVICE_CLIENTIDforeignkey（CLIENTID）
referencesclient（ID）;
createtableelectricity
yearmonthchar（6）,
snumnumber（10）
altertableelectricity
addconstraintPK_ELECTRICITY_IDprimarykey（ID）;
addconstraintFK_ELECTRICITY_DEVICEIDforeignkey（DEVICEID）
referencesdevice（DEVICEID）;
createtableRECEIVABLES
basicfeenumber（7,2）,
flagchar
（1）
altertableRECEIVABLES
addconstraintPK_RECEIVABLES_IDprimarykey（ID）;
addconstraintFK_RECEIVABLES_DEVICEIDforeignkey（DEVICEID）
createtablePAYFEE
paymoneynumber（7,2）,
paydatedate,
bankcodechar
（2）,
typechar（4）,
bankserialvarchar2（20）
altertablePAYFEE
addconstraintPK_PAYFEE_IDprimarykey（ID）;
addconstraintFK_PAYFEE_DEVICEIDforeignkey（DEVICEID）
addconstraintFK_PAYFEE_BANKCODEforeignkey（BANKCODE）
referencesBANK（CODE）;
createtableBANKRECORD
payfeenumber（7,2）,
altertableBANKRECORD
addconstraintPK_BANKRECORD_IDprimarykey（ID）;
addconstraintFK_BANKRECORD_BANKCODEforeignkey（BANKCODE）
createtableCHECKRESULT
checkdatedate,
banktotalcountnumber（4）,
banktotalmoneynumber（10,2）,
ourtotalcountnumber（4）,
ourtotalmoneynumber（10,2）
altertablecHECKRESULT
addconstraintPK_CHECKRESULT_IDprimarykey（ID）;
altertableCHECKRESULT
addconstraintFK_CHECKRESULT_BANKCODEforeignkey（BANKCODE）
createtablecheck_exception
bankserialvarchar2（20）,
bankmoneynumber（7,2）,
ourmoneynumber（7,2）,
exceptiontypechar（3）
altertablecheck_exception
addconstraintPK_CHECKEXCEPTION_IDprimarykey（ID）;
altertableCHECK_EXCEPTION
addconstraintFK_CHECKEXCEPTION_BANKCODEforeignkey（BANKCODE）
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,'
）='
201608'
groupbymonth
--实收费用
selectyearmonth,sum（basicfee）asreceivableMoney
fromreceivables
groupbyyearmonth
havingyearmonth='
--应收费用
（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='
--月份条件
groupbya.clientid
selects0.*
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,'
groupbyto_char（paydate,'
orderbycount（bankserial）desc
whererownum<
2;
--查询8月份付款人数最多的一天
（8）按设备类型使用人数从高到低排序查询列出设备类型，使用人数。
selectdevice.type,count（*）asnum
fromdevice
groupbydevice.type
orderbycount（*）desc
（9）统计每个月各银行缴费人次，从高到低排序。
selectto_char（paydate,'
）yearmonth,bank.name,count（payfee.id）num
frombankjoinpayfeeonbank.code=payfee.bankcode
）,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+basicfee;
=smoney+basicfee*0.08;
ifdtype='
01'
then
=smoney+basicfee*0.1;
else
=smoney+basicfee*0.15;
endif;
selectround（sysdate-add_months（to_date（yearmonth,'
）,1））
intodays
fromdual;
selectTO_CHAR（SYSDATE,'
DDD'
）intodays2fromdual;
ifdays>
0then
ifdtype='
then--居民违约金跨年与不跨年违约金比例相同
=smoney+basicfee*0.001*days;
else
ifdays<
days2then--其他，不跨年
=smoney+basicfee*0.002*days;
else--其他，跨年
=smoney+basicfee*0.002*（days-days2）+basicfee*0.003*（days2）;
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;
elsif（smoney!
=0）then--设备余额不够缴费
=smoney-d_balance;
endqueryfee1;
代码2：
（按用户号获得设备号，将设备号传入代码1的存储过程中）
createorreplaceprocedureQueryFee（clientnoinclient.id%type,smoneyoutnumber）is
devicenonumber;
d_smoneynumber;
selectd.deviceid
fromclientc,deviced
wherec.id=d.clientid
andc.id=clientno;
fetchtemp_cursorintodeviceno;
queryfee1（deviceno,d_smoney）;
=smoney+d_smoney;
endQueryFee;
测试截图：
设备6的应收费用表：
查询设备6的欠费金额：
主要创新点：
1.我将修改标志位flag和扣费的过程写在了此存储过程中。
查询时如果设备余额大于欠费数，则用余额对设备进行缴费，更新flag=2（第二天凌晨所有的flag=2更改为1）是为了标志是今天的扣费修改过程，方便冲正。
2.我新设置了一个表，bankrecord用来记录扣费记录，方便冲正的时候将设备金额变回来。
3.在计算跨年费用时，我使用了selectTO_CHAR（SYSDATE,'
首先判断当前时间是一年中的第几天，再根据老师的代码，设备欠费天数days作比较。
如果days>
days2,说明存在跨年的欠费，否则不存在。
2.缴费
代码：
（添加记录到payfee表中，并更改设备余额）
createorreplaceprocedurepayfee1（devicenoinnumber,paymoneyinnumber,resultsoutvarchar）is
paydatedate;
ifpaymoney>
selecttrunc（sysdate）intopaydatefromdual;
insertintopayfeevalues（paysequence.nextval,deviceno,paymoney,paydate,19,2001,bankserial.nextval）;
updatedevicesetbala
升级会员 