PostgreSQL存儲(chǔ)過(guò)程源碼分析

這篇文章主要介紹了PostgreSQL存儲(chǔ)過(guò)程源碼分析的相關(guān)知識(shí)，內(nèi)容詳細(xì)易懂，操作簡(jiǎn)單快捷，具有一定借鑒價(jià)值，相信大家閱讀完這篇PostgreSQL存儲(chǔ)過(guò)程源碼分析文章都會(huì)有所收獲，下面我們一起來(lái)看看吧。

游標(biāo)

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

使用游標(biāo)的步驟大體如下：

• 聲明游標(biāo)變量；

• 打開(kāi)游標(biāo)；

• 從游標(biāo)中獲取結(jié)果；

• 判斷是否存在更多結(jié)果。如果存在，執(zhí)行第 3 步；否則，執(zhí)行第 5 步；

• 關(guān)閉游標(biāo)。

我們直接通過(guò)一個(gè)示例演示使用游標(biāo)的過(guò)程：

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
  -- 打開(kāi)游標(biāo)
  OPEN cur_emp(60);

  LOOP
    -- 獲取游標(biāo)中的記錄
    FETCH cur_emp INTO rec_emp;
    -- 沒(méi)有找到更多數(shù)據(jù)時(shí)退出循環(huán)
    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

首先，聲明了一個(gè)游標(biāo) cur_emp，并且綁定了一個(gè)查詢語(yǔ)句，通過(guò)一個(gè)參數(shù) p_deptid 獲取指定部門(mén)的員工；然后使用 OPEN 打開(kāi)游標(biāo)；接著在循環(huán)中使用 FETCH 語(yǔ)句獲取游標(biāo)中的記錄，如果沒(méi)有找到更多數(shù)據(jù)退出循環(huán)語(yǔ)句；變量 rec_emp 用于存儲(chǔ)游標(biāo)中的記錄；最后使用 CLOSE 語(yǔ)句關(guān)閉游標(biāo)，釋放資源。

游標(biāo)是 PL/pgSQL 中的一個(gè)強(qiáng)大的數(shù)據(jù)處理功能，更多的使用方法可以參考官方文檔。

錯(cuò)誤處理

報(bào)告錯(cuò)誤和信息

PL/pgSQL 提供了 RAISE 語(yǔ)句，用于打印消息或者拋出錯(cuò)誤：

RAISE level format;

不同的 level 代表了錯(cuò)誤的不同嚴(yán)重級(jí)別，包括：

DEBUG
LOG
NOTICE
INFO
WARNING
EXCEPTION

在上文示例中，我們經(jīng)常使用 NOTICE 輸出一些信息。如果不指定 level，默認(rèn)為 EXCEPTION，將會(huì)拋出異常并且終止代碼運(yùn)行。

format 是一個(gè)用于提供信息內(nèi)容的字符串，可以使用百分號(hào)（%）占位符接收參數(shù)的值， 兩個(gè)連寫(xiě)的百分號(hào)（%%）表示輸出百分號(hào)自身。

以下是一些 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 %

從結(jié)果可以看出，并非所有的消息都會(huì)打印到客戶端和服務(wù)器日志中。這個(gè)可以通過(guò)配置參數(shù) client_min_messages 和 log_min_messages 進(jìn)行設(shè)置。

對(duì)于 EXCEPTION 級(jí)別的錯(cuò)誤，可以支持額外的選項(xiàng)：

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 可以是以下選項(xiàng)：

MESSAGE，設(shè)置錯(cuò)誤消息。如果 RAISE 語(yǔ)句中已經(jīng)包含了 format 字符串，不能再使用該選項(xiàng)。
DETAIL，指定錯(cuò)誤詳細(xì)信息。
HINT，設(shè)置一個(gè)提示信息。
ERRCODE，指定一個(gè)錯(cuò)誤碼（SQLSTATE）?？梢允俏臋n中的條件名稱或者五個(gè)字符組成的 SQLSTATE 代碼。
COLUMN、CONSTRAINT、DATATYPE、TABLE、SCHEMA，返回相關(guān)對(duì)象的名稱。
以下是一些示例：

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 語(yǔ)句，用于調(diào)試存儲(chǔ)過(guò)程和函數(shù)：

ASSERT condition [ , message ];

其中，condition 是一個(gè)布爾表達(dá)式；如果它的結(jié)果為真，ASSERT 通過(guò)；如果結(jié)果為假或者 NULL，將會(huì)拋出 ASSERT_FAILURE 異常。message 用于提供額外的錯(cuò)誤信息，默認(rèn)為“assertion failed”。例如：

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

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

??注意，ASSERT 只適用于代碼調(diào)試；輸出錯(cuò)誤信息使用 RAISE 語(yǔ)句。

捕獲異常

默認(rèn)情況下，PL/pgSQL 遇到錯(cuò)誤時(shí)會(huì)終止代碼執(zhí)行，同時(shí)撤銷事務(wù)。我們也可以在代碼塊中使用 EXCEPTION 捕獲錯(cuò)誤并繼續(xù)事務(wù)：

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

如果代碼執(zhí)行出錯(cuò)，程序?qū)?huì)進(jìn)入 EXCEPTION 模塊；依次匹配 condition，找到第一個(gè)匹配的分支并執(zhí)行相應(yīng)的 handler_statements；如果沒(méi)有找到任何匹配的分支，繼續(xù)拋出錯(cuò)誤。

以下是一個(gè)除零錯(cuò)誤的示例：

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

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

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

自定義函數(shù)

要?jiǎng)?chuàng)建一個(gè)自定義的 PL/pgSQL 函數(shù)，可以使用 CREATE FUNCTION 語(yǔ)句：

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

CREATE 表示創(chuàng)建函數(shù)，OR REPLACE 表示替換函數(shù)定義；name 是函數(shù)名；括號(hào)內(nèi)是參數(shù)，多個(gè)參數(shù)使用逗號(hào)分隔；argmode 可以是 IN（輸入）、OUT（輸出）、INOUT（輸入輸出）或者 VARIADIC（數(shù)量可變），默認(rèn)為 IN；argname 是參數(shù)名稱；argtype 是參數(shù)的類型；default_expr 是參數(shù)的默認(rèn)值；rettype 是返回?cái)?shù)據(jù)的類型；AS 后面是函數(shù)的定義，和上文中的匿名塊相同；最后，LANGUAGE 指定函數(shù)實(shí)現(xiàn)的語(yǔ)言，也可以是其他過(guò)程語(yǔ)言。

以下示例創(chuàng)建一個(gè)函數(shù) get_emp_count，用于返回指定部門(mén)中的員工數(shù)量：

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;

創(chuàng)建該函數(shù)之后，可以像內(nèi)置函數(shù)一樣在 SQL 語(yǔ)句中進(jìn)行調(diào)用：

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 函數(shù)支持重載（Overloading），也就是相同的函數(shù)名具有不同的函數(shù)參數(shù)。例如，以下語(yǔ)句創(chuàng)建一個(gè)重載的函數(shù) get_emp_count，返回指定部門(mén)指定日期之后入職的員工數(shù)量：

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;

查詢每個(gè)部門(mén) 2005 年之后入職的員工數(shù)量：

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|
...

我們?cè)賮?lái)看一個(gè) VARIADIC 參數(shù)的示例：

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;

參數(shù) nums 是一個(gè)數(shù)組，可以傳入任意多個(gè)參數(shù)；然后計(jì)算它們的和值。例如：

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

如果函數(shù)不需要返回結(jié)果，可以返回 void 類型；或者直接使用存儲(chǔ)過(guò)程。

存儲(chǔ)過(guò)程

PostgreSQL 11 增加了存儲(chǔ)過(guò)程，使用 CREATE PROCEDURE 語(yǔ)句創(chuàng)建：

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

存儲(chǔ)過(guò)程的定義和函數(shù)主要的區(qū)別在于沒(méi)有返回值，其他內(nèi)容都類似。以下示例創(chuàng)建了一個(gè)存儲(chǔ)過(guò)程 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;

調(diào)用存儲(chǔ)過(guò)程使用 CALL 語(yǔ)句：

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

事務(wù)管理

在存儲(chǔ)過(guò)程內(nèi)部，可以使用 COMMIT 或者 ROLLBACK 語(yǔ)句提交或者回滾事務(wù)。例如：

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|

只有偶數(shù)才會(huì)被最終提交。

關(guān)于“PostgreSQL存儲(chǔ)過(guò)程源碼分析”這篇文章的內(nèi)容就介紹到這里，感謝各位的閱讀！相信大家對(duì)“PostgreSQL存儲(chǔ)過(guò)程源碼分析”知識(shí)都有一定的了解，大家如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道。