帶你聚焦GaussDB(DWS)儲存時遊標使用

語言: CN / TW / HK
摘要:遊標是一種資料處理方法,提供了在查詢結果集中進行逐行遍歷瀏覽資料的方法,也可以將遊標當做上下文區域的控制代碼或者指標,藉助遊標對指定位置的資料進行查詢與處理。

本文分享自華為雲社群《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---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

隱式遊標

對於非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級數倉黑科技,後臺還可獲取眾多學習資料哦~

 

點選關注,第一時間瞭解華為雲新鮮技術~