东北大学数据库应用程序设计实践报告Word文件下载.docx

1、（3）缴费 在当月电费清单生成完毕后，用户可进行电费缴纳，缴纳金额可是任意金额。系统将缴费金额存入设备余额中，再次查询则欠费应该减少。（4）冲正 用户在缴费过程中如果给其他用户缴费了，在当日0点前可以冲正，即把钱收回，放入余额，向payfee表中添加一个负数金额、相同银行流水号的记录。并且修改设备余额，此时查询欠费应该有改变。 （5）对帐 每个银行每日凌晨给电力公司的代缴费系统发送对账信息，代缴费系统记录对账结果，对账明细，对账异常信息进行存储。错误信息为100银行没有此记录。101企业没有此流水号.102银行企业金额不等。2数据库设计（1）ER图设计：自己设计的ER图：经过老师修正统一的ER

2、图：（2）建表语句 - Create tablecreate table Bank（ id number（4）, name varchar2（20）, code char（2）;- Create/Recreate primary, unique and foreign key constraints alter table Bank add constraint PK_BANK_ID primary key （ID）;alter table BANK add constraint PK_BANK_CODE unique （CODE）;- Create tablecreate table cli

3、ent address varchar2（80）, tel varchar2（20）alter table client add constraint PK_CLIENT_ID primary key （ID）;create table device deviceid number（4）, clientid number（4）, type char（2）, balance number（7,2）alter table device add constraint PK_DEVICE_DEVICEID primary key （DEVICEID）; add constraint FK_DEVICE

4、_CLIENTID foreign key （CLIENTID） references client （ID）;create table electricity yearmonth char（6）, snum number（10）alter table electricity add constraint PK_ELECTRICITY_ID primary key （ID）; add constraint FK_ELECTRICITY_DEVICEID foreign key （DEVICEID） references device （DEVICEID）;create table RECEIV

5、ABLES basicfee number（7,2）, flag char（1）alter table RECEIVABLES add constraint PK_RECEIVABLES_ID primary key （ID）; add constraint FK_RECEIVABLES_DEVICEID foreign key （DEVICEID）create table PAYFEE paymoney number（7,2）, paydate date, bankcode char（2）, type char（4）, bankserial varchar2（20）alter table P

6、AYFEE add constraint PK_PAYFEE_ID primary key （ID）; add constraint FK_PAYFEE_DEVICEID foreign key （DEVICEID） add constraint FK_PAYFEE_BANKCODE foreign key （BANKCODE） references BANK （CODE）;create table BANKRECORD payfee number（7,2）,alter table BANKRECORD add constraint PK_BANKRECORD_ID primary key （

7、ID）; add constraint FK_BANKRECORD_BANKCODE foreign key （BANKCODE）create table CHECKRESULT checkdate date, banktotalcount number（4）, banktotalmoney number（10,2）, ourtotalcount number（4）, ourtotalmoney number（10,2）alter table cHECKRESULT add constraint PK_CHECKRESULT_ID primary key （ID）;alter table CH

8、ECKRESULT add constraint FK_CHECKRESULT_BANKCODE foreign key （BANKCODE）create table check_exception bankserial varchar2（20）, bankmoney number（7,2）, ourmoney number（7,2）, exceptiontype char（3）alter table check_exception add constraint PK_CHECKEXCEPTION_ID primary key （ID）;alter table CHECK_EXCEPTION

9、add constraint FK_CHECKEXCEPTION_BANKCODE foreign key （BANKCODE）3数据库端的系统实现 1.十条sql语句（1）查询出所有欠费用户。（为了使测试方便，修改添加了一些数据，见附录）select a.clientid,c.name,a.deviceid,b.yearmonthfrom device a join receivables b on a.deviceid=b.deviceid join client c on a.clientid=c.idwhere b.flag=0 order by 1,3,4（2）查询出拥有超过2个设备

10、的用户SELECT clientid,nameFROM （SELECT clientid, COUNT（*） CT FROM device GROUP BY clientid） join client on client.id=clientidWHERE CT 2（3）统计电力企业某个月的总应收费用，实收费用select month,sum（paymoney）from（select to_char（paydate,yyyymm） as Month,paymoneyfrom payfee p where to_char（paydate,）=201608group by month-实收费用sel

11、ect yearmonth ,sum（basicfee） as receivableMoneyfrom receivablesgroup by yearmonthhaving yearmonth=-应收费用（4）查询出所有欠费超过半年的用户with s as（select b.deviceid ,count（b.deviceid）from receivables bwhere flag=0group by b.deviceidhaving count（b.deviceid）1-我将题目修改成超过一个月select device.clientid,device.deviceidfrom devi

12、ce join s on device.deviceid=s.deviceidorder by 1,2（5）查询任意用户的欠费总额select clientid,sum（b.basicfee）from device a join receivables b on a.deviceid=b.deviceidwhere clientid=1 group by clientid ,flaghaving flag=0（6）查询出某个月用电量最高的3名用户with s as （select sum（b.snum） as sum_num, a.clientidfrom device ainner join

13、 electricity b on a.deviceid = b.deviceidwhere b.yearmonth = - 月份条件group by a.clientidselect s0.*select s.clientid, s.sum_numfrom sorder by s.sum_num desc）s0where rownum = 3（7）查询出电力企业某个月哪天的缴费人数最多select day,numfrom （ select count（id） as num ,to_char（paydate,yyyymmdd） as day from payfee where to_char（

14、paydate, group by to_char（paydate, order by count（bankserial） descwhere rownum2;-查询8月份付款人数最多的一天（8）按设备类型使用人数从高到低排序查询列出设备类型，使用人数。select device.type,count（*） as numfrom devicegroup by device.typeorder by count（*） desc（9）统计每个月各银行缴费人次，从高到低排序。 select to_char（paydate,） yearmonth,bank.name, count（payfee.id）

15、 num from bank join payfee on bank.code = payfee.bankcode）,bank.name order by yearmonth,num desc; -增加了一条记录，修改了两条记录（10）查询出电力企业所有新增用户（使用设备不足半年）。select client.id,device.deviceidfrom client join device on client.id = device.clientidjoin electricity on device.deviceid = electricity.deviceidgroup by clien

16、t.id,device.deviceidhaving count（yearmonth）0 then if dtype= then -居民 违约金 跨年与不跨年违约金比例相同 =smoney+basicfee*0.001*days; else if daysdays2 then - 其他， 不跨年 =smoney+basicfee*0.002*days; else -其他，跨年=smoney+basicfee*0.002*（days-days2）+basicfee*0.003*（days2）; end loop; select balance into d_balance from device

17、 where deviceid=deviceno; if（smoneydays2 ,说明存在跨年的欠费，否则不存在。2.缴费代码：（添加记录到payfee表中，并更改设备余额） create or replace procedure payfee1（deviceno in number,paymoney in number,results out varchar ） is paydate date; if paymoney select trunc（sysdate） into paydate from dual; insert into payfee values（paysequence.nextval,deviceno,paymoney,paydate,19,2001,bankserial.nextval）; update device set bala