游标

功能描述

GaussDB 100支持四种游标，分别为显式游标、引用游标、FOR循环游标和隐式游标。

注意事项

游标不支持RETURN子句。

引用游标可以作为输出参数返回，但显式游标和隐式游标不能作为输出参数返回。
隐式游标不需要声明；显式游标在声明时就绑定了SELECT语句，因此在编译阶段校验SELECT语句；引用游标可以动态关联不同的SELECT语句，因此在执行阶段才能校验SELECT语句。
游标变量目前实现均为弱数据类型相关，不支持在游标类型的定义语句中使用“RETURN return_type”子句指定返回结果的数据类型，而是以游标打开的行记录的数据类型为准。

声明游标

语法格式

方式一：
```
cursor_name SYS_REFCURSOR;
```

方式二：

--定义一个REF游标类型（该游标类型和SYS_REFCURSOR是等效的）。
TYPE type_name IS REF CURSOR;

--声明一个REF游标类型的游标。cursor_name type_name;

声明显式游标。

CURSOR cursor_name [(param_list)] IS (select_statement);

声明引用游标（使用以下两种声明的引用游标是等效的）。

参数说明

cursor_name
所声明游标的名称。
param_list
显式游标的参数列表。
声明显式游标时，关键字IS后面的SELECT语句的WHERE子句可以使用这些参数。显式游标的参数列表是可选的，如果定义了参数，则必须在打开游标时传递相应的实际参数。
select_statement
用于声明显式游标的SELECT语句。
声明显式游标时，关键字IS后必须使用SELECT语句。该SELECT语句是对表或视图的查询语句，甚至也可以是联合查询。可以带WHERE条件、ORDER BY或GROUP BY等子句，但不能使用INTO子句。SELECT语句的详细信息请参见SELECT。
SYS_REFCURSOR
系统游标，用于声明引用游标。
使用“cursor_name SYS_REFCURSOR;”声明游标时，不需要带SELECT语句。声明后的变量可做为输出参数，在OPEN阶段绑定SQL语句。
type_name
自定义REF游标类型的名称。

示例

--删除已存在的同名表。
DROP TABLE IF EXISTS test;
--创建表test。
CREATE TABLE test(a int,b int);
--声明游标。
DECLARE
TYPE type_name IS RECORD (
  a INT,
  b INT
);CURSOR c1 IS SELECT * FROM test ORDER BY a;   //声明显式游标c2 sys_refcursor;     // 声明引用游标（方式一）
abc type_name;TYPE tcur IS REF CURSOR;cursor_k tcur;     // 声明引用游标（方式二）
rec test%rowtype;
BEGIN
OPEN c2 FOR SELECT a FROM test ORDER BY a;
CLOSE c2;
OPEN c2 FOR SELECT a,b FROM test ORDER BY a;
FETCH c2 INTO abc;
CLOSE c2;
DBMS_OUTPUT.PUT_LINE('result is ' || abc.a);
DBMS_OUTPUT.PUT_LINE('result is ' || abc.b);
OPEN cursor_k FOR (SELECT * FROM test);
FETCH cursor_k INTO rec;
close cursor_k;
END;
/

使用显式游标

注意事项

在显式游标的OPEN语句中，不能使用FOR子句。
在游标的FETCH语句中，INTO子句的变量列表的列数必须和游标返回的结果集的列数一致，否则会返回错误“NOT_ENOUGH_VALUE”。
在游标的FETCH语句中，INTO子句使用记录变量存储数据时，只能使用一个记录变量，并且该记录变量的列数必须和返回结果集的列数一致，否则会返回错误“NOT_ENOUGH_VALUE”。
在游标的FETCH语句中，如果定义的赋值变量的数据类型和返回结果集中对应列的数据类型不同，会发生强制数据类型转换，如果数据类型转换失败，会返回数据转换错误。
显式游标被打开后，建议在使用游标结束后显式地关闭该游标。如果一个游标没有显式关闭，那么在退出匿名块或者存储过程执行时，系统会自动释放该游标。

语法格式

--打开游标。
OPEN cursor_name;
--从游标中读取数据。
FETCH cursor_name INTO { variant_list | record_variant };
--关闭游标。
CLOSE cursor_name;

参数说明

course_name
要使用的游标的名称。
variant_list
变量列表，用于存放从游标中读取的数据。
record_variant
Record类型变量，用于存放从游标中读取的数据。

示例（使用游标的语句以粗体显示）

--删除已存在的同名表。
DROP TABLE IF EXISTS emp;
--创建表emp。
CREATE TABLE emp(empno int, empname varchar(50), job varchar(50), sal int);
--向表emp中插ru记录。
INSERT INTO emp VALUES(123, 'abc', 'doctor', 456);
--提交事务。
COMMIT;
--声明并使用游标。
DECLARE       
       CURSOR c_job
       IS
       SELECT * FROM emp WHERE job='doctor';
       c_row emp%rowtype;
BEGIN       OPEN c_job;
         LOOP           
           FETCH c_job INTO c_row;           
           EXIT WHEN c_job%notfound;
            DBMS_OUTPUT.PUT_LINE(c_row.empno||'-'||c_row.empname ||'-'||c_row.job||'-'||c_row.sal);
         END LOOP;       
      CLOSE c_job;END;
/

使用引用游标

注意事项

在引用游标的OPEN语句中，必须使用FOR子句。FOR子句中的SQL语句可以是SELECT语句，也可以是动态SQL语句（注意动态SQL也必须是SELECT子句，否则在执行阶段会报错）。
在游标的FETCH语句中，INTO子句的变量列表的列数必须和游标返回的结果集的列数一致，否则会返回错误“NOT_ENOUGH_VALUE”。
在游标的FETCH语句中，INTO子句使用记录变量存储数据时，只能使用一个记录变量，并且该记录变量的列数必须和返回结果集的列数一致，否则会返回错误“NOT_ENOUGH_VALUE”。
引用游标可用作SELECT语句的出参，直接返回给客户端做为结果集，也可通过系统函数DBMS_SQL.RETURN_RESULT返回给客户端做为结果集。
引用游标的OPEN语句使用FOR子句对游标赋值。

语法格式

--打开游标。
OPEN cursor_name FOR { dynamic_sql | select statement };
--从游标中读取数据。
FETCH cursor_name INTO { variant_list | record_variant };
--关闭游标。
CLOSE cursor_name;

参数说明

course_name
要使用的游标的名称。
variant_list
变量列表，用于存储从游标中读取的数据。
record_variant
Record类型变量，用于存储从游标中读取的数据。

示例（使用游标的语句以粗体显示）

DECLARE
       c2 SYS_REFCURSOR;
       abc test%rowtype;
BEGIN       OPEN c2 FOR SELECT a FROM test ORDER BY a;       CLOSE c2;       OPEN c2 FOR SELECT a,b FROM test ORDER BY a;       FETCH c2 INTO abc;       CLOSE c2;
       DBMS_OUTPUT.PUT_LINE('result is ' || abc.a);
       DBMS_OUTPUT.PUT_LINE('result is ' || abc.b);
END;
/

使用FOR循环遍历游标

注意事项

使用FOR循环遍历显式游标时，自动执行游标的OPEN、FETCH、CLOSE语句和循环语句的功能，即进入循环时，游标FOR循环语句自动打开游标，并提取第一行游标数据，当程序处理完当前所提取的数据而进入下一次循环时，游标FOR循环语句自动提取下一行数据供程序处理，当提取完结果集合中的所有数据行后结束循环，并自动关闭游标。如果在循环体内执行游标的OPEN、FETCH、CLOSE语句，会返回“INVALID_CURSOR”的错误。
使用FOR循环遍历游标时，如果关键字IN后面是游标名，则该游标名必须是显式游标或者引用游标的名称。
使用FOR循环遍历隐式游标时，关键字IN后面必须是一个SELECT语句。

语法格式

使用FOR循环遍历隐式游标。

FOR index_name IN select_statement LOOP    statement;
END LOOP;

使用FOR循环遍历显式游标。

FOR index_name IN cursor_name LOOP    statement;
END LOOP;

参数说明

index_name
循环变量的名称。
cursor_name
FOR循环要遍历的游标的名称。
select_statement
SELECT语句。指定隐式游标的遍历列表。
statement
循环体。不允许为空。

示例

使用FOR循环遍历显式游标（以粗体显示）。

DECLARE
       CURSOR c1 IS
       SELECT a,b FROM test ORDER BY a;
BEGIN
       DELETE FROM test;
       INSERT INTO test(a,b) VALUES(1,100);
       INSERT INTO test(a,b) VALUES(1,100);       FOR item IN c1         LOOP           DBMS_OUTPUT.PUT_LINE('A = ' || item.a || ',B = ' || item.b);           DBMS_OUTPUT.PUT_LINE('CURSOR%ISOPEN   is ' || c1%ISOPEN);           DBMS_OUTPUT.PUT_LINE('CURSOR%FOUND    is ' || c1%FOUND);           DBMS_OUTPUT.PUT_LINE('CURSOR%NOTFOUND is ' || c1%NOTFOUND);           DBMS_OUTPUT.PUT_LINE('CURSOR%ROWCOUNT is ' || c1%ROWCOUNT);         END LOOP;
       DBMS_OUTPUT.PUT_LINE('after for loop');
       DBMS_OUTPUT.PUT_LINE('CURSOR%ISOPEN   is ' || c1%ISOPEN);
END;
/

使用FOR循环遍历隐式游标（以粗体显示）。

BEGIN     FOR a IN(SELECT * FROM emp WHERE ename LIKE '%zhangsan%' AND sal > 9000 ORDER BY empno;)       LOOP         DBMS_OUTPUT.PUT_LINE('a is emp:'||a.empno||'name:'||a.ename||'job:'||a.job||'sal:'||a.sal);         DBMS_OUTPUT.PUT_LINE(sql%rowcount);       END LOOP;END;
/

游标属性

游标属性分为四种，分别为%ISOPEN、%FOUND、%NOTFOUND、%ROWCOUNT。

%ISOPEN属性用于确定游标是否处于打开状态。
%FOUND属性和%NOTFOUND属性用于判断最后一次FETCH抓取数据是否成功，二者逻辑相反。
%ROWCOUNT属性用于返回从游标中读取的记录条数。

显式游标属性的返回值请参见表1。隐式游标属性的返回值请参见表2。

表1 显式游标的游标属性返回值说明
属性	说明
游标名%ISOPEN	指定游标未定义或者只定义未OPEN，返回值为FALSE；指定游标定义后OPEN，返回值为TRUE。
游标名%FOUND	指定游标未OPEN，返回INVALID CURSOR报错；指定游标OPEN后，未执行，返回值为NULL；指定游标执行后，未影响到行记录，返回值为FALSE；指定游标执行后，有影响到行记录，返回值为TRUE。
游标名%NOTFOUND	指定游标未OPEN，返回INVALID CURSOR报错；指定游标OPEN后，未执行，返回值为NULL；指定游标执行后，有影响到行记录，返回值为FALSE；指定游标执行后，未影响到行记录，返回值为TRUE。
游标名%ROWCOUNT	指定游标未OPEN，返回值为INVALID CURSOR报错；指定游标OPEN后，未执行，返回值为NULL；指定游标执行后，返回值为影响到的行记录的行数。

表2 隐式游标的游标属性返回值说明
属性	说明
SQL%ISOPEN	返回值始终为FALSE。
SQL%FOUND	没有执行过SQL，返回值为NULL; 最近执行的SQL有影响到行记录，返回值为TRUE; 最近执行的SQL未影响到行记录，返回值为FALSE;
SQL%NOTFOUND	没有执行过SQL，返回值为NULL; 最近执行的SQL有影响到行记录，返回值为FALSE; 最近执行的SQL未影响到行记录，返回值为TRUE;
SQL%ROWCOUNT	没有执行过SQL，返回值为NULL; 最近执行过SQL，返回值为影响到的行记录的行数。

游标赋值与引用

功能描述
定义的游标变量在数据库中实际是一个指针。如果源游标未打开，则将源游标的值赋给目的游标后，目的游标为空游标；如果源游标已经打开，则将源游标的值赋给目的游标后，目的游标指向源游标，所有对目的游标或源游标的操作，实际为对同一游标的操作。
语法格式
```
rc1 := rc2;
```
参数说明

rc1
目的游标。
rc2
源游标。

示例（赋值语句和引用语句以粗体显示）

源游标未打开，目的游标给源游标赋值以后为空游标。

CREATE OR REPLACE PROCEDURE proc1 AS
     rc1 SYS_REFCURSOR;
     rc2 SYS_REFCURSOR;
BEGIN     rc1 := rc2;
     OPEN rc2 FOR SELECT 1 FROM SYS_DUMMY;     DBMS_SQL.RETURN_RESULT(rc1);END;
/

 EXEC proc1;

GS-00932, PL/SQL(SYS.PROC1) terminated with execute errors
[7:6] PL/SQL(DBMS_SQL.RETURN_RESULT) terminated with execute errors
[7:29] GS-00924, cursor not open

源游标已经打开，目的游标给源游标赋值后指向源游标，所有对目的游标或源游标的操作，实际为对同一游标的操作。

CREATE OR REPLACE PROCEDURE proc2 AS
     rc1 SYS_REFCURSOR;
     rc2 SYS_REFCURSOR;
BEGIN     OPEN rc2 FOR SELECT 1 FROM SYS_DUMMY;
     rc1 := rc2;
     DBMS_SQL.RETURN_RESULT(rc1);END;
/

EXEC proc2;
PL/SQL procedure successfully completed.
ResultSet #1
1
----------
1

ITPub博客

GaussDB 100 游标

游标