3.8.2　显式游标

对于PL/SQL块中的SELECT语句，可以用显式游标来处理。显式游标是一个打开的工作区，在这个工作区里保存SELECT语句的执行结果。用显式游标可以处理返回0行、一行、多行等各种情况，并且在返回0行或多行数据这两种特殊情况下，不会引发NO_DATA_FOUND和TOO_MANY_ROWS异常。使用游标处理SELECT语句的步骤如下：

1）声明游标

2）打开游标

3）逐行取出游标中的行，并分别进行处理

4）关闭游标

游标的工作过程如图3.4所示。

图　3.4　游标的工作过程

游标的声明在PL/SQL块的声明部分进行。声明的语法格式为：

DECLARE

CURSOR游标名IS

SELECT语句；

例如，下面的语句声明了一个名为cur_1的游标：

DECLARE

CURSOR cur_1 IS

SELECT*FROM emp；

由于SELECT语句的执行结果将存放在工作区中，因此不需要使用INTO子句将返回的数据赋给变量。

为了处理游标中的数据，首先要打开游标。打开游标意味着将指定的SELECT语句交给数据库服务器执行，并将返回结果存放在工作区中。打开游标的命令是OPEN，其语法格式为：

OPEN游标名

例如，打开游标cur_1的语句为：

OPEN cur_1；

游标打开后，就可以取出游标中的数据，并对其进行处理了。从游标中取出数据的命令是FETCH。FETCH命令一次取出一行数据，并将其赋给指定的变量。FETCH命令的格式为：

FETCH 游标 INTO 变量1，变量2，……

游标中的数据只有在取出后才能进行处理。为此，需要在FETCH语句中用INTO子句指定多个变量，分别存放一行数据中各个列的值。FETCH命令将一行数据中各列的值依次赋给指定的变量。需要注意的是，变量的类型、数目要与游标中一行的各列相对应。

在用FETCH命令取出游标中的数据时，可以设想有一个指针，指向游标中的一行数据。当游标刚刚打开时，指针指向第一行，以后每取出一行，指针自动指向下一行，直到将所有的数据都取出为止。用FETCH命令取数据的过程如图3.5所示。

游标在使用完后，应该及时关闭，以释放它所占用的内存空间。关闭游标的命令是CLOSE，其语法格式为：

CLOSE 游标名

当游标关闭后，不能再从游标中获取数据。如果需要，可以再次打开游标。

考虑下面的游标。从emp表中检索员工7902的姓名、工资、工作时间。由于SELECT命令仅返回一行数据，所以处理的过程很简单。

图　3.5　用FETCH命令取出游标中的数据的过程

DECLARE

name emp.ename%type；

salary emp.sal%type；

hire_date emp.hiredate%type；

CURSOR cur_1 IS

SELECT ename, sal, hiredate FROM emp WHERE empno=7902；

BEGIN

open cur_1；

fetch cur_1 INTO name, salary, hire_date；

dbms_output.put_line（'姓名：'||name||'工资：'||salary||'工作时间：'||hire_date）；

close cur_1；

END；

这个块的执行结果为：

姓名：FORD　工资：3000　工作时间：03-DEC-81

为了使程序更加简洁，在PL/SQL块中可以使用记录变量。首先声明一个记录变量，它的结构与游标的结构相同。然后可以使用FETCH语句将游标中的一行数据取出后存放在记录变量中，接下来就可以对这个记录变量进行处理了。例如，对上面的PL/SQL块进行修改，在PL/SQL块中使用记录变量。修改后的代码如下：

DECLARE

CURSOR cur_2 IS

SELECT ename, sal, hiredate FROM emp WHERE empno=7902；

e cur_2%rowtype；

BEGIN

open cur_2；

fetch cur_2 INTO e；

dbms_output.put_line（'姓名：'||e.ename||'工资：'||e.sal||'工作时间：'||e.hiredate）；

close cur_2；

END；

这个块的执行结果与使用简单变量时的结果完全相同。

在上面的块中通过一个简单的游标，处理一行数据。在使用游标时，必须考虑各种特殊情况。如果SELECT语句没有返回结果，游标是空的，这时FETCH语句将取不到数据。如果SELECT语句返回多行数据，这时用一条FETCH语句仅能取到游标中的一行数据。利用游标的属性可以了解游标当前的状态，防止各种意外情况的发生。表3.7列出了显式游标的若干属性。

在下面的PL/SQL块中使用了显式游标的属性，使得PL/SQL块能够处理各种例外情况，比如没有取到合适的数据，或者取到多行数据。

DECLARE

CURSOR cur_3 IS

SELECT ename, sal, hiredate FROM emp WHERE deptno=20；

e cur_3%rowtype；

BEGIN

if not cur_3%ISOPEN then—如果游标没有打开，则打开它

open cur_3；

END if；

fetch cur_3 INTO e；—取出第一行数据

while cur_3%found loop

dbms_output.put_line（'姓名：'||e.ename||'工资：'||e.sal||'工作时间：'||e.hiredate）；

fetch cur_3 INTO e；

END loop；

dbms_output.put_line（'员工总数：'||cur_3%rowcount）；—获取的总行数

close cur_3；—关闭游标

END；

这个块的执行结果为：

姓名：SMITH　工资：800　工作时间：17-12月-80

姓名：JONES　工资：2975　工作时间：02-4月-81

姓名：SCOTT　工资：3000　工作时间：19-4月-87

姓名：ADAMS　工资：1100　工作时间：23-5月-87

姓名：FORD　工资：3000　工作时间：03-12月-81

员工总数：5

这个PL/SQL块的功能是查询部门20的所有员工的姓名、工资和工作时间。如果这个部门不存在，则不显示任何员工的信息，仅显示“员工总数：0”的信息。如果该部门有一个或多个员工，则显示他们的信息，并打印该部门员工总数。

在块中首先用游标的%ISOPEN属性判断游标是否打开，如果没有打开，则打开它。然后用FETCH语句取出第一行，并用游标的%FOUND属性判断是否取到数据。如果游标是空的，则这个属性的值为FALSE，这样就不用继续取数据了。如果取到了数据，则处理这行数据，并试图取下一行。这样通过循环的方式，每取到一行数据，就试图再取下一行，然后判断是否取到数据，直到将所有数据取出。

在游标的四个属性中，%ISOPEN属性用于测试游标的状态。其他三个属性用来测试FETCH命令的执行结果，%FOUND和%NOTFOUND属性用来测试最近的一次FETCH是否取到数据，%ROWCOUNT属性表示自游标打开以来，到目前为止，用FETCH命令获取的行数。

下面的例子用另外一种形式的循环处理游标。

DECLARE

CURSOR cur_4 IS

SELECT ename, sal, hiredate FROM emp WHERE deptno=20；

e cur_4%rowtype；

BEGIN

if not cur_4%ISOPEN then—如果游标没有打开，则打开它

open cur_4；

END if；

loop

fetch cur_4 INTO e；

EXIT WHEN cur_4%NOTFOUND；—如果最近一次FETCH没有取到数据，则退出循环，否则，对取到的数据进行处理

dbms_output.put_line（'姓名：'||e.ename||'工资：'||e.sal||'工作时间：'||e.hiredate）；

END loop；

dbms_output.put_line（'员工总数：'||cur_4%rowcount）；—获取的总行数

close cur_4；—关闭游标

END；

游标中的数据一般是通过循环方式来处理的。在上面两个例子中定义了两个游标cur_3和cur_4，并用常规的循环方法进行处理。PL/SQL提供了一种更简便的方法处理游标，这种方法利用FOR循环，逐行处理游标中的行。FOR语句的格式为：

FOR变量IN游标名LOOP

处理变量

END LOOP；

这里把游标中的数据当做一个集合，一次从中取出一行，赋给一个记录类型变量，然后就可以处理这个变量了。这个变量在使用之前不需要定义，在循环开始时自动产生，在FOR语句中可以直接使用，这个变量的结构与游标的结构完全相同。

利用FOR循环从游标中取数据时，不需要用OPEN命令打开游标。当循环开始执行时，游标被自动打开。游标在使用完后，也不需要执行CLOSE命令关闭。

FOR循环的循环体每执行一次，就会自动取出游标中的一行数据，赋给记录类型变量，然后指针自动往下移动，所以不需要通过FETCH命令获取游标中的数据。例如，下面的PL/SQL块利用FOR循环处理游标中的数据。

DECLARE

CURSOR cur_5 IS

SELECT ename, sal, hiredate FROM emp WHERE deptno=20；

e_count integer：=0；

BEGIN

for e in cur_5 loop

dbms_output.put_line（'姓名：'||e.ename||'工资：'||e.sal||'工作时间：'||e.hiredate）；

e_count：=e_count+1；

END loop；

dbms_output.put_line（'员工总数：'||e_count）；

END；

由于在循环执行结束时，游标已经关闭，因此无法再用游标的%rowcount属性统计获取的数据行数。在块中声明了一个变量e_count，每进行一次循环，变量e_count加1，这样就可以统计出获取的总行数。

从上面的例子可以看出，利用FOR循环可以大大简化游标的处理过程。

需要注意的是，为了重点说明游标的用法，在与游标有关的例子中，对于从游标中取出的数据，仅仅显示在屏幕上。读者可以根据需要，对这些数据进行其他的处理，比如写入其他表中。