东北大学数据库应用程序设计实践报告Word文件下载.docx
- 文档编号:19910720
- 上传时间:2023-01-12
- 格式:DOCX
- 页数:37
- 大小:731.74KB
东北大学数据库应用程序设计实践报告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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 东北大学 数据库 应用 程序设计 实践 报告