PostgreSQL 11 新特性之存儲過程

文章目錄

PostgreSQL 11 增加了一個新的模式對象：存儲過程（Stored Procedure）。存儲過程和函數（Function）類似，不過它沒有傳回值。

存儲過程最大的優勢就是能夠支援事務控制，也就是可以在定義中使用

COMMIT

或者

ROLLBACK

語句。

使用

CREATE\ALTER\DROP PROCEDURE

指令建立\修改\删除存儲過程，使用

CALL

指令調用存儲過程。支援存儲過程的伺服器端程式設計語言包括：PL/pgSQL、PL/Perl、PL/Python、PL/Tcl 以及 SPI 。

先來看一個簡單示例：

CREATE TABLE tbl(id int, name text);

CREATE PROCEDURE proc1(pid integer, pname text)
LANGUAGE sql
AS $$
INSERT INTO tbl(id, name) VALUES (pid, pname);
$$;
           

使用

CALL

語句調用存儲過程：

CALL proc1(1, 'the first value');
CALL proc1(pname=>'the second value', pid=>2);
           

檢視一下結果：

SELECT * FROM tbl;
 id |       name       
----+------------------
  1 | the first value
  2 | the second value
(2 rows)
           

存儲過程增加了對事務的支援：

CREATE OR REPLACE PROCEDURE proc2()
 LANGUAGE plpgsql
AS $$
BEGIN
 FOR i IN 0..9 LOOP
  INSERT INTO tbl(id, name) VALUES (i, 'value: '|| i);
  IF i % 2 = 0 THEN
   COMMIT;
  ELSE
   ROLLBACK;
  END IF;
 END LOOP;
END;
$$ ;
           

調用存儲過程 proc2，即使沒有參數，仍然需要加上括号（()）：

檢視結果：

SELECT * FROM tbl;
 id |       name       
----+------------------
  1 | the first value
  2 | the second value
  0 | value: 0
  2 | value: 2
  4 | value: 4
  6 | value: 6
  8 | value: 8
(7 rows)
           

隻有id 為偶數的記錄成功插入表中，奇數都被

ROLLBACK

語句回退了。

在 psql 中，使用 \df 指令檢視存儲過程：

\df
                         List of functions
 Schema | Name  | Result data type |   Argument data types   | Type 
--------+-------+------------------+-------------------------+------
 public | add   | integer          | integer, integer        | func
 public | proc1 |                  | pid integer, pname text | proc
 public | proc2 |                  |                         | proc
(3 rows)
           

對于存儲過程，Type 的值為 proc。使用 \sf 指令可以檢視存儲過程的定義：

\sf proc1
CREATE OR REPLACE PROCEDURE public.proc1(pid integer, pname text)
 LANGUAGE sql
AS $procedure$
INSERT INTO tbl(id, name) VALUES (pid, pname);
$procedure$
           

PROCDEURE 目前還不支援自治事務（autonomous transaction）。

相關參考文檔：

CREATE PROCEDURE

ALTER PROCEDURE

DROP PROCEDURE

CALL