Oracle PL/SQL开发基础（第二十七弹：事务处理简介）

张小龙net馆藏 2017-10-18

展开全文

事务（Transaction）是一个由多条SQL语句组成的工作逻辑单元，这些语句要么全部执行成功，要么全部不执行，只要有一条SQL语句执行失败，已执行的SQL语句会全部回滚到执行之前的状态，这样就保证了数据库数据的一致性。

一个事务必须要满足ACID，即原子性、一致性、隔离性和持久性这4个属性。
- 原子性：事务必须是原子工作单元，对其进行的数据修改，要么全部执行，要么全部不执行。
- 一致性：事务在完成时，必须使所有的数据都保持一致状态，即所有的数据都要发生更改，以保证数据的完整性。
- 隔离性：两个事务的执行是互不干扰的，一个事务不可能看到其他事务运行时、运行中间某一时刻的数据。
- 持久性：一旦事务被提交之后，数据库的变化就会被持久地保留下来，即使运行数据局软件的机器后来崩溃也是如此。

PL/SQL提供了如下的语句用于事务的管理：
- COMMIT：保存自上一次COMMIT或ROLLBACK以来的所有改变，并且释放所有的锁。
- ROLLBACK：回滚所有自上一次COMMIT或ROLLBACK以来的所有改变，并且释放所有的锁。
- ROLLBACK TO SAVEPOINT：回滚所有的改变到一个已经保存的保存点，并且释放所有该范围内的锁。
- SAVEPOINT：建立一个保存点，允许完成局部的回滚操作。
- SET TRANSACTION：允许开始一个只读或读写会话，建立一个隔离级别，或者是将当前的事务赋给一个特定的回滚段。
- LOCK TABLE：允许使用特定的模式锁定整个数据库表，这将覆盖默认的行级别的锁定。

使用COMMIT提交事务

COMMIT语法如下：

COMMIT [WORK] [COMMENT text]；1

可选的WORK仅为了增强可读性，并没有任何其他的作用。可选的COMMENT用来为某个分布式事务添加注释，如果在COMMIT时出现网络或机器故障，Oracle会在数据字典中保存COMMENT提供的文本内容和相关的事务ID，文本内容必须是用引号括起来的长度不超过50个字符的文字，如：

COMMIT COMMENT '在提交订单时出现了错误';1

一般用法如：

DECLARE
   dept_no   NUMBER (2) := 70;
BEGIN
   --开始事务
   INSERT INTO dept 
        VALUES (dept_no, '市场部', '北京');               --插入部门记录
   INSERT INTO emp                                        --插入员工记录
        VALUES (7997, '威尔', '销售人员', NULL, TRUNC (SYSDATE), 5000,300, dept_no);
   --提交事务
   COMMIT;
END;1
2
3
4
5
6
7
8
9
10
11

COMMIT语句在执行后会释放在会话上添加的任何表锁和行锁，比如使用SELECT FOR UPDATE语句添加的锁，还会清除自上次COMMIT或ROLLBACK以来添加的任何保存点。

使用ROLLBACK回滚事务

语法如下：

ROLLBACK [WORK] [TO [SAVEPOINT] savepoint_name];1

一般用法如：

DECLARE
   dept_no   NUMBER (2) := 70;
BEGIN
   --开始事务
   INSERT INTO dept 
        VALUES (dept_no, '市场部', '北京');               --插入部门记录
   INSERT INTO dept 
        VALUES (dept_no, '后勤部', '上海');               --插入相同编号的部门记录        
   INSERT INTO emp                                        --插入员工记录
        VALUES (7997, '威尔', '销售人员', NULL, TRUNC (SYSDATE), 5000,300, dept_no);
   --提交事务
   COMMIT;
EXCEPTION
   WHEN DUP_VAL_ON_INDEX THEN                            --捕足异常
     DBMS_OUTPUT.PUT_LINE(SQLERRM);                   --显示异常消息
     ROLLBACK;                                           --回滚异常
END;1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

使用SAVEPOINT保存点

默认情况下，ROLLBACK会撤销整个事务，如果编写了一个很大的PL.SQL块，使用了很多DML语句，此时回滚操作的工作量是比较大的。PL/SQL提供了语句级别的回滚，允许将一个大的事务分成很多语句级的小块，每一个小块作为一个保存点，这样在执行PL/SQL程序时，如果发生了错误，Oracle可以回滚到指定的保存点，而不一定要撤销整个事务。
ROLLBACK TO SAVEPOINT命令执行之后，将会发生如下的几件事：
- 从保存点以后所做的工作都被撤销，但是保存点未被释放，如果需要，可以再次撤销该保存点。
- 自该保存点以后SQL语句所需的锁和资源都被释放。
- 虽然撤销到保存点，但是并不是结束整个事务，SQL语句处于挂起状态。

使用如：

DECLARE
   dept_no   NUMBER (2) :=90;
BEGIN
   INSERT INTO dept 
        VALUES (dept_no, '市场部', '北京');               --插入部门记录
   SAVEPOINT B;   
   INSERT INTO emp                                        --插入员工记录
        VALUES (7997, '威尔', '销售人员', NULL, TRUNC (SYSDATE), 5000,300, dept_no);        
   SAVEPOINT C;                
   INSERT INTO dept 
        VALUES (dept_no, '后勤部', '上海');               --插入相同编号的部门记录
   --提交事务
   COMMIT;
EXCEPTION
   WHEN DUP_VAL_ON_INDEX THEN                            --捕足异常
     DBMS_OUTPUT.PUT_LINE(SQLERRM);                   --显示异常消息
     ROLLBACK TO B;                                      --回滚异常
END;1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

不过要提一下，在一般项目中，使用SAVEPOINT的情况并不多。

使用SET TRANSACTION设置事务属性

SET TRANSACTION允许开始一个只读或只写的事务，建立隔离级别或者是为当前的事务分配一个特定的回滚段。
SET TRANSACTION必须是事务处理中的第一个语句，并且只能出现一次。

SET TRANSACTION语法如下：

SET TRANSACTION parameter;1

parameter用来指定事务的参数，有如下几种：
READ ONLY：用于建立只读事务，如：

SET TRANSACTION READ ONLY;1

READ WRITE：建立读写事务，如：

SET TRANSACTION READ WRITE;1

ISOLATION LEVEL：用来设置事务的隔离级别，即规定在事务中如何处理DML事务，可以设置SERIALIZABLE和READ COMMITTED这两个选项。SERIALIZABLE会使得对已修改但没有提交的数据对象的DML事务失败。READ COMMITTED对已修改但没有提交的数据库对象的DML事务进行修改时，会等待前面的DML锁消失，这是Oracle的默认特性。如：

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION ISOLATION READ COMMITTED;1
2

USE ROLLBACK SEGMENT：给事务定义一个合适的回滚段，如：

SET TRANSACTION ISOLATION USE ROLLBACK SEGMENT segmentname;1

看一个例子：

DECLARE
   v_1981 NUMBER(2);
   v_1982 NUMBER(2);
   v_1983 NUMBER(2);
BEGIN
   --SET TRANSACTION必须在事务的第1条语句，因此可以在COMMIT或ROLLBACK后面。
   COMMIT;
   SET TRANSACTION READ ONLY NAME '统计年度入职数据';     --使用NAME为事务命名
   --使用SELECT语句执行查询
   SELECT COUNT(empno) INTO v_1981 FROM emp WHERE TO_CHAR(hiredate,'YYYY')='1981';
   SELECT COUNT(empno) INTO v_1982 FROM emp WHERE TO_CHAR(hiredate,'YYYY')='1982';
   SELECT COUNT(empno) INTO v_1983 FROM emp WHERE TO_CHAR(hiredate,'YYYY')='1983';  
   COMMIT;  --终止只读事务
   DBMS_OUTPUT.PUT_LINE('1981年入职人数：'||v_1981);   --显示统计的结果
   DBMS_OUTPUT.PUT_LINE('1982年入职人数：'||v_1982);
   DBMS_OUTPUT.PUT_LINE('1983年入职人数：'||v_1983);              
END; 1
2
3
4