佛山网页网站设计多少钱,网站备案地区名,查国外企业用什么软件,百度怎么搜索图片此文章是根据官方改变 模拟帐户转账流程1.JOHN帐户扣除-DAVID帐户增加-记录日志#xff0d;事务提交三个操作必须全部完成此事务才完成#xff0c;否则失败创建帐户余额表自增字段自增序列#xff1b;createsequencesaving_seqincrementby1startwith1maxvalue99999999999999…此文章是根据官方改变 模拟帐户转账流程 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 paypay-200 where a.account_id1; --SAVEPOINT after_update_savind_accounts; 设置rollback点 --增加david支票余额 update checking_accounts b set check_paycheck_pay200 where b.check_id2; --写入日志表 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
