课 程 编 号: B 0
数据库应用程序设计实践
报告
姓 名
学 号
班 级
指导教师
开设学期
2016- 20 17第一学期 开设时间
开设时间
第13周——第15周 报告日期
报告日期
2016/12/16
评定成绩
评 定 人
评定日期
东北大学软件学院
1?问题定义
银行代收费系统给电力公司开发的一套缴费系统,方便用户通过网银支付电费。
主要的用例图:
图1银行代收费系统用例图
根据用例图得出主要的业务需求:
(1) 抄表
系统管理员把抄表记录录入系统,抄表记录包括当前电表数、抄表日期、抄表人等 信息,根据抄表记录,系统自动计算每个计费设备当月的应收电费。每个计费设备有唯 一编号。
(2) 查询
用户随时查询欠费金额。一个用户名下可能多个计费设备,查询欠费时,将所有计 费设备欠费总和输出。需要考虑设备的余额问题。如果余额大于欠费,则欠费为 0,更
新余额,修改receivable 中flag标志。
(3) 缴费
在当月电费清单生成完毕后,用户可进行电费缴纳,缴纳金额可是任意金额。系统 将缴费金额存入设备余额中,再次查询则欠费应该减少。
(4) 冲正
用户在缴费过程中如果给其他用户缴费了,在当日 0点前可以冲正,即把钱收回,
放入余额,向payfee表中添加一个负数金额、相同银行流水号的记录。并且修改设备 余额,此时查询欠费应该有改变。
(5) 对帐
每个银行每日凌晨给电力公司的代缴费系统发送对账信息, 代缴费系统记录对账结
果,对账明细,对账异常信息进行存储。错误信息为 100银行没有此记录。101企业没
有此流水号.102银行企业金额不等。
CitjteXM.SendJ]Possesspayfeebankserialidtypecheckdatepaymoney银行记录telbankserialbanktotalcountnamename缴给banktotalmoney缴纳客户idcodebal;拥有idourtotalmoney产生设备产生flagourmoneyididsnum: bankserial -bankmoneycheckdate建表语句⑵费用(实缴)费用(应收)yearmonthI ”basicfee2.数据库设计(1) ER图设计: 自己设计的ER图:--Create tablecreate table Bank经过老师修正统一的Citjte
X
M.
Send
J]
Possess
payfee
bankserial
id
type
checkdate
paymoney
银行记录
tel
bankserial
banktotalcount
name
name
缴给
banktotalmoney
缴纳
客户
id
code
bal;
拥有
id
ourtotalmoney
产生
设备
产生
flag
ourmoney
id
id
snum
: bankserial -
bankmoney
checkdate
建表语句
⑵
费用(实缴)
费用(应收)
yearmonth
I ”
basicfee
2.数据库设计
(1) ER图设计: 自己设计的ER图:
--Create table
create table Bank
经过老师修正统一的ER图:
” Antic ter —n
〔i岭i紗mer
-Pay Record
^t'nertil
IT l.edger
Ad^lill
Aoneter Record
U国k
liank
Rir-ir'i
银行对账异常表
银行
银行对账总表
抄表记录
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 table
create 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 table
create 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 table
create 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 table
create 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 table create 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 table
create 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 con stra int PK_BANKRECORD_ID primary key (ID);
alter table BANKRECORD
add con strai nt FK_BANKRECORD_BANKCODE foreig n key (BANKCODE) refere nces BANK (CODE);
--Create table
create table CHECKRESULT
(
id n umber(4),
checkdate date,
ban kcode char(2),
ban ktotalcou nt n umber(4),
ban ktotalm oney n umber(10,2), ourtotalcou nt n umber(4), ourtotalm oney n umber(10,2)
) --Create/Recreate primary, uni que and foreig n key con stra ints
alter table cHECKRESULT
add con stra int PK_CHECKRESULT_ID primary key (ID);
alter table CHECKRESULT
add con strai nt FK_CHECKRESULT_BANKCODE foreig n key (BANKCODE) refere nces BANK (CODE);
--Create table
create table check_excepti on (
id n umber(4),
checkdate date, ban kcode char(2),
ban kserial varchar2(20), bankmoney n umber(7,2), ourm oney n umber(7,2),
excepti on type char(3)
) --Create/Recreate primary, uni que and foreig n key con stra ints alter table check_excepti on
add con stra int PK_CHECKEXCEPTION_ID primary key (ID);
alter table CHECK_EXCEPTION
add con strai nt FK_CHECKEXCEPTION_BANKCODE foreig n key (BANKCODE) refere nces BANK (CODE);
数据库端的系统实现
1.十条sql语句
(1)查询出所有欠费用户。(为了使测试方便,修改添加了一些数据,见附录)
select
onfrom device a join receivables b on = join client c where =0
on
order by 1,3,4
查询出拥有超过 2个设备的用户
SELECEIientid , name
FROM( SELECTtlientid , COUNT) CT FROMevice GROUPBYclientid ) join client on =clientid
WHERCT > 2
统计电力企业某个月的总应收费用,实收费用
select month, sum( paymoney)
from (
select to_char ( paydate , 'yyyymm' ) as Month, paymoney from payfee p
where to_char (paydate , 'yyyymm' )= '201608'
)-
group by month
--实收费用
select yearmonth , sum( basicfee ) as receivableMoney from receivables
group by yearmonth
having yearmonth ='201608'
--应收费用
查询出所有欠费超过半年的用户
with s as(
select , count from receivables b
where flag =0
group by
having count >1 --我将题目修改成超过一个月 )
select , from device join s on
order by 1, 2
掘
掘 Eq. -vti甘i ? ak( sefleft lb--drVK'?id CMJrt(bfrarn b-fla^=:O 射存呻 叶 brdev■■- > r I — 討
f l 让 1 43 I'
fl. h >ia^Li c^t d f coiiut fti
irsi i* tee hits b
A -F-i
pr by b. d.*vi z ei d
h“i ng “si (b d■“ E⑴>1此磨榔#辰说Sb 匚H
cl i A-ljl L X dw< L e^i ck-vj 匚
frn dm C4 jcm t kvsct. itn le*ictid
(Mr勒r tiy 2
「称q粘 剧田写虹?申?
□Jmr nmc^D
氓B
L21X
3
2 L2C4
=0
ft G 4L
-禮 -M J 行昶潴,H^j a 01$ 秒
查询任意用户的欠费总额
select clientid from device awhere clientid,sumjoin receivables b =1
select clientid from device a
where clientid
,sum
join receivables b =1
on
,flag
,flag
” hiGSE AS sreja* -La
” hiGSE AS sreja* -La i 订甘送辜,輕时□.djs 彗
点! SQL fiC- - dli efitid,s um | b.ba^ictee^ IrEm dwce a join reEeivsbli? b □<■ a.dw ceid-=-b. d evirei.._ |二 | E (—
田l 旨 規计希
el i —tf 4, 1X e i
* ▲
Er d『 davi cw a. j o i ii t wc v.liLa-e b on a. sE&cri cdLd=b_
T
vLuf? cllMlt
“ i> |i ly ?li nlid 3 flftg
Kii.VL >1^. zl^=0<
lp=M
CUStULL -W 3.K^lJi±i
k
1
I 500
查询出某个月用电量最高的 3名用户
with s as (
select sum as sum_num
from device a
inner join electricity b on = where = '201608'-- 月份条件 group by
)
select s0. *
from ( select ,
from s
order by desc) s0
where rownum <= 3
(7)查询出电力企业某个月哪天的缴费人数最多
select day, num
from
( 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 rownum<2;--查询8月份付款人数最多的一天
按设备类型使用人数从高到低排序查询列出设备类型,使用人数。
select , count (*) as num
from device
group by
order by count (*) desc
统计每个月各银行缴费人次,从高到低排序。
select to_char (paydate ,'yyyymm') yearmonth “ count num from bank join payfee on =
group by to_char (paydate , 'yyyymm'),
order by yearmonth , num desc;
--增加了一条记录,修改了两条记录
查询出电力企业所有新增用户(使用设备不足半年)
select ,
from client join device on =
join electricity on =
group by ,
having count (yearmonth )< 6;
--如果某个设备的抄表记录数小于 6,则其使用不足半年
附录: 添加修改的数据:
1.在device表下,加入数据如图:
2.在receivables 表中,加入四个设备两个月的应收记录如图:
3.在payfee里加入1201设备201609的付款记录
3 沁―沁? 曲豪整 ~lf r^L:7
啡 Wt 和:ft
ft * - ?z卫 fwwkuu m eh miw” ■11!^ 31J 闻-JLd-Si'J ■me? jn m nn^iri ▼3L9S ]!□ Hl FLW>BjJ -2 血闻WlP-^i'l亍
ft * - ?z
卫 fwwk
uu m eh miw” ■
11!^ 31J 闻-JLd-Si'J ■
me? jn m nn^iri ▼
3L9S ]!□ Hl FLW>BjJ -
2 血闻WlP-^i'l亍
■1 1 閔 WJ ■
■ 】皿闻鬥I常创I ?■
a iea m m—i ?
m DO : Dl&flj'l *
E]?,
FZE!?ftll.J|-^ ikMl^WMA^iskTE
_^LiE-ttiWi. -if- □ WW HIT
J = ]f
Wl 曲MH
IV iz ::m
IK* 聞-^b1. 4/| <
】齿 U :110-^/]-
W,-.in'., v/i ?
?=TC: E^n^O* MJl'?U£.iuJI..TTiArP
臥E } rmEd wl-JUtfu^W-i
芒迅血工倉雌 31!XUA£1U£iM:F
:平『;row n 31營⑷认1他为
:KHiBffimc:己 皆jj 和 ywju?s怀片
■ ■ ■ —J ― ■ B 49 ? Mir tf mm ?碎nwr, MHbw CT. E).
向electricity 中插入数据,如下图,比较用户。
将payfee中的部分记录的 bankcode更改,订单日期也进行更改
增加记录到bank表中。
2.事物存储过程
(1)查询
代码1:(按设备号进行查询,在代码 2中被调用)
create or replace procedure queryfee1 (deviceno in number, smoney out number ) is
basicfee number( 7, 2);
yearmonth %ype ;
dtype %ype ;
days number;
days2 number;
d_balance number;
rid number;
chargedate date;
cursor temp_cursor is
select ,“
from device d , receivables r
where =
and = 0
and =deviceno ;
begin
smoney := 0;
open temp_cursor ;
loop
fetch temp_cursor into basicfee , yearmonth , dtype , rid ; exit when temp_cursor %iotfound ;
smoney := smoney+basicfee ;
smoney := smoney+basicfee *;
if dtype = '01' then
smoney := smoney + basicfee *;
else
smoney := smoney + basicfee *;
end if ;
select round (sysdate - add_months (to_date (yearmonth , 'yyyymm' ), 1)) into days
from dual ;
select TO CHARSYSDATEDDD') into days2 from dual ;
if days >0 then
if dtype ='01' then --居民 违约金跨年与不跨年违约金比例相同
smoney := smoney+basicfee ** days;
else |
if days <days2 then --其他,不跨年 smoney := smoney+basicfee ** days;
else --其他,跨年
smoney := smoney+basicfee **( days - days2)+ basicfee **( days2);
end if ; end if ;
end if ;[
end loop ;
select balance into d_balance from device where deviceid =deviceno ;
if (smoney<=d_balance and smoney!= 0) then --如果设备余额大于欠费 余额更新、欠费置0
select trunc (sysdate ) into chargedate from dual ;--截取到日 insert into devicerecord values (deviceno , smoney, rid ,chargedate );--把设备扣费记 录保存保存
update device set balance =balance - smoney where deviceid =deviceno ;
update receivables set flag =2 where id =rid ;
smoney := 0;
elsif (smoney!=0) then --设备余额不够缴费
smoney := smoney- d_balance ;
end if ;
end queryfeel
代码2:(按用户号获得设备号,将设备号传入代码 1的存储过程中)
create or replace procedure QueryFee (clientno in %ype , smoney out number) is
deviceno number;
d_smoney number;
cursor temp_cursor is
select
from client c , device d
where =
and = clientno ;
begin
smoney :=0;
open temp_cursor ;
loop
fetch temp_cursor into deviceno : exit when temp_cursor %iotfound ;
queryfee1 (deviceno , d_smoney); smoney := smoney+d_smoney end loop ;
end QueryFee ;
测试截图:
设备6的应收费用表:
查询设备6的欠费金额:
登全 1丈虫 也
p
elk
E
p
p
-tiiCIF AS ST3DEA -to 己执打■巒寸。005 秒
? - pracedunfQUFYFfEONE 曲车刻UW肛 即RS看出]側帝应圈 礦b-=£lIL--f&JJ fJn prrpudbc
? - pracedunfQUFYFfEONE 曲车
刻UW肛 即RS看出]側帝应圈 礦
b-=£lIL
--f&JJ fJn prrpudbc
qu teli ent?)=> ■ fll 卩山上山环
in Dncy -? sm.jtsyl:
4 nd:
我将修改标志位flag和扣费的过程写在了此存储过程中。查询时如果设备余额大于欠费
数,则用余额对设备进行缴费,更新 flag=2 (第二天凌晨所有的flag=2更改为1)是为了
标志是今天的扣费修改过程,方便冲正。
我新设置了一个表,bankrecord用来记录扣费记录, 方便冲正的时候将设备金额变回来。
在计算跨年费用时,我使用了 select TO_CHARSYSDATEDDD') into days2 from dual ;
首先判断当前时间是一年中的第几天,再根据老师的代码,设备欠费天数 days作比较。
如果days>days2 ,说明存在跨年的欠费,否则不存在。
2.缴费
代码:(添加记录到payfee表中,并更改设备余额)
paydate begindate ;create or replace procedure payfee1 (deviceno in number, paymoney in number, results out varchar ) is
paydate begin
date ;
if paymoney>0 then
select trunc (sysdate ) into paydate from dual ;--截取到日 insert into payfee values , deviceno , paymoney, paydate , 19, 2001,;
update device set balance =balance +paymoney where deviceid =deviceno ; results :='成功';
else
results :='缴费失败,缴费金额不能少于 0';
end if ;
end payfee1 ;
测试截图
StijCfir,prcKPdure Di¥FFFl?XF 前瞄木 三「訝
? - lizElI思SISJBiK 卡1已执行.棚寸n nj』专
缴费之后的payfee表:(增加了一条付费记录)
鶴计东]
MllfCt 1, K 1
TnET t C.CTJLCC1
mid froi
『A¥TRF I
*
¥
L n
mE-gcnp
PATBDSEr ?AYBMT
jEJJKjaH3_
rm
3MsTSEEIkl
iBfiHII 1
? 1 2tdl
z
21K. OD ?
lb
3C0L
yj_nj
W/iggLL sAa±
目 迫L 璋口 _ select t\ trnwid frcni PAYFEE T where deuiteid^fi 回
=野 & 2:1T
再次查询欠费金额:(欠费金额为0)
hrEEE .t S7SD3\ 由?巧漱盘抒,耗叶(J佃屯?
1BIE籀岀
皆]「无即冷匿
—1 f ill1 r_b# 丿 fwMrn q,i^rTf kLii&l.n4 =》:dLi曾sxIew* =〉:si*myj;
snonty
U.
k
dlj ^titiin Inlecet
G
* V
Vl'C .-L->
p
ru*i
-补。畀kS 3TSH1!* -ft 已执打*袪討n Tift M,
缴费之后的设备表:(余额由3变成)
设备6应收费用表:(flag=2用来标志是今天刚刚缴费的,方便冲正。 )
主要创新点:
按照设备号进行缴费,向payfee中添加记录。
使用序列来控制流水号,和id。
3.冲正
代码
create or replace procedure Reverse ( re_bankserial in %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 ,“
from payfee p
where =re_bankserial and 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 , deviceno , 0- re_money, re_date , re_bankcode , 2000, re_bankserial );
select balance into d_balance from device where deviceid =deviceno ;--取岀设备的余额
if (d_balance >re_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
测试截图
.易漁雄 口 ■ prCK^r^- RFVFR^F^XF 力gt
.易漁雄 口 ■ prCK^r^- RFVFR^F^XF 力gt;
—帀卫1
脚则体|1BMS踽[娜棣[踝娱
酸 n' 1
| E P -
将刚刚为6的缴费冲正。
> :riwulist),
Ffrultl 二
r
SlTLE^
F晒
StTLIVg:
*栩
-更已拠冇中耗时£1 I.幽砂
此时的payfee表:(增加了一条冲正记录)
应收费用表:(flag标志为变成0)
设备表:(回到缴费前3元状态)
查询设备6的欠费情况:(回到缴费前的状态,仍然欠费)
主要创新点:
实验的主要思路是主要确定有一致的缴费记录,并且流水号记录只有一次, 说明没有冲
正过,然后进行冲正。
冲正过程中,读取设备此时余额与缴费金额(或者说是冲正金额)作对比,根据“设备
初始金额+缴费金额-扣费金额=设备此时余额”,如果设备此时余额 <缴费金额,这说明 执行了扣费过程,需要进行receivable 中标志位的更改,也需要修改 device表中的设 备余额,这时候用到了扣费记录表(自己建的)找到扣费金额。那么 设备此时的余额+
扣费金额-缴费金额=设备初始金额?如果设备此时余额 >缴费金额,这说明缴费之后 依然欠费,没有进行扣费。这时候只需要向 payfee中添加记录,并修改设备金额;
对总账
代码
create or replace procedure checkmoney (check_bankcode in char, total_count in number,
total_money in bankcode1
number, check_date in varchar , results out varchar ) is varchar (2);
paydatel 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 paymoney<0; 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 :='对账成功';J
else
results :='对账失败,执行对账明细。';
check_detail end if ;
(check_date );
end checkmoney
测试截图 对账成功(:
Payfee表中数据:
对账失败,调用对账明细:
QUS首出1幫讣豪丨腿圖1
i 屮□' uj ? P
nil
,^l ShiEfiC - proredure C H BC KMOWEY^XE =, 回 | 塚 |
higra it
— f i/J fA# jhrrirfd'ffira
> ]|H til ? h > h h- I j" \ - 'k -' I '.-I IJ ? Ilk? II 1-
=> :
rlhArk^fl^l n => cK>r-k_^4i t(
re;s =A :refillJ
?cul;
世
*
曲 -1 1
Fl皿 ?
0
■>
Fl b bl °
I
*
r tK.nl IE.
Slriiug ■
-二爭 q qj * 匹srsniA -U已払行.料寸J郑秒
主要创新点:
主要思路是用游标把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 ,,“
from bankrecord b full outer join payfee p on = where not in
(select bankserial --找到冲正的记录和被冲正的记录
from payfee
where type =2000) and to_char , 'yyyymmdd' )=check_date or to_char , '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 , ppaydate , Bbankcode, Pbankserial , Bpayfee , Ppayfee , 100);
elsif (Pbankserial is null ) then
insert into check_exception
values , Ppaydate , Bbankcode, Bbankserial , Bpayfee , Ppayfee , 101);
elsif ( Bpayfee != Ppayfee) then
insert into check_exception
values , Ppaydate , Bbankcode, Bbankserial , Bpayfee , Ppayfee , 102); end if ;
end loop ;
end check_detail ;
测试截图:
对账失败时
蚩 - prweckj re CHBCKMOMEY^XE 铀甲本
训?cms首出|幫词iin据■跟尊
(Vil
(V
ib亡日九
■ lid-'taiijriuyi(r-K*rt(_Tijuk-M il* 二、:ckjaclUbutocwd^ t^tal_ccinit = A i3toiL_c*nDi.J
从靶叱和U 二〉1, re;J.is =A :resiilt.3'1
HiUr丄血g ?
因 -1[
FIB ■
0
卜
t D>t al rri0:uy
Fl mart f
L
2时B12诫 亠
兀
r3 I b.
SlfkEL^ *
□ QJ?^-311 AS空官皿止 -U已扳行.耗时J.閃3秒
Checkexception 表中数据:100-银行无此流水号
101- 企业无此流水号
102- 银行与企业记录的钱数不等
102- 银行与企业记录的钱数不等
主要创新点:
主要思路是将payfee表和银行记录表进行全连接,然后进行筛选。
因为一开始我在构建游标时使用了 where paydate=check_date 筛选条件,忽略了全连接之
后企业没有此流水号记录,paydate为空的情况,导致了企业方无此流水号这种错误记录不 会出现。所以将条件改为 to_char , 'yyyymmdd' )= check_date or to_char , 'yyyymmdd' ) is null ;
4.程序实现 调用存储过程代码: package database;
import class procedure {
public static void main(String[] args) throws ParseException{ String driver = "";
String url = "jdbc:oracle:thin:@localhost:49159:XE"; 询");
"2. 缴费 ");
"3. 冲正 ");
"4. 对总帐 ");
"5. 对明细账 ");
"0. 退出 ");
" 请输入选项: ");
choice = ();
switch (choice) { case 1:
" 请输入客户号: "); int customerno = ();
cs = ("call QUERYFEE(,)"); (1, customerno);
(2, ;
();
double smoney = (2);
break; case 2:
cs = ("call payfee1(,,)"); " 请输入设备号: "); deviceno = ();
" 请输入缴费金额: "); payMoney = ();
(1, deviceno);
(2, payMoney);
(3, ;
(); result = (3);
break; case 3:
" 请输入银行流水号: "); bankSerial = ();
cs = ("call reverse(,)"); (1, bankSerial);
(2, ;
(); result = (2);
break;
case 4:
"请输入银行代号:”);
ban kCode =();
"请输入总笔数:");
totalNum =();
"请输入总金额:");
totalM oney =();
"请输入日期:");
date1 =();
d = new SimpleDateFormat("yyyy-MM-dd").parse(date1);
cs = ("call checkm on ey(,,,,)");
(1, ban kSerial);
(2, totalNum);
(3, totalMo ney);
(4, new (5,;
();
result = (5);
break;
case 0:
logi n = false;
"拜拜!! !");
break;
default:
"输入有误!请重新输入:");
break;
}
}
} catch(ClassNotF oun dExcepti on e) {
"连接异常");
} catch(SQLException e) {
();
}
}
}
遇到的问题及其解决方案
查询对账明细时, 对账明细表中没有错误类型为 100的记录,即企业有流水号、 银行没
有流水号的情况。
错误原因:因为一开始我在构建游标时使用了 where paydate=check_date 筛选条件,忽略
了全连接之后企业没有此流水号记录, paydate为空的情况,导致了企业方无此流水号这种
错误记录不会出现。
解决方案:将条件改为 to_char , 'yyyymmdd' )= check_date or to_char ,'yyyymmdd' ) is null ; 程序
可以正确运行。
同一缴费记录冲正可以多次进行。
错误原因:未考虑仅可冲正一次的限制。
解决方案:在从payfee表中读取数据的时候, 对流水号有两次(说明已冲正)的进行剔除。
在java程序实现的时候,出现错误:
[jg口. woiL.SQLEKxsIzian: OlriA-@6576:下卷看祐’摹匕n? ?:卑g
拭 or dtle Jdb c-drl 叱「. Da la tws e?r rtr- , t *i rcwSql C K" ptlon fNt 抽 m 纯"no「? I刖枝:di"
at oracle, jdbc. dr ive r ? T 4CT Tlocr ■ ip roc e ?E r rcr( T 4C rTTx「■ "i dm; )
at orarls driver 『4CTTT°p尸.dfoce電籃rer「TMFTT■&盘f ,S前曾n “丁用型)
at or a cle - j db c - dr iver. T4C8QO11,"T4匚50。11』"inw: 7埠鮎
at ora cle. jdbc - dr iver - T4CC allable St:a tarre n二-d oOal LB〔 丁 ACCmUmBgJLwHi tmtEBiETrt - ~j?瞽a :212)
flt cwac le r jdbc ?7厂:1咤厂 .1"4€511<113]上5匕日1:上0~匕仃上?"亡匸讥匕冷「配輛乂丁乂亡畠11甜1匕5“灶*|~|1:,花“:勢 st oraclc. jdbc.drivtr.OracIcStatciicHt.deEutcWithTimcciut(- jjavdzHEP) $t Or . j'ftt - di" 1 UH - Or"S匚iFfTEpHr&STSt勲咤|11 s Fl戡utt严 rl^lPi何t 十刊呻01 ?[託占:斗2£号)
st er a clc-jdbc-dr iwer - Oro c IcPrcp-o r^d Stotcmcn t 1 ex ecute (Or Pre p^red Sts tcnic nt - i : 3339)
aT or a els . j?dtie _ dr iv=r. Ora 匚 let all ableS^atefnen t:. fi: erute (131^二1芒丄日11:3^1£呂十3^卩币&口七_亍:32云:3 ) at Hute ?procedlt u ? nmn ' 口厂oc亡du厂u , b
解决方法:错误的原因是我在使用数据库的时候, 登陆账户使用的是系统权限。 所以我将数
据库内容改成了 normol权限。
创新点
1) 冲正过程中,对设备余额进行修改,让其返回初始状态。我将修改标志位 flag和扣费
的过程写在了此存储过程中。 查询时如果设备余额大于欠费数, 则用余额对设备进行缴
费,更新flag=2 (第二天凌晨所有的flag=2更改为1 )是为了标志是今天的扣费修改 过程,方便冲正。我新设置了一个表, ban krecord 用来记录扣费记录,方便冲正的时
候将设备金额变回来。
2) 查询过程中,我把查询分成了两个存储过程, 一个是按设备号进行查询,一个是按客户
号进行查询,然后客户号查询时调用设备查询过程。 查询过程中就进行了扣费过程, 如
果余额大于欠费,则进行扣费,并修改标志位。
3) 冲正过程中,根据“设备初始金额+缴费金额-扣费金额=设备此时余额”,读取设备此时
余额与缴费金额(或者说是冲正金额)作对比:如果设备此时余额 <缴费金额,这说明
执行了扣费过程,需要进行receivable 中标志位的更改,也需要修改device表中的设 备余额,这时候用到了扣费记录表 (自己建的)找到扣费金额。 那么 设备此时的余额+
扣费金额-缴费金额=设备初始金额;如果设备此时余额 >缴费金额,这说明缴费之后依 然欠费,没有进行扣费。这时候只需要向 payfee中添加记录,并修改设备金额;
4) 在计算跨年费用时