此文章是根據官方改變
模擬帳戶轉賬流程
1.JOHN帳戶扣除-DAVID帳戶增加-記錄日志－事務提交
三個操作必須全部完成此事務才完成，否則失敗
創建帳戶余額表自增字段自增序列；
createsequencesaving_seqincrementby1startwith1maxvalue999999999999999999nocyclecache20;
創建支票表自增字段自增序列；
createsequencecheck_seqincrementby1startwith1maxvalue999999999999999999nocyclecache20;
創建日志記錄自增字段自增序列；l
create?sequence?log_seq?increment?by?1?start?with?1?maxvalue?999999999999999999?nocycle?cache?20?;
2.創建余額表saving_accounts
createtablesaving_accounts
(account_idintprimarykey,
account_namevarchar2(20) ,
paynumber(15,2) );
commentontablesaving_accountsis?'帳戶余額表';
commentoncolumnsaving_accounts.account_idis'帳戶ID';
commentoncolumnsaving_accounts.account_nameis'帳戶名稱';
commentoncolumnsaving_accounts.payis'帳戶余額';
創建支票余額表
createtablechecking_accounts
(check_idintprimarykey,
check_namevarchar2(20) ,
check_paynumber(15,2) );
commentontablechecking_accountsis?'支票帳戶余額表';
commentoncolumnchecking_accounts.check_idis'支票帳戶ID';
commentoncolumnchecking_accounts.check_nameis'支票帳戶名稱';
commentoncolumnchecking_accounts.check_payis'支票帳戶余額';
創建轉賬日志表
createtablelog_accounts
(log_idintprimarykey,
log_datedate?default(sysdate)notnull,
account_idintnotnull,
check_idintnotnull,
change_paynumber(15,2) );
commentontablelog_accountsis?'轉賬日志表';
commentoncolumnlog_accounts.log_idis'轉賬日志ID';
commentoncolumnlog_accounts.log_dateis'轉賬日期';
commentoncolumnlog_accounts.account_idis'轉賬帳戶ID';
commentoncolumnlog_accounts.check_idis'支票帳戶ID';
commentoncolumnlog_accounts.change_payis'支票帳戶余額';
查詢建表是否成功
select*fromlog_accounts;
select*fromsaving_accounts;
select*fromchecking_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）。回滾后就如同此語句從未執行過。
