存储过程

存储过程的特性

AnalyticDB PostgreSQL 7.0版的内核版本升级至PostgreSQL 12，可以较好地支持存储过程能力。通过合理地使用存储过程，您可以在业务开发中可以获得以下收益：

• 存储过程整合了一些列SQL，并分隔不同业务的SQL。这种特性使得存储过程易于维护，极大地提升数据库开发者的效率。

• 调用存储过程非常简单，数据库开发者可以高效地在不同业务场景中复用存储过程。

• 不同的存储过程可以赋予不同的用户权限，帮助提升数据库使用的安全性。

注意事项

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

语法及参数介绍

具体信息，请参见CREATE PROCEDURE。

存储过程与函数的区别

AnalyticDB PostgreSQL 6.0版的函数（Function）可以实现大部分的存储过程功能，通常建议您使用函数来实现存储过程业务，但是存储过程仍然是许多AnalyticDB PostgreSQL版用户、及PostgreSQL从业者们期待已久的功能，具体原因主要为：

• 存储过程使用CREATE PROCEDURE语法，并通过CALL调用，符合SQL语法标准，减少用户从其它支持存储过程数据库的业务迁移至AnalyticDB PostgreSQL版的工作量。

• 存储过程支持内部开启事务块，进行事务提交（Commit）或者事务回滚（Rollback），而函数无此功能，只能整体提交或回滚一个事务。

• 存储过程无类似Function的返回值，但是可以通过output参数获取返回结果。

示例

示例一

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

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

相关文档

AnalyticDB PostgreSQL助力彩数实现全文检索加工及分析