【翻译】Oracle游标详细说明

   这篇文章是选取官方文档的部分章节翻译过来的，去除了原文中的例子，并在结尾补充了几个例子。有兴趣的朋友可以点击文章末尾的连接去阅读官方文档。

一、游标的定义

  游标是指向专用SQL区域的指针，该区域存储有关处理特定SELECT或DML语句的信息。本章解释的游标是会话游标。会话游标存在于会话中直到会话结束。由PL/SQL创建和管理的游标称为隐式游标，由用户创建和管理的游标称为显式游标。你可以通过游标的属性获取任意会话游标的相关信息。通过查询动态性能视图V$OPEN_CURSOR，可以列出当前已经打开和解析的游标。

二、隐式游标

  每次执行select 或DML操作时，都会生成隐式游标。用户不能控制隐式游标，但可以通过游标的属性获取游标的信息。

  隐式游标属性的语法是SQLattribute（因此隐式游标也通常叫做SQL游标）。SQLattribute总是指向最近运行的select或DML语句。如果最近没有运行这样的语句，则属性返回NULL。

  当语句运行结束时，隐式游标关闭，但是它的属性会被保留到另一条select或DML语句执行。最近的语句运行的结果可能属于不同的值，如果要保留属性值供以后使用，可以将其保存在本地变量中。另外，其他的操作，如子程序的调用，可能在你测试之前改变属性值。

隐式游标的属性有以下几种：

三、显式游标

  显式游标由用户创建并管理。用户在使用显式游标之前必须声明和定义游标名称并将其与查询相关联（通常，查询会返回多行）。然后用户可以通过以下方式处理查询结果集：

  使用open命令打开游标，使用fetch获取行，使用close关闭游标；

  与隐式游标不同，可以通过名称引用显示游标或游标变量。因此，显式游标或游标变量称为命名游标。

3.1 声明和定义显式游标

  用户可以首先声明一个显式游标，然后在同一个块，子程序或包中定义它；或者同时声明和定义它。

只声明游标，具有如下的语法：

CURSOR cursor_name [ parameter_list ] RETURN return_type;

声明游标并定义游标的语法：

CURSOR cursor_name [ parameter_list ] [ RETURN return_type ]  IS select_statement;

声明显式游标的例子：

DECLARE  CURSOR c1 RETURN departments%ROWTYPE;    -- 声明C1  

CURSOR c2 IS SELECT employee_id, job_id, salary -- 声明并且定义 c2 

FROM employees    

WHERE salary > 2000;  

CURSOR c1 RETURN departments%ROWTYPE IS -- 定义 c1,-- 重复return type 

SELECT * FROM departments 

WHERE department_id = 110;  

CURSOR c3 RETURN locations%ROWTYPE;      -- 声明c3  

CURSOR c3 IS                     -- 定义c3, -- 忽略 return type      

SELECT * FROM locations                  

WHERE country_id = 'JP'; 

BEGIN  NULL; 

END; /

3.2 打开和关闭游标

  声明和定义显式游标后，可以使用OPEN语句打开它；您使用CLOSE语句关闭一个打开的显式游标，从而允许重用其资源。关闭游标后，无法从其结果集中提取记录或引用其属性。

  用户可以重新打开一个已经关闭的游标。游标在重新打开之间必须要被关闭。否则，PL/SQL会引发预定义异常CURSOR_ALREADY_OPEN.

3.3 使用显式游标获取数据

  打开一个显式游标后，可以使用FETCH语句获取结果集。返回一行的FETCH语句的基本语法是：

    FETCH cursor_name INTO into_clause

  into_clause是一个变量列表或单个记录变量。 对于查询返回的每个列，变量列表或记录必须具有对应的类型兼容变量或字段。％TYPE和％ROWTYPE属性可用于声明变量和记录以在FETCH语句中使用。FETCH语句检索结果集的当前行，将该行的列值存储到变量或记录中，并将光标前移到下一行。通常，在LOOP语句中使用FETCH语句，当FETCH语句用完行时退出。 要检测此退出条件，请使用游标属性％NOTFOUND。因为当FETCH语句不返回任何行时，PL/SQL不会引发异常。

3.4 显式游标查询中的变量

  显式游标查询可以引用其作用域中的任何变量。 当打开显式游标时，PL / SQL将评估查询中的任何变量，并在标识结果集时使用这些值。 稍后更改变量的值不会更改结果集。【定义游标时，游标中结果集就已经确定，如要改变结果集，必须关闭游标并重新打开游标】

3.5 当显式游标列需要别名时

  当显式游标查询包含虚拟列（表达式）时，如果满足以下任一条件，则该列必须具有别名：

  1、使用游标读取使用％ROWTYPE声明的记录。

  2、需要引用虚拟列

3.6 接受参数的显示游标

  用户可以创建具有形式参数的显式游标，然后在每次打开游标时将不同的实际参数传递到游标。 在游标查询中，可以在可以使用常量的任何位置使用正式的光标参数。在光标查询之外，用户不能引用正式的光标参数。

3.7 显示游标的属性

%ISOPEN  TRUE：游标打开状态；FALSE：其他状态。

%FOUND   NULL:显示游标打开但是还没有获取第一行

TRUE:从显式游标的最近一次提取返回了一行

FALSE：其他情况

%NOTFOUND NULL:显示游标打开但是还没有获取第一行

FALSE：从显式游标的最近一次提取返回了一行

TRUE:其他情况

%ROWCOUNT：在显式游标打开后但在第一次提取之前为零；否则就获取行数。

四、例子

4.1 获取tabs中表名称的两个例子

方法一：将结果集装在记录中

declare

--表行的记录变量

cursor mycur is select * from tabs;

cur_result tabs%rowtype;

begin

open mycur;--打开游标

loop

fetch mycur into cur_result;--获取数据

exit when mycur%notfound;--退出循环

dbms_output.put_line('The table name is '||cur_result.table_name);

end loop;

close mycur;--关闭游标

end;

方法二：将结果集装在变量中

declare

--变量

cursor mycur is select table_name from tabs;

v_tablename tabs.table_name%type;

begin

open mycur;--打开游标

loop

fetch mycur into v_tablename;--获取数据

exit when mycur%notfound;--退出循环

dbms_output.put_line('The table name is '||v_tablename);

end loop;

close mycur;--关闭游标

end;

方法三：将结果集装在游标类型的row%type中

declare

--表行的记录变量

cursor mycur is select table_name from tabs;

cur_result mycur%rowtype;

begin

open mycur;--打开游标

loop

fetch mycur into cur_result;--获取数据

exit when mycur%notfound;--退出循环

dbms_output.put_line('The table name is '||cur_result.table_name);

end loop;

close mycur;--关闭游标

end;

4.2 动态游标

静态游标的声明与定义必须在同一个块中执行，动态游标可以在begin之前声明游标，在打开游标时定义sql语句，即可以使用open cursor_name for sql_statement的形式打开游标。sql_statement可以是静态的SQL语句，也可以是动态的SQL语句。

例一：静态的SQL语句

declare

--表行的记录变量

type cursor_type is ref cursor;

mycur cursor_type;

cur_result tabs%rowtype;

begin

open mycur for select * from tabs;--打开游标

loop

fetch mycur into cur_result;--获取数据

exit when mycur%notfound;--退出循环

dbms_output.put_line('The table name is '||cur_result.table_name);

end loop;

close mycur;--关闭游标

end;

例二：动态的SQL语句

declare

type cursor_type is ref cursor;

mycur cursor_type;

cur_result tabs%rowtype;

v_sql varchar2(1000);

begin

v_sql:='select * from tabs';

open mycur for v_sql;--打开游标

loop

fetch mycur into cur_result;--获取数据

exit when mycur%notfound;--退出循环

dbms_output.put_line('The table name is '||cur_result.table_name);

end loop;

close mycur;--关闭游标

end;

五、其他说明

5.1 Oracle游标的官方文档

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/static.htm#LNPLS00602