幫助中心
首頁 Hologres 開發參考 SQL參考 預存程序

預存程序

更新時間：
Copy as MD
產品

預存程序（PROCEDURE）是由一組先行編譯的SQL語句組成的集合，可以在資料庫中進行儲存並反覆調用。本文為您介紹在Hologres中使用預存程序的方法。

使用限制

  • Hologres 從 Hologres V3.0 版本起支援 PL/pgSQL 文法的預存程序，PL/pgSQL文法詳情請參見SQL Procedural Language。

  • 在Hologres的預存程序中，支援多條DDL語句事務，支援多條DML混合事務，暫不支援DDL和DML混合事務，詳情請參見事務。

  • 預存程序不支援設定傳回值，無法作為UDF（User-defined Functions）使用。

  • 預存程序中不支援RETURN QUERY返回結果集，如需返回資料請使用視圖或暫存資料表。

  • 預存程序中不支援CURSOR操作。

  • 預存程序中不支援嵌套定義其他預存程序。

  • 預存程序中不支援EXECUTE ... INTO將動態SQL結果賦值給變數，需使用SELECT ... INTO替代。

許可權說明

  • CREATE PROCEDURE需要使用者有Database中的Create許可權，與建立表許可權一致，詳情請參見SQL-CREATE PROCEDURE。

  • CREATE OR REPLACE需要使用者同時擁有Database的Create許可權和目標預存程序的OWNER許可權，詳見SQL-CREATE PROCEDURE。

  • 執行預存程序需要使用者有預存程序的EXECUTE許可權，詳見SQL-CALL。

命令參考

Hologres支援的預存程序文法相容PostgreSQL，具體文法如下：

建立預存程序

CREATE [ OR REPLACE ] PROCEDURE
    <procedure_name> ([<argname> <argtype>])
LANGUAGE 'plpgsql'
AS <definition>;

參數

說明

procedure_name

預存程序名稱。

argname

參數名稱。參數可選，取決於預存程序設計。

argtype

參數類型。

definition

定義預存程序的具體實現，可以是一個SQL語句或者代碼塊。

更多參數詳情請參見SQL-CREATE PROCEDURE。

修改預存程序

ALTER PROCEDURE <procedure_name> ([<argname> <argtype>])
    OWNER TO <new_owner> | CURRENT_USER | SESSION_USER;

參數

說明

new_owner

新使用者名稱。

CURRENT_USER

目前使用者。

SESSION_USER

會話使用者。

參數詳情請參見SQL-ALTER PROCEDURE。

刪除預存程序

DROP PROCEDURE [ IF EXISTS ] <procedure_name> ([<argname> <argtype>]);

參數詳情請參見SQL-DROP PROCEDURE。

執行預存程序

CALL <procedure_name> ([<argument>]);

參數

說明

argument

預存程序所需的參數。參數可選，取決於預存程序設計。

參數詳情請參見SQL-CALL。

使用樣本

  • 樣本1：含多條DDL語句事務的預存程序。

    1. 建立預存程序。

      CREATE OR REPLACE PROCEDURE procedure_1()
LANGUAGE 'plpgsql'
AS $$
BEGIN
    --- TXN1 --- 
    CREATE TABLE a1(key int);
    CREATE TABLE a2(key int);
    COMMIT; 

    --- TXN2 ---
    CREATE TABLE a3(key int);
    CREATE TABLE a4(key int);
    ROLLBACK;
END; 
$$;

    2. 調用預存程序：表a1、a2建立成功，a3、a4未建立。

      CALL procedure_1();

  • 樣本2：含多條DML語句事務的預存程序。

    1. 建立預存程序

      CREATE OR REPLACE PROCEDURE procedure_2()
LANGUAGE 'plpgsql'
AS $$
BEGIN
    INSERT INTO a1 VALUES(1);
    INSERT INTO a2 VALUES(2);
    ROLLBACK;
END;
$$;

CREATE OR REPLACE PROCEDURE procedure_3()
LANGUAGE 'plpgsql'
AS $$
BEGIN
    INSERT INTO a1 VALUES(1);
    INSERT INTO a2 VALUES(2);
END;
$$;

    2. 執行預存程序

      • 執行procedure_2：支援ROLLBACK，資料未成功寫入

        -- 開啟DML事務功能
SET hg_experimental_enable_transaction = ON;

-- 執行預存程序
CALL procedure_2();

      • 執行procedure_3：資料成功寫入

        -- 開啟DML事務功能
SET hg_experimental_enable_transaction = ON;

-- 執行預存程序
CALL procedure_3();

  • 樣本3：同時含DDL和DML的預存程序。

    1. 建立預存程序：Hologres暫不支援DDL和DML混合事務，因此在預存程序中，需要對DDL和DML分別執行COMMIT。

      CREATE OR REPLACE PROCEDURE procedure_4()
LANGUAGE 'plpgsql'
AS $$
BEGIN
    INSERT INTO a1 VALUES(1);
    COMMIT;	
    CREATE TABLE bb(key int);
    COMMIT;	
    INSERT INTO a1 VALUES(2);
    INSERT INTO bb VALUES(1);    
    COMMIT;	
END;
$$;

    2. 執行預存程序：建表和資料寫入均成功。

      -- 開啟DML事務功能
SET hg_experimental_enable_transaction = ON;

-- 執行預存程序
CALL procedure_4();

  • 樣本4：含常見用法的預存程序包括定義入參、定義中間變數、定義迴圈、定義IF條件、定義EXCEPTION等。

    1. 建立預存程序。

      CREATE OR REPLACE PROCEDURE procedure_5(input text)
LANGUAGE 'plpgsql'
AS $$
-- 定義中間變數
DECLARE
sql1 text;
BEGIN
    -- 向入參的表裡寫入一行資料
    EXECUTE 'insert into ' || input || ' values(1);';
    COMMIT;

    -- 建a3表
    CREATE TABLE a3(key int);
    COMMIT;

    -- 使用中間變數，向a3表寫入一條資料
    sql1 = 'insert into a3 values(1);';
    EXECUTE sql1;

    -- 定義FOR迴圈
    FOR i IN 1..10 LOOP
        BEGIN
            -- i=1已存在表中，所以只打一條日誌
            IF i IN (SELECT KEY FROM a3) THEN
                RAISE NOTICE 'Data already exists.';
            -- 其他數字不存在表中，所以嘗試寫入，同時RAISE EXCEPTION，而後COMMIT
            ELSE
                INSERT INTO a3 VALUES(i);
                RAISE EXCEPTION 'HG_PLPGSQL_NEED_RETRY';
                COMMIT; 
            END IF;
        -- 針對RAISE的EXCEPTION，打一條日誌
        EXCEPTION 
            WHEN OTHERS THEN
                RAISE NOTICE 'Catch error.';
        END;
    END LOOP;

END;
$$;

    2. 執行預存程序：a3表中寫入資料1、其餘資料不寫入，相關日誌全部列印。

      -- 開啟DML事務功能
SET hg_experimental_enable_transaction = ON;

-- 執行預存程序
CALL procedure_5('a1');

  • 樣本5：使用CASE WHEN動態計算變數並在多條INSERT語句中複用。

    1. 建立目標表和預存程序。預存程序中使用DECLARE聲明變數，通過CASE WHEN條件判斷動態計算時間參數，並在多條INSERT語句中複用該變數。

      -- 建立目標表
CREATE TABLE test_dynamic_param (
    event_name TEXT,
    start_time TIMESTAMPTZ,
    end_time TIMESTAMPTZ
);

-- 建立預存程序：使用CASE WHEN動態計算時間參數
CREATE OR REPLACE PROCEDURE procedure_dyn_time()
LANGUAGE 'plpgsql'
AS $$
DECLARE
    new_end_time TIMESTAMPTZ;
    base_start_time TIMESTAMPTZ := '2024-01-01 00:00:00+08'::TIMESTAMPTZ;
BEGIN
    -- 使用CASE WHEN動態計算end_time
    new_end_time := CASE
        WHEN NOW() > base_start_time + INTERVAL '5 min'
        THEN base_start_time + INTERVAL '1 hour'
        ELSE base_start_time + INTERVAL '5 min'
    END;

    -- 在多條INSERT語句中複用該變數
    INSERT INTO test_dynamic_param VALUES('event1', base_start_time, new_end_time);
    INSERT INTO test_dynamic_param VALUES('event2', base_start_time, new_end_time);
END;
$$;

    2. 執行預存程序並驗證結果。兩條記錄的end_time值一致，均基於CASE WHEN計算結果。

      -- 開啟DML事務功能
SET hg_experimental_enable_transaction = ON;

-- 執行預存程序
CALL procedure_dyn_time();

-- 驗證：兩條記錄的end_time值一致
SELECT * FROM test_dynamic_param;

管理預存程序

  • 查看已建立的預存程序。

    SELECT
    p.proname AS procedure_name,
    pg_get_function_identity_arguments(p.oid) AS argument_types,
    REPLACE(pg_get_functiondef(p.oid),'$procedure$','$$') AS procedure_detail,
    n.nspname AS schema_name,
    r.rolname AS owner_name,
    d.description AS description
FROM
    pg_proc p
    INNER JOIN pg_namespace n ON p.pronamespace = n.oid
    INNER JOIN pg_roles r ON p.proowner = r.oid
    LEFT JOIN pg_description d ON p.oid = d.objoid
WHERE
    r.rolname != 'holo_admin'
    AND p.prokind = 'p'
ORDER BY
    n.nspname,
    p.proname;

  • 查看預存程序定義。

    SELECT pg_get_functiondef('<procedure_name>'::regproc);

常見問題

由於Hologres是分布式系統，其中接入節點FE也是分布式的。當表發生DDL變更時，不同接入節點之間需要即時同步中繼資料，如果中繼資料未同步完成，DDL變更可能會失敗。針對上述情境，Hologres在大部分情況下會自動重試，無需手動重複提交DDL變更。但在預存程序中，無法支援自動重試，上述情境會直接返回錯誤資訊為“HG_PLPGSQL_NEED_RETRY”的報錯。

針對高頻DDL變更的表，建議在預存程序中手動定義重試邏輯，以免預存程序頻繁報錯。重試邏輯如下：

CREATE OR REPLACE PROCEDURE procedure_6()
LANGUAGE 'plpgsql'
AS $$
BEGIN
    WHILE TRUE LOOP
        BEGIN
            -- 嘗試執行DDL語句，如果成功，則退出迴圈
            CREATE TABLE a3(key int);
            COMMIT;
            EXIT;
        EXCEPTION
            -- 如果遇到HG_PLPGSQL_NEED_RETRY報錯，則列印日誌，並自動重試
            WHEN HG_PLPGSQL_NEED_RETRY THEN 
                RAISE NOTICE 'DDL need retry';
        END;
    END LOOP;
END;
$$;
上一篇：Extension擴充下一篇：自訂函數UDF（Remote UDF）