PostgreSQL存儲過程源碼分析

這篇文章主要介紹了PostgreSQL存儲過程源碼分析的相關知識，內容詳細易懂，操作簡單快捷，具有一定借鑒價值，相信大家閱讀完這篇PostgreSQL存儲過程源碼分析文章都會有所收獲，下面我們一起來看看吧。

游標

PL/pgSQL 游標允許我們封裝一個查詢，然后每次處理結果集中的一條記錄。游標可以將大結果集拆分成許多小的記錄，避免內存溢出；另外，我們可以定義一個返回游標引用的函數，然后調用程序可以基于這個引用處理返回的結果集。

使用游標的步驟大體如下：

• 聲明游標變量；

• 打開游標；

• 從游標中獲取結果；

• 判斷是否存在更多結果。如果存在，執行第 3 步；否則，執行第 5 步；

• 關閉游標。

我們直接通過一個示例演示使用游標的過程：

DO $$
DECLARE 
  rec_emp RECORD;
  cur_emp CURSOR(p_deptid INTEGER) FOR
    SELECT first_name, last_name, hire_date 
    FROM employees
    WHERE department_id = p_deptid;
BEGIN
  -- 打開游標
  OPEN cur_emp(60);

  LOOP
    -- 獲取游標中的記錄
    FETCH cur_emp INTO rec_emp;
    -- 沒有找到更多數據時退出循環
    EXIT WHEN NOT FOUND;

    RAISE NOTICE '%,% hired at:%' , rec_emp.first_name, rec_emp.last_name, rec_emp.hire_date;
  END LOOP;

  -- Close the cursor
  CLOSE cur_emp;
END $$;

NOTICE:  Alexander,Hunold hired at:2006-01-03
NOTICE:  Bruce,Ernst hired at:2007-05-21
NOTICE:  David,Austin hired at:2005-06-25
NOTICE:  Valli,Pataballa hired at:2006-02-05
NOTICE:  Diana,Lorentz hired at:2007-02-07

首先，聲明了一個游標 cur_emp，并且綁定了一個查詢語句，通過一個參數 p_deptid 獲取指定部門的員工；然后使用 OPEN 打開游標；接著在循環中使用 FETCH 語句獲取游標中的記錄，如果沒有找到更多數據退出循環語句；變量 rec_emp 用于存儲游標中的記錄；最后使用 CLOSE 語句關閉游標，釋放資源。

游標是 PL/pgSQL 中的一個強大的數據處理功能，更多的使用方法可以參考官方文檔。

錯誤處理

報告錯誤和信息

PL/pgSQL 提供了 RAISE 語句，用于打印消息或者拋出錯誤：

RAISE level format;

不同的 level 代表了錯誤的不同嚴重級別，包括：

DEBUG
LOG
NOTICE
INFO
WARNING
EXCEPTION

在上文示例中，我們經常使用 NOTICE 輸出一些信息。如果不指定 level，默認為 EXCEPTION，將會拋出異常并且終止代碼運行。

format 是一個用于提供信息內容的字符串，可以使用百分號（%）占位符接收參數的值， 兩個連寫的百分號（%%）表示輸出百分號自身。

以下是一些 RAISE 示例：

DO $$ 
BEGIN 
  RAISE DEBUG 'This is a debug text.';
  RAISE INFO 'This is an information.';
  RAISE LOG 'This is a log.';
  RAISE WARNING 'This is a warning at %', now();
  RAISE NOTICE 'This is a notice %%';
END $$;

INFO:  This is an information.
WARNING:  This is a warning at 2020-05-16 11:27:06.138569+08
NOTICE:  This is a notice %

從結果可以看出，并非所有的消息都會打印到客戶端和服務器日志中。這個可以通過配置參數 client_min_messages 和 log_min_messages 進行設置。

對于 EXCEPTION 級別的錯誤，可以支持額外的選項：

RAISE [ EXCEPTION ] format USING option = expression [, ... ];
RAISE [ EXCEPTION ] condition_name USING option = expression [, ... ];
RAISE [ EXCEPTION ] SQLSTATE 'sqlstate' USING option = expression [, ... ];
RAISE [ EXCEPTION ] USING option = expression [, ... ];

其中，option 可以是以下選項：

MESSAGE，設置錯誤消息。如果 RAISE 語句中已經包含了 format 字符串，不能再使用該選項。
DETAIL，指定錯誤詳細信息。
HINT，設置一個提示信息。
ERRCODE，指定一個錯誤碼（SQLSTATE）。可以是文檔中的條件名稱或者五個字符組成的 SQLSTATE 代碼。
COLUMN、CONSTRAINT、DATATYPE、TABLE、SCHEMA，返回相關對象的名稱。
以下是一些示例：

RAISE EXCEPTION 'Nonexistent ID --> %', user_id
      USING HINT = 'Please check your user ID';

RAISE 'Duplicate user ID: %', user_id USING ERRCODE = 'unique_violation';
RAISE 'Duplicate user ID: %', user_id USING ERRCODE = '23505';

RAISE division_by_zero;
RAISE SQLSTATE '22012';

檢查斷言

PL/pgSQL 提供了 ASSERT 語句，用于調試存儲過程和函數：

ASSERT condition [ , message ];

其中，condition 是一個布爾表達式；如果它的結果為真，ASSERT 通過；如果結果為假或者 NULL，將會拋出 ASSERT_FAILURE 異常。message 用于提供額外的錯誤信息，默認為“assertion failed”。例如：

DO $$ 
DECLARE 
  i integer := 1;
BEGIN 
  ASSERT i = 0, 'i 的初始值應該為 0！';
END $$;

ERROR:  i 的初始值應該為 0！
CONTEXT:  PL/pgSQL function inline_code_block line 5 at ASSERT

??注意，ASSERT 只適用于代碼調試；輸出錯誤信息使用 RAISE 語句。

捕獲異常

默認情況下，PL/pgSQL 遇到錯誤時會終止代碼執行，同時撤銷事務。我們也可以在代碼塊中使用 EXCEPTION 捕獲錯誤并繼續事務：

[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
EXCEPTION
    WHEN condition [ OR condition ... ] THEN
        handler_statements
    [ WHEN condition [ OR condition ... ] THEN
          handler_statements
      ... ]
END;

如果代碼執行出錯，程序將會進入 EXCEPTION 模塊；依次匹配 condition，找到第一個匹配的分支并執行相應的 handler_statements；如果沒有找到任何匹配的分支，繼續拋出錯誤。

以下是一個除零錯誤的示例：

DO $$ 
DECLARE 
  i integer := 1;
BEGIN 
  i := i / 0;
EXCEPTION
  WHEN division_by_zero THEN
    RAISE NOTICE '除零錯誤！';
  WHEN OTHERS THEN
    RAISE NOTICE '其他錯誤！';
END $$;

NOTICE:  除零錯誤！
OTHERS 用于捕獲未指定的錯誤類型。

PL/pgSQL 還提供了捕獲詳細錯誤信息的 GET STACKED DIAGNOSTICS 語句，具體可以參考官方文檔。

自定義函數

要創建一個自定義的 PL/pgSQL 函數，可以使用 CREATE FUNCTION 語句：

CREATE [ OR REPLACE ] FUNCTION
  name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
  RETURNS rettype
AS $$
DECLARE
  declarations
BEGIN
  statements;
  ...
END; $$
LANGUAGE plpgsql;

CREATE 表示創建函數，OR REPLACE 表示替換函數定義；name 是函數名；括號內是參數，多個參數使用逗號分隔；argmode 可以是 IN（輸入）、OUT（輸出）、INOUT（輸入輸出）或者 VARIADIC（數量可變），默認為 IN；argname 是參數名稱；argtype 是參數的類型；default_expr 是參數的默認值；rettype 是返回數據的類型；AS 后面是函數的定義，和上文中的匿名塊相同；最后，LANGUAGE 指定函數實現的語言，也可以是其他過程語言。

以下示例創建一個函數 get_emp_count，用于返回指定部門中的員工數量：

CREATE OR REPLACE FUNCTION get_emp_count(p_deptid integer)
 RETURNS integer 
AS $$
DECLARE
  ln_count integer;
BEGIN
  select count(*) into ln_count
  from employees
  where department_id = p_deptid;

  return ln_count;
END; $$
LANGUAGE plpgsql;

創建該函數之后，可以像內置函數一樣在 SQL 語句中進行調用：

select department_id,department_name,get_emp_count(department_id)
from departments d;
department_id|department_name     |get_emp_count|
-------------|--------------------|-------------|
           10|Administration      |            1|
           20|Marketing           |            2|
           30|Purchasing          |            6|
...

PL/pgSQL 函數支持重載（Overloading），也就是相同的函數名具有不同的函數參數。例如，以下語句創建一個重載的函數 get_emp_count，返回指定部門指定日期之后入職的員工數量：

CREATE OR REPLACE FUNCTION get_emp_count(p_deptid integer, p_hiredate date)
 RETURNS integer 
AS $$
DECLARE
  ln_count integer;
BEGIN
  select count(*) into ln_count
  from employees
  where department_id = p_deptid and hire_date >= p_hiredate;

  return ln_count;
END; $$
LANGUAGE plpgsql;

查詢每個部門 2005 年之后入職的員工數量：

select department_id,department_name,get_emp_count(department_id),get_emp_count(department_id, '2005-01-01')
from departments d;
department_id|department_name     |get_emp_count|get_emp_count|
-------------|--------------------|-------------|-------------|
           10|Administration      |            1|            0|
           20|Marketing           |            2|            1|
           30|Purchasing          |            6|            4|
...

我們再來看一個 VARIADIC 參數的示例：

CREATE OR REPLACE FUNCTION sum_num(
  VARIADIC nums numeric[])
  RETURNS numeric
AS $$
DECLARE ln_total numeric;
BEGIN
  SELECT SUM(nums[i]) INTO ln_total
  FROM generate_subscripts(nums, 1) t(i);

  RETURN ln_total;
END; $$
LANGUAGE plpgsql;

參數 nums 是一個數組，可以傳入任意多個參數；然后計算它們的和值。例如：

SELECT sum_num(1,2), sum_num(1,2,3);
sum_num|sum_num|
-------|-------|
      3|      6|

如果函數不需要返回結果，可以返回 void 類型；或者直接使用存儲過程。

存儲過程

PostgreSQL 11 增加了存儲過程，使用 CREATE PROCEDURE 語句創建：

CREATE [ OR REPLACE ] PROCEDURE
  name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
AS $$
DECLARE
  declarations
BEGIN
  statements;
  ...
END; $$
LANGUAGE plpgsql;

存儲過程的定義和函數主要的區別在于沒有返回值，其他內容都類似。以下示例創建了一個存儲過程 update_emp，用于修改員工的信息：

CREATE OR REPLACE PROCEDURE update_emp(
  p_empid in integer,
  p_salary in numeric,
  p_phone in varchar)
AS $$
BEGIN
  update employees 
  set salary = p_salary,
      phone_number = p_phone
  where employee_id = p_empid;
END; $$
LANGUAGE plpgsql;

調用存儲過程使用 CALL 語句：

call update_emp(100, 25000, '515.123.4560');

事務管理

在存儲過程內部，可以使用 COMMIT 或者 ROLLBACK 語句提交或者回滾事務。例如：

create table test(a int);

CREATE PROCEDURE transaction_test()
LANGUAGE plpgsql
AS $$
BEGIN
    FOR i IN 0..9 LOOP
        INSERT INTO test (a) VALUES (i);
        IF i % 2 = 0 THEN
            COMMIT;
        ELSE
            ROLLBACK;
        END IF;
    END LOOP;
END
$$;

CALL transaction_test();
select * from test;
a|
-|
0|
2|
4|
6|
8|

只有偶數才會被最終提交。

關于“PostgreSQL存儲過程源碼分析”這篇文章的內容就介紹到這里，感謝各位的閱讀！相信大家對“PostgreSQL存儲過程源碼分析”知識都有一定的了解，大家如果還想學習更多知識，歡迎關注億速云行業資訊頻道。