本文分享自华为云社区《GaussDB(DWS) SQL进阶之PLSQL（二）-游标》，作者： xxxsql123 。
前言
游标是一种数据处理方法，提供了在查询结果集中进行逐行遍历浏览数据的方法，也可以将游标当做上下文区域的句柄或者指针，借助游标对指定位置的数据进行查询与处理，本章我们主要聚焦于 GaussDB(DWS)存储过程中的游标使用。
显式游标
显示游标主要用于处理存储过程中的查询结果集是游标常用的用法，具体分为如下几个步骤：
Step 1 定义游标：
静态游标定义：
即定义一个游标名以及与其相对应的 SELECT 语句
语法图：
示例如下：
--在存储过程的DECLARE中声明游标定义 CURSOR C1 IS SELECT section_name, place_id FROM hr.sections WHERE section_id <= 50; CURSOR C2(sect_id INTEGER) IS SELECT section_name, place_id FROM hr.sections WHERE section_id <= sect_id;
复制代码
动态游标定义：
即 ref 游标，可以通过静态的 SQL 语句在合适的时候动态的打开游标。先定义 ref 游标类型，后面通过 open for 动态绑定 SELECT 语句。
语法图：
示例如下：
--在存储过程的DECLARE中声明游标定义TYPE CURSOR_TYPE IS REF CURSOR;
复制代码
同时 GaussDB(DWS)做了 Oracle 兼容，支持 sys_refcursor 动态游标类型，函数或存储过程可以通过 sys_refcursor 参数传入或传出游标结果集合，函数也可以通过返回 sys_refcursor 来返回游标结果集合。
语法图：
示例如下：
--在存储过程的DECLARE中声明游标定义C1 SYS_REFCURSOR;
复制代码
Step 2 打开游标：
静态游标打开：
即执行游标对应的 SELECT 语句，将结果集放入工作区，将游标的指针指向工作区的起始位置。
语法图：
示例如下：
--在存储过程的BODY中打开游标OPEN C1;OPEN C2(10);
复制代码
动态游标打开：
通过 OPEN FOR 语句打开动态游标，通过 USING 对 SELECT 语句进行动态绑定。
语法图：
示例如下：
--在存储过程的BODY中打开游标SQL_STR := 'SELECT section_name, place_id FROM hr.sections WHERE section_id <= :DEPT_NO;';OPEN C3 FOR SQL_STR USING 50;
复制代码
Step 3 提取游标数据：
即提取游标指针指向的数据
语法图：
示例：
--在存储过程的BODY中执行FETCH C3 INTO DEPT_NAME, DEPT_LOC;
复制代码
Step 4 循环处理游标数据：
提取数据后可以基于存储过程的语句灵活发挥
例如，给工资低于 3000 的员工增加 500 块钱工资
--在存储过程的BODY中执行 LOOP FETCH C INTO V_EMPNO, V_SAL; EXIT WHEN C%NOTFOUND; IF V_SAL<=3000 THEN UPDATE hr.staffs_t1 SET salary =salary + 500 WHERE staff_id = V_EMPNO; END IF; END LOOP;
复制代码
Step 5 关闭游标：
在处理完游标的数据后，应及时释放游标，以便释放游标所占用系统资源，游标关闭后工作区将变成无效，不能再使用 FETCH 语句获取其中数据。关闭后的游标可以使用 OPEN 语句重新打开。
语法图：
--在存储过程的BODY中执行CLOSE C1;--关闭游标
复制代码
游标属性
我们可以通过游标的属性来了解当前游标的状态。下面将介绍 4 中游标属性：
※ %FOUND 布尔型属性：当最近一次读记录时成功返回，则值为 TRUE。
※ %NOTFOUND 布尔型属性：与 %FOUND 相反。
※ %ISOPEN 布尔型属性：当游标已打开时返回 TRUE。
※ %ROWCOUNT 数值型属性：返回已从游标中读取的记录数。
示例：
OPEN C1;--打开游标LOOP --通过游标取值 FETCH C1 INTO DEPT_NAME, DEPT_LOC; EXIT WHEN C1%NOTFOUND; DBMS_OUTPUT.PUT_LINE(DEPT_NAME||'---'||DEPT_LOC);END LOOP;CLOSE C1;--关闭游标
复制代码
接下来我们将结合前面所学习的知识，在存储过程运用显示游标。
数据准备：
CREATE SCHEMA hr;SET CURRENT_SCHEMA = 'hr';DROP TABLE IF EXISTS sections;CREATE TABLE sections(section_id INT, section_name VARCHAR(100), place_id NUMBER(4)) DISTRIBUTE BY HASH(section_id);INSERT INTO sections VALUES (1, 'section_name1', 1),(2, 'section_name2', 2),(3, 'section_name3', 3);
复制代码
显示游标使用示例：
--游标参数的传递方法。CREATE OR REPLACE PROCEDURE cursor_proc1()AS DECLARE DEPT_NAME VARCHAR(100); DEPT_LOC NUMBER(4); --定义游标 CURSOR C1 IS SELECT section_name, place_id FROM hr.sections WHERE section_id <= 50; CURSOR C2(sect_id INTEGER) IS SELECT section_name, place_id FROM hr.sections WHERE section_id <= sect_id; TYPE CURSOR_TYPE IS REF CURSOR; C3 CURSOR_TYPE; SQL_STR VARCHAR(100);BEGIN OPEN C1;--打开游标 LOOP --通过游标取值 FETCH C1 INTO DEPT_NAME, DEPT_LOC; EXIT WHEN C1%NOTFOUND; DBMS_OUTPUT.PUT_LINE(DEPT_NAME||'---'||DEPT_LOC); END LOOP; CLOSE C1;--关闭游标
OPEN C2(10); LOOP FETCH C2 INTO DEPT_NAME, DEPT_LOC; EXIT WHEN C2%NOTFOUND; DBMS_OUTPUT.PUT_LINE(DEPT_NAME||'---'||DEPT_LOC); END LOOP; CLOSE C2; SQL_STR := 'SELECT section_name, place_id FROM hr.sections WHERE section_id <= :DEPT_NO;'; OPEN C3 FOR SQL_STR USING 50; LOOP FETCH C3 INTO DEPT_NAME, DEPT_LOC; EXIT WHEN C3%NOTFOUND; DBMS_OUTPUT.PUT_LINE(DEPT_NAME||'---'||DEPT_LOC); END LOOP; CLOSE C3;END;/
CALL cursor_proc1();
DROP PROCEDURE cursor_proc1;
复制代码
执行结果：
postgres=# CALL cursor_proc1();section_name3---3section_name1---1section_name2---2section_name1---1section_name2---2section_name3---3section_name1---1section_name2---2section_name3---3 cursor_proc1--------------
(1 row)
复制代码
SYS_REFCURSOR 游标示例：
--SYS_REFCURSOR类型做为函数参数CREATE OR REPLACE PROCEDURE proc_sys_ref(O OUT SYS_REFCURSOR)IS C1 SYS_REFCURSOR; BEGIN OPEN C1 FOR SELECT section_ID FROM HR.sections ORDER BY section_ID; O := C1; END; /
DECLARE C1 SYS_REFCURSOR; TEMP NUMBER(4); BEGIN proc_sys_ref(C1); LOOP FETCH C1 INTO TEMP; DBMS_OUTPUT.PUT_LINE(C1%ROWCOUNT); EXIT WHEN C1%NOTFOUND; END LOOP; END; /
--删除存储过程DROP PROCEDURE proc_sys_ref;
复制代码
执行结果：
postgres=# DECLAREpostgres-# C1 SYS_REFCURSOR;postgres-# TEMP NUMBER(4);postgres-# BEGINpostgres$# proc_sys_ref(C1);postgres$# LOOPpostgres$# FETCH C1 INTO TEMP;postgres$# DBMS_OUTPUT.PUT_LINE(C1%ROWCOUNT);postgres$# EXIT WHEN C1%NOTFOUND;postgres$# END LOOP;postgres$# END;postgres$# /1233ANONYMOUS BLOCK EXECUTE
复制代码
隐式游标
对于非 SELECT 语句，例如 UPDATE，DELETE 操作，系统会自动的未这些操作设置游标，这些有系统隐含创建的游标即隐式游标。隐式游标的定义，打开，取值，关闭操作均有系统自动的完成，无需用户进行处理，用户只能通过隐式游标的相关属性完成相应的操作。
隐式游标属性：
※ SQL%FOUND 布尔型属性：当最近一次读记录时成功返回，则值为 TRUE。
※ SQL%NOTFOUND 布尔型属性：与 %FOUND 相反。
※ SQL%ROWCOUNT 数值型属性：返回已从游标中读取得记录数。
※ SQL%ISOPEN 布尔型属性：取值总是 FALSE。SQL 语句执行完毕立即关闭隐式游标。
隐式游标示例如下：
--删除EMP表中某部门的所有员工，如果该部门中已没有员工，则在DEPT表中删除该部门。CREATE TABLE hr.staffs_t1 AS TABLE hr.staffs;CREATE TABLE hr.sections_t1 AS TABLE hr.sections;
CREATE OR REPLACE PROCEDURE proc_cursor3() AS DECLARE V_DEPTNO NUMBER(4) := 100; BEGIN DELETE FROM hr.staffs WHERE section_ID = V_DEPTNO; --根据游标状态做进一步处理 IF SQL%NOTFOUND THEN DELETE FROM hr.sections_t1 WHERE section_ID = V_DEPTNO; END IF; END;/
CALL proc_cursor3();
--删除存储过程和临时表DROP PROCEDURE proc_cursor3;DROP TABLE hr.staffs_t1;DROP TABLE hr.sections_t1;
复制代码
以上就是在 GuassDB(DWS)的存储过程中游标的基本使用。
总结
GuassDB(DWS)的游标使用在 postgresql 的基础上做了对 Oracle 的语法兼容，存储过程中的游标功能对于原来依赖 Oracle 的系统可以平滑的迁移。同时由于 GuassDB(DWS)是分布式架构，和 postgresql 本身以及 GuassDB(DWS)的单机模式上游标的行为细节上会略有不同，例如事务中的 DECLARE CURSOR 由于分布式和单机的实现差异导致在 pg_cursors 视图查询结果差异等。
接下来的时间里将会像大家逐步介绍存储过程的自定义用户类型等章节，敬请期待~
想了解 GuassDB(DWS)更多信息，欢迎微信搜索“GaussDB DWS”关注微信公众号，和您分享最新最全的 PB 级数仓黑科技，后台还可获取众多学习资料哦~
点击关注，第一时间了解华为云新鲜技术~
评论