實例詳解資料庫的游標管理

本文分享自華為雲社區《GaussDB資料庫SQL系列-游標管理》，作者：酷哥。

一、前言

在資料庫中，游標（cursor）是一種非常重要的工具，用於在資料庫查詢結果集中進行定位和操作。游標提供了一種在多行數據結果集中逐行處理每一行的機制，允許開發人員對每一行的數據進行操作，如檢索、過濾、修改等。本文將結合GaussDB資料庫，簡單的給大家做一介紹。

二、概述（GaussDB）

1、游標概述

在GaussDB資料庫中，為了處理SQL語句，存儲過程進程分配一段記憶體區域來保存上下文聯繫。游標是指向上下文區域的句柄或指針。藉助游標，存儲過程可以控制上下文區域的變化。

2、游標的使用分類

游標的使用分為顯式游標和隱式游標。對於不同的SQL語句，游標的使用情況不同。

• 顯式游標：顯式游標主要用於對查詢語句的處理，尤其是在查詢結果為多條記錄的情況下。

• 隱式游標：對於非查詢語句，如修改、刪除操作，則由系統自動地為這些操作設置游標並創建其工作區，這些由系統隱含創建的游標稱為隱式游標，隱式游標的名稱為SQL，這是由系統定義的。

• 游標迴圈：游標在WHILE語句、LOOP語句中的使用稱為游標迴圈，一般這種迴圈都需要使用OPEN、FETCH和CLOSE語句。

三、GaussDB中的顯式游標（示例）

1、顯式游標的使用與操作步驟

• 第一步，定義（聲明）游標：定義一個游標名，以及與其相對應的SELECT語句。

• 第二步，打開游標：執行游標所對應的SELECT語句，將其查詢結果放入工作區，並且指針指向工作區的首部，標識游標結果集合。如果游標查詢語句中帶有FOR UPDATE選項，OPEN語句還將鎖定資料庫表中游標結果集合對應的數據行。

• 第三步，提取游標數據：檢索結果集合中的數據行，放入指定的輸出變數中。

• 第四步，對該記錄進行處理。

• 第五步，繼續處理，直到活動集合中沒有記錄。

• 第六步，關閉游標：當提取和處理完游標結果集合數據後，應及時關閉游標，以釋放該游標所占用的系統資源，並使該游標的工作區變成無效，不能再使用FETCH語句獲取其中數據。關閉後的游標可以使用OPEN語句重新打開。

2、顯式游標示例

--給工資大於等於20000的員工降薪500。

--複製一張測試表

CREATE TABLE company2 AS TABLE company1;
CREATE OR REPLACE PROCEDURE cursor_p()

AS

DECLARE

v_id NUMBER(6);

v_salary NUMBER(8,2);

CURSOR c1 IS SELECT id, salary FROM company2;

BEGIN

OPEN c1;

LOOP

FETCH c1 INTO v_id, v_salary;

EXIT WHEN c1%NOTFOUND;

IF v_salary>=20000 THEN

UPDATE company2 SET salary =salary - 500 WHERE id = v_id;

END IF;

END LOOP;

CLOSE c1;

END;

/
--執行存儲過程

CALL cursor_p();
--查看，比對結果變化

SELECT t2.*,t1.salary as "降薪前" FROM company2 t2 LEFT JOIN company1 t1 ON t2.id=t1.id ORDER BY ID ASC;

游標屬性說明：

%NOTFOUND是游標的屬性之一，用於控製程序流程或者瞭解程式的狀態。此處的意思是，當最近的DML（數據操作語言）操作（如INSERT，UPDATE，DELETE等）沒有影響任何行時，該屬性為真。也就是說，如果對錶執行的操作沒有找到任何匹配的行，那麼這個屬性就會為真，'EXIT WHEN c1%NOTFOUND;' 就會執行。

執行結果：

四、GaussDB中的隱式游標（示例）

1、隱式游標簡介

對於隱式游標的操作，如定義、打開、取值及關閉操作，都由系統自動地完成，無需用戶進行處理。用戶只能通過隱式游標的相關屬性，來完成相應的操作。在隱式游標的工作區中，所存放的數據是最新處理的一條SQL語句所包含的數據，與用戶自定義的顯式游標無關。

2、隱式游標示例

--創建臨時表，刪除已離職的員工Allen，如果Allen已經不存在，則新增一條新員工信息（重名）

CREATE TABLE company3 AS TABLE company1;
CREATE OR REPLACE PROCEDURE cursor_p1()

AS

DECLARE

v_name VARCHAR(10) := 'Allen';

BEGIN

     DELETE FROM company3 WHERE name = v_name;

--根據游標狀態做進一步處理

IF SQL%NOTFOUND THEN

INSERT INTO company3 VALUES(4,v_name,24,'China',30000);

END IF;

END;

/
--執行

CALL cursor_p1();
--查看，比對執行結果

SELECT *, 'company1' as "company1" FROM company1 WHERE NAME ='Allen'

union all

SELECT *, 'company3' as "company3" FROM company3 WHERE NAME ='Allen'

游標屬性說明：

SQL%NOTFOUND 是GaussDB資料庫中的一個屬性，用於檢查最近的 SQL 語句是否對資料庫沒有任何影響。如果 SQL 語句沒有找到任何匹配的記錄，那麼這個屬性就為真。所以，本段SQL中的IF SQL%NOTFOUND THEN 這一行代碼的意思是：如果最近的 SQL 語句沒有找到任何匹配的記錄，那麼就執行後續的代碼。通常，這個語句用於處理 SQL 查詢可能找不到數據的情況。

執行結果：

五、小結

在GaussDB資料庫中，游標主要用於執行複雜的查詢語句、處理多行數據以及進行數據操作等場景。游標的使用可以提高程式的靈活性和可維護性，並且可以減少不必要的記憶體消耗。使用游標，開發人員可以靈活地控制查詢結果集的遍歷順序和處理方式，以滿足不同的業務需求。

點擊關註，第一時間瞭解華為雲新鮮技術~