預存程序 - Hologres

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

使用限制

Hologres從V3.0版本起支援PL/pgSQL文法的預存程序，PL/pgSQL文法詳情請參見SQL Procedural Language。
在Hologres的預存程序中，支援多條DDL語句事務，支援多條DML混合事務，暫不支援DDL和DML混合事務，詳情請參見SQL事務能力。
預存程序不支援設定傳回值，無法作為UDF（User-defined Functions）使用。

許可權說明

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語句事務的預存程序。

建立預存程序。

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; 
$$;

調用預存程序：表a1、a2建立成功，a3、a4未建立。
```
CALL procedure_1();
```

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

建立預存程序

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;
$$;

執行預存程序

執行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的預存程序。

建立預存程序：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;
$$;

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

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

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

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

建立預存程序。

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;
$$;

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

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

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

管理預存程序

查看已建立的預存程序。

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;
$$;