PostgreSQL最终获得存储过程

 

介绍

PostgreSQL自古以来就一直支持用户定义函数。函数使我们能够重复存储和执行过程代码。函数的缺点是它缺乏对函数主体中Transaction的支持，整个函数主体在隐式事务中执行。

问题

函数中缺少事务支持意味着如果必须在事务中执行多组语句，则必须为每个语句编写一个函数。

在将RDBMS（例如Oracle或SQL Server）迁移到PostgreSQL 10.x或更早版本时，必须将存储过程重新编写为PostgreSQL函数。如果存储过程在其中使用事务逻辑，则必须将它们重写为多个PostgreSQL函数，每个事务一个。这是一项非常困难且容易出错的任务，因为它涉及手动重写代码和涵盖所有用例的全面单元测试。很难确保PostgreSQL函数所做的事情与重写它们的存储过程完全相同。

存储过程

PostgreSQL 11.5。发行于2018年10月18号。包含PROCEDURE作为新的Schema对象。使用CREATE PROCEDURE语句创建存储过程。您也可以使用CREATE OR REPLACE类似于“函数”的语法。您可以启动多个新事务，在存储过程中提交或回滚它们。

重载的存储过程

存储过程可以像函数一样重载，即您可以使用不同的参数创建多个存储过程。调用存储过程时，过程解析取决于参数的最佳匹配，就像函数一样。

嵌套存储过程

存储过程也可以嵌套，即您可以从另一个存储过程中调用存储过程，类似于函数。

存储过程与函数

尽管存储过程和函数看起来非常相似，但基本差异不大：

特征

储存程序

功能

在表达式中使用

没有

是

返回值

没有

是

返回值作为OUT参数

是

没有

返回一个结果集

是

是（作为表函数）

返回多个结果集

是

没有

为什么要存储过程？

1. 存储过程允许通过多个COMMIT和ROLLBACK语句进行事务控制。
2. 由于可以按原样重写存储过程而无需将它们重写为多个函数，因此使从Oracle和SQL Server数据库到PostgreSQL的迁移非常容易。
3. CREATE PROCEDURE的语法非常相似，CREATE FUNCTION学习曲线非常狭窄。

创建一个新的存储过程

要创建新的存储过程，请使用以下CREATE PROCEDURE 语句。它与CREATE FUNCTION语句非常相似，只不过它没有RETURNS 子句。这是一个例子。

CREATE OR REPLACE PROCEDURE TestProcedure() 
 LANGUAGE plpgsql 
 AS $$
 DECLARE
 BEGIN
     CREATE TABLE table1 (id int, name text);
     INSERT INTO  table1 VALUES (1, "Andy");
     COMMIT;
     CREATE TABLE table2 (id int, name text);
     INSERT INTO  table2 VALUES (1, "Brian");
     ROLLBACK;
 END $$;

有关CREATE PROCEDURE语句的完整语法，请查看此文档页面。

执行存储过程

您可以使用以下CALL 语句执行存储过程

CALL TestProcedure();

结论

存储过程支持一直是PostgreSQL中最受期待的功能之一，特别是考虑到其他数据库平台使用存储过程已有很长时间了。此新功能的最重要优点是它将加快其他数据库到PostgreSQL的迁移。