Oracle游标详解

前言：

游标在PLSQL中作为对数据库操作的必备部分应该熟练掌握，灵活运用游标才能深刻领会到程序控制数据库操作的内涵。

在PLSQL块中执行SELECT,INSERT,UPDATE和DELETE语句事，Oracle会在内存中为其分配上下文区（Context Area），即缓冲区。游标是指向该区的一个指针。个人理解其相当于C或者JAVA中指向一个临时数组或者链表的指针。使用游标可以针对缓冲区中的每一条记录进行处理。

游标分为显示游标和隐式游标两种。在每个用户会话中，可以同时打开多个游标，其数量是由数据库初始化参数文件中的OPEN CURSORS参数定义的。

查看最大打开游标数：在命令窗口执行show parameter open_cursors
修改最大打开游标数：alter system set open_cursors=1000 scope=both;（修改到1000）
查看当前打开游标数：select count(*) from v$open_cursor;

游标的处理包括游标声明，打开游标，提取游标，关闭游标4步，其操作流程如下图。

游标声明：

对游标的声明定义了游标的名字，并将该游标和一个SELECT语句关联起来。显示游标的声明在DECLARE块中，声明语法为：

CURSOR<游标名> IS <SELECT语句>
[RETURN datatype] ;
其中，游标名就是游标的名字，SELECT语句就是要处理的查询语句。RETURN是可选的，如果写了，则SELECT语句的返回类型应与RETURN声明的类型完全一致。
需要注意的是，在游标定义的SELECT语句中，包括连接和带有UNION子句的语句，但不包含INTO子句。INTO子句是提取游标的一部分。

打开游标：

打开游标的语法为：

OPEN <游标名>；
其中，<游标名>标识了一个已经被声明的游标。
打开游标就是执行了声明中的SELECT语句。执行完毕后，查询结果装载入内存中，游标此时停留在查询结果的首部（注意：并不是第一行）。当打开一个游标时，会完成以下几件事情。
1.检查联编变量的取值。
2.根据联编变量的取值，确定活动集。
3.活动集的指针指向第一行。
打开一个已经被打开的游标是合法的，因为PLSQL会在打开之前隐式地执行一条CLOSE语句，当然，不建议这样做。

一次可以同时打开多个游标。

提取游标：

取值语句是FETCH，它的用法有两种，如下所示：

         FETCH<游标名> INTO <变量列表>

或者FETCH <游标名> INTO PL/SQL记录

其中，变量列表是用逗号隔开的已经声名过的SQL变量，而PLSQL记录是指已经声名过的PLSQL记录。

在上述两种用法中，INTO子句中的变量类型必须与查询列表的选择列表的类型相兼容，否则不能执行。

FETCH语句每执行一次，游标就向后移动一行，直到结束。

游标只能逐个向后移动，不能跳跃或者向前移动。

关闭游标:

当游标指向的结果集被检索完，或者游标无需再使用后，应当关闭游标，释放用来存储结果集的存储空间或者临时空间。
关闭游标用CLOSE关键字。
CLOSE <游标名>

一旦关闭了游标，同时也就关闭了游标绑定的SELECT操作，此时内存区已经释放，再进行提取数据或者再次关闭该游标，会产生ORA1001或者ORA1002错误。

游标使用示例：

DECLARE
CURSOR_TABLE_XM VARCHAR2(50);
CURSOR_TABLE_BH NUMBER;
TARGET_BH VARCHAR2(5);
CURSOR CUR_TEST IS SELECT XM,BH FROM CURSOR_TABLE ;
begin
OPEN CUR_TEST;
  FETCH CUR_TEST INTO CURSOR_TABLE_XM,CURSOR_TABLE_BH;
  LOOP
      EXIT WHEN  CURSOR_TABLE_BH=10;
 	    dbms_output.put_line(CURSOR_TABLE_BH||' '||CURSOR_TABLE_XM);
      FETCH CUR_TEST INTO CURSOR_TABLE_XM,CURSOR_TABLE_BH;
  END LOOP;
CLOSE CUR_TEST;
end;

游标也可以用来实现插入，删除和修改记录，但是必须在游标定义时指定FOR 子句后面的自定义类型。使用FOR 语句实现游标的隐式自动打开和关闭。

注意用更新游标的时候，不能在游标期间commit. 否则会报ORA-01002: fetch out of sequence

就是COMMIT;导致错误

在打开有for update的cursor时，系统会给取出的数据加上排他锁(exclusive),这样在这个锁释放前其他用户不能对这些记录作update、delete和加锁。

而一旦执行了commit,锁就释放了，游标也变成无效的,再去fetch数据时就出现错误了。因而要把commit放在循环外，等到所有数据处理完成后再commit,然后关闭cursor.

隐式游标：

在PLSQL中，使用SELECT的同时，Oracle会隐式的使用游标，这种游标无需用户定义，也不需要打开和关闭，全是Oracle自己控制。

隐式游标必须要有一个INTO子句，所以使用隐式游标的SELECT只能选中一行数据或者只返回一行数据。

游标属性

无论是显式游标还是隐式游标，它们都有%ISOPEN,%FOUND,%NOTFOUND,%ROWCOUNT四种属性。这四种属性描述了与游标操作相关的DML语句的执行情况。上述四种属性只能用在流程控制语句内，不能直接在SQL语句内使用。

游标的属性：

游标属性	显式游标引用方法	隐式游标引用方法
是否找到游标	%FOUND	SQL%FOUND
是否没有找到游标	%NOTFOUND	SQL%NOTFOUND
游标行数	%ROWCOUNT	SQL%ROWCOUNT
游标是否打开	%ISOPEN	SQL%ISOPEN

参数化游标:

在游标声明时，我们可以把SELECT语句中用到的数据使用变量代替，在游标被打开时以参数的形式传入值，可以使游标在使用时更加的灵活，达到动态使用的目的。

参数化游标的声明和使用方法示例：

DECLARE

CURSOR(CURSOUR_BH NUMBER) IS SELECT BH,XM FROM CURSOR_TABLEWHERE BH<CURSOUR_BH;

BEGIN

OPEN CURSOR(10);

…

END;

参数化游标使用示例：

DECLARE
CURSOR_TABLE_XM VARCHAR2(50);
CURSOR_TABLE_BH NUMBER;
CURSOR CUR_TEST(TEMP_BH NUMBER) IS SELECT XM,BH FROM CURSOR_TABLE WHERE BH<TEMP_BH ORDER BY BH ;
begin

OPEN CUR_TEST(20);
  FETCH CUR_TEST INTO CURSOR_TABLE_XM,CURSOR_TABLE_BH;
  LOOP
      EXIT WHEN CUR_TEST%NOTFOUND ;
 	    dbms_output.put_line(CURSOR_TABLE_BH||' '||CURSOR_TABLE_XM);
      FETCH CUR_TEST INTO CURSOR_TABLE_XM,CURSOR_TABLE_BH;
  END LOOP;
CLOSE CUR_TEST;
end;

游标变量

前面所讲到的游标都是在声明时就与一个SQL语句绑定，并且该SQL语句在编译时就是已知的，静态的。就如同常量和变量的区别一样，游标变量可以在运行时决定关联的SQL语句，是动态的。因此，游标变量可以对应多个查询。
游标变量是REF类型的变量，类似于C中的二级指针，在使用游标变量之前，需要先声明，再在运行时为其分配存储空间。
PLSQL中引用类型声明方式为REF TYPE，其中TYPE是已经被定义过的类型。游标可以使用的类型就是REF CURSOR。
完整的游标变量的声明方式如下：
TYPE <游标变量名> IS REF CURSOR
RETURN  <返回类型>;
上述带有返回类型的游标变量是受限的，它在打开时所绑定的SELECT语句的返回类型必须与游标变量声明的返回类型一致，否则会返回ORA-6504错误。而Oracle还有一种非受限的游标变量，它在声明时没有RETURN语句，可以为任何SELECT语句打开。
游标变量的打开需要使用OPEN FOR 语句，语法如下：
OPEN <游标变量> FOR <SELECT语句>

游标变量的关闭和静态游标类似，都是使用CLOSE关键字。同样，关闭一个已经关闭的游标变量是非法的。

非受限游标变量示例：

DECLARE
CURSOR_TABLE_XM VARCHAR2(50);
CURSOR_TABLE_BH NUMBER;
TYPE T_REF IS REF CURSOR;
CURSOR_TEST T_REF;
BEGIN
    OPEN CURSOR_TEST FOR SELECT * FROM CURSOR_TABLE;
     FETCH CURSOR_TEST INTO CURSOR_TABLE_XM,CURSOR_TABLE_BH;
  LOOP
      EXIT WHEN CURSOR_TEST%ROWCOUNT > 10 OR CURSOR_TEST%NOTFOUND;
 	    dbms_output.put_line(CURSOR_TABLE_BH||' '||CURSOR_TABLE_XM);
      FETCH CURSOR_TEST INTO CURSOR_TABLE_XM,CURSOR_TABLE_BH;
  END LOOP;
    CLOSE CURSOR_TEST;
END;