存储过程_实时数仓 Hologres(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 function_name,
    p.prosrc AS function_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;
$$;