显式游标

由用户以变量的形式定义的游标。

定义游标

在块语句、存储过程、存储函数、包的变量定义部分进行游标定义。游标支持两种形式进行定义。

语法格式

CursorDef::=
    CURSOR ColId [ func_args ] { IS | AS } SelectStmt [ parallel_opt ] [ opt_wait ]

CursorStmt::=
    DECLARE ColId [ func_args ] CURSOR FOR SelectStmt [ parallel_opt ] [ opt_wait ]

参数说明

• ColId：定义的游标名称。
• func_args：可选参数列表，若游标定义为参数游标，则打开游标时需相应的给出实参。
• SelectStmt: 一个完整的SELECT语句，它定义了游标将要操作的数据集，可以为单表查询、联合查询、视图查询、分组统计、排序查询等。
• parallel_opt：并行选项，指定游标是否应该并行执行。
• opt_wait：等待选项，指明当没有立即可用的资源时，游标的行为（例如等待直到资源可用，还是立即返回错误）。

打开游标

在定义了游标后，访问游标数据前必须进行打开游标操作，否则无法正常访问游标数据。

语法格式

OPEN ColId [(paramter type[,paramter type...])];

提取数据

提取游标数据用于逐行获取游标的查询返回结果，同时可将对应结果填充至变量，需要注意的是提取数据是默认每次获取一行数据，要返回多行记录需重复执行或在循环语句中执行。

语法格式

FETCH ColId [ opt_bulk ] [ opt_into_list ]

opt_bulk::=
    BULK COLLECT

参数说明

• opt_bulk：用于指定是否使用批量获取（BULK COLLECT）
• opt_into_list：指定将检索到的数据存储到变量或集合中。

游标返回结果包含多列时，可采用多变量赋值或记录变量赋值的方式进行，记录变量可通过tab_name%ROWTYPE或cursor_name%ROWTYPE方式定义。

关闭游标

游标使用完毕后必须显式关闭游标，释放其所占资源。

语法格式

CLOSE ColId;

游标属性

显式游标的属性：

• %ROWCOUNT：指示FETCH语句返回记录行数。
• %FOUND：指示前一FETCH是否返回一行数据，若提取到记录该值为TRUE，反之为FALSE。
• %NOTFOUND：与%FOUND含义相反，当FETCH提取到数据时该值为FALSE，未提取到数据返回TRUE。
• %ISOPEN：指示游标是否打开，显式在提取数据前必须执行打开游标操作，若已打开则该值为TURE，否则为FALSE。

可以通过游标名%属性的方式使用上述游标属性。

示例

使用CURSOR IS定义了一个带有参数inid的游标cur，该游标将返回id大于inid的所有记录并返回总行数。

-- 创建表并插入数据
SQL> CREATE TABLE test_cur(id INT,name VARCHAR(20));

SQL> INSERT INTO test_cur VALUES(1,'TEST1')(2,'TEST2')(3,'TEST3');

-- 定义并使用游标
SQL> DECLARE
  CURSOR cur(inid INT) IS
    SELECT * FROM test_cur WHERE id > inid;
  oid   INT;
  oname VARCHAR;
BEGIN
  IF NOT cur%ISOPEN THEN
    OPEN cur(1);
  END IF;
  FETCH cur INTO oid, oname;
  WHILE cur%FOUND LOOP
    SEND_MSG('ID IS:' || oid || ' NAME IS:' || oname);
    FETCH cur INTO oid, oname;
  END LOOP;
  SEND_MSG('TOTAL FETCH :' || cur%ROWCOUNT || ' ROWS');
  CLOSE cur;
END;
/
-- 输出
ID IS:2 NAME IS:TEST2
ID IS:3 NAME IS:TEST3
TOTAL FETCH :2 ROWS

使用DECLARE CURSOR FOR方式定义游标。

-- 定义游标
SQL> DECLARE cur(inid INT) CURSOR FOR SELECT * FROM test_cur WHERE id > inid;
    /

ID | NAME | 
------------------------------------------------------------------------------

-- 打开游标
SQL> OPEN cur(1);    

-- 获取数据
SQL> FETCH cur;

ID | NAME | 
------------------------------------------------------------------------------
2 | TEST2|
3 | TEST3|

-- 关闭游标
SQL> CLOSE cur;

使用BULK COLLECT批量返回结果。

SQL> DECLARE
  CURSOR cur IS
    SELECT * FROM test_cur;
  TYPE trow IS TABLE OF cur%ROWTYPE;
  otab trow;
BEGIN
  OPEN cur;
  FETCH cur BULK COLLECT INTO otab;
  CLOSE cur;
  FOR i IN 1 .. otab.COUNT LOOP
    SEND_MSG(otab(i).id || '|' || otab(i).name);
  END LOOP;
END;
/
1|TEST1
2|TEST2
3|TEST3