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

课程编号：B080109004数据库应用程序设计实践报告姓名学号班级指导教师开设学期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） 建表语句 - 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 client( id number(4), name varchar2(20), address varchar2(80), tel varchar2(20);- Create/Recreate primary, unique and foreign key constraints alter table client add constraint PK_CLIENT_ID primary key (ID);- Create tablecreate table device( deviceid number(4), clientid number(4), type char(2), balance number(7,2);- Create/Recreate primary, unique and foreign key constraints alter table device add constraint PK_DEVICE_DEVICEID primary key (DEVICEID);alter table device add constraint FK_DEVICE_CLIENTID foreign key (CLIENTID) references client (ID);- Create tablecreate table electricity( id number(4), deviceid number(4), yearmonth char(6), snum number(10);- Create/Recreate primary, unique and foreign key constraints alter table electricity add constraint PK_ELECTRICITY_ID primary key (ID);alter table electricity add constraint FK_ELECTRICITY_DEVICEID foreign key (DEVICEID) references device (DEVICEID);- Create tablecreate table RECEIVABLES( id number(4), yearmonth char(6), deviceid number(4), basicfee number(7,2), flag char(1);- Create/Recreate primary, unique and foreign key constraints alter table RECEIVABLES add constraint PK_RECEIVABLES_ID primary key (ID);alter table RECEIVABLES add constraint FK_RECEIVABLES_DEVICEID foreign key (DEVICEID) references device (DEVICEID);- Create tablecreate table PAYFEE( id number(4), deviceid number(4), paymoney number(7,2), paydate date, bankcode char(2), type char(4), bankserial varchar2(20);- Create/Recreate primary, unique and foreign key constraints alter table PAYFEE add constraint PK_PAYFEE_ID primary key (ID);alter table PAYFEE add constraint FK_PAYFEE_DEVICEID foreign key (DEVICEID) references device (DEVICEID);alter table PAYFEE add constraint FK_PAYFEE_BANKCODE foreign key (BANKCODE) references BANK (CODE);- Create tablecreate table BANKRECORD( id number(4), payfee number(7,2), bankcode char(2), bankserial varchar2(20);- Create/Recreate primary, unique and foreign key constraints alter table BANKRECORD add constraint PK_BANKRECORD_ID primary key (ID);alter table BANKRECORD add constraint FK_BANKRECORD_BANKCODE foreign key (BANKCODE) references BANK (CODE);- Create tablecreate table CHECKRESULT( id number(4), checkdate date, bankcode char(2), banktotalcount number(4), banktotalmoney number(10,2), ourtotalcount number(4), ourtotalmoney number(10,2);- Create/Recreate primary, unique and foreign key constraints alter table cHECKRESULT add constraint PK_CHECKRESULT_ID primary key (ID);alter table CHECKRESULT add constraint FK_CHECKRESULT_BANKCODE foreign key (BANKCODE) references BANK (CODE);- Create tablecreate table check_exception( id number(4), checkdate date, bankcode char(2), bankserial varchar2(20), bankmoney number(7,2), ourmoney number(7,2), exceptiontype char(3);- Create/Recreate primary, unique and foreign key constraints alter table check_exception add constraint PK_CHECKEXCEPTION_ID primary key (ID);alter table CHECK_EXCEPTION add constraint FK_CHECKEXCEPTION_BANKCODE foreign key (BANKCODE) references BANK (CODE);3数据库端的系统实现 1.十条sql语句（1） 查询出所有欠费用户。（为了使测试方便，修改添加了一些数据，见附录）select a.clientid,,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个设备的用户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,yyyymm)=201608)group by month-实收费用select yearmonth ,sum(basicfee) as receivableMoneyfrom receivablesgroup by yearmonthhaving yearmonth=201608-应收费用（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 device 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 electricity b on a.deviceid = b.deviceidwhere b.yearmonth = 201608 - 月份条件group by a.clientid)select s0.*from(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(paydate,yyyymm)=201608 group by to_char(paydate,yyyymmdd) order by count(bankserial) desc)where rownum2;-查询8月份付款人数最多的一天（8） 按设备类型使用人数从高到低排序查询列出设备类型，使用人数。select device.type,count(*) as numfrom devicegroup by device.typeorder by count(*) desc（9） 统计每个月各银行缴费人次，从高到低排序。 select to_char(paydate,yyyymm) yearmonth,, count(payfee.id) num from bank join payfee on bank.code = payfee.bankcode group by to_char(paydate,yyyymm), 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 client.id,device.deviceidhaving count(yearmonth)0 then if dtype=01 then -居民 违约金 跨年与不跨年违约金比例相同 smoney:=smoney+basicfee*0.001*days; else if daysdays2 then - 其他， 不跨年 smoney:=smoney+basicfee*0.002*days; else -其他，跨年 smoney:=smoney+basicfee*0.002*(days-days2)+basicfee*0.003*(days2); end if; end if; end if; end loop; select balance into d_balance from device where deviceid=deviceno; if(smoneydays2 ,说明存在跨年的欠费，否则不存在。2. 缴费代码：（添加记录到payfee表中，并更改设备余额） create or replace procedure payfee1(deviceno in number,paymoney in number,results out varchar ) is paydate date;begin if paymoney0 then select trunc(sysdate) into paydate from dual;-截取到日 insert into payfee values(paysequence.nextval,deviceno,paymoney,paydate,19,2001,bankserial.nextval); update device set balance=balance+paymoney where deviceid=deviceno; results:=成功; else results:=缴费失败，缴费金额不能少于0; end if; end payfee1;测试截图缴费之后的payfee表：（增加了一条付费记录）再次查询欠费金额：（欠费金额为0）缴费之后的设备表：（余额由3变成58.8）设备6应收费用表：（flag=2用来标志是今天刚刚缴费的，方便冲正。）主要创新点：按照设备号进行缴费，向payfee中添加记录。使用序列来控制流水号，和id。3. 冲正代码create or replace procedure Reverse ( re_bankserial in payfee.bankserial%type,results out varchar) is deviceno number; re_money number; -冲正钱数 d_balance number;-设备余额 d_smoney number; -设备扣费前的余额 re_bankcode char(2); re_date date; paydate date; cursor temp_cursor is select p.deviceid,p.paymoney,p.bankcode,p.paydate from payfee p where p.bankserial=re_bankserial and p.bankserial in ( select bankserial from payfee group by bankserial having count(*)=1);-没被冲正过的记录begin open temp_cursor; fetch temp_cursor into deviceno,re_money,re_bankcode,paydate; select trunc(sysdate) into re_date from dual;-截取到日 if temp_cursor%NOTFOUND -判断有无数据 then results :=失败,没有找到此流水号; elsif paydate!=re_date then results:=失败，不是本日记录。; else insert into payfee values(paysequence.nextval,deviceno,0-re_money,re_date,re_bankcode,2000,re_bankserial); select balance into d_balance from device where deviceid=deviceno;-取出设备的余额 if(d_balancere_money) then -设备余额大于充值钱数 说明充值的钱数不够，flag不变 update device set balance=balance-re_money where deviceid=deviceno; results :=成功; else -设备余额小于充值钱数，说明充值之后够了，并且扣费成功了 update receivables set flag = 0 where deviceid=deviceno and flag=2; select distinct money into d_smoney from devicerecord where deviceid=deviceno and chargedate=paydate; update device set balance=balance+d_smoney-re_money where deviceid=deviceno; results :=成功; end if; end if;end Reverse ;测试截图将刚刚为6的缴费冲正。此时的payfee表：（增加了一条冲正记录）应收费用表：（flag标志为变成0）设备表：（回到缴费前3元状态）查询设备6的欠费情况：（回到缴费前的状态，仍然欠费141.2）主要创新点：实验的主要思路是主要确定有一致的缴费记录，并且流水号记录只有一次，说明没有冲正过，然后进行冲正。冲正过程中，读取设备此时余额与缴费金额（或者说是冲正金额）作对比，根据“设备初始金额+缴费金额-扣费金额=设备此时余额”，如果设备此时余额缴费金额 ，这说明缴费之后依然欠费，没有进行扣费。这时候只需要向payfee中添加记录，并修改设备金额；4. 对总账代码create or replace procedure checkmoney(check_bankcode in char,total_count in number, total_money in number,check_date in varchar,results out varchar) is bankcode1 varchar(2); paydate1 date; total_money1 number; total_count1 number; re_count number; cursor temp_cursor is select bankcode,paydate,sum(paymoney),count(*) from payfee group by bankcode,paydate having bankcode=check_bankcode and to_char(paydate,yyyymmdd)=check_date; begin select count(*) into re_count from payfee where bankcode=check_bankcode and to_char(paydate,yyyymmdd)=check_date and paymoney0; open temp_cursor; fetch temp_cursor into bankcode1,paydate1,total_money1,total_count1; total_count1:=total_count1-re_count*2; if total_count1=total_count and total_money1=total_money then results:=对账成功; else results:=对账失败,执行对账明细。; check_detail (check_date); end if; end checkmoney;测试截图对账成功（：Payfee表中数据：对账失败，调用对账明细：主要创新点：主要思路是用游标把payfee表中符合银行代码、日期的记录保存起来，然后提取出来进行比较。失败则调用对明细账模块，将错误信息存储。5. 对明细帐代码：create or replace procedure check_detail(check_date in char) is Bbankcode varchar(2); Bpayfee number; Bbankserial varchar(20); Pbankserial varchar(20); Ppayfee number; Ppaydate date; cursor temp_cursor is select b.bankcode,b.payfee ,b.bankserial,p.bankserial,p.paymoney from bankrecord b full outer join payfee p on p.bankserial=b.bankserial where p.bankserial not in (select bankserial-找到冲正的记录 和 被冲正的记录 from payfee where type=2000) and to_char(p.paydate,yyyymmdd)=check_date or to_char(p.paydate,yyyymmdd) is null; begin select trunc(sysdate) into Ppaydate from dual;-截取到日 open temp_cursor; loop fetch temp_cursor into BBankcode,Bpayfee,Bbankserial,Pbankserial,Ppayfee; exit when temp_cursor%notfound; if(Bbankserial is null) then insert into check_exception values(checkid.nextval,ppaydate,Bbankcode,Pbankserial,Bpayfee,Ppayfee,100); elsif(Pbankserial is null) then insert into check_exception values(checkid.nextval,Ppaydate,Bbankcode,Bbankserial,Bpayfee,Ppayfee,101); elsif(Bpayfee!=Ppayfee) then insert into check_exception values(checkid.nextval,Ppaydate,Bbankcode,Bbankserial,Bpayfee,Ppayfee,102); end if; end loop;end check_detail;测试截图：对账失败时Checkexception表中数据：100-银行无此流水号 101-企业无此流水号 102-银行与企业记录的钱数不等主要创新点：主要思路是将payfee表和银行记录表进行全连接，然后进行筛选。因为一开始我在构建游标时使用了where paydate=check_date筛选条件，忽略了全连接之后企业没有此流水号记录，paydate 为空的情况，导致了企业方无此流水号这种错误记录不会出现。所以将条件改为to_char(p.paydate,yyyymmdd)=check_date or to_char(p.paydate,yyyymmdd) is null; 4程序实现调用存储过程代码：package database;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.sql.Types;import java.text.ParseException;import java.text.SimpleDateFormat;import java.util.Date;import java.util.Scanner;public class procedure public static void main(String args) throws ParseExceptionString driver = oracle.jdbc.OracleDriver;String url = jdbc:oracle:thin:localhost:49159:XE;/连接字符串，49159对应的是我的oracle在我的电脑上使用的端口，XE是服务名String user = hr;String password = hr;try Class.forName(driver);Connection connection = DriverManager.getConnection(url, user, password);System.out.println(连接成功);boolean login = true;Scanner scanner = new Scanner(System.in);int choice;CallableStatement cs;String bankSerial = null;String result;String bankCode;String date1 = null;int totalNum;int totalMoney;String date;int deviceno; int payMoney;while(login) System.out.println(1.查询);System.out.println(2.缴费);System.out.println(3.冲正);System.out.println(4.对总帐);System.out.println(5.对明细账);System.out.println(0.退出);System.out.println(请输入选项：);choice = scanner.nextInt();switch (choice) case 1:System.out.println(请输入客户号：);int customerno = scanner.nextInt();cs = connection.prepareCall(call QUERYFEE(?,?);cs.setInt(1, customerno);cs.registerOutParameter(2, Types.NU