游标参数的作用于被限制在游标的作用域中。你不能在与游标相关联的SELECT子句之外指定游标参数。下面的PL/SQL代码片断的错误就在于游标参数标志符：program_name并不是程序块中的本地变量。相反，它是游标的正规参数，只能在游标中被定义。

DECLARE

CURSOR scariness_cur (program_name VARCHAR2)

IS

SELECT SUM (scary_level) total_scary_level

FROM tales_from_the_crypt

WHERE prog_name = program_name;

BEGIN

program_name := 'THE BREATHING MUMMY';

 

OPEN scariness_cur (program_name);

END;

游标参数的模式

对于游标参数来说，它的语法和过程、函数的参数定义相类似，不同的是，游标参数只能是输入型的。你不能把游标参数设置为OUT或IN OUT类型。游标不能通过参数返回检索出来的数值。

游标参数的默认值

以下是一个设置游标参数默认值的范例：

CURSOR emp_cur (emp_id_in NUMBER := 0)

IS

SELECT employee_id, emp_name

FROM employee

WHERE employee_id = emp_id_in;

如果Joe Smith的雇员ID是1001，那么以下的代码就会把游标参数设置为1001，同时，my_emp_name也取值为：JOE SMITH

OPEN emp_cur (1001);

FETCH emp_cur INTO my_emp_id, my_emp_name;

因为参数emp_id_in本身有一个默认值，所以我也可以不给游标定义参数九打开并提取记录，这是，游标参数使用的就是默认值。

1.3.4 批量集合（BULK COLLECT）

从Oracle8i开始，就引入了一个非常有用的特性，大大提高了查询的效率，它就是：BULK COLLECT子句。

使用BULK COLLECT，你可以通过一个显式或隐式游标，只需循环一次就能从数据库中取出多行记录。BULK COLLECT减少了PL/SQL和SQL引擎之间的上下文交换，因此减少了检索数据的开销。

特别是对于某些时候，我们需要使用游标一条记录、一条记录地处理，就可能使用了多次循环。在这种情况，我们就可以使用批量集合，一次性地把记录取出，放入一个集合变量中，然后再对该变量进行处理，减少系统的开销。

下面我们来看两个范例，一个是使用BULK COLLECT的，一个没有：

范例1、使用多次循环来检索并显示数据。

范例2、一次性获取数据，然后再循环显示。

执行：

下面是一些有关BULK COLLECT的建议：

1、从Oracle9i开始，你都可以在静态SQL和动态SQL中使用BULK COLLECT。

2、你可以在任何地方的SELECT INTO，FETCH INTO，和RETURNING INTO子句中使用BULK COLLECT关键字。

3、在Oracle9i以前，你引用的集合变量只能够存储标量值（字符串、数字和日期）。换句话说，你不能提取一条记录存放到一个记录型的数据结构中（这个数据结构是一个集合变量中的一行记录）。

4、SQL引擎会自动地初始化和扩展你在BULK COLLECT子句中引用的集合变量。它会从索引1开始，连续地把记录填充进集合中，并且会重写任何以前定义了的数据。

5、你不能在一个FORALL语句中使用SELECT...BULK COLLECT语句。

6、如果找不到任何记录，SELECT...BULK COLLECT也不会产生NO_DATA_FOUND异常。相应地，你必须检查集合的内容，以察看里面是否有数据。

7、在执行查询以前，BULK COLLECT操作会清空在INTO子句中引用的集合。如果查询没有返回任何记录，这个集合的COUNT方法就会返回0。

如果对于Oracle9i或以上的版本，我们把上面的例子写得更简单一些：

create or replace procedure Bulk_Demo2(i_deptno in number) is

type t_tbl1 is table of emp%ROWTYPE index by binary_integer;

emp_info1 t_tbl1;

 

type emp_rec is record(

empno emp.empno%type,

ename emp.ename%type);

type t_tbl2 is table of emp_rec index by binary_integer;

emp_info2 t_tbl2;

 

begin

Select t.* BULK COLLECT

INTO emp_info1

From emp t

Where t.deptno = i_deptno;

Dbms_Output.put_line('------批量存放整行记录类型-------');

For i in 1 .. emp_info1.count Loop

Dbms_Output.put_line(emp_info1(i).empno || ' ' || emp_info1(i)

.ename || ' ' || emp_info1(i)

.job || ' ' || emp_info1(i)

.sal || ' ' || emp_info1(i).hiredate);

End Loop;

Dbms_Output.put_line('+++++++++++++++++++++++++++++++++++++++');

Dbms_Output.put_line('------批量存放自定义记录类型-------');

select t.empno, t.ename BULK COLLECT

INTO emp_info2

From emp t

Where t.deptno = i_deptno;

For i in 1 .. emp_info2.count Loop

Dbms_Output.put_line(emp_info2(i).empno ||' '|| emp_info2(i).ename);

End Loop;

Dbms_Output.put_line('++++++++++++++++++++++++++++++++++++++');

end BULK_demo2;

运行结果：

1.3.4.1 限制BULK COLLECT提取的记录数目

Oracle对于BULK COLLECT提供了一个LIMIT关键字来让你限制从数据库检索出来的记录数目。语法如下：

FETCH cursor BULK COLLECT INTO ... [LIMIT rows];

这里的rows参数可以是一个字面值，变量或者是表达式——都指向一个整数（不然的话，Oracle将会给出一个VALUE_ERROR的异常信息）。

LIMIT对于BULK COLLECT来说是非常有用的。它帮助你清楚地了解并管理程序所用到过程数据。比如说，你要精确地查询并处理1000条记录。你可以使用BULK COLLECT来取出这些记录，并且残生了一个相对较大的集合。但是这种方法会消耗许多PGA（全局进程区）内存。如果这个代码是被很多独立的会话使用，那么你的应用就会因为PGA频繁的页面交换而效率低下。

下面的这段代码（在Oracle9i的HR模式下），在FETCH语句中使用了LIMIT语句。请注意，这里使用了集合的COUNT方法来确认是否有数据被取出。而%FOUND和%NOTFOUND属性在这里就不能用来检测是否有数据被取出。

create or replace procedure LimitDemo is

CURSOR allrows_cur IS

SELECT employee_id FROM employees;

TYPE employee_aat IS TABLE OF

employees.employee_id%Type INDEX BY BINARY_INTEGER;

l_employees employee_aat;

begin

OPEN allrows_cur;

Loop

EXIT WHEN allrows_cur%NOTFOUND;

FETCH allrows_cur BULK COLLECT

INTO l_employees LIMIT 100;

Dbms_Output.put_line(l_employees.count);

end loop;

close allrows_cur;

end LimitDemo;

获得结果是：

 

1.3.5 使用SELECT ... FOR UPDATE

当你使用一般的Select语句来查询数据库中的数据时，被选中的行并不会加锁。一般而言，你所读取到的记录都是在更新（或其他DML语句）没有提交之前的状态。但是，有的时候，你希望在你改变它们之前就能够锁住它们（而不是在执行变更时加锁）。Oracle为SELECT提供了FOR UPDATE子句来实现这个功能。

当你执行了一条SELECT...FOR UPDATE语句，Oracle就会自动地给所有被选中的记录加上排他的行级别锁，使这些记录只能“因你而改变”。别的用户是不能对这些记录执行任何变更，直到你执行了一条ROLLBACK或COMMIT语句。

以下是两个实例（在HR模式下），显示了在游标中使用FOR UPDATE子句的方法。

CURSOR emp_cur1 IS

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME , EMAIL

FROM employees

WHERE EMPLOYEE_ID <200

FOR UPDATE;

 

CURSOR emp_cur2 IS

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME , EMAIL

FROM employees

WHERE EMPLOYEE_ID <200

FOR UPDATE OF EMPLOYEE_ID;

第一个游标使用了无标志的FOR UPDATE子句，而第二个游标使用了查询里面的一个列名（EMPLOYEE_ID）来标志FOR UPDATE子句。

你也可以在SELECT中，针对多张表来使用FOR UPDATE子句。在这种情况下，只有在FOR UPDATE子句中引用到的列的所属表，其里面被选中的纪录才会被锁定。在下面的例子中（HR模式下），FOR UPDATE子句就不会在jobs表中产生任何锁。

Select e.employee_id,e.first_name,e.last_name

From employees e,jobs j

Where e.job_id=j.job_id and j.job_title='Programmer'

FOR UPDATE OF e.employee_id

我们用DBA用户登录到Oracle，察看被锁住的对象的情况：

SELECT a.owner,t.LOCKED_MODE,a.object_name,s.SID

From v_$locked_object t,v$session s, all_objects a

WHERE t.SESSION_ID=s.SID AND t.OBJECT_ID=a.object_id

得到：

在FOR UPDATE子句中仅仅提及了employee_id列，在查询列表中，没有jobs表的列。

在FOR UPDATE子句中的OF关键字后面跟着的列，并不局限于你所要查询的列。但是，系统依然会对所有被选中的行加锁；而OF列表仅仅是给你一条途径来更清晰地纪录你要变更的东西。如果你仅仅是使用了FOR UPDATE子句，而没有在OF关键字后面跟上列名，那么数据库就会锁住所有FROM关键字后面表里的被选中的数据行。

此外，你也不必一定要在SELECT ... FOR UPDATE语句之后执行UPDATE或DELETE语句。

最后，你还可以给FOR UPDATE子句扩展NOWAIT关键字，告诉Oracle——如果表被其他用户锁住的话，就不必等待，而是立即把控制权转移到你的程序上，从而你就可以执行其他的工作或者仅仅简单地等待一段时间。如果没有NOWAIT关键字，你的进程将会被阻塞，直到数据表可用。除非数据表是在远程数据库上，否则等待时间是没有限制的。Oracle的初始化参数：DISTRIBUTED_LOCK_TIMEOUT就是被用来设置这个参数的。

1.3.5.1 使用COMMIT释放锁

一旦一个使用了FOR UPDATE子句的游标被打开，那么所有被选中的纪录都会被锁住，直到你的会话结束，或者执行了一个COMMIT语句来保存所有数据变更的结果，又或者执行一个ROLLBACK语句来取消所有的数据变更。所以，在你执行了COMMIT或ROLLBACK之后，你就再不能对使用了FOR UPDATE的游标进行FETCH操作。

考虑以下的范例：

DECLARE

 

CURSOR all_sal_cur

IS

SELECT empno,sal FROM emp

FOR UPDATE OF empno;

BEGIN

FOR sal_rec IN all_sal_cur

LOOP

IF sal_rec.sal < 1000 THEN

UPDATE emp SET sal=1000

WHERE empno = sal_rec.empno;

COMMIT;

END IF;

END LOOP;

END;

得到的结果为：

Suppose this loop finds its first YOUCANDOIT job. It then commits an assignment of a job to STEVEN. When it tries to FETCH the next record, the program raises the following exception:

假设上面的LOOP循环，取出了一条纪录（其中的sal字段小于1000） ,那么程序就会进行数据更新，然后commit。但是，在下一次循环中，如果程序又试图去读取游标的下一条纪录，系统就会报以下错误：

ORA-01002: fetch out of sequence （读取违反顺序）。

如果你试图从一个使用了SELECT FOR UPDATE的游标中提取数据，并且提交或回滚事务，那么你就应在这些代码中，防止从游标中再次读取纪录。

1.3.5.2 WHERE CURRENT OF 子句

PL/SQL为游标里的UPDATE和DELETE命令提供了WHERE CURRENT OF子句。这个子句允许你很容易地对最新读取的数据进行操作。

更新最近读取出来的记录：

UPDATE table_name

SET set_clause

WHERE CURRENT OF cursor_name;

从数据库中删除最近读取出来的记录：

DELETE

FROM table_name

WHERE CURRENT OF cursor_name;

需要注意的是WHERE CURRENT OF子句引用的是游标，而不是下一条存储下一条数据行的记录。(源码网整理，)

 

使用WHERE CURRENT OF子句的最大好处就是，你不必在查找纪录所需条件的两个地方写代码了。如果没有WHERE CURRENT OF，你就重复使用游标中的WHERE子句来定位需要操作的记录。这样的结果就是，如果以后数据表结构发生了变化，你就得重写几乎所有的SQL代码来支持这种变更。如果你使用了WHERE CURRENT OF子句，那么你就仅仅需要改写SELECT命令中的WHERE子句。

这个看起来似乎是一个微不足道的优势，但是它可以在你程序中的许多地方起作用。使用WHERE CURRENT OF子句，%TYPE与%ROWTYPE声明属性，游标LOOP循环，本地模块化和其他PL/SQL结构可以很大程度上地减少数据库程序的维护成本。

让我们来看看这个子句是如何作用的。

在下面这个例子中，我读取纪录中的工资，如果工资小于1000的，那么就增加到1000。有一种很普遍的写法是在UPDATE子句中，根据游标记录里的empno来重复写WHERE子句，如下：

WHERE empno=emp_rec.empno;

如果使用了WHERE CURRENT OF子句的话，就可以不用写这些和表结构相关的代码。

下面是使用了WHERE CURRENT OF子句的另一个版本：

DECLARE

 

CURSOR all_sal_cur

IS

SELECT empno,sal FROM emp

FOR UPDATE OF empno;

BEGIN

FOR sal_rec IN all_sal_cur

LOOP

IF sal_rec.sal < 1000 THEN

UPDATE emp SET sal=1000

WHERE CURRENT OF all_sal_cur;

END IF;

END LOOP;

COMMIT;

END;

得到的结果为：

1.4 游标变量和REF CURSOR类型

一个游标变量就是指向或者引用一个潜在游标的变量，也就是说，游标变量就是对PL/SQL工作区的引用。显式或隐式游标都是静态的——与特定的查询绑定。而游标变量却能够为任何查询打开，甚至在同一个程序的执行过程中。

使用游标变量最大的好处在于，它提供了一个不同PL/SQL程序之间传递查询结果集（results of queries）的方法，这种传递也可以在客户端的PL/SQL程序和服务器端程序之间进行。

它同时也意味着：不同程序可以有效地共享游标。比如，在CS环境下，一个客户端程序能够打开和从游标变量中读取数据，并且能够把这个变量作为一个参数传递给服务端的存储过程。这个存储过程就能够继续读取数据并把控制权返回给客户端程序，使之能够关闭游标。你也可以在不同的存储过程或不同的数据库之间执行同样的动作。

这个过程，如下图所示，为PL/SQL程序管理和共享游标数据提供了巨大的可能性：

在不同程序中引用游标变量

1.4.1 为什么使用游标变量

游标变量允许你做以下的工作：

在程序的执行阶段，你可以让游标在不同的时候与不同的查询联系起来。换句话说，一个游标变量能够从不同的结果集中获取数据。

把游标变量作为一个参数传递给过程或者函数。你能够，从本质上说，通过传递结果集的引用从而共享一个游标的查询数据。

对于游标变量，和静态游标一样，你可以在程序中打开、关闭和读取游标指向的数据。你也能够对游标变量引用游标属性：%ISOPEN、%FOUND、%NOTFOUND和%ROWCOUNT。

你也可以把一个游标的内容赋给另外一个游标变量。因为游标变量是一个变量，所以它能够在赋值操作中使用。但是，对于这一类的变量引用是有限制的。这会在下面的章节中阐述。

1.4.2 与静态游标的共同点

对于游标变量设计的一个关键之处就是：任何可能的时候，对游标对象的管理命令应该和管理静态游标的一样。除了声明和打开游标变量的语法有所提升之外，其他对于游标变量的操作和静态变量是一样的。

CLOSE语句

在下面的例子中，我声明了一个引用游标类型和一个基于此类型的游标变量。然后我使用与静态游标相同的语法来关闭这个游标变量。

DECLARE

TYPE var_cur_type IS REF CURSOR;

var_cur var_cur_type;

BEGIN

CLOSE var_cur;

END;

游标的属性

你能够使用以下的四种游标属性，用法和静态游标完全一致。如果我声明了一个游标变量（就像上一个例子），然后我可以对这个游标变量使用游标属性，如下所示；

var_cur%ISOPEN

var_cur%FOUND

var_cur%NOTFOUND

var_cur%ROWCOUNT

从游标变量中提取数据

但从一个游标变量提取数据到PL/SQL的数据结构时，你可以使用FETCH语句。但是，PL/SQL也对游标变量的数据检取设置了额外的规范——也就是游标对象返回的数据类型必须和INTO关键字右边的变量的数据类型保持一致。

因为游标变量的大部分特性都和显式游标相同，所以下面的大部分内容将集中在游标变量特有的属性上。

1.4.3 声明引用游标类型

就像定义一个PL/SQL表或一个基于程序定义的记录一样，你必须进行两种完全不同的声明步骤来实现一个游标变量的创建：

创建一个引用游标变量类型

根据这个类型创建实际的游标变量。

创建一个被引用的游标类型的语法如下：

TYPE cursor_type_name IS REF CURSOR [ RETURN return_type ];

在这里，cursor_type_name是这个游标类型的名字，return_type是这种游标返回的数据类型。return_type对于一个普通游标的RETURN字句来说，可以为任何有效的数据结构，也可以是使用了%ROWTYPE属性定义的记录类型。

注意：RETURN子句对于REF CURSOR类型来说是可选，所以下面两个声明都是有效的：

TYPE company_curtype IS REF CURSOR RETURN company%ROWTYPE;

TYPE generic_curtype IS REF CURSOR;

第一种REF CURSOR的定义要强一些，因为它把一个记录类型（或数据行类型）与游标变量类型在声明时就结合在了一起。任何属于这种类型的游标变量都可以被SQL命令操作，并且从中检取与声明时已确定好的数据类型相匹配的数据。使用强REF TYPE的优点在于，编译器可以知道开发人员在FETCH语句中的数据结构是否与游标变量所指定的数据类型相吻合。

第二种类型是没有RETURN子句的，被称为弱类型。这类游标变量不会与任何数据结构相关联。弱类型的游标变量与强类型相比，要灵活得多。它们可以在任何查询中使用，可以与任何数据类型相关联，甚至在一个程序的范围内变更。

从Oracle9i开始，Oracle就提供了一种预定义的弱REF CURSOR类型，称之为SYS_REFCURSOR。你可以不再用定义自己的弱游标变量类型了，使用Oracle提供的就好了，如下所示：

DECLARE

my_cursor SYS_REFCURSOR;

1.4.4 声明游标变量

创建一个游标变量的语法如下：

cursor_name cursor_type_name;

在这里，cursor_name就是这个游标的名字，而cursor_type_name是在前面代码中TYPE语句所定义的游标类型名。

以下是一个游标变量的创建例子：

DECLARE

 

TYPE sports_car_cur_type IS REF CURSOR RETURN car%ROWTYPE;

 

sports_car_cur sports_car_cur_type;

BEGIN

...

END;

分清定义一个游标变量和创建一个确实存在的游标对象结果集（被游标的SQL语句所定义）之间的区别是很重要的。一个常量就是一个数值，而一个变量就是指向或引用一个游标对象。这些区别如下图所示，注意在不同程序中的两个不同的游标变量都指向同一个游标对象。

声明一个游标变量并没有创建一个游标对象。要想创建一个游标对象，你必须使用OPEN FOR 语法来创建一个新的游标对象并且分配给这个变量。

1.4.5 打开游标变量

当你打开游标时，你就给这个游标分配了一个值（游标对象）。所以，对于传统的OPEN语句来说，允许游标变量在FOR子句后面跟SELECT命令，如下所示：

OPEN cursor_name FOR select_statement;

在这里cursor_name是一个游标或游标变量的名字，而select_statement是一个SQL SELECT语句。

对于强REF CURSOR类型的游标变量来说，SELECT语句的结构（列或字段集合）必须与声明游标变量时在RETURN子句中指定的数据类型相匹配。如下所示：

如果游标变量是被定义成一个弱REF CURSOR类型，你就能够把任意一个查询赋予它。如下所示：我打开了一个游标变量三次，每一次都使用了不同的查询。

DECLARE

TYPE emp_curtype IS REF CURSOR;

emp_curvar emp_curtype;

BEGIN

OPEN emp_curvar FOR SELECT * FROM emp;

OPEN emp_curvar FOR SELECT employee_id FROM emp;

OPEN emp_curvar FOR SELECT company_id, name FROM company;

END;

最后一个OPEN语句甚至与employee表没有任何关系。

如果游标变量还没有被分配给任何一个游标对象，那么OPEN FOR语句就会隐式地给这个变量创建一个游标对象。如果在打开游标变量的时候，这个变量已经指向了一个游标对象，那么OPEN FOR语句就不会创建一个新的对象，相反，它会重用已经存在的对象并且把新的查询赋予这个对象。游标对象本身是与游标或查询分开存储的。

1.4.6 从游标变量中检取数据

正如前面所描述的一样，对游标变量使用FETCH语句和静态游标一样。如下所示：

FETCH cursor_variable_name INTO record_name;

FETCH cursor_variable_name INTO variable_name, variable_name ...;

当游标变量被声明为强REF CURSOR类型，那么PL/SQL编译器就会确认在INTO关键字后面的数据类型是否与该游标变量绑定的查询的数据结构想匹配。

如果游标变量是属于弱REF CURSOR类型，那么PL/SQL编译器就不能像对强REF CURSOR类型一样进行数据类型匹配检查。因为在声明游标变量时并没有指定数据类型，所以在编译时，是没有办法知道哪一个游标对象会被分配给这个变量。

所以，对数据类型匹配性的检查只能在运行时进行，也就是当FETCH将要被执行时。在这个时刻，如果查询和INTO子句不能在结构上匹配，那么PL/SQL运行引擎就会抛出预定义的ROWTYPE_MISMATCH异常。但是要注意的是，如果有可能的话，PL/SQL竟会自动地对数据类型进行转换。

1.4.7 游标变量的使用规范

本小节将会更加详细地对游标变量的使用进行探讨。其中包含了数据行类型的匹配规则，游标变量的别名和范围问题。

首先要记住的是，游标变量是一个游标对象或数据库查询的引用。它本身并不是对象。一个游标变量满足以下条件中的任意一个，那么就可以说它引用了一个给定的查询：

1、对游标变量执行了一个OPEN FOR 语句，与一个查询绑定在一起。

2、一个游标变量从另一个游标变量那里获值，从而指向查询。

对于游标变量，你可以执行赋值操作，并且把这个值作为参数传递给存储过程和函数。为了在两个游标变量之间执行这种动作，不同的游标变量必须遵从一组编译时和运行时的数据类型匹配规则。

1.4.7.1 编译时游标数据类型匹配规则

以下是一些PL/SQL遵循的编译时规则。

如果以下条件为真，那么两个游标变量（包括存储过程的参数）是相匹配的：

1、两个变量（参数）都是强REF CURSOR类型，而且都有相同的返回类型。

2、两个变量（或参数）都是一个弱REF CURSOR类型，不论返回类型是什么。

3、一个变量（或参数）是任意的强REF CURSOR类型，而另一个是任意的弱REF CURSOR类型。

4、一个强REF CURSOR类型的游标变量（或参数）可以打开一个查询并返回记录类型，而这个记录类型在结构上与声明时的rowtype_ name是一致的。

5、一个弱REF CURSOR类型的游标变量（或参数）可以打开任意的查询，而且可以从这个变量中检取数据到任意的变量列表或记录结构中。

如果其中一个是弱REF CURSOR类型的游标变量，那么PL/SQL编译器就不能够真正地判断这两个不同的游标变量是否相匹配。而这种情况可能在运行时发生。下一节将提到这种情况。

1.4.7.2 运行时游标数据类型匹配规则

一个弱REF CURSOR类型的游标变量（或参数）能够被创建来引用一个返回任意数据类型的查询，而与其之前所指向的游标对象无关。

一个强REF CURSOR类型的游标变量只能用来打开一个具有相同返回数据类型的查询。

如果以下两个条件为真，两个记录（或一组变量）将会被认为在结构上与隐式类型转换相匹配：

在两个记录（或变量组）里，域的数目是一致的。

对于一个记录里的域，与之相对应的另一个组里的域具有相同的PL/SQL数据类型，或者是具有系统能够隐式转换的域。

对于一个在FETCH语句中使用的游标变量（或参数），与之联系的查询必须在结构上与INTO子句后面的记录或变量组相匹配。

1.4.7.3 游标变量别名

如果你指派一个游标变量给另一个游标变量，它们对于同一个游标对象来说就互为别名。它们共享同一个指向游标对象的引用（游标查询出来的结果集）。任何通过其中一个游标变量对游标对象进行操作，也会影响到另一个游标变量。

下面这个匿名块显示了游标别名的工作方式：

从上图的程序中我们可以看到，在第8行，我们把cur_emp1指派给了cur_emp2，所以两个游标共享一个游标对象；但是在第11行时关闭了cur_emp1，也就是说销毁了公有的游标对象，所以第12行，在从cur_emp2游标中提取数据时，就会报错。

游标对象的状态的任何改变都会通过任何一个引用这个游标对象的游标变量看到。

1.4.7.4 游标对象的作用域

一个游标变量的作用域与一个静态游标一样：在游标变量被声明的PL/SQL块中（除非是在包中声明，这样就使得变量为全局可用）。而分配给游标变量的游标对象的作用域，却完全不同。

一旦执行一个OPEN FOR语句就会创建一个游标对象，这个游标对象会一直保持可用，直到指向这个游标对象的活动游标变量执行了关闭操作。这就意味着，你能够创建一个游标对象在某个范围内，同时把它分配给一个游标变量。同样，如果把这个游标对象分配给另一个处于不同作用域的游标变量，那么这个对象的作用域就会跨越原有的作用域（有可能到另一个PL/SQL块中）。

在下面这个例子，我使用一个内嵌块来演示在内嵌块中创建的游标对象如何把它的作用域扩展到外围域中。

1.4.8 把游标变量作为参数传递

在对过程或函数的调用时，你可以把游标变量作为一个参数来进行传递。当你在程序中的参数列表中使用了一个游标变量，你就必须设置这个参数的输入输出模式和数据类型（REF CURSOR类型）。

1.4.8.1 确定REF CURSOR类型

在你的程序头部，你必须确定作为参数的游标变量的REF CURSOR类型。要做到这一点，这个游标类型必须是已经被定义的。

如果你在一个程序中创建了一个本地模块，你就能够在同一个程序中定义游标类型。它就可以作为参数使用，这个方法如下所示：

DECLARE

 

TYPE curvar_type IS REF CURSOR RETURN company%ROWTYPE;

 

PROCEDURE open_query (curvar_out OUT curvar_type)

IS

local_cur curvar_type;

BEGIN

OPEN local_cur FOR SELECT * FROM company;

curvar_out := local_cur;

END;

BEGIN

...

END;

如果你正在创建一个独立的过程或函数，那么你引用一个已预先定义好的REF CURSOR类型的唯一途径就是，把TYPE语句放到一个包定义段里。所有在包定义段里声明的变量，都可以在你的会话中作为全局变量使用，所以你能够使用“.”引用的方式在程序中引用它们，如下面这个例子所示：

l 创建一个有REF CURSOR类型声明的包：

PACKAGE company

IS

 

TYPE curvar_type IS REF CURSOR RETURN company%ROWTYPE;

END package;

l 在一个独立的过程中，就可以通过引用这个REF CURSOR类型，如下所示：

PROCEDURE open_company (curvar_out OUT company.curvar_type) IS

BEGIN

...

END;

1.4.8.2 设定参数的模式

就像其他参数一样，一个游标变量参数可以为以下模式中的一种：

1. IN

在程序中为只读。

2. OUT

仅能被程序写

3. IN OUT

可被程序读或者写。

要记住，游标变量的值是游标对象的引用，而不是游标对象的状态。换句话说，在你从一个游标减去数据或关闭游标之后，游标变量的值并没有发生变化。

Only two operations, in fact, may change the value of a cursor variable (that is, the cursor object to which the variable points):

事实上，只有两种操作恩能够改变一个游标变量的值：

1. 对一个游标变量进行赋值

2. 使用一个OPEN FOR语句

如果游标变量已经指向了一个游标对象，那么OPEN FOR将不会确实地改变这个引用。它只是简单地改变联系这个对象的查询。

FETCH和CLOSE操作能够影响游标对象的状态，但是不会改变这个游标对象本身的引用，而这就是游标变量的值。

这里有一个具有游标变量参数的程序范例：

PROCEDURE assign_curvar

(old_curvar_in IN company.curvar_type,

new_curvar_out OUT company.curvar_type)

IS

BEGIN

new_curvar_out := old_curvar_in;

END;

这个过程拷贝老的游标变量给新的变量。第一个参数是输入模式，因为它只在表达式的右边出现。而第二个参数必须为输出模式（或输入输出模式），因为它在程序被改变。注意curvar_type是在company包中预先定义了的。

1.4.9 游标变量的限制

有关游标变量的限制如下所示；需要注意的是，随着Oracle版本的升级，这些限制可能有部分被取消：

l 游标变量不能在包中声明，因为它们没有一个持久的状态。

l 你不能使用RPC（remote procedure calls）来从一个服务器传递游标变量给另一个服务器。

l 如果你在PL/SQL中，把一个游标变量作为一个绑定变量或宿主变量，你就不能在服务器中从中获取数据，除非你也在相同的服务器调用中打开了它。

l 在OPEN FOR语句中与一个游标变量相关联的查询是不能使用FOR UPDATE子句的。（但是这在Oracle9i或以上版本是允许的）。

l 你不能使用比较操作符来对游标变量进行相等性、不等性或是否为空进行检测。

l 你不能对一个游标变量赋NULL值。如果你试图这样做，Oracle系统就会产生一个PLS-00382的异常。

l 数据库的列不能存储游标变量的值。你不能在CREATE TABLE语句中对列的定义中使用REF CURSOR类型。

在一个内嵌表，联合数组或数组中的元素，是不能存储游标变量的值。你不能使用REF CURSOR类型来定义集合众的元素类型。

1.5 游标表达式

Oracle在SQL语言中提供了一个强有力的工具：游标表达式。一个游标表达式从一个查询中返回一个内嵌的游标。在这个内嵌游标的结果集中，每一行数据包含了在SQL查询中的可允许的数值范围；它也能包含被其他子查询所产生的游标。

因此，你能够使用游标表达式来返回一个大的和复杂的，从一张或多张表获取的数据集合。游标表达式的复杂程度，取决于查询和结果集。然而，了解所有从Oracle RDBMS提取数据的可能途径，还有大有好处的。

You can use cursor expressions in any of the following:

你能够在以下任何一种情况使用游标表达式：

l 显式游标声明

l 动态SQL查询。

l REF CURSOR 声明和变量。

你不能在一个隐式查询中使用游标表达式。

游标表达式的语法是相当简单的：

CURSOR (subquery)

当Oracle从父游标或外围游标那里检取包含游标表达式的数据行时，Oracle就会隐式地打开一个内嵌的游标，这个游标就是被上述的游标表达式所定义。在以下情况发生时，这个内迁游标将会被关闭：

l 你显式地关闭这个游标。

l 外围或父游标被重新执行，关闭或撤销。

l 当从父游标检取数据时，发生异常。内嵌游标就会与父游标一起被关闭。

1.5.1 使用游标表达式

你可以通过两种不同的，但是非常有用的方法来使用游标表达式：

1. 在一个外围查询中把字查询作为一列来检取数据。

2. 把一个查询转换成一个结果集，而这个结果集就可以被当成一个参数传递给一个流型或变换函数。

1.5.1.1 把子查询作为列检取数据

下面这个过程（HR模式）展示了使用内嵌CURSOR表达式来获取子查询数据的方法。最外层的查询取出数据的两个部分：城市的地址和一个内嵌游标——其包含了在这个城市的部门。这个内嵌游标，依次地从更深一层的内嵌游标中取出数据，其包含了在每一个部门中的所有雇员。

我当然也可以创建并操作多个独立的游标来获取信息。但是，CURSOR表达式给我们使用一个完全不同方法的机会，这个方法更精确和高效，它使所有的操作都在SQL语句执行器中完成，并且减少了上下文的交互。

CREATE OR REPLACE PROCEDURE emp_report(p_locid NUMBER) IS

TYPE refcursor IS REF CURSOR;

-- 这个查询返回两列, 但是第二列是一个游标

-- 这个游标让我们获取有关联的结果集

CURSOR all_in_one_cur 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) AS dname

FROM locations l

WHERE l.location_id = p_locid;

departments_cur refcursor;

employees_cur refcursor;

v_city locations.city%TYPE;

v_dname departments.department_name%TYPE;

v_ename employees.last_name%TYPE;

BEGIN

OPEN all_in_one_cur;

LOOP

FETCH all_in_one_cur

INTO v_city, departments_cur;

EXIT WHEN all_in_one_cur%NOTFOUND;

-- 现在我基于部门开始循环 同时 我不需要显式地

-- 打开这个游标，Oracle会自动完成。

LOOP

FETCH departments_cur

INTO v_dname, employees_cur;

EXIT WHEN departments_cur%NOTFOUND;

-- 现在我基于每个部门的员工开始循环.

-- 同样，我也不需要显式地打开游标

LOOP

FETCH employees_cur

INTO v_ename;

EXIT WHEN employees_cur%NOTFOUND;

DBMS_OUTPUT.put_line(v_city || ' ' || v_dname || ' ' || v_ename);

END LOOP;

END LOOP;

END LOOP;

CLOSE all_in_one_cur;

END;

程序运行结果如下图所示：

1.5.2 游标表达式的限制

对于游标表达式，有以下一些限制条件：

l 你不能对一个隐式游标使用游标表达式，因为目前还没有提供从内嵌游标中直接提取数据到一个PL/SQL数据结构的方法。

l 游标表达式只能在最外层的SELECT子句的字段列表中出现。

l 你能够把游标表达式放置在一个SELECT语句中，而且这个语句是不能嵌套在其他查询表达式里面，除非它本身就被定义为一个游标表达式的子查询。

l 一个游标表达式能够被用来作为一个表函数的参数，这个表函数可以在SELECT语句中FROM子句中被调用。

l 在声明一个视图时，游标表达式不能使用。

l 在动态SQL中使用游标表达式时，你是不能使用BIND和EXECUTE命令的。