GaussDB(DWS) SQL进阶之PLSQL（二）-游标

1. 前言

游标是一种数据处理方法，提供了在查询结果集中进行逐行遍历浏览数据的方法，也可以将游标当做上下文区域的句柄或者指针，借助游标对指定位置的数据进行查询与处理，本章我们主要聚焦于GaussDB(DWS)存储过程中的游标使用。

2. 显式游标

显示游标主要用于处理存储过程中的查询结果集是游标常用的用法，具体分为如下几个步骤：

2.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; 

2.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;

2.3 提取游标数据

即提取游标指针指向的数据

语法图：

示例：

--在存储过程的BODY中执行
FETCH C3 INTO DEPT_NAME, DEPT_LOC;

2.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;

2.5 关闭游标

在处理完游标的数据后，应及时释放游标，以便释放游标所占用系统资源，游标关闭后工作区将变成无效，不能再使用FETCH语句获取其中数据。关闭后的游标可以使用OPEN语句重新打开。

语法图：

--在存储过程的BODY中执行
CLOSE C1;--关闭游标

2.6 游标属性

我们可以通过游标的属性来了解当前游标的状态。下面将介绍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---3
section_name1---1
section_name2---2
section_name1---1
section_name2---2
section_name3---3
section_name1---1
section_name2---2
section_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=# DECLARE
postgres-# C1 SYS_REFCURSOR;
postgres-# TEMP NUMBER(4);
postgres-# BEGIN
postgres$# proc_sys_ref(C1);
postgres$# LOOP
postgres$#   FETCH C1 INTO TEMP;
postgres$#   DBMS_OUTPUT.PUT_LINE(C1%ROWCOUNT);
postgres$#   EXIT WHEN C1%NOTFOUND;
postgres$# END LOOP;
postgres$# END;
postgres$# /
1
2
3
3
ANONYMOUS BLOCK EXECUTE

3. 隐式游标

对于非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)的存储过程中游标的基本使用。

4. 总结

GuassDB(DWS)的游标使用在postgresql的基础上做了对Oracle的语法兼容，存储过程中的游标功能对于原来依赖Oracle的系统可以平滑的迁移。同时由于GuassDB(DWS)是分布式架构，和postgresql本身以及GuassDB(DWS)的单机模式上游标的行为细节上会略有不同，例如事务中的DECLARE CURSOR由于分布式和单机的实现差异导致在pg_cursors视图查询结果差异等。