AnalyticDB PostgreSQL版7.0版本支持了存储过程（SQL Stored Procedures），让您在使用AnalyticDB PostgreSQL版时能够更方便高效地开发业务，同时更好地兼容了Oracle等传统数仓的业务。本文将介绍存储过程的使用方法。

背景信息

AnalyticDB PostgreSQL版6.0版本中，函数（Function）虽然可以实现大部分存储过程功能，但是使用函数代替存储过程的方式会产生额外的工作量，且函数内部不支持事务控制，无法完全代替存储过程功能。因此AnalyticDB PostgreSQL版7.0版本新增了存储过程功能。

在7.0版本，您可以直接在存储过程中控制事务。Oracle等传统数仓的业务可以通过存储过程更方便、平滑地迁移至AnalyticDB PostgreSQL版。

注意事项

暂不支持在DMS上使用存储过程，如需使用存储过程，建议您在客户端工具psql上进行操作。

语法及参数介绍

具体信息，请参见CREATE PROCEDURE。

示例

示例一

本示例中将创建一个存储过程，对其中一个事务进行提交，另外一个事务进行回滚。

  1. 创建存储过程，并在存储过程中控制不同事务。
    CREATE PROCEDURE proc() LANGUAGE plpgsql
AS $$
  BEGIN
    CREATE TABLE table_a (id int);
    INSERT INTO table_a VALUES(1);
    COMMIT;

    CREATE TABLE table_b (cid int);
    INSERT INTO table_b VALUES(1);
    ROLLBACK;
  END;
$$;
  2. 调用存储过程。
     CALL proc();
  3. 查看存储过程执行结果。

    在psql中执行\d查看当前库中的所有表，返回信息如下： 

                    List of relations
 Schema |  Name   | Type  |   Owner    | Storage
--------+---------+-------+------------+---------
 public | table_a | table | adbpgadmin | heap
(1 row)

    查看表table_a：

    SELECT * FROM table_a;

    返回信息如下：

     id
----
  1
(1 row)

    通过上述存储过程执行结果可以看到表table_a相关事务提交，成功建表并写入数据；而表table_b相关的事务在存储过程中被回滚。

示例二

本示例将分别在存储过程和函数中执行一个循环任务并回收表的存储空间，以体现存储过程和函数在提交事物方面的区别。

  • 存储过程提供了事务提交能力，您可以在存储过程执行过程中对事务进行提交。以下示例，存储过程可以实现在循环任务期间及时回收表空间：
    CREATE PROCEDURE run_procedure()  LANGUAGE plpgsql
AS $$
 BEGIN
    FOR i in 1..10 LOOP
        INSERT INTO t VALUES(i);-- 在表t上执行一系列任务，示例中为INSERT任务。
        TRUNCATE t;
        COMMIT;  -- 在循环内提交TRUNCATE任务，回收表t的物理存储空间。
    END LOOP;
  END;
$$;
  • 函数无法在执行过程中进行事务提交，仅能在函数执行完成后作为一个事务一次性提交。以下示例，函数无法在循环任务期间回收表空间：
    CREATE FUNCTION run_function() RETURNS void LANGUAGE plpgsql
AS $$
 BEGIN
    FOR i in 1..10 LOOP
        INSERT INTO t VALUES(i);-- 在表t上执行一系列任务，示例中为INSERT任务。
        TRUNCATE t;
        -- COMMIT; 不支持事务提交。
    END LOOP;
  END;
$$;