第六章 PL/SQL与Oracle间交互（转载）

第六章 PL/SQL与Oracle间交互

一、PL/SQL支持的SQL一览

PL/SQL扩展了SQL，变得更加强大和易用。我们可以用PL/SQL灵活安全地操作Oracle数据，因为它支持所有的SQL数据操作语句(除了EXPLAIN PLAN)，事务控制语句，函数，伪列和操作符。PL/SQL还支持动态SQL，能让我们动态地执行SQL数据定义，数据控制和会话控制语句。除此之外，PL/SQL还遵循ANSI/ISO的SQL标准。

1、数据操作

我们可以使用INSERT、UPDATE、DELETE、SELECT和LOCK TABLE来操作Oracle数据。INSERT用于向数据表中插入新行；UPDATE能修改行；DELETE可以删除不需要的行；SELECT可以按照我们给定的查询标准检索出满足条件的行；LOCK TABLE可以临时限制对一个数据表的访问。

2、事务控制

Oracle是面向事务的，它使用事务来保证数据的完整性。数据库事务是指作为单个逻辑工作单元执行的一系列SQL操作。

同时，Oracle还可以通过事务将我们的操作持久化或取消。如果我们的程序在事务中有一步执行失败，Oracle就会发现错误并回滚整个事务。这样，数据库就会自动恢复到先前的状态。

我们可以使用COMMIT、ROLLBACK、SAVEPOINT和SET TRANSACTION命令来控制事务。COMMIT会提交事务，将数据库的变化永久性地提交；ROLLBACK会终止当前事务，并放弃自事务开启后所有发生变化的内容；SAVEPOINT能够标记当前事务的处理进度；一并使用ROLLBACK和SAVEPOINT就可以回滚部分事务。SET TRANSACTION可以设置事务的属性，如读写访问和隔离级别。

3、SQL函数

PL/SQL能让我们使用所有的SQL聚合函数：AVG、COUNT、GROUPING、MAX、MIN、STDDEV、SUM和VARIANCE。除了COUNT(*)之外，所有的聚合函数都会忽略空值。

我们可以在SQL语句中使用聚合函数，但不能在过程化语句中使用。聚合函数会作用于整个字段，除非我们使用SELECT GROUP BY语句对返回的结果行进行分组排序。如果我们忽略GROUP BY子句，聚合函数就会把所有的结果行当作一个整体来处理。我们可以使用下面的语法来调用聚合函数：

function_name([ALL | DISTINCT] expression)

expression会引用一个或多个数据库字段。如果我们指定ALL(默认的)，聚合函数就会考虑所有的行，也包括重复的行内容。如果我们指定的是DISTINCT，聚合函数只会考虑有区别的值。例如，下面的语句只返回数据表emp中不同的职别的个数：

SELECT COUNT(DISTINCT job) INTO job_count FROM emp;

函数COUNT可以让我们使用星号(*)操作符返回一个数据表中行的个数。例如，下面的语句返回表emp中行的个数：

SELECT COUNT(*) INTO emp_count FROM emp;

如果使用默认的ALL，聚合函数会考虑所有的列值，包括重复项；如果改换DISTINCT的话，聚合函数就只会考虑重复项中的一个值。

4、SQL伪列

PL/SQL可以辨识CURRVAL、LEVEL、NEXTVAL、ROWID和ROWNUM这样能返回特殊的数据项的SQL伪列。伪列并不是真实存在于数据表中的字段，但它们的行为看起来跟真实字段一样。例如，我们在查询时就可以选取伪列。但是，我们不允许在SQL语句中向伪列中插入值。伪列只能在SQL语句中使用，不能用在过程化语句中。

• CURRVAL and NEXTVAL

序列(sequence)是一个能产生顺序编号的模式对象。创建序列时，我们可以指定它的初始值和增量值。CURRVAL能返回指定序列的当前值。

在使用序列前，我们必须先调用NEXTVAL，NEXTVAL的作用是生成并返回序列的下一个顺序值。想要得到序列中的当前值或是下一个值，我们必须使用点标志，方法如下：

sequence_name.CURRVAL sequence_name.NEXTVAL

创建序列后，我们就可以在事务处理中用它产生唯一的顺序编号了。但是，我们只能在用SELECT列表、VALUES子句和SET子句中使用CURRVAL和NEXTVAL。在下面的例子中，我们使用序列向两个数据表中插入相同的雇员编号：

INSERT INTO emp VALUES (empno_seq.NEXTVAL, my_ename, ...); INSERT INTO sals VALUES (empno_seq.CURRVAL, my_sal, ...);

在事务中调用NEXTVAL时，序列的值会立即发生改变，不管我们是否使用提交或回滚语句。

• LEVEL

我们可以用LEVEL配合SELECT CONNECT BY语句把数据表中的行组织成一个树形结构。LEVEL能返回树形结构中节点的级数。根节点的级数是1，根节点的子节点级数是2，孙子节点级数是3，依次类推。

在START WITH子句中，我们指定辨识根节点的条件。我们可以使用PRIOR操作符指定树的遍历方向(从根向下或从枝干向上)。

• ROWID

ROWID能够返回数据表中行的ROWID(二进制地址)。我们可以使用UROWID类型变量来存放具有可读性的ROWID，下例中，我们声明了一个名为row_id的变量：

DECLARE   row_id UROWID;

当我们把物理rowid放到UROWID变量中时，就要使用函数ROWIDTOCHAR，它能把二进制值转成一个长度为18字节的字符串。然后我们就可以在UPDATE或DELETE语句的WHERE子句中，把UROWID变量的值同ROWID伪列值进行比较并找出从游标中取得的最新行。

• ROWNUM

ROWNUM能够返回从数据表中选出的行的标识。被选取的第一行的ROWNUM是1，第二行是2，依次类推。如果SELECT语句包含一个ORDER BY子句，ROWNUM会在排序操作之前被指定。

我们可以在UPDATE语句中用ROWNUM为每一行数据赋上一个唯一的值。同样，也可以在SELECT语句的WHERE子句中用ROWNUM限定选取的行的个数，如下例：

DECLARE   CURSOR c1 IS     SELECT empno, sal       FROM emp      WHERE sal > 2000 AND ROWNUM <= 10;   -- returns 10 rows

ROWNUM的值只是在行被检索出来的时候才会增长，所以，只有在WHERE子句中的ROWNUM才有意义：

... WHERE ROWNUM ... WHERE ROWNUM <= constant;

5、SQL操作符

PL/SQL能让我们在SQL语句中使用所有的SQL比较操作符、集合操作符和行操作符。这里简要描述一下其中几个操作符的功能。

• 比较操作符

我们常常需要在一个数据操作语句的WHERE子句中使用比较操作符来判断一个表达式的值是TRUE、FALSE还是NULL。下面的表格描述了各个操作符的用途：

操作符	描述
ALL	把同一个列表中的一个值与其他值或是子查询返回的值进行比较，如果所有的比较结果都为TRUE，那么结果值就为TRUE。
ANY, SOME	把同一个列表中的一个值与其它值或是子查询返回的值进行比较，只要其中有一个结果值为TRUE，那么结果值就为TRUE。
BETWEEN	测试一个值是否在指定的区间范围内。
EXISTS	如果子查询至少返回一行数据，那么EXISTS的结果就为TRUE。
IN	测试指定的值是否是集合成员。
IS	测试指定的值是否是NULL。
LIKE	测试一个字符串是否满足一个指定的样式，其中包含通配符。

• 集合操作符

集合操作符的作用是把两个查询的结果合并成一个结果。INTERSECT返回两个结果的交集；MINUS把目标结果集与另外一个结果集比较，并把存在于另一个结果集中的记录从目标结果集中去除，然后返回目标结果集；UNION把两个结果集的内容合并，在有重复项的情况下，只保留其中一项；UNION ALL同UNION的作用类似，但它会保留所有记录，不管是否有相同的记录。

• 行操作符

行操作符能返回或引用特定的行。ALL会保留查询或聚合表达式中的重复行；DISTINCT的作用与ALL相反，用于消除重复行；PRIOR能引用树形查询中返回的当前行的父级行。

二、管理游标

PL/SQL有两种类型的游标：隐式游标和显式游标。PL/SQL为所有的SQL数据操作语句都声明了一个隐式的游标，其中也包括只返回一条记录的查询。但是，对于返回多于一条记录的查询来说，我们必须声明显式的游标，然后使用游标FOR循环或使用BULK COLLECT子句。

1、显式游标一览

查询返回的行集合可能是由零行、一行或多行组成，这个结果取决于我们的检索条件。当一个查询返回多行记录时，我们可以显式地声明一个游标来处理每一行数据。游标的声明可以放到PL/SQL块、子程序或包的声明部分。

我们可以使用三种命令来控制游标：OPEN、FETCH和CLOSE。首先用OPEN语句初始化一个游标，然后重复执行FETCH语句取出已检索到的数据，或是使用BULK COLLECT批量选取数据。当处理完结果集中最后一行数据时，就可以用CLOSE语句关闭游标。我们还可以同时打开多个游标并发处理多个查询操作。

• 声明游标

在PL/SQL中，向前引用是不允许的。所以我们必须在其它语句引用游标之前声明它。声明游标时，我们需要为它命名，并用下面的语法把它和一个查询相关联：

CURSOR cursor_name [(parameter[, parameter]...)]   [RETURN return_type] IS select_statement;

return_type必须是记录或是数据表的行类型，parameter的含义如下：

cursor_parameter_name [IN] datatype [{:= | DEFAULT} expression]

例如，我们可以像下面这样声明游标c1和c2：

DECLARE   CURSOR c1 IS     SELECT empno, ename, job, sal       FROM emp      WHERE sal > 2000;   CURSOR c2 RETURN dept%ROWTYPE IS     SELECT *       FROM dept      WHERE deptno = 10;

游标名是一个未声明的标识符，而不是PL/SQL变量名。我们不能把值赋给游标名或是在一个表达式中使用它。但是，游标和变量有着同样的作用域规则。虽然在数据表为游标命名是允许的，但并不推荐这样做。

游标是可以接收参数的，这些参数可以在关联查询中允许出现常量的地方使用。游标的形式参数都必须是IN模式的。因此，它们不能把值赋给实际参数。并且，我们不能给游标的参数添加NOT NULL约束。

如下例所示，我们为游标参数初始化一个默认值。这样，我们就能给游标传递不同个数的实参。并且，我们还可以在不改变游标引用的情况下添加新的参数。

DECLARE   CURSOR c1 (low INTEGER DEFAULT 0, high INTEGER DEFAULT 99) IS     SELECT *       FROM ...

游标参数作用域对于游标来说是本地的，这也就意味着它们只能在游标声明时所指定的查询语句中使用。游标参数的值只在游标被打开的时候被使用。

• 打开游标

游标只能在打开之后才能执行查询操作。对于使用了FOR UPDATE子句的游标来说，OPEN语句会把满足查询条件的行琐住，如下例所示：

DECLARE   CURSOR c1 IS      SELECT ename, job       FROM emp      WHERE sal < 3000;   ... BEGIN   OPEN c1;   ... END;

OPEN语句执行时结果集中的行并不被选取，只有在FETCH语句执行的时候数据才被取得。

• 传递游标参数

我们可以用OPEN语句来为游标传递参数。如果在没有使用参数默认值的情况下，我们就必须在OPEN语句中为游标声明中的每一个形式参数指定一个对应的实际参数。比如下面的游标声明：

DECLARE   emp_name   emp.ename%TYPE;   salary     emp.sal%TYPE;   CURSOR c1 (NAME VARCHAR2, salary NUMBER) IS     SELECT *       FROM ...

下面三个语句都能打开游标：

OPEN c1(emp_name, 3000); OPEN c1('ATTLEY', 1500); OPEN c1(emp_name, salary);

在上面的例子中，标识符salary用在游标声明的时候，它引用的是形式参数。但是，当用在OPEN语句中时，它引用的就是PL/SQL变量，为了避免混乱，最好使用唯一的标识。

有默认值的形参不需要有对应的实参，在OPEN语句执行时程序会自动使用它们的默认值。

我们可以用位置标识法或名称标识法把OPEN语句中的实参和游标声明中的形参关联起来。每个实参的数据类型和它对应的形参数据类型必须兼容。

• 从游标中取值

除非在FETCH时使用BULK COLLECT子句，否则FETCH语句每次只会从结果集中取出一条记录，并把游标向下移动，指向当前记录的下一条记录。下面演示了FETCH语句的用法：

FETCH c1 INTO my_empno, my_ename, my_deptno;

对于游标查询返回的每一个字段值，在INTO列表中都必须有一个与之对应且类型兼容的变量。通常，我们可以像下面这样使用FETCH语句：

LOOP   FETCH c1 INTO my_record;     EXIT WHEN c1%NOTFOUND;   -- process data record END LOOP;

查询可以在它的作用域范围内引用PL/SQL变量，但是，在查询中的任何变量只有在游标被打开时才计算它的值。在下面的例子中，每个被检索出来的salary只和2相乘，即使factor值在后续的操作中已经发生了改变：

DECLARE   my_sal   emp.sal%TYPE;   my_job   emp.job%TYPE;   factor   INTEGER        := 2;   CURSOR c1 IS     SELECT factor * sal       FROM emp      WHERE job = my_job; BEGIN   ...   OPEN c1;   -- here factor equals 2   LOOP     FETCH c1      INTO my_sal;     EXIT WHEN c1%NOTFOUND;     factor  := factor + 1;   -- does not affect FETCH   END LOOP; END;

要想改变查询结果集或查询中的变量值，我们就必须关闭并重新打开含有输入变量的游标，这样才能使用新的输入变量值。

但是，我们可以为同一游标每次取得的数据指定一个不同的INTO列表。这样每次取得的数据就会被放到目标变量中去，如下例所示：

DECLARE   CURSOR c1 IS     SELECT ename       FROM emp;   name1   emp.ename%TYPE;   name2   emp.ename%TYPE;   name3   emp.ename%TYPE; BEGIN   OPEN c1;   FETCH c1    INTO name1;   -- this fetches first row   FETCH c1    INTO name2;   -- this fetches second row   FETCH c1    INTO name3;   -- this fetches third row   ...   CLOSE c1; END;

如果游标的指向超过了结果集中的最后一行，那么目标变量的值就无法确定。

注意：在结果集的最后一次执行的FETCH语句一定取不到数据，而且没有异常发生。想要发现这种情况，我们就必须得使用游标的%FOUND或%NOTFOUND属性。

• 批量取得游标中的数据

BULK COLLECT子句能让我们批量的绑定数据。这样我们就能一次性从结果集中取得所有的行。在下面的例子中，我们从游标中批量取得数据分别放到两个集合中去：

DECLARE   TYPE numtab IS TABLE OF emp.empno%TYPE;   TYPE nametab IS TABLE OF emp.ename%TYPE;   nums    numtab;   names   nametab;   CURSOR c1 IS     SELECT empno, ename       FROM emp      WHERE job = 'CLERK'; BEGIN   OPEN c1;   FETCH c1   BULK COLLECT INTO nums, names;   ...   CLOSE c1; END;

• 关闭游标

CLOSE语句可以关闭游标，游标被关闭后，我们还可以重新打开它。操作一个已经关闭了的游标会抛出预定义异常INVALID_CURSOR。

• 在游标中使用子查询

子查询就是出现在另外一个SQL数据操作语句中的查询(通常用圆括号封闭)。计算时，子查询能为语句提供一个值或是一个值集合。通常，子查询用在WHERE子句中。例如，下面的查询就用于返回不在芝加哥居住的雇员：

DECLARE   CURSOR c1 IS     SELECT empno, ename       FROM emp      WHERE deptno IN (SELECT deptno                         FROM dept                        WHERE loc <> 'CHICAGO');

再举一个在FROM子句中使用子查询的例子，下面语句能够返回含有五个或五个以上雇员的部门编号和部门名称：

DECLARE   CURSOR c1 IS     SELECT t1.deptno, dname, "STAFF"       FROM dept t1, (SELECT   deptno, COUNT (*) "STAFF"                          FROM emp                      GROUP BY deptno) t2      WHERE t1.deptno = t2.deptno AND "STAFF" >= 5;

针对一个数据表的子查询只执行一次，而具有关联关系的子查询会为每一行结果执行一次。如下面的查询，它的作用是找出工资超过所在部门的平均工资的雇员姓名和工资。对于emp表中的每一行，关联子查询都会为它计算一次部门的平均工资。

DECLARE   CURSOR c1 IS     SELECT   deptno, ename, sal         FROM emp t        WHERE sal > (SELECT AVG (sal)                       FROM emp                      WHERE t.deptno = deptno)     ORDER BY deptno;

2、隐式游标一览

Oracle会隐式地打开一个游标处理所有那些不与显式游标相关联的SQL语句。我们可以引用这个隐式的SQL游标，虽然不能使用OPEN、FETCH和CLOSE语句来控制SQL游标，但可以利用它的属性来获取与最近执行的SQL语句的相关信息。

三、用包将游标的声明和游标体分离

我们可以将游标说明从游标体中分离出来并放到包中。这样做的话就可以在不改变游标说明的条件下修改游标体。我们可以在包说明中用下面语法编写游标说明：

CURSOR cursor_name [(parameter[, parameter]...)] RETURN return_type;

在下面的例子中，我们可以使用%ROWTYPE属性来代表数据表emp中的行类型：

CREATE PACKAGE emp_stuff AS   CURSOR c1 RETURN emp%ROWTYPE;   -- declare cursor spec   ... END emp_stuff; CREATE PACKAGE BODY emp_stuff AS   CURSOR c1 RETURN emp%ROWTYPE IS     SELECT *       FROM emp      WHERE sal > 2500;   -- define cursor body   ... END emp_stuff;

游标说明部分并不含有SELECT语句，因为后面RETURN子句中指明了返回值的数据类型。并且，游标体中含有的SELECT语句列表中的每一项，必须和说明部分的RETURN子句相匹配。

打包游标有着更好的灵活性。例如，我们可以任意修改上例中声明的游标的游标体而不用修改游标说明：

CREATE PACKAGE BODY emp_stuff AS   CURSOR c1 RETURN emp%ROWTYPE IS     SELECT *       FROM emp      WHERE deptno = 20;   -- new WHERE clause   ... END emp_stuff;

我们可以使用点标志从一个PL/SQL块或子程序中引用一个打包游标，如下例所示：

 

DECLARE   emp_rec   emp%ROWTYPE;   ... BEGIN   ...   OPEN emp_stuff.c1;   LOOP     FETCH emp_stuff.c1      INTO emp_rec;     EXIT WHEN emp_suff.c1%NOTFOUND;     ...   END LOOP;   CLOSE emp_stuff.c1; END;

打包游标的作用域并不局限于某个特定的PL/SQL块。所以，打开一个打包游标后，它会一直保持打开状态直到我们关闭它或是退出Oracle会话。

四、使用游标FOR循环

在大多数需要使用显式游标的情况下，我们都可以用一个游标FOR循环来代替OPEN、FETCH和CLOSE语句。游标FOR循环隐式地声明了一个%ROWTYPE类型的记录作为它的循环索引，打开游标，然后反复执行把结果集中的行放到索引中去，最后在所有行都被处理完成后关闭游标。

思考下面PL/SQL块例子，它能从一个实验中计算出结果，然后把结果保存在一张临时表中。FOR循环的索引c1_rec是被隐式声明的记录。它的每一个域都保存来自游标c1中取出的值。对独立的域的引用可以使用点标志。

DECLARE   RESULT   temp.col1%TYPE;   CURSOR c1 IS     SELECT n1, n2, n3       FROM data_table      WHERE exper_num = 1; BEGIN   FOR c1_rec IN c1 LOOP     /* calculate and store the results */     RESULT  := c1_rec.n2 / (c1_rec.n1 + c1_rec.n3);     INSERT INTO temp          VALUES (RESULT, NULL, NULL);   END LOOP;   COMMIT; END;

当进入游标FOR循环时后，游标的名称就不属于用OPEN语句打开的游标，也不属于封闭游标FOR循环。在每个循环之前，PL/SQL会把数据放到隐式声明的记录中去。记录的有效作用范围只在循环内，所以我们不能在循环的外部引用它。

循环内的语句序列会为每一个满足条件的结果行执行一次，当游标离开循环时，游标会被自动地关闭，这包括正常地使用EXIT或GOTO语句来结束循环，或是因异常抛出而退出循环的情况。

1、使用子查询代替显式游标

有时候我们并不需要声明游标，因为PL/SQL允许我们使用子查询来进行替代。下面的游标FOR循环先计算奖金值，然后把结果插入数据表中：

DECLARE   bonus   REAL; BEGIN   FOR emp_rec IN (SELECT empno, sal, comm                     FROM emp) LOOP     bonus  := (emp_rec.sal * 0.05) + (emp_rec.comm * 0.25);     INSERT INTO bonuses          VALUES (emp_rec.empno, bonus);   END LOOP;   COMMIT; END;

2、使用游标子查询

我们可以使用游标子查询(又称游标表达式)把一个查询结果集作为参数传递给函数。如下例：

SELECT * FROM TABLE(StockPivot(CURSOR(SELECT * FROM StockTable)));

游标子查询通常可以用在表函数中，这将在第八章详细讨论。

3、在游标FOR循环中为表达式定义别名

隐式声明的记录中每个域存放着最近取得的数据。记录的域名称和SELECT列表中的字段相对应。但是，如果SELECT中含有表达式时会发生什么呢？看一下下面的例子：

CURSOR c1 IS   SELECT empno, sal + NVL (comm, 0) wages, job     FROM ...

这样的情况下，我们就必须为表达式起一个别名。如下例，wages就是表达式sal+NVL(comm,0)的一个别名：

CURSOR c1 IS   SELECT empno, sal + NVL (comm, 0) wages, job     FROM ...

如果要引用对应的域，就得使用别名进行代替，如下例所示：

IF emp_rec.wages THEN ...

4、为游标FOR循环传递参数

我们可以在游标FOR循环中把参数传递给游标。下例中，我们传递一个部门编号。然后计算出该部门应付给它的雇员的工资数额。并且，我们可以判断出有多少雇员的工资超过2000和/或他们的佣金大于他们的工资。

DECLARE   CURSOR emp_cursor (dnum NUMBER) IS     SELECT sal, comm       FROM emp      WHERE deptno = dnum;   total_wages   NUMBER (11, 2) := 0;   high_paid     NUMBER (4)     := 0;   higher_comm   NUMBER (4)     := 0; BEGIN   /* The number of iterations will equal the number of rows   returned by emp_cursor. */   FOR emp_record IN emp_cursor (20) LOOP     emp_record.comm  := NVL (emp_record.comm, 0);     total_wages      := total_wages + emp_record.sal + emp_record.comm;     IF emp_record.sal > 2000.00 THEN       high_paid  := high_paid + 1;     END IF;     IF emp_record.comm > emp_record.sal THEN       higher_comm  := higher_comm + 1;     END IF;   END LOOP;   INSERT INTO temp        VALUES (high_paid, higher_comm,                'Total Wages: ' || TO_CHAR (total_wages));   COMMIT; END;

五、使用游标变量

跟游标一样，游标变量也是指向多行查询的结果集中的当前行。但是，游标与游标变量不同之处就和常量与变量不同之处类似。游标是静态的，而游标变量是动态的，因为游标变量并不与某个特定的查询相绑定。所以，游标变量可以打开任何类型兼容的查询，灵活性很大。

并且，我们还可以为游标变量赋新值，把它作为参数传递给本地和存储子程序。这就很容易地让我们把数据检索集中化处理。

游标变量可以在每个PL/SQL客户端使用。例如，我们可以在OCI或Pro*C这样的主环境中声明游标变量，然后把它作为输入主变量(绑定变量)传给PL/SQL。并且，像Oracle Forms和Oracle Reports这样的含有PL/SQL引擎的开发工具，完全可以在客户端使用游标变量。Oracle服务器也有一个PL/SQL引擎。所以，我们可以在应用程序和服务器之间通过远程调用(RPC)来回传递游标变量。

1、什么是游标变量

游标变量同C或Pascal语言中的指针类似，它指向一块内存地址，而不是地址中的内容本身。所以，声明一个游标变量可以创建一个指针，而不是具体的内容。在PL/SQL中，指针是一个REF X类型，REF是REFERENCE的缩写，而X代表对象的类型。因此，游标变量的数据类型是REF CURSOR。

为了执行多行查询，Oracle会开启一个未命名的工作区来存放处理信息。我们可以用显式游标为工作区命名然后访问相关的信息；或者声明指向工作区的一个游标变量。无论在什么地方使用游标，它总是指向同一个查询工作区，而游标变量则可以指向不同的工作区。所以，游标和游标变量不能交互使用；也就是说，我们不能在该使用游标的地方使用游标变量，不能在该使用游标变量的地方使用游标。

2、为什么使用游标变量

我们主要是在PL/SQL存储子程序和各种客户端之间用游标变量来传递查询结果。PL/SQL和其他客户端程序都不拥有结果集，它们只是共享一个指向存放结果集工作区的指针而已。例如，一个OCI客户端，一个Oracle Forms应用程序和Oracle服务器可以引用同一个工作区。

只要有游标变量指向查询工作区，我们就可以引用它。因此，我们可以把游标变量的值自由地从一个作用域传递到另一个。例如，我们把主游标变量传递到嵌套在Pro*C程序中的PL/SQL块，游标变量指向的工作区就可以被访问。

如果客户端含有PL/SQL引擎，那么从客户端调用服务器端就不会有什么约束。假如我们在客户端声明游标变量，在服务器端打开并取得数据，然后把取得的结果返回给客户端。这些操作都是在服务器端完成，从而也减少了网络流量。

3、定义REF CURSOR类型

创建游标变量分为两个步骤。首先，我们得定义REF CURSOR类型，然后声明该类型的游标变量。我们可以在任何PL/SQL块、子程序或包内使用下面语法来定义REF CURSOR类型：

TYPE ref_type_name IS REF CURSOR [RETURN return_type];

ref_type_name是类型区分符，return_type必须是记录类型或是代表数据表中的行类型。在下面的例子中，我们把返回的数据类型指定为数据表dept的行类型：

DECLARE   TYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE;

REF CURSOR类型可以是强类型也可以是弱类型。如下例所示，强类型REF CURSOR需要指定返回类型，而弱类型没有这个要求：

DECLARE   TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;   -- strong   TYPE GenericCurTyp IS REF CURSOR;   -- weak

强类型REF CURSOR在编译时会检查类型兼容，这样能更好的避免发生错误。但是，弱类型REF CURSOR具有更大的灵活性，因为它允许我们把游标变量与任何相兼容的查询关联起来。

4、声明游标变量

定义了REF CURSOR后，就可以在PL/SQL块或子程序中声明该类型的游标变量了。在下面的程序中，我们声明了游标变量dept_cv：

DECLARE TYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE; dept_cv DeptCurTyp;   -- declare cursor variable

注意：不能在包里声明游标变量。与打包变量不同，游标变量没有一个持久的状态。记住，声明游标变量就是创建了一个指针，它只是指向一个内容，本身并不存在，所以，游标变量是不能存放到数据库中的。变量的作用域规则也同样适用于游标变量。本地的PL/SQL游标变量在我们进入块或子程序时被初始化，在退出时被销毁。

在REF CURSOR定义的RETURN子句中，我们可以使用%ROWTYPE指定返回类型，如下例：

 

DECLARE   TYPE tmpcurtyp IS REF CURSOR     RETURN emp%ROWTYPE;   tmp_cv   tmpcurtyp;   -- declare cursor variable   TYPE empcurtyp IS REF CURSOR     RETURN tmp_cv%ROWTYPE;   emp_cv   empcurtyp;   -- declare cursor variable

同样，也可以使用%TYPE指定返回值类型，如下例：

DECLARE   dept_rec   dept%ROWTYPE;   -- declare record variable   TYPE deptcurtyp IS REF CURSOR     RETURN dept_rec%TYPE;   dept_cv    deptcurtyp;   -- declare cursor variable

最后一个例子，我们把RETURN子句的返回类型指定为用户自定义的RECORD类型：

DECLARE   TYPE emprectyp IS RECORD (     empno   NUMBER (4),     ename   VARCHAR2(1O),     sal     NUMBER (7, 2)   );   TYPE empcurtyp IS REF CURSOR     RETURN emprectyp;   emp_cv   empcurtyp;   -- declare cursor variable

• 作为参数的游标变量

我们可以把游标变量声明为函数和过程的形式参数。在下面例子中，我们定义REF CURSOR类型的EmpCurTyp，然后把该类型的游标变量作为过程的形式参数：

DECLARE   TYPE empcurtyp IS REF CURSOR     RETURN emp%ROWTYPE;   PROCEDURE open_emp_cv (emp_cv IN OUT empcurtyp) IS   ...

注意：跟所有的指针一样，游标变量也能增加参数别名出现的可能性。

5、控制游标变量

我们可以使用三个语句来控制游标变量：OPEN-FOR、FETCH和CLOSE。首先，使用OPEN打开游标变量，然后从结果集中FETCH数据，当完成所有的处理后，就可以使用CLOSE语句关闭游标变量。

• 打开游标变量

OPEN-FOR语句把一个游标变量和一个多行查询关联起来并执行查询。语法如下：

OPEN {cursor_variable | :host_cursor_variable} FOR { select_statement | dynamic_string [USING bind_argument[, bind_argument]...] };

host_cursor_variable是声明在PL/SQL主环境中的游标变量，dynamic_string代表多行查询的字符串表达式。

注意，这里只讨论使用静态SQL的情况。

与游标不同，游标变量是没有参数的。但这个并不影响灵活性，因为我们可以把整个查询传递给游标变量。被传递的查询语句可以使用主变量、PL/SQL变量、参数和函数。

下例中，我们首先打开游标变量emp_cv。与游标相似，我们也可以在游标变量上使用%FOUND、%NOTFOUND、%ISOPEN和%ROWCOUNT：

IF NOT emp_cv%ISOPEN THEN   /* Open cursor variable. */   OPEN emp_cv FOR SELECT * FROM emp; END IF;

其它的OPEN-FOR语句可以使用不同的查询打开同样的游标变量。在重新打开游标变量之前是不需要关闭它的(但对一个静态游标使用多次OPEN操作时，系统会抛出预定义异常CURSOR_ALREADY_OPEN)。为执行一个不同的查询而重新打开游标变量时，前面的查询结果就会丢失。

一般地，我们可以把游标变量传递给过程，然后由过程负责打开它，如下例，打包过程打开游标变量emp_cv：

CREATE PACKAGE emp_data AS   ...   TYPE empcurtyp IS REF CURSOR     RETURN emp%ROWTYPE;   PROCEDURE open_emp_cv (emp_cv IN OUT empcurtyp); END emp_data; CREATE PACKAGE BODY emp_data AS   ...   PROCEDURE open_emp_cv (emp_cv IN OUT empcurtyp) IS   BEGIN     OPEN emp_cv FOR       SELECT *         FROM emp;   END open_emp_cv; END emp_data;

当我们把游标变量声明为一个打开游标变量的子程序的形式参数时，就必须要指定参数模式为IN OUT模式。那样的话，子程序就可以把一个打开的游标变量返回给调用者。

另外，我们还可以使用独立的过程来打开游标变量。只要简单的在包里定义REF CURSOR类型，然后在一个独立的过程中引用它就行了。例如，如果我们创建了下面的无体包，我们就能在独立的过程中引用包中所定义的REF CURSOR了：

CREATE PACKAGE cv_types AS   TYPE genericcurtyp IS REF CURSOR;   TYPE empcurtyp IS REF CURSOR     RETURN emp%ROWTYPE;   TYPE deptcurtyp IS REF CURSOR     RETURN dept%ROWTYPE;   ... END cv_types;

下例中，我们创建一个引用REF CURSOR类型EmpCurTyp的过程，这个类型是在包cv_types中定义的。

CREATE PROCEDURE open_emp_cv (emp_cv IN OUT cv_types.empcurtyp) AS BEGIN   OPEN emp_cv FOR     SELECT *       FROM emp; END open_emp_cv;

为把数据检索集中化处理，我们可以把一个存储过程中类型兼容的查询进行分组。在下面的例子中，打包过程声明了一个选择器作为它的形势参数。调用时，过程会为选定的查询打开游标变量：

CREATE PACKAGE emp_data AS   TYPE empcurtyp IS REF CURSOR     RETURN emp%ROWTYPE;   PROCEDURE open_emp_cv (emp_cv IN OUT empcurtyp, choice INT); END emp_data; CREATE PACKAGE BODY emp_data AS   PROCEDURE open_emp_cv (emp_cv IN OUT empcurtyp, choice INT) IS   BEGIN     IF choice = 1 THEN       OPEN emp_cv FOR         SELECT *           FROM emp          WHERE comm IS NOT NULL;     ELSIF choice = 2 THEN       OPEN emp_cv FOR         SELECT *           FROM emp          WHERE sal > 2500;     ELSIF choice = 3 THEN       OPEN emp_cv FOR         SELECT *           FROM emp          WHERE deptno = 20;     END IF;   END; END emp_data;

为了获取更大的灵活性，我们可以把游标变量和选择器传递给过程，让它执行查询然后返回不同的查询结果。如下例所示：

CREATE PACKAGE admin_data AS   TYPE gencurtyp IS REF CURSOR;   PROCEDURE open_cv (generic_cv IN OUT gencurtyp, choice INT); END admin_data; CREATE PACKAGE BODY admin_data AS   PROCEDURE open_cv (generic_cv IN OUT gencurtyp, choice INT) IS   BEGIN     IF choice = 1 THEN       OPEN generic_cv FOR         SELECT *           FROM emp;     ELSIF choice = 2 THEN       OPEN generic_cv FOR         SELECT *           FROM dept;     ELSIF choice = 3 THEN       OPEN generic_cv FOR         SELECT *           FROM salgrade;     END IF;   END; END admin_data;

• 使用游标变量作为主变量

我们可以在OCI或Pro*C程序这样的PL/SQL主环境中声明游标变量。在使用游标变量之前，我们需要把它作为主变量传递给PL/SQL。在下面的Pro*C例子中，我们把主游标变量和选择器一并传递给PL/SQL块，然后为被选择的查询打开游标变量：

EXEC SQL BEGIN DECLARE SECTION; ... /* Declare host cursor variable. */ SQL_CURSOR generic_cv; int choice; EXEC SQL END DECLARE SECTION; ... /* Initialize host cursor variable. */ EXEC SQL ALLOCATE :generic_cv; ... /* Pass host cursor variable and selector to PL/SQL block. */ EXEC SQL EXECUTE BEGIN   IF :choice = 1 THEN     OPEN :generic_cv FOR       SELECT *         FROM emp;   ELSIF :choice = 2 THEN     OPEN :generic_cv FOR       SELECT *         FROM dept;   ELSIF :choice = 3 THEN     OPEN :generic_cv FOR       SELECT *         FROM salgrade;   END IF; END; END-EXEC;

主游标变量与任何查询的返回类型都兼容，它们就像PL/SQL中的弱类型游标变量一样。

• 从游标变量中取得数据

FETCH语句能从多行查询的结果集中取得数据，语法如下：

FETCH {cursor_variable_name | :host_cursor_variable_name} [BULK COLLECT] INTO {variable_name[, variable_name]... | record_name};

下面的例子中，我们每次都从游标变量emp_cv中取出一条数据放到用户定义的记录emp_rec中：

LOOP   /* Fetch from cursor variable. */   FETCH emp_cv    INTO emp_rec;   EXIT WHEN emp_cv%NOTFOUND;   -- exit when last row is fetched   -- process data record END LOOP;

我们可以使用BULK COLLECT子句批量地从游标变量中取得数据放到一个或多个集合中。如下例所示：

DECLARE   TYPE empcurtyp IS REF CURSOR     RETURN emp%ROWTYPE;   TYPE namelist IS TABLE OF emp.ename%TYPE;   TYPE sallist IS TABLE OF emp.sal%TYPE;   emp_cv   empcurtyp;   names    namelist;   sals     sallist; BEGIN   OPEN emp_cv FOR     SELECT ename, sal       FROM emp;   FETCH emp_cv   BULK COLLECT INTO names, sals;   ... END;

当游标变量被打开时，关联查询中的所有变量都会被计算。如果要改变查询中的结果集或要使用变量的最新值，我们就必须重新打开游标变量。不过我们可以为每一个从游标变量中取得数据使用不同的INTO子句。

PL/SQL能保证游标变量的返回类型与FETCH语句中的INTO子句后面的类型相兼容。对于游标变量的关联查询返回的每一个字段，INTO子句后面都必须有一个与之相对应的、类型兼容的域或变量。同样，字段的个数和域的个数也应该相同。否则的话，就会产生错误。如果游标变量是强类型的话，这个错误在编译期就会发生；如果是弱类型，错误会在运行时发生。在运行时，PL/SQL会在第一次取得数据之前抛出预定义异常ROWTYPE_MISMATCH。所以，如果我们捕获到错误，并使用一个不同的INTO子句再次执行FETCH语句，就不会丢失数据。

如果我们把游标变量声明为从游标变量中取得数据的子程序的形式参数，那么我们必须指定参数模式为IN或IN OUT模式。但是，如果在子程序中还需要打开游标变量的话，就必须使用IN OUT模式。

如果我们从一个已经关闭了的或是一个未打开的游标变量中选取数据，PL/SQL就会抛出预定义异常INVALID_CURSOR。

• 关闭游标变量

CLOSE语句会关闭游标变量。如果执行了关闭操作，相关的结果集就不确定了。关闭操作的语法如下：

CLOSE {cursor_variable_name | :host_cursor_variable_name);

在下面的例子中，当最后一行数据也被处理完毕时，我们就可以关闭游标变量emp_cv：

LOOP   FETCH emp_cv    INTO emp_rec;   EXIT WHEN emp_cv%NOTFOUND;   -- process data record END LOOP; /* Close cursor variable. */ CLOSE emp_cv;

当把游标变量作为用于关闭游标变量的子程序的形式参数时，我们必须指定它的参数模式为IN或IN OUT模式。

如果我们从一个已经关闭了的或是一个未打开的游标变量中选取数据，PL/SQL就会抛出预定义异常INVALID_CURSOR。

6、游标变量示例：主从表

思考下面的存储过程，它的作用是搜索图书馆数据库中的图书、期刊和磁带。主表存放标题和类别编号(其中1=书，2=期刊，3=磁带)。三个详细表分别保存特定类别的信息。在调用时，存储过程会按照标题来搜索主表，然后利用主表提供的类别编号到从详细表中检索详细内容。

CREATE PACKAGE cv_types AS   TYPE libcurtyp IS REF CURSOR;   ... END cv_types; CREATE PROCEDURE FIND_ITEM (title VARCHAR2,                             lib_cv IN OUT cv_types.libcurtyp) AS   code   BINARY_INTEGER; BEGIN   SELECT item_code     INTO code     FROM titles    WHERE item_title = title;   IF code = 1 THEN     OPEN lib_cv FOR       SELECT *         FROM books        WHERE book_title = title;   ELSIF code = 2 THEN     OPEN lib_cv FOR       SELECT *         FROM periodicals        WHERE periodical_title = title;   ELSIF code = 3 THEN     OPEN lib_cv FOR       SELECT *         FROM tapes        WHERE tape_title = title;   END IF; END FIND_ITEM;

7、游标变量示例：客户端PL/SQL块

一个客户端应用程序可能会使用下面的PL/SQL块来显示检索出来的信息：

DECLARE   lib_cv           cv_types.libcurtyp;   book_rec         books%ROWTYPE;   periodical_rec   periodicals%ROWTYPE;   tape_rec         tapes%ROWTYPE; BEGIN   get_title (:title);   -- title is a host variable   FIND_ITEM (:title, lib_cv);   FETCH lib_cv    INTO book_rec;   display_book (book_rec); EXCEPTION   WHEN ROWTYPE_MISMATCH THEN     BEGIN       FETCH lib_cv        INTO periodical_rec;       display_periodical (periodical_rec);     EXCEPTION       WHEN ROWTYPE_MISMATCH THEN         FETCH lib_cv          INTO tape_rec;         display_tape (tape_rec);     END; END;

8、游标变量示例：Pro*C程序

下面的Pro*C程序让用户选择一张数据表，然后使用游标变量进行查询，并返回查询结果：

#include  #include  void sql_error(); main() {   char temp[32];   EXEC SQL BEGIN DECLARE SECTION;   char * uid = "scott/tiger";   SQL_CURSOR generic_cv; /* cursor variable */   int table_num; /* selector */   struct /* EMP record */   {     int emp_num;     char emp_name[11];     char job_title[10];     int manager;     char hire_date[10];     float salary;     float commission;     int dept_num;   } emp_rec;   struct /* DEPT record */   {     int dept_num;     char dept_name[15];     char location[14];   } dept_rec;   struct /* BONUS record */   {     char emp_name[11];     char job_title[10];     float salary;   } bonus_rec;   EXEC SQL END DECLARE SECTION;   /* Handle Oracle errors. */   EXEC SQL WHENEVER SQLERROR DO sql_error();   /* Connect to Oracle. */   EXEC SQL CONNECT :uid;   /* Initialize cursor variable. */   EXEC SQL ALLOCATE :generic_cv;   /* Exit loop when done fetching. */   EXEC SQL WHENEVER NOT FOUND DO break;   for (;;)   {     printf("/n1 = EMP, 2 = DEPT, 3 = BONUS");     printf("/nEnter table number (0 to quit): ");     gets(temp);     table_num = atoi(temp);     if (table_num <= 0) break;     /* Open cursor variable. */     EXEC SQL EXECUTE     BEGIN       IF :table_num = 1 THEN         OPEN :generic_cv FOR           SELECT *             FROM emp;       ELSIF :table_num = 2 THEN         OPEN :generic_cv FOR           SELECT *             FROM dept;       ELSIF :table_num = 3 THEN         OPEN :generic_cv FOR           SELECT *             FROM bonus;       END IF;     END;     END-EXEC;     for (;;)     {       switch (table_num)       {       case 1: /* Fetch row into EMP record. */         EXEC SQL FETCH :generic_cv INTO :emp_rec;         break;       case 2: /* Fetch row into DEPT record. */         EXEC SQL FETCH :generic_cv INTO :dept_rec;         break;       case 3: /* Fetch row into BONUS record. */         EXEC SQL FETCH :generic_cv INTO :bonus_rec;         break;       }       /* Process data record here. */     }     /* Close cursor variable. */     EXEC SQL CLOSE :generic_cv;   }   exit(0); } void sql_error() {   /* Handle SQL error here. */ }

9、游标变量示例：SQL*Plus中操作主变量

主变量就是一个声明在主环境中的变量，它会被传递到一个或多个PL/SQL程序中，在程序中可以跟其他的变量一样使用。在SQL*Plus环境里，可以使用命令VARIABLE来声明主变量。例如，我们可以像下面这样声明一个NUMBER类型的主变量：

VARIABLE return_code NUMBER

SQL*Plus和PL/SQL都能引用主变量，SQL*Plus还可以显示主变量的值。但是，在PL/SQL中引用主变量的时候，我们必须加上冒号(:)前缀，如下例所示：

DECLARE   ... BEGIN   :return_code := 0;   IF credit_check_ok(acct_no) THEN     :return_code := 1;   END IF;   ... END;

在SQL*Plus环境里，我们可以使用PRINT命令来显示主变量的值，例如：

SQL> PRINT return_code RETURN_CODE ----------- 1

SQL*Plus中的REF CURSOR数据类型可以让我们声明游标变量，这样就可以使用存储子程序返回的查询结果。在下面的脚本中，我们声明了REFCURSOR类型的主变量。我们还可以在SQL*Plus中使用SET AUTOPRINT ON命令来自动地显示查询结果：

CREATE PACKAGE emp_data AS   TYPE emprectyp IS RECORD (     emp_id      NUMBER (4),     emp_name    VARCHAR2 (10),     job_title   VARCHAR2 (9),     dept_name   VARCHAR2 (14),     dept_loc    VARCHAR2 (13)   );   TYPE empcurtyp IS REF CURSOR     RETURN emprectyp;   PROCEDURE get_staff (dept_no IN NUMBER, emp_cv IN OUT empcurtyp); END; / CREATE PACKAGE BODY emp_data AS   PROCEDURE get_staff (dept_no IN NUMBER, emp_cv IN OUT empcurtyp) IS   BEGIN     OPEN emp_cv FOR       SELECT   empno, ename, job, dname, loc           FROM emp, dept          WHERE emp.deptno = dept_no AND emp.deptno = dept.deptno       ORDER BY empno;   END; END; / COLUMN EMPNO HEADING Number COLUMN ENAME HEADING Name COLUMN JOB HEADING JobTitle COLUMN DNAME HEADING Department COLUMN LOC HEADING Location SET AUTOPRINT ON VARIABLE cv REFCURSOR EXECUTE emp_data.get_staff(20, :cv)

10、在向PL/SQL块传递主游标变量时减少网络负荷

在向PL/SQL传递主游标变量时，我们可以把多个OPEN-FOR语句组合在一起使用，以便减少网络流量。例如，下面的PL/SQL块：

/* anonymous PL/SQL block in host environment */ BEGIN   OPEN :emp_cv FOR     SELECT *       FROM emp;   OPEN :dept_cv FOR     SELECT *       FROM dept;   OPEN :grade_cv FOR     SELECT *       FROM salgrade;   OPEN :pay_cv FOR     SELECT *       FROM payroll;   OPEN :ins_cv FOR     SELECT *       FROM insurance; END;

在Oracle Forms中，这种方法可能很有用，比如我们在填充一个多模块窗体的时候。

当我们向PL/SQL块传递一个主游标变量时(游标变量由该块负责打开)，游标变量指向的查询工作区在块结束后还是能够被访问的。这就能让我们的OCI或Pro*C程序在普通的游标操作中继续使用这些工作区。下面的例子中，我们在一个块中打开了多个这样的工作区：

BEGIN   OPEN :c1 FOR     SELECT 1       FROM DUAL;   OPEN :c2 FOR     SELECT 1       FROM DUAL;   OPEN :c3 FOR     SELECT 1       FROM DUAL;   OPEN :c4 FOR     SELECT 1       FROM DUAL;   OPEN :c5 FOR     SELECT 1       FROM DUAL;   ... END;

赋给c1、c1、c1、c1、c1的游标都可以正常使用。当使用完毕时，只要像下面这样简单的关闭就可以了：

BEGIN   CLOSE :c1;   CLOSE :c2;   CLOSE :c3;   CLOSE :c4;   CLOSE :c5;   ... END;

11、避免游标变量的错误

如果在赋值操作中的两个游标变量都是强类型，那么它们必须有着完全相同的数据类型。下面的例子中，虽然游标变量的返回类型相同，但是在赋值操作时也会引起异常，这是因为它们的数据类型不相同：

DECLARE   TYPE empcurtyp IS REF CURSOR     RETURN emp%ROWTYPE;   TYPE tmpcurtyp IS REF CURSOR     RETURN emp%ROWTYPE;   PROCEDURE open_emp_cv (emp_cv IN OUT empcurtyp, tmp_cv IN OUT tmpcurtyp) IS   BEGIN     ...     emp_cv  := tmp_cv;   -- causes 'wrong type' error   END;

如果其中一个或两个游标变量是弱类型，那它们就没必要类型相同了。

如果我们要对一个没有指向工作区的游标变量进行数据取得、关闭或调用游标属性的操作，PL/SQL就会跑出一个INVALID_CURSOR异常。我们有两种方法可以让游标变量(或参数)指向工作区：

1. 对游标变量使用OPEN-FOR语句。

1. 把一个已经OPEN过的主游标变量赋给PL/SQL游标变量。

下面的例子演示了如何使用这两种方法：

DECLARE   TYPE empcurtyp IS REF CURSOR     RETURN emp%ROWTYPE;   emp_cv1   empcurtyp;   emp_cv2   empcurtyp;   emp_rec   emp%ROWTYPE; BEGIN   /* The following assignment is useless because emp_cv1   does not point to a query work area yet. */   emp_cv2  := emp_cv1;   -- useless   /* Make emp_cv1 point to a query work area. */   OPEN emp_cv1 FOR     SELECT *       FROM emp;   /* Use emp_cv1 to fetch first row from emp table. */   FETCH emp_cv1    INTO emp_rec;   /* The following fetch raises an exception because emp_cv2   does not point to a query work area yet. */   FETCH emp_cv2    INTO emp_rec;   -- raises INVALID_CURSOR EXCEPTION   WHEN INVALID_CURSOR THEN     /* Make emp_cv1 and emp_cv2 point to same work area. */     emp_cv2  := emp_cv1;     /* Use emp_cv2 to fetch second row from emp table. */     FETCH emp_cv2      INTO emp_rec;     /* Reuse work area for another query. */     OPEN emp_cv2 FOR       SELECT *         FROM old_emp;     /* Use emp_cv1 to fetch first row from old_emp table.     The following fetch succeeds because emp_cv1 and     emp_cv2 point to the same query work area. */     FETCH emp_cv1      INTO emp_rec;   -- succeeds END;

把游标变量当作参数传递时一定要小心。在运行时，如果实参和形参的返回类型不兼容，PL/SQL就会抛出ROWTYPE_MISMATCH异常。

在下面的Pro*C程序中，我们定义了打包REF CURSOR类型，并指明返回类型为emp%ROWTYPE。下一步，创建一个引用这个新类型的过程。然后在PL/SQL块内为表dept的查询打开主游标变量。但是，在把打开的游标变量传递给存储过程的时候，由于形参和实参的返回类型不兼容，PL/SQL就抛出了ROWTYPE_MISMATCH异常。

CREATE PACKAGE cv_types AS   TYPE empcurtyp IS REF CURSOR     RETURN emp%ROWTYPE;   ... END cv_types; / CREATE PROCEDURE open_emp_cv (emp_cv IN OUT cv_types.empcurtyp) AS BEGIN   OPEN emp_cv FOR     SELECT *       FROM emp; END open_emp_cv; / -- anonymous PL/SQL block in Pro*C program EXEC SQL EXECUTE BEGIN   OPEN :cv FOR     SELECT *       FROM dept;   ...   open_emp_cv (:cv);   -- raises ROWTYPE_MISMATCH END; END-EXEC;

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 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 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, ');   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能设置事务的属性，如读写访问和隔离级别。

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

• 进入与退出

当我们进入一个自治事务的执行部分时，主事务就被会挂起。当我们退出程序时，主事务就会恢复。要想正常地退出事务，我们就要显示地提交或回滚所有的自治事务。如果程序(或任何由它调用的程序)中含有状态无法确定的事务，就会有异常抛出，无法确定的事务就会被回滚。

• 提交与回滚

COMMIT和ROLLBACK会结束活动自治事务但不退出自治程序。如下图所示，当一个事务结束，紧接着的下一条SQL语句就会开启另一个事务。

• 使用保存点

保存点的作用域就是定义它的事务。定义在主事务中的保存点与定义在自治事务中的保存点没有任何关系。实际上，主事务和一个自治事务是可以使用相同的保存点名称的。

我们只能回滚到当前事务中标记的保存点。所以，当在一个自治事务中，我们不能回滚到主事务中标记的保存点。要是想这样做的话，我们就必须用已存在的自治程序恢复主事务。

在主事务中，如果回滚到一个在我们开启一个自治事务之前标记的保存点，那么回滚操作并不会影响到自治事务。记住，自治事务完全独立于主事务。

• 避免错误

为了避免一些公共错误，在设计自治事务时一定要记住以下几点：

1. 如果一个自治事务要访问主事务(自治程序退出之前是不能恢复的)拥有的资源，就可能发生死锁。那样的话，Oracle就会在自治事务中抛出异常，如果异常未被捕获，主事务就会被回滚。
2. Oracle初始化参数TRANSACTIONS指定了并行事务的最大数量。如果自治事务(与主事务并行执行)没有被考虑的话这个数字就有可能被超出。
3. 如果我们没有使用提交或回滚操作退出一个活动自治事务，Oracle就会抛出一个异常。如果异常未被捕获，事务就会回滚。

4、使用自治触发器

很多时候，我们可以使用数据库触发器记下事件。假定我们要跟踪一个数据表所有的插入操作，即使是那些后来被回滚掉的。在下例中，我们用触发器把重复的行插入到一个影像表(shadow table)。由于是自治触发器，所以，触发器能把插入的内容提交到影像表中，无论我们是否把插入内容提交到主表中。

-- create a main table and its shadow table CREATE TABLE parts (pnum NUMBER(4), pname VARCHAR2(15)); CREATE TABLE parts_log (pnum NUMBER(4), pname VARCHAR2(15)); -- create an autonomous trigger that inserts into the -- shadow table before each insert into the main table CREATE TRIGGER parts_trig   BEFORE INSERT   ON parts   FOR EACH ROW DECLARE   PRAGMA AUTONOMOUS_TRANSACTION; BEGIN   INSERT INTO parts_log        VALUES (:NEW.pnum, :NEW.pname);   COMMIT; END;-- insert a row into the main table, and then commit the insert INSERT INTO parts      VALUES (1040, 'Head Gasket'); COMMIT ; -- insert another row, but then roll back the insert INSERT INTO parts      VALUES (2075, 'Oil Pan'); ROLLBACK ; -- show that only committed inserts add rows to the main table SELECT * FROM parts ORDER BY pnum; PNUM PNAME ------- --------------- 1040 Head Gasket -- show that both committed and rolled-back inserts add rows -- to the shadow table SELECT * FROM parts_log ORDER BY pnum; PNUM PNAME ------- --------------- 1040 Head Gasket 2075 Oil Pan

不同于常规触发器的是，自治触发器还能使用本地动态SQL执行DDL语句。下例中，触发器bonus_trig在表bonus更新后，删除临时表temp_bonus：

CREATE TRIGGER bonus_trig   AFTER UPDATE   ON bonus DECLARE   PRAGMA AUTONOMOUS_TRANSACTION;   -- enables trigger to perform DDL BEGIN   EXECUTE IMMEDIATE 'DROP TABLE temp_bonus'; END;

5、从SQL中调用自治函数

从SQL语句中调用的函数，必须遵守控制副作用的规则。为了检查是否与规则相冲突，可以使用编译指示RESTRICT_REFERENCES。它的作用是判断函数是否读写数据表或打包变量。

但是，在定义的时候，自治程序总不会与规则"不读数据库"(RNDS)和"不写数据库"(WNDS)相冲突，即使事实上是冲突的。这样的特性是很有用，在下面的例子中，当我们从查询中调用打包函数log_msg时，它能向数据表debug_output插入数据，而且还不与规则"不写数据库"冲突：

-- create the debug table CREATE TABLE debug_output (msg VARCHAR2(200)); -- create the package spec CREATE PACKAGE debugging AS   FUNCTION log_msg (msg VARCHAR2)     RETURN VARCHAR2;   PRAGMA RESTRICT_REFERENCES (log_msg, WNDS, RNDS); END debugging; -- create the package body CREATE PACKAGE BODY debugging AS   FUNCTION log_msg (msg VARCHAR2)     RETURN VARCHAR2 IS     PRAGMA AUTONOMOUS_TRANSACTION;   BEGIN     -- the following insert does not violate the constraint     -- WNDS because this is an autonomous routine     INSERT INTO debug_output          VALUES (msg);     COMMIT;     RETURN msg;   END; END debugging; -- call the packaged function from a query DECLARE   my_empno   NUMBER (4);   my_ename   VARCHAR2 (15); BEGIN   ...   SELECT debugging.log_msg (ename)     INTO my_ename     FROM emp    WHERE empno = my_empno;   -- even if you roll back in this scope, the insert   -- into 'debug_output' remains committed because   -- it is part of an autonomous transaction   IF ... THEN     ROLLBACK;   END IF; END;

十、确保PL/SQL程序的向后兼容

PL/SQL第二版允许使用一些不再使用的非正常功能：

1. 声明变量时,可以对记录和表类型向前引用
2. 在函数说明的RETURN子句中指定变量(非类型)名称
3. 使用IN模式的参数为index-by表的元素赋值
4. 把一个IN模式的记录中的一个字段作为另一个子程序的OUT模式参数
5. 把一个OUT模式的记录中的一个字段放置于赋值符号的右边
6. 在SELECT语句的FROM列表中使用OUT模式参数

为了向后兼容，我们可以设置PLSQL_V2_COMPATIBILITY标识来保留第二版的这些功能，在服务器端有两种方法设置这个标识：

1. 把下面行添加到Oracle初始化文件中：

   PLSQL_V2_COMPATIBILITY=TRUE

2. 执行下面的语句：

   ALTER SESSION SET plsql_v2_compatibility = TRUE; ALTER SYSTEM SET plsql_v2_compatibility = TRUE;

如果我们把标识指定为FALSE(默认的)，就不能使用非正常功能。在客户端，命令行选项可以设置标识。例如，使用Oracle预编译程序，我们就可以在命令行中指定运行时选项DBMS。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26622598/viewspace-718130/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26622598/viewspace-718130/