一.ATM实战开发的简介。
学习了几天的Oracle，开始着手用数据库PL/SQL语言做一个简单的ATM取款机业务，主要是为了巩固数据库的知识，并非真正的去实现高端的业务。有兴趣的可以看看，希望对同胞们都有用。
- ATM的表。它有四张表，用户信息表userinfo，卡号信息表cardinfo，交易信息表tradeinfo，存款类型表deposit。
| 用户信息表userInfo | |||
| customerID | int | 客户编号 | 主键 |
| customerName | varchar(10) | 用户名 | not null |
| personID | varcahr(20) | 身份证号 | not null unique 只能是15位 或者18位符合实际的身份证号 |
| telephone | varcahr(20) | 联系电话 | not null，格式为xxxx-xxxxxxxx或者xxx-xxxxxxxx 或者11手机号 |
| address | varchar(30) | 居住地址 | 可选 |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
--创建userinfo表--
create
table
UserInfo(
customerID
int
primary
key
,
customerName
varchar
(10)
not
null
,
personID
varchar
(20)
not
null
unique
,
telephone
varchar
(20)
not
null
,
address
varchar
(30)
);
--为身份证号和电话增加正则表达式约束--
alter
table
userinfo
add
constraint
CK_TELEPHONE
check
(regexp_like(telephone,
'^1[3,4,5,7,8][0-9]-[0-9]{8}$|^[0-9]{3,4}-[0-9]{8}$'
));
alter
table
userinfo
add
constraint
CK_PERSONID
check
(regexp_like(personid,
'^[0-9]{15}$|^[0-9]{17}[0-9,x]$'
));
|
| 卡号信息表cardInfo | |||
| cardID | varchar(30) | 卡号 | 主键，如1010 3576 xxxx xxxx， 每4位后面有空格，卡号随机产生。 |
| curID | varchar(5) | 货币种类 | 必填，默认为RMB |
| savingID | varchar(5) | 存款类型 | 外键，必填。 |
| openDate | date | 开户日期 | 必填。默认为当前时间 |
| openMoney | decimal(10,2) | 开户金额 | 必填，不低于1. |
| balance | decimal(10,2) | 余额 | 必填，不低于1. |
| pwd | varchar(10) | 密码 | 必填，6位数字。默认为888888 |
| isReportLoss | char(2) | 是否挂失 | 必填，只能是'是'或'否'。默认为'否' |
customerid int 开户编号 外键，必填。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
--创建cardinfo表--
create
table
CardInfo(
cardID
varchar
(30)
primary
key
,
curID
varchar
(5)
default
'RMB'
not
null
,
savingID
varchar
(5)
not
null
,
openDate
date
default
sysdate
not
null
,
openMoney
decimal
(10,2)
not
null
check
(openMoney>=1),
balance
decimal
(10,2)
not
null
check
(balance>=1),
pwd
varchar
(10)
default
'888888'
not
null
,
IsReportLoss
char
(2)
default
'否'
not
null
,
customerID
int
not
null
references
UserInfo(customerID)
);
--为卡号和密码增加正则表达式约束--
alter
table
cardinfo
add
constraint
CK_PWD
check
(regexp_like(pwd,
'^[0-9]{6}$'
));
alter
table
cardinfo
add
constraint
CK_CARDID
check
(regexp_like(cardid,
'^1010[[:space:]]3576[[:space:]][0-9]{4}[[:space:]][0-9]{4}$'
));
|
| 交易信息表tradeInfo | |||
| transdate | date | 交易日期 | 必填。默认为系统时间 |
| cardID | varchar(30) | 卡号 | 外键，必填。 |
| transType | varchar(10) | 交易类型 | 必填，只能是存入或者支取 |
| transmoney | decimal(10,2) | 交易金额 | 必填，大于0 |
| remark | varchar(50) | 备注 | 可选 |
|
1
2
3
4
5
6
7
8
|
--创建tradeinfo表--
create
table
TradeInfo(
transDate
date
default
sysdate
not
null
,
cardID
varchar
(30)
not
null
references
CardInfo(cardID),
transType
varchar
(10)
not
null
,
transMoney
decimal
(10,2)
not
null
,
remark
varchar
(50)
);
|
--为transtype增加约束--
alter table tradeinfo add constraint CK_TRANSTYPE check (transtype in('支取','存入'));
| 存款类型表deposit | |||
| savingID | int | 类型编号 | 主键 |
| savingName | varchar(20) | 存款类型名称 | not null unique |
- ATM模拟实现的业务。
1.修改密码。
2.挂失。
3.查询本周开户的卡号。
4.查询本月一次性交易金额最高的卡号。
5.查询卡号挂失的用户的信息。
6.开户。
7.存款或者取款。
二.插入数据。
- 为deposit表插入数据。
插入数据前我们应该有这样一个认识，与客户无关的信息表先插入数据，与客户有关的次之。因为你开户的时候，客户存款类型必须与存款类型表deposit表的一条记录匹配才行。这就像一个银行一样，不管有多少客户，你银行本身的数据以及功能是必须有的。所以，先插入与银行的信息有关与客户无关的表的数据。
为deposit插入数据的时候，存款类型编号我们一般也不会自己去输入，所以要创建一个插入类型名称时，自动插入类型编号的触发器。
sequence savingid_incr --创建序列不能加or replace
start
with
1
increment
by
1
nomaxvalue
nocycle
cache 30;
然后创建savingid的触发器。
|
1
2
3
4
5
6
7
8
|
--创建savingid的触发器--
create
or
replace
trigger
savingid_insert
before
insert
on
deposit
for
each row
declare
begin
:new.savingid:=savingid_incr.nextval;
end
;
|
现在就可以插入数据了。
|
1
2
3
|
insert
into
deposit(savingname)
values
(
'定期'
);
insert
into
deposit(savingname)
values
(
'活期期'
);
insert
into
deposit(savingname)
values
(
'定活两便'
);
|
检测数据。select * from deposit;可以看到三条savingid自动产生并插入的记录。
- 为userinfo表和cardinfo表插入数据：
对于userinfo表，在插入数据的时候，我们不可能每次都去插入客户编号，所以要为客户编号创建一个插入其他数据时的客户编号自动插入的触发器；还要为卡号创建一个随机产生的过程，并且开户的时候通过过程去插入，因为卡号是随机产生并且不能重复，所以我把这个判断写入了开户的业务逻辑中。
在这里的话，我直接去实现开户，然后通过开户为userinfo表和cardinfo表插入数据，东西很多，我们一步一步来。
先为customerid创建序列。
|
1
2
3
4
5
6
|
create
sequence
id_incr
--创建序列不能加or replace
start
with
1001
increment
by
1
nomaxvalue
nocycle
cache 30;
|
再为customerid创键触发器。
|
1
2
3
4
5
6
7
8
|
create
or
replace
trigger
id_insert
before
insert
on
userinfo
for
each row
declare
next_customerid userinfo.customerid%type;
begin
:new.customerid:=id_incr.nextval;
end
;
|
为cardid创建随机产生的过程。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
create
or
replace
procedure
r_cardid(out_id
out
varchar2)
as
r_num number;
front_id varchar2(4);
back_id varchar2(4);
real_id varchar2(20);
begin
select
lpad(trunc(dbms_random.value*100000000),8,0)
into
r_num
from
dual;
front_id:=to_char(substr(r_num,1,4));
back_id:=to_char(substr(r_num,5,4));
real_id:=
'1010 3576 '
||front_id||
' '
||back_id;
out_id:=real_id;
end
;<BR>
|
开户的时候，除了customerid和cardid以及表给的默认值，其他都是与用户开户有关的信息。所以准备工作做好之后，我们就可以实现开户的业务了。
*****************************************************开户******************************************************
******开户的数据*********
开户时需要用户输入的有：
身份证号——personid
存款类型——savingid
存款金额——openmoney
本卡密码——pwd
(在开户时如果用户是第一次开户的话，就又需要的输入的有：)
姓名——customername
联系方式——telephone
地址——address系统自动赋予的值有：
用户号——customerid(触发器触发)
卡号——cardid(调用r_cardid(outid)过程)
其他都为系统给的默认值。
******开户的存储过程逻辑******
1.先判断用户是不是第一个开卡，如果是，那么先创建用户信息，再开户。
2.用户创建之后或者用户已存在时，调用卡号随机产生的存储过程，产生一个随机产生的不重复的卡号。
3.卡号产生之后，判断用户输入的存款类型是否正确，正确，就可以开户了。不正确则撤销之前所有的操作并且提示开户终止。
4.开户就是插入一条符合逻辑的cardinfo记录，并且提示开户的最终信息。
******开户的存储过程************
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
|
create
or
replace
procedure
openAccount(
temp_personid
in
userinfo.personid%type,
temp_savingname
in
deposit.savingname%type,
temp_openmoney
in
cardinfo.openmoney%type,
temp_pwd
in
cardinfo.pwd%type,
temp_customername
in
userinfo.customername%type,
temp_telephone
in
userinfo.telephone%type,
temp_address
in
userinfo.address%type)
as
isnullpersonid userinfo.personid%type;
--select into判断身份证号是否存在。
temp_cardid cardinfo.cardid%type;
--select into判断新产生的卡号是否存在,并且在后来是要用的。
temp_savingid cardinfo.savingid%type;
--select into 判断用户输入的存款类型是否可用。
temp_customerid userinfo.customerid%type;
begin
begin
--判断用户是否存在
select
personid
into
isnullpersonid
from
userinfo
where
personid=temp_personid;
exception
when
no_data_found
then
-----创建用户----
insert
into
userinfo(customername,personid,telephone,address)
values
(temp_customername,temp_personid,temp_telephone,temp_address);
end
;
begin
while 1=1 loop
--产生一个唯一不重复的卡号
r_cardid(temp_cardid);
select
cardid
into
temp_cardid
from
cardinfo
where
cardid=temp_cardid;
--如果没有找到，则证明新产生的卡号是唯一的，进入exception继续完成操作。
end
loop;
exception
when
no_data_found
then
--来到这里说明产生的卡号是可用的，接下来就应该判断存款类型temp_savingid.
begin
select
savingid
into
temp_savingid
from
deposit
where
savingname=temp_savingname;
--如果存在，那么就可以开户了，如果不存在，则撤销之前的所有操作，用事务。
--customerid是之前就有或者在开户中自动产生的，所以这里要通过SQL找到它。
select
customerid
into
temp_customerid
from
userinfo
where
personid=temp_personid;
--开户---
insert
into
cardinfo(cardid,savingid,openmoney,balance,pwd,customerid)
values
(temp_cardid,temp_savingid,temp_openmoney,temp_openmoney,temp_pwd,temp_customerid);
dbms_output.put_line(
' 开户成功！'
);
dbms_output.put_line(
'您的银行卡号为：'
||temp_cardid);
dbms_output.put_line(
'开户日期：'
||sysdate||
' 开户金额 '
||temp_openmoney);
exception
when
no_data_found
then
rollback
;
--撤销之前的所有操作
raise_application_error(-20000,
'存款类型不正确，开户终止!'
);
end
;
end
;
end
;
|
***************利用开户存储过程来插入数据******************
插入一条用户第一次开户的数据：
set serveroutput on;
execute openAccount(410181199308084016,'定期',50000,762723,'徐万轩','151-03891462','河南省郑州市');
插入一条老用户开户的数据：
set serveroutput on;
execute openAccount(410181199308084016,'活期',50000,762723,'徐万轩','151-03891462','河南省郑州市');
此时查表就会发现有两条cardinfo记录，一条userinfo记录。
- 为tradeinfo表插入数据。
tradeinfo表示记录交易信息的，所以我们可以调用存取款的过程来为tradeinfo表插入数据。
*****************************************************存取款存储过程*******************************************
******存取款需要用户的数据**********
需要用户输入的信息：
存款或者取款的金额：temp_money
存款或者取款的类型：temp_transtype
银行卡号：temp_cardid
******存取款的实现逻辑*************
1.首先判断用户输入的卡号是否存在，不存在则退出操作。
2.卡号存在，再判断卡是否挂失，如果挂失，提醒并退出操作。
3.没有挂失的话，判断存取款类型是否正确，如果正确，就可以存取款了。
4.存取款时，更新cardinfo表卡的balance余额，并且插入一条交易信息表。
5.如果取款之后，余额小于1，就会发生检查约束错误，捕获错误并且利用事务的原理rollback之前的操作。
*******存取款的存储过程**************
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
|
create
or
replace
procedure
inout_money(
temp_money
in
number,
temp_transtype
in
tradeinfo.transtype%type,
temp_cardid
in
cardinfo.cardid%type
)
as
isnulltranstype tradeinfo.transtype%type;
--判断存取款类型是否正确
isnullcardid cardinfo.cardid%type;
--判断银行卡是否存在
isnullloss cardinfo.isreportloss%type;
--判断银行卡是否冻结
begin
begin
--判断卡号是否存在
select
cardid
into
isnullcardid
from
cardinfo
where
cardid=temp_cardid;
exception
when
no_data_found
then
begin
raise_application_error(-20000,
'卡号不存在！'
);
end
;
end
;
begin
--先判断卡号是否冻结
select
isreportloss
into
isnullloss
from
cardinfo
where
cardid=temp_cardid;
if isnullloss=
'是'
then
raise_application_error(-20001,
'该卡已冻结，不能执行该操作！'
);
end
if;
--判断存取款类型是否存在
select
distinct
transtype
into
isnulltranstype
from
tradeinfo
where
transtype=temp_transtype;
if temp_transtype=
'支取'
then
update
cardinfo
set
balance=balance-temp_money
where
cardid=temp_cardid;
insert
into
tradeinfo(cardid,transtype,transmoney)
values
(temp_cardid,temp_transtype,temp_money);
dbms_output.put_line(
'取出'
||temp_money||
'RMB！'
);
elsif temp_transtype=
'存入'
then
update
cardinfo
set
balance=balance+temp_money
where
cardid=temp_cardid;
insert
into
tradeinfo(cardid,transtype,transmoney)
values
(temp_cardid,temp_transtype,temp_money);
dbms_output.put_line(
'存入'
||temp_money||
'RMB！'
);
end
if;
exception
when
no_data_found
then
rollback
;
raise_application_error(-20002,
'存取款类型不正确！'
);
when
others
then
dbms_output.put_line(
'余额不能少于1'
);
rollback
;
end
;
end
;
|
*************利用存取款存储过程来插入tradeinfo数据*****************
set serveroutput on;
execute inout_money(500,'存入','1010 3576 1685 3672');
set serveroutput on;
execute inout_money(500,'支取','1010 3576 1685 3672');
这时，trande表会有两条记录，一个是1010 3576 1685 3672支取的记录，一个是1010 3576 1685 3672的存入记录。
其实，开户和存取款的过程与插入数据时两回事，但是我们却可以利用这两个过程来实现数据的插入，本人在这里也是懒省事。其实，这样插入数据的话，思路上也比较好理解。毕竟，对于练习来说，我们在实现业务之前的数据只是为了检查这些数据的插入是否满足表的约束和它的合理性，但是，在实际的开发过程中，一个业务的完成的前提条件是你必须有实现这个业务的功能，所以先实现业务，通过业务来插入数据时最为合理的。不过这样对于初学者或者基础不太扎实的同胞可能就有些难以理解了，没事，慢慢来吧。
三.实现业务逻辑。
- 挂失。
********挂失需要用户输入的信息**********
1.卡号。
2.密码。
3.账户ID。
******挂失的存储过程*********
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
create
or
replace
procedure
lose(temp_cardid
in
cardinfo.cardid%type,
temp_pwd
in
number,temp_customerid
in
varchar2)
as
row_info cardinfo%rowtype;
islose varchar2(5);
begin
select
isreportloss
into
islose
from
cardinfo
where
cardid=temp_cardid;
if islose=
'是'
then
dbms_output.put_line(
'此卡已经挂失！'
);
goto
last_point;
end
if;
select
*
into
row_info
from
cardinfo
where
cardid=temp_cardid;
if row_info.pwd=temp_pwd
and
row_info.customerid=temp_customerid
then
update
cardinfo
set
IsReportLoss=
'是'
where
cardid=temp_cardid;
dbms_output.put_line(
'挂失成功!'
);
else
dbms_output.put_line(
'对不起，您输入卡的信息不正确，不能挂失！'
);
end
if;
<<last_point>>
null
;
exception
when
NO_DATA_FOUND
then
dbms_output.put_line(
'您输入的卡号不存在！'
);
end
;
|
**********测试***********
set serveroutput on;
execute lose('1010 3576 4654 1134','888866','1001');
- 修改密码。
********修改密码所需的用户的信息********
1.卡号
2.密码
**********修改密码的存储过程**********
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
create
or
replace
procedure
up_pwd(temp_cardid
in
varchar2,
temp_newpwd
in
number)
as
temp
varchar2(30);
BEGIN
select
cardid
into
temp
from
cardinfo
where
cardid=temp_cardid;
update
cardinfo
set
pwd=temp_newpwd
where
cardid=temp_cardid;
dbms_output.put_line(
'修改密码成功！'
);
EXCEPTION
when
no_data_found
then
dbms_output.put_line(
'输入的卡号不存在!'
);
when
others
then
dbms_output.put_line(
'违反检查约束'
);
END
;
|
*********修改密码的测试*************
set serveroutput on;
execute up_pwd('1010 3576 0030 0000','666652');
-
查询本周开户的卡号。
|
1
|
select
*
from
cardinfo
where
opendate>=trunc(sysdate,
'day'
);
|
-
查询本月一次性交易金额最高的卡号。
|
1
2
3
4
|
select
*
from
tradeinfo;
--from后面跟本月的搜索结果，where处控制transmoney为最大值。
select
distinct
*
from
(
select
*
from
tradeinfo
where
transdate>trunc(sysdate,
'month'
))
where
transmoney
in
(
select
max
(transmoney)
from
tradeinfo);
|
-
查询卡号挂失的用户的信息。
|
1
2
3
4
|
select
u.customername,u.customerid,u.personid,u.telephone,u.address
from
userinfo u
inner
join
cardinfo c
on
c.customerid=u.customerid
where
c.isreportloss=
'是'
;
|
四.开发中遇到的一些问题。
在开发的过程中，遇到了许多问题，因为我学习Oracle数据库也就十天左右的时间，对于一些基本的还不是很熟悉。
遇到的问题：
1.创建过程的时候不能有declare关键字。
2.goto流程控制不能goto到exception执行体中。
3.select into语句千万别返回多行语句，在编译的时候是不会出错的，调用的时候出错也会提示较多的错误，修改很麻烦。比如，查看存款类型是否合法的时候，就需要在select语句前加上distinct来确保返回的是一条语句。
4.创建序列不可以使用 or replace。
5.添加行级触发器的时候，赋值用 ：new.属性值来赋值。如果select into语句中也赋值的话，就会用两次序列自动产生的值，所以编译器不会报错，但是你的序列的增长率却是你想要的二倍。
6.在像开户这样的逻辑实现过程中，需要多次去用select去判断。而且它还有判断之后需要共同实现的部分，所以对于我一些新手来说，还是比较难于理解begin end的嵌套结构。bengin end里面可以嵌套begin end；exception之后的when then也可以接着begin end并且也可以嵌套。
7.日期函数不是很熟悉。
tranc(date,day)日期date所在周的周日的日期。
interval '2' month将2作为month来运算。日期的加减默认是天。
last_day(date)，date日期所在月的最后一天的日期。
add_months(date,2) date日期两个月之后的日期。
8.数值函数不是很熟悉。
dbms_random.value产生0-1之间的小数，精确到很多位。
lpad(对象,位数,0)向对象左侧填充0，知道对象的位数=输出的位数。
trunc(数值对象[,截取精度])，如果截取精度>0,则截取到小数点后第几位，如果截取精度<0,则截取到小数点前第几位，截取的部分用0填充。如果截取精度=0，则去掉小数部分。截取精度不写的话默认为0.
2075
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
