此文章是根据官方改变

模拟帐户转账流程

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;

commit work;

 

总结:

如果在一个 SQL 语句在执行过程中发生了错误，那么此语句对数据库产生的影响将被回滚（roll back）。回滚后就如同此语句从未执行过。