Oracle 19C入门到精通之PL/SQL游标

游标提供了一种从表中检索数据并进行操作的灵活手段，游标主要用在服务器上，处理由客户端发送给服务器端的SQL语句，或是批处理、存储过程、触发器中的数据处理请求。游标的作用就相当于指针，通过游标PL/SQL程序可以一次处理查询结果集中的一行，并可以对该行数据执行特定操作，从而为用户在处理数据的过程中提供了很大方便。

在Oracle中，通过游标操作数据主要使用显式游标和隐式游标。另外，还包括具有引用类型特性的REF游标。

1. 基本原理

在PL/SQL块中执行SELECT、INSERT、UPDATE和DELETE语句时，Oracle会在内存中为其分配上下文区(context area)，即一个缓冲区。游标是指向该区的一个指针，或是命名一个工作区(work area)，或是一种结构化数据类型。游标为应用程序提供了一种具有对多行数据查询结果集中的每一行数据分别进行单独处理的方法，是设计嵌入式SQL语句的应用程序的常用编程方法。

游标分为显式游标和隐式游标两种；

显式游标是由用户声明和操作的一种游标；

隐式游标是Oracle为所有数据操纵语句（包括只返回单行数据的查询语句）自动声明和操作的一种游标。

在每个用户会话中，可以同时打开多个游标，其数量由数据库初始化参数文件中的open cursors参数定义。

2. 显式游标

显式游标是由用户声明和操作的一种游标，通常用于操作查询结果集（即由SELECT语句返回的查询结果），使用它处理数据的步骤包括声明游标、打开游标、读取游标和关闭游标4个步骤。其中，读取游标可能需要反复操作，因为游标每次只能读取一行数据，所以对于多条记录，需要反复读取，直到游标读取不到数据为止。其操作过程如下图所示：

声明游标需要在块的声明部分进行，其他3个步骤都在执行部分或异常处理中进行。

2.1. 声明游标

声明游标主要包括指定游标名称和为游标提供结果集的SELECT语句，语法格式如下：

CURSOR cur_name[(input_parameter1[,input_parameter2]…)] [RETURN ret_type]

IS select_ sentence;

cur_name：表示所声明的游标名称。

ret_type：表示执行游标操作后的返回值类型，这是一个可选项。

select_ sentence：游标所使用的SELECT语句，它为游标的反复读取提供了结果集。

input_parameter1：作为游标的“输入参数”，可以有多个，这是一个可选项。它指定用户在打开游标后向游标中传递的值，该参数的定义和初始化格式如下：

para_name [IN] DATATYPE [{:= | DEFAULT} para_value]

其中，para_name表示参数名称，其后面的关键字IN表示输入方向，可以省略；DATATYPE表示参数的数据类型，但数据类型不可以指定长度；para_value表示该参数的初始值或默认值，它也可以是一个表达式；para_name参数的初始值既可以以常规的方式赋值(:=)，也可以使用关键字DEFAULT初始化默认值。

与声明变量一样，声明游标也应该放在PL/SQL块的declare部分;

声明游标，用来读取emp表中职务为销售员(SALESMAN)的员工信息，代码如下:

declare

cursor cur_emp(var_job in varchar2:='SALESMAN')

is select empno,ename,sal

from emp

where job=var_job;

在上述代码中，首先声明了一个名称为cur_emp的游标，并定义了一个输入参数var_job（类型为varchar2，但不可以指定长度，如varchar2(10)，否则程序报错），该参数用来存储员工的职务（初始值为SALESMAN）；然后使用SELECT语句检索得到职务是销售员的结果集，以等待游标逐行读取它。

2.2. 打开游标

在游标声明完毕之后，必须打开游标才能使用。打开游标的语法格式如下：

OPEN cur_name[(para_value1[,para_value2]…)];

cur_name：要打开的游标名称。

para_value1：指定“输入参数”的值，根据声明游标时的实际情况，可以是多个或一个，这是一个可选项。如果在声明游标时定义了“输入参数”，并初始化其值，而在此处省略“输入参数”的值，则表示游标将使用“输入参数”的初始值；若在此处指定“输入参数”的值，则表示游标将使用这个指定的“参数值”。

打开游标就是执行定义的SELECT语句。执行完毕，将查询结果装入内存，游标停在查询结果的首部（注意，并不是第一行）。打开一个游标时，会完成以下几件事：

检查联编变量的取值。

根据联编变量的取值，确定活动集。

活动集的指针指向第一行。

--打开游标cur_emp，给游标的“输入参数”赋值为“MANAGER”

OPEN cur_emp('MANAGER');

这里可以省略“('MANAGER')”，这样表示“输入参数”的值仍然使用其初始值（即SALESMAN）；

2.3. 读取游标

当打开一个游标之后，就可以读取游标中的数据了，读取游标就是逐行将结果集中的数据保存到变量中。读取游标使用FETCH…INTO语句，其语法格式如下：

FETCH cur_name INTO {variable};

cur_name：要读取的游标名称。

variable：一个变量列表或“记录”变量（RECORD类型），Oracle使用“记录”变量来存储游标中的数据，要比使用变量列表方便得多。

在游标中包含一个数据行指针，它用来指向当前数据行。刚刚打开游标时，指针指向结果集中的第一行，当使用FETCH…INTO语句读取数据完毕之后，游标中的指针将自动指向下一行数据。这样，就可以在循环结构中使用FETCH…INTO语句来读取数据，每一次循环都会从结果集中读取一行数据，直到指针指向结果集中最后一条记录之后为止（实际上，最后一条记录之后是不存在的，是空的，这里只是表示遍历完所有的数据行），这时游标的%FOUND属性值为FALSE。

声明一个检索emp表中员工信息的游标，然后打开游标，并指定检索职务是MANAGER的员工信息，接着使用FETCH…INTO语句和WHILE循环语句读取游标中的所有员工信息，最后输出读取的员工信息，代码如下：

set serveroutput on

declare

/*声明游标，检索员工信息*/

cursor cur_emp (var_job in varchar2:='SALESMAN')

is select empno,ename,sal

from emp

where job=var_job;

type record_emp is record  --声明一个记录类型（RECORD类型）

(

/*定义当前记录的成员变量*/

var_empno emp.empno%type,

var_ename emp.ename%type,

var_sal emp.sal%type

);

emp_row record_emp;    --声明一个record_emp类型的变量

begin

open cur_emp('MANAGER');        --打开游标

fetch cur_emp into emp_row;  --先让指针指向结果集中的第一行，并将值保存到emp_row中

while cur_emp%found loop

dbms_output.put_line(emp_row.var_ename||'的编号是'||emp_row.var_empno||',工资是'||emp_row.var_sal);

fetch cur_emp into emp_row;  --让指针指向结果集中的下一行，并将值保存到emp_row中

end loop;

close cur_emp;   --关闭游标

end;

/

2.4. 关闭游标

当所有的活动集都被检索以后，游标就应该被关闭。PL/SQL程序将被告知对于游标的处理已经结束，与游标相关联的资源可以被释放了。这些资源包括用来存储活动集的存储空间，以及用来存储活动集的临时空间。关闭游标的语法格式如下：

CLOSE cur_name;

参数cur_name表示要关闭的游标名称。一旦关闭了游标，SELECT操作就会被关闭，并释放占用的内存区。如果再从游标提取数据就是非法的，这样做会产生以下两种Oracle错误：

ORA-1001：Invalid CUSOR --非法游标

ORA-1002：FETCH out of sequence --超出界限

3. 隐式游标

在执行一个SQL语句时，Oracle会自动创建一个隐式游标，这个游标是内存中处理该语句的工作区域。隐式游标主要是处理数据操作语句（如UPDATE、DELETE语句）的执行结果，当然在特殊情况下，也可以处理SELECT语句的查询结果。由于隐式游标也有属性，因此当使用隐式游标的属性时，需要在属性前面加上隐式游标的默认名称—SQL。

在实际的PL/SQL编程中，经常使用隐式游标来判断更新数据行或删除数据行的情况。

把emp表中销售员（即SALESMAN）的工资上调20%，然后使用隐式游标SQL的%ROWCOUNT属性输出上调工资的员工数量，代码如下：

set serveroutput on

begin

update emp

set sal=sal*(1+0.2)

where job='SALESMAN';    --把销售员的工资上调20%

if sql%notfound then     --若UPDATE语句没有影响到任何一行数据

dbms_output.put_line('没有员工需要上调工资');

else   --若UPDATE语句至少影响到一行数据

dbms_output.put_line('有'||sql%rowcount||'个员工工资上调20%');

end if;

end;

/

在上述代码中，标识符SQL就是UPDATE语句在更新数据过程中所使用的隐式游标，它通常处于隐藏状态，是由Oracle系统自动创建的。当需要使用隐式游标的属性时，标识符SQL就必须显式地添加到属性名称之前。另外，无论是隐式游标还是显式游标，它们的属性总是反映最近的一条SQL语句的处理结果。因此，在一个PL/SQL块中出现多个SQL语句时，游标的属性值只能反映出紧挨着它的上一条SQL语句的处理结果。

4. 游标的属性

无论是显式游标还是隐式游标，都具有%FOUND、%NOTFOUND、%ROWCOUNT和%ISOPEN 4个属性，通过这4个属性可以获知SQL语句的执行结果以及该游标的状态信息。

游标属性只能用在PL/SQL的流程控制语句内，而不能用在SQL语句内。下面对这4个属性的功能进行讲解。

%FOUND：布尔型属性，如果SQL语句至少影响到一行数据，则该属性为TRUE，否则为FALSE。

%NOTFOUND：布尔型属性，与%FOUND属性的功能相反。

%ROWCOUNT：数字型属性，返回受SQL语句影响的行数。

%ISOPEN：布尔型属性，当游标已经打开时返回TRUE，当游标关闭时返回FALSE。

4.1. 是否找到游标(%FOUND)

%FOUND属性表示当前游标是否指向有效一行，若是则值为TRUE，否则值为FALSE。检查此属性可以判断是否结束游标使用。

open cur_emp;                          --打开游标

fetch cur_emp into var_ename,var_job;  --将第一行数据放入变量中，游标后移

loop

exit when not cur_em%found;          --使用了%FOUND属性

end loop;

在隐式游标中%FOUND属性的引用方法是SQL %FOUND。使用SQL %FOUND，代码如下：

delete from emp where empno = emp_id;--emp_id为一个有值变量

if sql%found then--如果删除成功，则将该行员工编号写入success表中

insert into success values(empno);

else --如果删除不成功，则将该行员工编号写入fail表中

insert into fail values(empno);

end if;

4.2. 是否没找到游标(%NOTFOUND)

%NOTFOUND属性与%FOUND属性类似，但其值恰好相反。

open cur_emp;  --打开游标

fetch cur_emp into var_ename,var_job;  --将第一行数据放入变量中，游标后移

loop

exit when cur_em%notfound;  --使用了%NOTFOUND属性

end loop;

在隐式游标中%NOTFOUND属性的引用方法是SQL %NOTFOUND。

delete from emp where empno = emp_id; --emp_id为一个有值变量

if sql %notfound then  --如果删除不成功，则将该行员工编号写入fail表中

insert into fail values(empno);

else    --如果删除成功，则将该行员工编号写入success表中

insert into success values(empno);

end if;

4.3. 游标行数(%ROWCOUNT)

%ROWCOUNT属性记录了游标抽取过的记录行数，也可以理解为当前游标所在的行号。这个属性在循环判断中也很有用，使得不必抽取所有记录行就可以中断游标操作。

loop

fetch cur_emp into var_empno,var_ename,var_job;

exit when cur_emp%rowcount = 10; --只抽取10条记录

…

end loop;

还可以用FOR语句控制游标的循环，系统隐含地定义了一个数据类型为ROWCOUNT的记录，作为循环计数器，将隐式地打开和关闭游标。

4.4. 游标是否打开(%ISOPEN)

%ISOPEN属性表示游标是否处于打开状态。在实际应用中，使用一个游标前，第一步往往是检查它的%ISOPEN属性，看其是否已打开，若没有，要打开游标再向下操作。这是防止程序运行过程中出错的关键一步。

if cur_emp%isopen tneh

fetch cur_emp into var_empno,var_ename,var_job;

else

open cur_emp;

end if;

在隐式游标中此属性的引用方法是SQL %ISOPEN。隐式游标中SQL %ISOPEN属性总为TRUE，因此在隐式游标使用中不用打开和关闭游标，也不用检查其打开状态。

4.5. 参数化游标

在定义游标时，可以带上参数，使得在使用游标时，根据参数不同所选中的数据行也不同，达到动态使用的目的。

声明一个游标，用于检索指定员工编号的员工信息，然后使用游标的%FOUND属性来判断是否检索到指定员工编号的员工信息，代码如下：

set serveroutput on

declare

var_ename varchar2(50);  --声明变量，用来存储员工名称

var_job varchar2(50);    --声明变量，用来存储员工的职务

/*声明游标，检索指定员工编号的员工信息*/

cursor cur_emp           --定义游标，检索指定编号的记录信息

is select ename,job

from emp

where empno=7499;

begin

open cur_emp;                          --打开游标

fetch cur_emp into var_ename,var_job;  --读取游标，并存储员工名称和职务

if cur_emp%found then                  --若检索到数据记录，则输出员工信息

dbms_output.put_line('编号是7499的员工名称为：'||var_ename||'，职务是：'||var_job);

else

dbms_output.put_line('无数据记录');  --提示无记录信息

end if;

end;

/

使用显式游标时，需要注意以下事项：

使用前必须用%ISOPEN检查其打开状态，只有此值为TRUE的游标才可使用，否则要先将游标打开。

在使用游标的过程中，每次都要用%FOUND或%NOTFOUND属性检查是否成功返回，即是否还有要操作的行。

将游标中行取至变量组中时，对应变量个数和数据类型必须完全一致。

使用完游标必须将其关闭，以释放相应内存资源。

5. 游标变量

如同常量和变量的区别一样，前面所讲的游标都是与SQL语句相关联的，它是静态的，并且在编译该块时此语句已经是可知的。而游标变量可以在运行时与不同的语句相关联，它是动态的。游标变量被用于处理多行的查询结果集。在同一个PL/SQL块中，游标变量不同于特定的查询绑定，而是在打开游标时才能确定所对应的查询。因此，游标变量可以一次对应多个查询。

使用游标变量之前，必须先声明它，然后在运行时必须为其分配存储空间。游标变量是REF类型的变量，类似于高级语句中的指针。

5.1. 声明游标变量

游标变量是一种引用类型。当程序运行时，它们可以指向不同的存储单元。如果要使用引用类型，首先要声明该变量，然后相应的存储单元必须被分配。PL/SQL中的引用类型变量通过下述语法进行声明：

REF type

type是已经被定义的类型。REF关键字指明新的类型必须是一个指向经过定义的类型的指针。因此，游标变量可以使用的类型就是REF CURSOR。

定义一个游标变量类型的完整语法如下：

TYPE  IS REF CURSOR

RETURN 

其中，是新的引用类型的名字，而是一个记录类型，它指明了最终由游标变量返回的选择列表的类型。

游标变量的返回类型必须是一个记录类型。它可以被显式声明为一个用户定义的记录，或者隐式使用%ROWTYPE进行声明。在定义了引用类型以后，就可以声明该变量了。

在声明部分，给出用于游标变量的不同游标，代码如下：

set serveroutput on

DECLARE

TYPE t_StudentRef IS REF CURSOR --定义使用%ROWTYPE

RETURN STUDENTS%ROWTYPE;

TYPE t_AbstractstudentsRecord IS RECORD( --定义新的记录类型

sname STUDENTS.sname%TYPE,

sex STUDENTS.sex%type);

v_AbstractStudentsRecord t_AbstractStudentsRecord;

TYPE t_AbstractStudentsRef IS REF CURSOR  --使用记录类型的游标变量

RETURN t_AbstractStudentsRecord;

TYPE t_NameRef2 IS REF CURSOR   --另一类型定义

RETURN v_AbstractStudentsRecord%TYPE;

v_StudentCV t_StudentsRef;      --声明上述类型的游标变量

v_AbstractStudentCV t_AbstractStudentsRef;

在上面代码中极少的游标变量是受限的，它的返回类型只能是特定类型。而在PL/SQL语句中，还有一种非受限游标变量，它在声明时没有RETURN子句。一个非受限游标变量可以为任何查询打开。

定义游标变量，代码如下：

DECLARE

--定义非受限游标变量

TYPE t_FlexibleRefIS REF CURSOR;

--游标变量

V_CURSORVar t_FlexibleRef;

5.2. 打开游标变量

如果要将一个游标变量与一个特定的SELECT语句相关联，需要使用OPEN FOR语句，其语法格式如下：

OPENFOR;

如果游标变量是受限的，则SELECT语句的返回类型必须与游标变量所受限的记录类型匹配，如果不匹配，则Oracle会返回错误ORA_6504。

打开游标变量v_StudentSCV，代码如下：

DECLARE

TYPE t_StudentRef IS REF CURSOR  --定义使用%ROWTYPE

RETURN STUDENTS%ROWTYPE;

v_StudentSCV t_StudentRef;       --定义新的记录类型

BEGIN

OPEN v_StudentSCV FOR

SELECT * FROM STUDENTS;

END;

5.3. 关闭游标变量

游标变量的关闭和静态游标的关闭类似，均使用CLOSE语句，这会释放查询所使用的空间。关闭已经关闭的游标变量是非法的。

6. 通过FOR语句循环游标

在使用隐式游标或显式游标处理具有多行数据的结果集时，可以配合使用FOR语句来完成。在使用FOR语句遍历游标中的数据时，可以把它的计时器看作一个自动的RECORD类型的变量。

在FOR语句中遍历隐式游标中的数据时，通常在关键字IN的后面提供由SELECT语句检索的结果集，在检索结果集的过程中，Oracle系统会自动提供一个隐式的游标SQL。

使用隐式游标和FOR语句检索出职务是销售员(SALESMAN)的员工信息并输出，代码如下：

set serveroutput on

begin

for emp_record in (select empno,ename,sal from emp where job='SALESMAN') --遍历隐式游标中的记录

loop

dbms_output.put('员工编号：'||emp_record.empno);       --输出员工编号

dbms_output.put('；员工名称：'||emp_record.ename);     --输出员工名称

dbms_output.put_line('；员工工资：'||emp_record.sal);  --输出员工工资

end loop;

end;

/

在FOR语句中遍历显式游标中的数据时，通常在关键字IN的后面提供游标的名称，其语法格式如下：

FOR var_auto_record IN cur_name LOOP

plsqlsentence;

END LOOP;

var_auto_record：自动的RECORD类型的变量，可以是任意合法的变量名称。

cur_name：指定的游标名称。

plsqlsentence：PL/SQL语句。

使用显式游标和FOR语句检索出部门编号是30的员工信息并输出，代码如下：

set serveroutput on

declare

cursor cur_emp is

select * from emp

where deptno = 30; --检索部门编号为30的员工信息

begin

for emp_record in cur_emp  --遍历员工信息

loop

dbms_output.put('员工编号：'||emp_record.empno);      --输出员工编号

dbms_output.put('；员工名称：'||emp_record.ename);    --输出员工名称

dbms_output.put_line('；员工职务：'||emp_record.job); --输出员工职务

end loop;

end;

/

注意：在使用游标（包括显式和隐式）的FOR循环中，可以声明游标，但不用进行打开游标、读取游标和关闭游标等操作，这些由Oracle系统自动完成。