第六章 PL/SQL与Oracle间交互(3)

12、游标变量的限制

目前，游标变量有以下限制：

1. 不能在包中声明游标变量。例如，下面的声明就是不允许的：

   CREATE PACKAGE emp_stuff AS
     TYPE empcurtyp IS REF CURSOR
       RETURN emp%ROWTYPE;
   
     emp_cv   empcurtyp;   -- not allowed
   END emp_stuff;

2. 处于另外一个服务器上的远程子程序不能接受游标变量的值。因此，我们不能使用RPC将游标变量从一个服务器传递到另一个服务器。
3. 如果我们把主游标变量传递到PL/SQL中，就不能在服务器端从这个游标变量中取得数据了，除非打开这个游标变量的操作也是在同一个服务器调用中进行的。
4. 不能简单的用比较操作符来判断两个游标变量是否是相等，不相等或空。
5. 不能为游标变量赋空值。
6. 不能在CREATE TABLE或CREATE VIEW语句中把字段类型指定为REF CURSOR类型。因为数据库字段是不能存放游标变量值的。
7. 不能把REF CURSOR类型作为集合的元素类型，也就是说，索引表，嵌套表和变长数组不能存放游标变量的值。
8. 游标和游标变量不能互相替换。如下例中，不能把适用于游标的FOR循环应用在游标变量上：

   DECLARE
     TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
     emp_cv EmpCurTyp;
     ...
   BEGIN
     ...
     FOR emp_rec IN emp_cv LOOP ...   -- not allowed
   END;

六、使用游标属性

每个显示的游标和游标变量都有四个属性：%FOUND、%ISOPEN、%NOTFOUND和%ROWCOUNT。这些属性都能返回关于数据操作语句执行的有用信息。我们可以在过程化语句中使用游标属性，但不能在SQL语句中使用。

1、显式游标属性一览

每个显式的游标和游标变量都有四个属性：%FOUND、%ISOPEN、%NOFOUND和%ROWCOUNT。我们可以在过程语句中使用这些属性，但不能再SQL语句中使用。

显式游标属性能返回多行查询的信息。当一个显式游标或游标变量被打开时，满足查询条件的行就会被做上标记，最终形成结果集。然后我们就可以就从结果集中取出行数据。

• %FOUND属性：行被取出了吗？

在一个游标或游标变量被打开后且在首次取得数据之前，%FOUND会产生NULL值。而此后每取得一行数据，它的值就为TRUE，直到最后一次取得数据失败，它的值变成FALSE。下例中，我们利用%FOUND的值来进行条件判断：

LOOP
  FETCH c1
   INTO my_ename, my_sal, my_hiredate;
  IF c1%FOUND THEN   -- fetch succeeded
    ...
  ELSE   -- fetch failed, so exit loop
    EXIT;
  END IF;
END LOOP;

在没有打开游标或游标变量的时候使用%FOUND会引起预定义异常INVALID_CURSOR。

• %ISOPEN属性：游标打开了吗？

%ISOPEN在对应的游标或游标变量打开的时候返回TRUE;否则返回FALSE。下例中，我们用%ISOPEN来进行条件判断：

IF c1%ISOPEN THEN   -- cursor is open
  ...
ELSE   -- cursor is closed, so open it
  OPEN c1;
END IF;

• %NOTFOUND属性：FETCH失败了吗？

%NOTFOUND属性的作用和%FOUND属性正好相反。它在数据取得失败时返回TRUE，成功时返回FALSE。在下面的例子中，当FETCH语句没有取得数据的时候，我们使用%NOTFOUND来退出循环：

LOOP
  FETCH c1
    INTO my_ename, my_sal, my_hiredate;
  EXIT WHEN c1%NOTFOUND;
  ...
END LOOP;

在取数据之前，%NOTFOUND会返回NULL。所以，如果FETCH从来都没有成功执行的话，循环就不会退出。那是因为只有EXIT WHEN语句中的WHEN子句值为TRUE时，这条语句才能被执行。为了安全起见，我们可以使用下面的EXIT语句进行替换：

EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;

在没有打开游标或游标变量的时候使用%NOTFOUND会引起预定义异常INVALID_CURSOR。

• %ROWCOUNT属性：已经取得了多少条数据？

当游标或游标变量被打开时，%ROWCOUNT值为零。每成功取得一条数据，%ROWCOUNT的值就加一。下例中，我们用%ROWCOUNT来判断取得的数据是否超过十条，然后采取相关的对策：

LOOP
  FETCH c1 
    INTO my_ename, my_deptno;
  IF c1%ROWCOUNT > 10 THEN
    ...
  END IF;
  ...
END LOOP;

在没有打开游标或游标变量的时候使用%ROWCOUNT会引起预定义异常INVALID_CURSOR。

下表是执行OPEN、FETCH或CLOSE语句前后对应的游标属性值：

 %FOUND%ISOPEN%NOTFOUND%ROWCOUNT

OPEN	之前	异常	FALSE	异常	异常
OPEN	之后	NULL	TRUE	NULL	0
First FETCH	之前	NULL	TRUE	NULL	0
First FETCH	之后	TRUE	TRUE	FALSE	1
Next FETCH(es)	之前	TRUE	TRUE	FALSE	1
Next	之后	TRUE	TRUE	FALSE	与数据条数相关
Last FETCH	之前	TRUE	TRUE	FALSE	与数据条数相关
Last FETCH	之后	FALSE	TRUE	TRUE	与数据条数相关
CLOSE	之前	FALSE	TRUE	TRUE	与数据条数相关
CLOSE	之后	异常	FALSE	异常	异常
注意： 如果在游标打开之前或关闭之后引用属性%FOUND、%NOTFOUND或%ROWCOUNT，都会引起INVALID_CURSOR异常。 第一个FETCH之后，如果结果集是空的，%FOUND会产生FALSE，%NOTFOUND会产生TRUE，%ROWCOUNT会产生0。

• 游标属性的一些实例

假设我们有一个名为data_table的数据表，用它来收集实验室的实验数据，并且我们需要分析实验1的数据。在下面的例子中，我们可以计算出实验结果并把它们放到一个名为temp的数据表中。

DECLARE
  num1     data_table.n1%TYPE;   -- Declare variables
  num2     data_table.n2%TYPE;   -- having same types as
  num3     data_table.n3%TYPE;   -- database columns
  RESULT   temp.col1%TYPE;

  CURSOR c1 IS
    SELECT n1, n2, n3
      FROM data_table
     WHERE exper_num = 1;
BEGIN
  OPEN c1;

  LOOP
    FETCH c1
     INTO num1, num2, num3;
    EXIT WHEN c1%NOTFOUND;   -- TRUE when FETCH finds no more rows
    RESULT  := num2 / (num1 + num3);
    INSERT INTO temp
         VALUES (RESULT, NULL, NULL);
  END LOOP;

  CLOSE c1;
  COMMIT;
END;

在接下来的例子中，我们会检查所有那些包含零件号码为5469的贮藏器，把它们的内容提取出来直到累计到1000个单位：

DECLARE
  CURSOR bin_cur (part_number NUMBER) IS
    SELECT        amt_in_bin
             FROM bins
            WHERE part_num = part_number AND amt_in_bin > 0
         ORDER BY bin_num
    FOR UPDATE OF amt_in_bin;

  bin_amt                  bins.amt_in_bin%TYPE;
  total_so_far             NUMBER (5)             := 0;
  amount_needed   CONSTANT NUMBER (5)             := 1000;
  bins_looked_at           NUMBER (3)             := 0;
BEGIN
  OPEN bin_cur (5469);

  WHILE total_so_far < amount_needed LOOP
    FETCH bin_cur
     INTO bin_amt;
    EXIT WHEN bin_cur%NOTFOUND;
    -- if we exit, there's not enough to fill the order
    bins_looked_at  := bins_looked_at + 1;
    IF total_so_far + bin_amt < amount_needed THEN
      UPDATE bins
         SET amt_in_bin = 0
       WHERE CURRENT OF bin_cur;
      -- take everything in the bin
      total_so_far  := total_so_far + bin_amt;
    ELSE   -- we finally have enough
      UPDATE bins
         SET amt_in_bin = amt_in_bin - (amount_needed - total_so_far)
       WHERE CURRENT OF bin_cur;
      total_so_far  := amount_needed;
    END IF;
  END LOOP;

  CLOSE bin_cur;
  INSERT INTO temp
       VALUES (NULL, bins_looked_at, '<- bins looked at');
  COMMIT;
END;

2、 隐式游标属性一览

隐式游标属性会返回一些关于INSERT、UPDATE、DELETE和SELECT INTO语句的执行信息。这些属性值总是与最近一次执行的语句相关。在Oracle打开SQL游标之前，隐式游标的所有属性都是NULL。

要注意SQL游标还有另外一个专门为FORALL语句设计的%BULK_ROWCOUNT属性。

隐式游标属性和显式游标相同，也有%FOUND、%ISOPEN、%NOTFOUND和%ROWCOUNT，并且这些属性的用法也和显式游标的类似，这里就不再详细说明。由于Oracle在执行完语句后会自动关闭SQL游标，所以SQL游标的%ISOPEN属性值总是FALSE。

• 隐式游标属性的使用准则

隐式游标的属性值总是与最后一次执行的语句相关，无论这个语句处于哪个作用域。所以，如果我们想把一个属性值保存起来以便以后使用，就要立即把它赋给一个布尔变量。下面的例子中依赖于IF语句的条件是很危险的，因为过程check_status可能会改变属性%NOTFOUND的值：

BEGIN
  ...
  UPDATE parts SET quantity = quantity - 1 WHERE partno = part_id;
  check_status(part_id);   -- procedure call
  IF SQL%NOTFOUND THEN   -- dangerous!
    ...
  END;
END;

我们可以像下面这样改善代码：

BEGIN
  ...
  UPDATE parts SET quantity = quantity - 1 WHERE partno = part_id;
  sql_notfound := SQL%NOTFOUND;   -- assign value to Boolean variable
  check_status(part_id);
  IF sql_notfound THEN ...
END;

如果SELECT INTO没有返回结果，PL/SQL就会抛出预定义异常NO_DATA_FOUND。如下例：

BEGIN
  ...
  SELECT sal INTO my_sal FROM emp WHERE empno = my_empno;
  -- might raise NO_DATA_FOUND
  IF SQL%NOTFOUND THEN   -- condition tested only when false
    ...   -- this action is never taken
  END IF;

上面的检查是没有作用的，因为IF语句只在%NOTFOUND值是假的情况下才能进行检查。当PL/SQL抛出NO_DATA_FOUND异常，正常的执行就会终止，控制权被交给异常处理部分。

但一个调用聚合函数的SELECT INTO语句从来不会抛出异常NO_DATA_FOUND，因为聚合函数总会返回一个值或空。在这种情况下，%NOTFOUND就会产生FALSE，如下例：

BEGIN
  ...
  SELECT MAX(sal) INTO my_sal FROM emp WHERE deptno = my_deptno;
  -- never raises NO_DATA_FOUND
  IF SQL%NOTFOUND THEN   -- always tested but never true
    ...   -- this action is never taken
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THEN ...   -- never invoked

七、使用游标表达式

一个游标表达式能返回一个嵌套游标。结果集中的每一行跟平常一样，每个字段都包含一些值，其中的一些包含的是嵌套游标。因此，一个独立的查询就能返回从多个数据表间检索出来的相关值。我们可以用嵌套循环来处理结果集,然后再处理每一行中的嵌套游标。

PL/SQL支持把游标表达式作为游标声明、REF CURSOR声明和游标变量的一部分的查询。我们还可以在动态游标查询中使用游标表达式。语法如下：

CURSOR ( subquery )

在从父级游标取得数据时，嵌套游标就会被隐式地打开。嵌套游标只有在下面的情况下才会被关闭：

1. 嵌套游标被用户显式地关闭
2. 父级游标被重新执行
3. 父级游标被关闭
4. 父级游标被取消
5. 在从嵌套游标的一个父级游标中取数据时发生错误。嵌套游标会被作为清理内容的一部分而被关闭

1、游标表达式的约束

1. 不能在隐式游标中使用游标表达式
2. 游标表达式只能出现在：
   1. 非子查询的SELECT语句中，并且这条语句不能是游标表达式本身的子查询
   2. 作为table函数的参数，出现在SELECT语句的FROM子句中
3. 游标表达式只能出现在查询说明的SELECT列表中
4. 游标表达式不能出现在视图声明中
5. 不能对游标表达式进行BIND和EXECUTE操作

2、游标表达式的示例

下例中，我们要用一个游标取出某个指定ID的地区中所有的部门。在我们取得每一个部门名称的过程中，我们也会从另一张表取出该部门雇员的详细信息。

CREATE OR REPLACE PROCEDURE emp_report (p_locid NUMBER) IS
  TYPE refcursor IS REF CURSOR;

  -- The query returns only 2 columns, but the second column is
  -- a cursor that lets us traverse a set of related information.
  CURSOR c1 IS
    SELECT l.city,
           CURSOR (SELECT d.department_name,
                          CURSOR (SELECT e.last_name
                                    FROM employees e
                                   WHERE e.department_id = d.department_id
                                 ) AS ename
                     FROM departments d
                    WHERE l.location_id = d.location_id
                  ) dname
      FROM locations l
     WHERE l.location_id = p_locid;

  loccur    refcursor;
  deptcur   refcursor;
  empcur    refcursor;
  v_city    locations.city%TYPE;
  v_dname   departments.department_name%TYPE;
  v_ename   employees.last_name%TYPE;
BEGIN
  OPEN c1;

  LOOP
    FETCH c1
     INTO v_city, loccur;
    EXIT WHEN c1%NOTFOUND;

    -- We can access the column C1.city, then process the results of
    -- the nested cursor.
    LOOP
      FETCH loccur
       INTO v_dname, deptcur;   -- No need to open
      EXIT WHEN loccur%NOTFOUND;

      LOOP
        FETCH deptcur
         INTO v_ename;   -- No need to open
        EXIT WHEN deptcur%NOTFOUND;
        DBMS_OUTPUT.put_line (v_city || ' ' || v_dname || ' ' || v_ename);
      END LOOP;
    END LOOP;
  END LOOP;

  CLOSE c1;
END;
/

八、PL/SQL中的事务处理一览

在这里，我们学习一下，如何使用事务处理的基本技术来保证数据库的一致性，这其中包括如何提交或取消对数据库的改动。Oracle管理下的工作或任务被称为会话。当我们运行应用程序或Oracle工具并连接到Oracle时，一个用户会话就会被开启。为了让用户会话可以"同步"工作并能共享计算机资源，Oracle就必须控制并发，所谓并发指的是多个用户同时访问同样的数据资源。要是没有合适的并发控制的话，就可能无法保证数据的完整性。也就是说，对数据的改变可能是在错误的秩序下完成的。

Oracle使用锁来控制并发访问数据。锁可以让我们临时占有某个数据库资源，如一个数据表或是表中的一条数据。这样，数据就不能被其他用户改变，直到我们结束对被锁定数据的处理。我们不需要显式地锁定一个资源，因为默认的锁机制会帮助我们保护数据和它的结构。但是，当我们想覆盖掉默认的锁时，我们就可以从多个锁模型中(如行共享和行排他)选出一个，发出请求为表或行加上我们选定的锁来替代默认的锁。

当两个或多个用户同时访问同一个模式对象时，就有可能发生死锁。比如说，两个用户要同时更新数据表，如果他们互相占有另外一个用户所要更新的资源，他们就会因得不到所需的资源而互相等待，直到Oracle向最后一个事务发出错误信号破除死锁为止。

当一个数据表在同一时刻被一个用户查询另一个用户更新时，Oracle就会为数据查询生成一个读一致的视图。一旦查询开始并继续执行的时候，被读取的数据是不会改变的。当更新活动执行时，Oracle会把数据表的数据和记录的变化内容放到回滚段中。Oracle利用回滚段建立读一致查询结果集并能在必要的时候取消所变化的内容。

1、如何用事务保护数据库

数据库事务是指作为单个逻辑工作单元执行的一系列操作。Oracle把一系列操作当作一个单元以便由语句引起的所有变动能够被一次性提交或回滚。如果在一个事务中某个环节执行失败，Oracle会自动地将数据内容恢复到执行前的状态。

程序中的第一条SQL语句会开启事务，当事务结束时，下一条SQL语句会自动地开启另一个事务。因此，每条SQL语句都是事务的一部分。一个分布式事务应该至少包含一条能够更新分布式数据库节点上的数据的SQL语句。

COMMIT和ROLLBACK语句能确保所有的数据库变化一次性提交，或一次性回滚。自上次提交或回滚之后的所有SQL语句又成为当前事务的一部分。SAVEPOINT语句能为当前事务处理中的当前点进行命名与标记。

2、使用COMMIT提交事务

COMMIT语句能终止当前事务，并把事务中的数据库变化提交到数据库中。在我们提交变化的内容之前，其他用户是无法访问到被修改了的数据；他们所看到的数据跟未修改之前的内容完全一样。

看一下事务的例子，假设把资金从一个银行的账户转入另一个银行的账户。这个事务需要做两次更新操作，借记第一个银行账户，然后借贷第二个银行账户。

BEGIN
  ...
  UPDATE accts
     SET bal = my_bal - debit
   WHERE acctno = 7715;
  ...
  UPDATE accts 
     SET bal = my_bal + credit
   WHERE acctno = 7720;

  COMMIT WORK;
END;

COMMIT命令会释放作用于表和行的锁，也能清除自上一次提交或回滚之后的所有保存点。可选关键字WORK只是用于改善可读性而已。而关键字END代表了PL/SQL块的结束，而不是事务的结束。就像块可以跨越多个事务一样，事务也能跨越多个块。

可选关键字COMMENT能让我们为某个分布式事务添加注释。在提交的过程中如果出现了网络或机器故障，分布式事务的状态就未知或是有疑问(in- doubt)的了。那样的话，Oracle会在数据词典中保存COMMENT提供的文本内容和相关的事务ID。文本内容必须用引号夹起来的长度不超过50 字符的文字。如下例：

COMMIT COMMENT 'In-doubt order transaction; notify Order Entry';

PL/SQL不支持FORCE子句，这个子句在SQL中可以手工提交一个有疑问的(in-doubt)分布式事务。例如，下面的语句是不允许的：

COMMIT FORCE '23.51.54';   -- not allowed P257

3、使用ROLLBACK回滚事务

ROLLBACK语句能终止当前事务并放弃所有的数据变更。使用回滚有两个原因。第一，如果我们不小心误删了数据，回滚能帮助我们恢复原始数据。第二，如果我们开启了一个因异常或SQL语句执行失败而不能完成的事务，回滚就能让我们的数据回到最初状态，然后重新再执行一次。如下面的例子，我们把一个雇员的信息插入到三个不同的数据表中。如果插入过程中出现主键冲突，就会抛出DUP_VAL_ON_INDEX异常，这时，我们就可以在异常控制部分中使用事务回滚了。

DECLARE
  emp_id INTEGER;
  ...
BEGIN
  SELECT empno, ...
    INTO emp_id, ...
    FROM new_emp 
   WHERE ...
  ...
  INSERT INTO emp VALUES (emp_id, ...);
  INSERT INTO tax VALUES (emp_id, ...);
  INSERT INTO pay VALUES (emp_id, ...);
  ...
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    ROLLBACK;
  ...
END;

• 语句级(Statement-Level)回滚

执行SQL之前，Oracle会标记一个隐式的保存点。然后，在语句执行失败的时候，Oracle就会自动执行回滚操作。例如，如果一条 INSERT语句因主键冲突而执行失败，语句就会被回滚。这时只有未执行成功的SQL所作的工作被丢弃。而那条语句之前执行成功的语句所作工作都会被保存下来。

Oracle还能回滚单条SQL语句并解除死锁，它会把错误发给参与执行的一个事务并回滚那个事务中的当前语句。

执行SQL语句之前，Oracle必须分析语法，确保语句满足语法规则并且语句内涉及到的模式对象都是有效的。语句执行时发现的错误能引起回滚操作，而分析时发现的错误不能引起回滚操作。

4、使用SAVEPOINT回滚部分事务

SAVEPOINT能为事务处理中的当前点进行命名和标记。使用ROLLBACK TO语句时，保存点能让我们恢复作了标记的事务的部分内容，而不是恢复整个事务。下例中，我们可以在插入操作之前标记一个保存点。如果INSERT语句要把一个重复的值插入字段empno，预定义异常DUP_VAL_ON_INDEX就会被抛出。那样，我们可以回滚到保存点，只有恢复插入操作。

DECLARE
  emp_id emp.empno%TYPE;
BEGIN
  UPDATE emp
     SET ... 
   WHERE empno = emp_id;
  DELETE FROM emp WHERE ...
  ...
  SAVEPOINT do_insert;
  INSERT INTO emp VALUES (emp_id, ...);
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    ROLLBACK TO do_insert;
END;

当我们回滚到一个保存点时，任何在那个保存点之后标记的保存点都会被擦除。但是，我们所回滚到的保存点不会被擦除。例如，如果我们标记了五个保存点，然后回滚到第三个，那么只有第四个和第五个保存点会被擦除。一个简单的回滚或提交都会擦除所有的保存点。

如果我们在一个递归子程序里标记了一个保存点，递归中每级都会有一个SAVEPOINT语句实例被执行。但是，我们只能回滚到最近标记的那个保存点。

保存点的名称是未声明的标识符并能在事务中反复使用。每次使用都会把保存点从它的旧位置移动到事务当前点。因此，回滚到保存点的操作只会影响到事务的当前部分。如下例所示：

BEGIN
  SAVEPOINT my_point;
  UPDATE emp 
     SET ... 
   WHERE empno = emp_id;
  ...
  SAVEPOINT my_point;   -- move my_point to current point
  INSERT INTO emp VALUES (emp_id, ...);
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK TO my_point;
END;

每一个会话中可用的保存点是没有限制的。一个有效的保存点就是一个自上一次提交或回滚之后的一个标记。

5、Oracle如何隐式回滚

在INSERT、UPDATE或DELETE语句执行之前，Oracle会标记一个隐式的保存点(对用户是不可用的)。如果语句执行失败， Oracle就会回滚到保存点。正常情况下，只有失败的SQL语句被回滚，而不是整个事务。但是，如果语句抛出了一个未捕获异常，主环境会决定回滚哪些内容。

如果我们的存储子程序因未捕获异常而退出，PL/SQL就不会为OUT模式参数进行赋值。并且，PL/SQL也不会对子程序所做的操作进行回滚。

6、终止事务

好的编程习惯是显式地执行提交或回滚每一个事务。是否在PL/SQL块或主环境中执行提交或回滚操作取决于程序的逻辑流程。如果我们没有显式地提交或回滚，主环境会决定它的最终状态。例如在SQL*PLUS中，如果PL/SQL块没有包含COMMIT或ROLLBACK语句，事务的最终状态就由块后的操作内容决定。如果我们执行一个数据定义，数据控制或COMMIT语句，或是调用EXIT，DISCONNECT或QUIT命令，Oracle都会提交事务。如果我们执行了ROLLBACK或退出SQL*PLUS会话，Oracle就会回滚事务。

在Oracle的预编译器环境中，如果程序非法结束，Oracle就会回滚事务。当程序显式地提交或回滚工作并使用RELEASE参数断开Oracle连接，它就能正常地退出：

EXEC SQL COMMIT WORK RELEASE;

7、使用SET TRANSACTION设置事务属性

我们可以使用SET TRANSACTION语句开启一个只读或只写的事务，建立隔离级别或把当前事务赋给一个指定的回滚段。只读事务对于运行那些涉及到一个或多个数据表的多查询来说，是很有用的；并且，在其他用户对表进行更新操作的时候，我们也可以对同样的表进行查询操作。

在只读事务中，所有的查询都会引用同一个提供多表，多查询，读一致视图的数据库快照。其他用户可以像平时一样继续查询或更新数据。在下面的例子中，作为一个商店经理，我们可以使用一个只读事务来收集过去一天、一周和一个月的销售量。在事务中，这些数字不会受到其他更新数据的用户的影响：

DECLARE
  daily_sales     REAL;
  weekly_sales    REAL;
  monthly_sales   REAL;
BEGIN
  ...
  COMMIT;   -- ends previous transaction
  SET TRANSACTION READ ONLY NAME 'Calculate sales figures';
  SELECT SUM (amt)
    INTO daily_sales
    FROM sales
   WHERE dte = SYSDATE;
  SELECT SUM (amt)
    INTO weekly_sales
    FROM sales
   WHERE dte > SYSDATE - 7;
  SELECT SUM (amt)
    INTO monthly_sales
    FROM sales
   WHERE dte > SYSDATE - 30;
  COMMIT;   -- ends read-only transaction
  ...
END;

SET TRANSACTION语句必须是只读事务中的第一条SQL语句，且只能出现一次。如果把事务设置成READ ONLY，后续查询就能看到事务开始之前提交的内容。使用READ ONLY并不会影响其他用户或事务。

• SET TRANSACTION的约束

只有SELECT INTO、OPEN、FETCH、CLOSE、LOCK TABLE、COMMIT和ROLLBACK语句才允许出现在只读事务中，并且查询过程不能使用FOR UPDATE。

8、覆盖默认锁

默认情况下，Oracle会自动地帮助我们锁定数据结构。但是，当覆盖掉默认的锁会对我们更加有利时，我们就可以发出请求为行或表添加特殊的数据锁。显式锁定能让我们在事务中共享数据表或拒绝对数据表的访问。

使用LOCK TABLE语句可以显式地锁住整张数据表；而SELECT FOR UPDATE可以锁定表中的特殊行，保证它们在更新或删除之前不会发生改变。但是，Oracle在执行更新或删除操作时会自动地获取行级锁(row- level locks)。所以，只在我们希望更新或删除操作执行之前锁住行才使用FOR UPDATE子句。

• 使用FOR UPDATE

当我们声明了一个被UPDATE或DELETE语句的子句CURRENT OF所引用的游标时，就必须使用FOR UPDATE子句来获取排它锁。如下例：

DECLARE
  CURSOR c1 IS
    SELECT     empno, sal
          FROM emp
         WHERE job = 'SALESMAN' AND comm > sal
    FOR UPDATE NOWAIT;

SELECT ... FOR UPDATE语句能够标记出那些将被更新或被删除的行，然后把它们一一锁定在结果集中。这在我们想对于行中已存在值进行修改时是很有用的。那样，我们就必须确定在更新行之前没有其他用户对它进行更改。

可选关键字NOWAIT能告诉Oracle，如果被请求行已经被其他用户锁定，那么就不需要等待了。控制权可以马上还给我们程序以便能够在重新获取锁之前做一些其他工作。如果不使用NOWAIT，Oracle会一直等待，直到能够访问到被锁定的行释放为止。

打开游标时，所有的行都会被锁住，而不仅仅是被取出的行。提交或回滚事务能够让行解除锁定。所以，我们不能在事务提交之后从FOR UPDATE的游标中取得数据。

查询多个数据表时，我们可以使用FOR UPDATE子句把行锁定限制在特定的表中。仅当FOR UPDATE OF子句引用到表中的一个字段的时候，该表中的行才会被锁定。例如，下面的查询就把行锁定在表emp，而不是dept：

DECLARE
  CURSOR c1 IS
    SELECT        ename, dname
             FROM emp, dept
            WHERE emp.deptno = dept.deptno AND job = 'MANAGER'
    FOR UPDATE OF sal;

如下例所示，我们可以使用UPDATE或DELETE语句的CURRENT OF子句来引用从游标中取出的最新的行数据：

DECLARE
  CURSOR c1 IS
    SELECT     empno, job, sal
          FROM emp
    FOR UPDATE;
  ...
BEGIN
  OPEN c1;

  LOOP
    FETCH c1
     INTO ...
    ...
    UPDATE emp
       SET sal = new_sal
     WHERE CURRENT OF c1;
  END LOOP;
END;

• 使用LOCK TABLE

我们可以使用LOCK TABLE语句把整张数据表用指定的锁模式进行锁定，这样就能共享或拒绝对这些表的访问。例如，下面的语句就把表emp用行共享的模式进行锁定。行共享锁允许并行访问数据表；它能阻止其他用户为了独占数据表而将整张表锁定。当事务提交或回滚后，锁就会被释放。

LOCK TABLE emp IN ROW SHARE MODE NOWAIT;

锁的模式决定了什么样的其它锁可以作用于数据表上。例如，许多用户都可以同时获取一个表上的行共享锁，但只可能有一个用户获取排他锁。当其中一个用户获取的排他锁时，其他的用户就不能插入、删除或更新表中的数据了。

一个表锁从不会阻止用户对表进行查询，而且查询也不会获取表锁。只有两个不同的事务尝试修改同样的数据时，才可能出现其中一个事务等待另一个事务完成的现象。

• 提交后的数据取得

FOR UPDATE子句能获取排他锁。打开游标时所有的行都会被锁住，在事务提交后锁会被释放。所以，我们不能在事务提交后从使用了FOR UPDATE子句的游标中取得数据。如果这样做的话，PL/SQL就会抛出异常。下例中，游标FOR循环在第十次插入操作后会执行失败：

DECLARE
  CURSOR c1 IS
    SELECT        ename
             FROM emp
    FOR UPDATE OF sal;

  ctr   NUMBER := 0;
BEGIN
  FOR emp_rec IN c1 LOOP   -- FETCHes implicitly
    ...
    ctr  := ctr + 1;
    INSERT INTO temp
         VALUES (ctr, 'still going');
    IF ctr >= 10 THEN
      COMMIT;   -- releases locks
    END IF;
  END LOOP;
END;

如果想在数据提交后也能取得数据，就不要使用FOR UPDATE和CURRENT OF子句。我们可以使用伪列ROWID模拟CURRENT OF子句。只要把每行的ROWID放到UROWID类型的变量中就可以了。然后在后续的更新和删除操作中用ROWID来辨识当前行。示例如下：

DECLARE
  CURSOR c1 IS
    SELECT ename, job, ROWID
      FROM emp;

  my_ename   emp.ename%TYPE;
  my_job     emp.job%TYPE;
  my_rowid   UROWID;
BEGIN
  OPEN c1;

  LOOP
    FETCH c1
     INTO my_ename, my_job, my_rowid;
    EXIT WHEN c1%NOTFOUND;
    UPDATE emp
       SET sal = sal * 1.05
     WHERE ROWID = my_rowid;
    -- this mimics WHERE CURRENT OF c1
    COMMIT;
  END LOOP;

  CLOSE c1;
END;

一定要注意，上面的例子中，被取得的记录并没有被锁住，因为我们没有使用FOR UPDATE子句。所以，其他用户可能无意地覆盖了我们所更新的内容。这样的话，游标就必须提供一个读一致的数据视图，而在更新中所使用的回滚段在游标关闭之前是不能被释放的。这就会降低行更新的处理速度。下面的例子演示了我们如何使用一个游标的%ROWTYPE属性，其中，游标引用了ROWID伪列：

DECLARE
  CURSOR c1 IS
    SELECT ename, sal, ROWID
      FROM emp;

  emp_rec   c1%ROWTYPE;
BEGIN
  OPEN c1;

  LOOP
    FETCH c1
     INTO emp_rec;
    EXIT WHEN c1%NOTFOUND;
    ...
    IF ... THEN
      DELETE FROM emp
            WHERE ROWID = emp_rec.ROWID;
    END IF;
  END LOOP;

  CLOSE c1;
END;

九、使用自治事务完成单个逻辑工作单元

数据库事务是指作为单个逻辑工作单元执行的一系列SQL操作。通常，一个事务是由另外一个事务开启。在某些应用程序中，一个事务必须在开启它的事务的作用域之外进行操作。

自治事务是一个由其他事务(主事务)开启的独立的事务。自治事务可以把主事务挂起，然后执行SQL操作，在提交或回滚这些操作后，重新恢复主事务。下图是从主事务(MT)到自治事务(AT)然后返回的过程演示：

1、自治事务的优点

自治事务一旦被开启，就完全独立。它不与主事务共享任何锁、资源或提交依赖(commit-dependency)。所以，我们不能把事件记入日志，增加重试计数器等等，即使是主事务执行了回滚操作。

更重要的是，自治事务可以帮助我们建立模块化和可重用的软件组件。例如，存储过程可以在它们自己的自治事务中独立执行。应用程序不必知道过程的匿名操作，存储过程也无需知道应用程序的事务上下文。这就使自治事务比常规事务更不容易出错，使用更方便。

另外，自治事务具有常规事务的所有功能。他们可以并发查询，分布处理，并能使用所有的事务控制语句，其中也包括SET TRANSACTION。

2、定义自治事务

我们可以使用编译指示(编译器指令)AUTONOMOUS_TRANSACTION来定义自治事务。这个编译指示会让PL/SQL编译器把"程序"标记为自治的(独立的)。这里的术语"程序"包含：

1. 顶级(Top-level，非嵌套)自治PL/SQL块
2. 本地的、独立的或打包的函数和过程
3. SQL对象类型的方法
4. 数据库触发器

我们可以把这个指令放到程序声明部分的任何地方。但为了良好的可读性，一般把它放到声明的最顶部，语法如下：

PRAGMA AUTONOMOUS_TRANSACTION;

在下面的例子中，我们把一个函数标记为自治：

CREATE PACKAGE banking AS
  ...
  FUNCTION balance (acct_id INTEGER)
    RETURN REAL;
END banking;

CREATE PACKAGE BODY banking AS
  ...
  FUNCTION balance (acct_id INTEGER)
    RETURN REAL IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    my_bal   REAL;
  BEGIN
    ...
  END;
END banking;

约束：我们不能在一个包中使用这个编译指示来达到把所有的子程序(或对象类型中的所有方法)标记为自治的目的。只有独立的程序才能被标记为自治的。例如，下面这样的编译指示是不能使用的：

CREATE PACKAGE banking AS
  PRAGMA AUTONOMOUS_TRANSACTION; -- not allowed
  ...
  FUNCTION balance (acct_id INTEGER) RETURN REAL;
  END banking;

在下面的例子中，我们再把一个独立的过程标记为自治：

CREATE PROCEDURE close_account (acct_id INTEGER, OUT balance) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
  my_bal REAL;
BEGIN ... END;

下面，我们把一个PL/SQL块标记为自治：

DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
  my_empno   NUMBER (4);
BEGIN
  ...
END;

约束：我们不可以把嵌套PL/SQL块标记为自治。

在下面的例子中，我们把一个数据库触发器标记为自治。与常规的触发器不同的是，自治触发器能够包含事务控制语句，如COMMIT和ROLLBACK。

CREATE TRIGGER parts_trigger
  BEFORE INSERT
  ON parts
  FOR EACH ROW
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO parts_log
       VALUES (:NEW.pnum, :NEW.pname);
  COMMIT;   -- allowed only in autonomous triggers
END;

• 自治事务VS嵌套事务

虽然匿名事务是由另一个事务开启的，但它并不是一个嵌套事务：

1. 它不与主事务共享事务资源。
2. 它不依赖于主事务。例如，如果主事务回滚了，嵌套事务就会回滚，但自治事务不会。
3. 它提交变化的内容对其他事务来说是立即可见的。(一个嵌套事务所提交的变化内容在主事务提交之前对其它事务是不可见的。)
4. 自治事务中抛出的异常会产生事务级回滚，而不是语句级回滚。

• 事务关联文

如下图所示，主事务与嵌套程序共享它的关联文，但不与自治事务共享。同样，当一个自治程序调用另一个自治程序(或自我递归调用)，程序也不会共享事务关联文。但是，当自治程序调用一个非自治程序的时候，程序会共享同一个事务关联文。

• 事务可见性

如图所示，自治事务在提交时它所做的内容变化对其它事务是可见的。当恢复到主事务的时候变化内容对主事务也是可见的，但这需要把它的隔离级别被设置为READ COMMITTED(默认)。

如果我们像下面一样把主事务的隔离级别设置为SERIALIZABLE，恢复主事务时，由它的自治事务所做的变化对主事务就不可见了。

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

3、控制自治事务

一个自治程序中的第一个SQL语句会开启一个事务。当事务结束时下一个SQL语句又会开启另一个事务。自上一次提交或回滚后执行的SQL语句组成了当前事务。要控制自治事务，需使用下面的语句，它们只能应用到当前事务：

1. COMMIT
2. ROLLBACK [TO savepoint_name]
3. SAVEPOINT savepoint_name
4. SET TRANSACTION

COMMIT可以结束当前事务并把数据的变化保存到数据库中。ROLLBACK可以结束当前事务并放弃所有的数据变化，把数据恢复到未变化之前的状态。ROLLBACK还可以部分恢复事务。SAVEPOINT可以在一个事务中标记当前点；SET TRANSACTION能设置事务的属性，如读写访问和隔离级别。

要注意的是，设置在主事务中的属性并不会影响到它的自治事务。

• 进入与退出

当我们进入一个自治事务的执行部分时，主事务就被会挂起。当我们退出程序时，主事务就会恢复。要想正常地退出事务，我们就要显示地提交或回滚所有的自治