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

GaussDB DWS 2022-03-01

1259

上期我们通过学习匿名块的使用、函数创建、存储过程的创建以及基本语法，了解了SQL进阶之PLSQL-匿名块、函数和存储过程，本期我们主要聚焦于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()

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)

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

隐式游标

对于非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()

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视图查询结果差异等。

复习一下：隐式游标属性有哪些？

抽屉里好像

藏了什么东西

……

1、SQL%FOUND布尔型属性；

2、SQL%NOTFOUND布尔型属性；

3、SQL%ROWCOUNT数值型属性；

4、SQL%ISOPEN布尔型属性。

点此拉开抽屉，一探究竟

拉开抽屉丨查看答案哦

往期精彩回顾

华为云GaussDB(DWS)携手兴盛优选，一站式实现数据业务科学决策 NEW
华为云GaussDB(DWS)助招行摘得2021 IDC中国金融行业技术应用场景创新奖 HOT
数据仓库GaussDB(DWS)蝉联“2021 中国大数据·数据仓库领域最佳产品奖”
华为云GaussDB(DWS)数据仓库以2048大规模节点通过信通院评测认证
数仓性能调优必读，带你进阶为性能调优高手

gaussdb

文章转载自GaussDB DWS，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。