PostgreSQL 11 新特性之存儲(chǔ)過(guò)程

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫(kù)管理與開發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫(kù)架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net

文章目錄

PostgreSQL 11 增加了一個(gè)新的模式對(duì)象：存儲(chǔ)過(guò)程（Stored Procedure）。存儲(chǔ)過(guò)程和函數(shù)（Function）類似，不過(guò)它沒(méi)有返回值。

存儲(chǔ)過(guò)程最大的優(yōu)勢(shì)就是能夠支持事務(wù)控制，也就是可以在定義中使用 COMMIT 或者 ROLLBACK 語(yǔ)句。

使用 CREATE\ALTER\DROP PROCEDURE 命令創(chuàng)建\修改\刪除存儲(chǔ)過(guò)程，使用 CALL 命令調(diào)用存儲(chǔ)過(guò)程。支持存儲(chǔ)過(guò)程的服務(wù)器端編程語(yǔ)言包括：PL/pgSQL、PL/Perl、PL/Python、PL/Tcl 以及 SPI 。

先來(lái)看一個(gè)簡(jiǎn)單示例：

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 語(yǔ)句調(diào)用存儲(chǔ)過(guò)程：

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

    
查看一下結(jié)果：

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


存儲(chǔ)過(guò)程增加了對(duì)事務(wù)的支持：

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

   
    
調(diào)用存儲(chǔ)過(guò)程 proc2，即使沒(méi)有參數(shù)，仍然需要加上括號(hào)（()）：

CALL proc2();

   
查看結(jié)果：

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 為偶數(shù)的記錄成功插入表中，奇數(shù)都被 ROLLBACK 語(yǔ)句回退了。

在 psql 中，使用 \df 命令查看存儲(chǔ)過(guò)程：

\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)

   
對(duì)于存儲(chǔ)過(guò)程，Type 的值為 proc。使用 \sf 命令可以查看存儲(chǔ)過(guò)程的定義：

\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 目前還不支持自治事務(wù)（autonomous transaction）。

相關(guān)參考文檔：
CREATE PROCEDURE
ALTER PROCEDURE
DROP PROCEDURE
CALL