韩顺平oracle视频笔记四(事务)
一、事物例子说明 create table student( uuid char(32) default sys_guid() not null primary key,name varchar(32) ); insert into system.student (name) values ('huangbiao'); insert into system.student (name) values ('liumei'); 备注:sys_guid()函数产生的数据只有32位。 SQL> select * from student; UUID NAME ------------------------------------ ------------ 00D4A520B3EE46CC85333591A072031C huangbiao 5C2634E72D5A405BA6342B1673178818 liumei savepoint a1; delete from student where name='huangbiao'; select * frSQL> savepoint a1; Savepoint created. SQL> delete from student where name='huangbiao'; 1 row deleted. SQL> select * from student; UUID NAME ------------------------------------ ----------- 5C2634E72D5A405BA6342B1673178818 liumeiom emp; SQL> savepoint a2; Savepoint created. SQL> delete from student where name='liumei'; 1 row deleted. SQL> select * from student; no rows selected SQL> rollback to a2; Rollback complete. SQL> select * from student; UUID NAME ------------------------------------ ------- 5C2634E72D5A405BA6342B1673178818 liumei SQL> rollback to a1; Rollback complete. SQL> select * from student; UUID NAME ------------------------------------ ------------ 00D4A520B3EE46CC85333591A072031C huangbiao 5C2634E72D5A405BA6342B1673178818 liumei 备注:这里不能使用commit命令,否则之前所用的savepoint都将全部不起作用了。 二、只读事务 只允许执行查询操作,不允许其它的DML操作事务,使用只读事务可以确保用户只能读取某时间点的数据,之后的数据将不会理睬。 例如机票代售点每天18点开始统计今天的销售情况,但是18点肯定还有人在操作数据库,因此这里使用只读事务之后就不会对统计造成影响。 1、打开PLSQL(1)工具输入: set transaction read only; SQL> select * from student; UUID NAME -------------------------------- --------- E622460F590C43C8BF746A3F43622DE3 huangbiao 40C039E5536E45A9905383333B05E499 liumei 2、打开另一个PLSQL(2)工具 insert into system.student (name) values ('zhangsan'); 3、使用PLSQL(1)查询数据 SQL> select * from student; UUID NAME -------------------------------- --------- E622460F590C43C8BF746A3F43622DE3 huangbiao 40C039E5536E45A9905383333B05E499 liumei 结果:发现第一个查询的数量和第二个PLSQL查询的数量是不一致的,这就是设置只读事务的功能 设置只读事务之后,将不会显示当前时间点之后的所有操作数据 4、使用PLSQL(1)插入数据 SQL> insert into system.student (name) values ('lisi'); insert into system.student (name) values ('lisi') * ERROR at line 1: ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction 三、java代码设置手动提交 import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; public class TestTrans { public static void main(String[] args) { Connection conn = null; Statement stmt = null; try { DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); System.out.println("driver is ok"); conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","system","admin"); //不让oracle自动提交,而是手动提交数据 conn.setAutoCommit(false); System.out.println("connection is ok"); stmt = conn.createStatement(); String sql1 = "update scott.emp set sal=sal-100 where ename='SMITH'"; String sql2 = "update scott.emp set sal=sal-100 where ename='SCOTT'"; stmt.execute(sql1); //这段代码是模拟操作出现异常的方法 int a = 1/0; stmt.execute(sql2); //执行完上面两个SQL语句之后才commit conn.commit(); } catch (SQLException e) { try { //如果出现异常将操作回滚,这样就能保证数据的一致性 conn.rollback(); } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } e.printStackTrace(); } if(stmt!=null){ try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if(conn!=null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } } (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |