oracle 事务测试

此文章是根据官方改变

模拟帐户转账流程 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; commit work; 总结: 如果在一个 SQL 语句在执行过程中发生了错误，那么此语句对数据库产生的影响将被回滚（roll back）。回滚后就如同此语句从未执行过。

转载于:https://www.cnblogs.com/shawnloong/p/3295503.html

总结

以上是生活随笔为你收集整理的oracle 事务测试的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。