此文章是根据官方改变
模拟帐户转账流程
1.JOHN帐户扣除-DAVID帐户增加-记录日志-事务提交
三个操作必须全部完成此事务才完成,否则失败
创建帐户余额表自增字段
自增序列
;
create
sequence
saving_seq
increment
by
1
start
with
1
maxvalue
999999999999999999
nocycle
cache
20
;
创建支票表自增字段
自增序列;
create
sequence
check_seq
increment
by
1
start
with
1
maxvalue
999999999999999999
nocycle
cache
20
;
创建日志记录自增字段自增序列;l
create
sequence log
_seq
increment
by
1
start
with
1
maxvalue
999999999999999999
nocycle
cache
20
;
2.创建余额表saving_accounts
create
table
saving_accounts
(account_id
int
primary
key
,
account_name
varchar2
(
20
) ,
pay
number
(
15
,
2
) );
comment
on
table
saving_accounts
is
'帐户余额表'
;
comment
on
column
saving_accounts.account_id
is
'帐户ID'
;
comment
on
column
saving_accounts.account_name
is
'帐户名称'
;
comment
on
column
saving_accounts.pay
is
'帐户余额'
;
创建支票余额表
create
table
checking_accounts
(check_id
int
primary
key
,
check_name
varchar2
(
20
) ,
check_pay
number
(
15
,
2
) );
comment
on
table
checking_accounts
is
'支票帐户余额表'
;
comment
on
column
checking_accounts.check_id
is
'支票帐户ID'
;
comment
on
column
checking_accounts.check_name
is
'支票帐户名称'
;
comment
on
column
checking_accounts.check_pay
is
'支票帐户余额'
;
创建转账日志表
create
table
log_accounts
(log_id
int
primary
key
,
log_date
date
default
(
sysdate
)
not
null
,
account_id
int
not
null
,
check_id
int
not
null
,
change_pay
number
(
15
,
2
) );
comment
on
table
log_accounts
is
'转账日志表'
;
comment
on
column
log_accounts.log_id
is
'转账日志ID'
;
comment
on
column
log_accounts.log_date
is
'转账日期'
;
comment
on
column
log_accounts.account_id
is
'转账帐户ID'
;
comment
on
column
log_accounts.check_id
is
'支票帐户ID'
;
comment
on
column
log_accounts.change_pay
is
'支票帐户余额'
;
查询建表是否成功
select
*
from
log_accounts;
select
*
from
saving_accounts;
select
*
from
checking_accounts;
3.插入数据
插入
saving_accounts
insert into saving_accounts
values(
saving_seq.nextval,'john',1000);
insert into saving_accounts
values(
saving_seq.nextval,'david',2000);
insert into saving_accounts
values(
saving_seq.nextval,'alex',3000);
insert into saving_accounts
values(
saving_seq.nextval,'lily',5000);
insert into saving_accounts
values(
saving_seq.nextval,'joe',1500);
commit;
插入checking_accounts
insert into
checking_accounts
values
(
check_seq
.nextval,'john',2000);
insert into
checking_accounts
values
(
check_seq
.nextval,'david',500);
insert into
checking_accounts
values
(
check_seq
.nextval,'alex',2000);
insert into
checking_accounts
values
(
check_seq
.nextval,'lily',1500);
insert into
checking_accounts
values
(
check_seq
.nextval,'joe',4000);
commit;
用户转账的步骤(转账到支票)
如john
防止事务失败可以加入异常处理
begin
savepoint sp1
--SET TRANSACTION NAME 'account_update'; 可以设置事务名称transaction name
--减少john 帐户余额200转入到david
update saving_accounts a
set pay=pay-200
where a.account_id=1;
--SAVEPOINT after_update_savind_accounts; 设置rollback点
--增加david支票余额
update checking_accounts b
set check_pay=check_pay+200
where b.check_id=2;
--写入日志表
insert into
log_accounts(log_id,account_id,check_id,change_pay)
values(log
_seq.nextval,1,2,200);
--ROLLBACK TO SAVEPOINT after_update_savind_accounts; 回滚到saingpoint after_update_savind_accounts
--rollback 将回滚事务account_update
exception
when others then
rollback to savepoint sp1;
end;
when others then
rollback to savepoint sp1;
end;
commit work;
总结:
如果在一个 SQL 语句在执行过程中发生了错误,那么此语句对数据库产生的影响将被回滚(roll back)。回滚后就如同此语句从未执行过。