PostgreSQL 存儲過程的進階講解(含游標、錯誤處理、自定義函數(shù)、事務)
介紹
上一篇我們講解了PostgreSQL 存儲過程的基本入門，滿足一些最簡單的使用，本章介紹相對復雜的使用方式。
游標
PL/pgSQL 游標允許我們封裝一個查詢，然后每次處理結果集中的一條記錄。游標可以將大結果集拆分成許多小的記錄，避免內(nèi)存溢出；另外，我們可以定義一個返回游標引用的函數(shù)，然后調(diào)用程序可以基于這個引用處理返回的結果集。
使用游標的步驟大體如下：
- 聲明游標變量；
- 打開游標；
- 從游標中獲取結果；
- 判斷是否存在更多結果。如果存在，執(zhí)行第 3 步；否則，執(zhí)行第 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;
-- 沒有找到更多數(shù)據(jù)時退出循環(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首先，聲明了一個游標 cur_emp，并且綁定了一個查詢語句，通過一個參數(shù) p_deptid 獲取指定部門的員工；然后使用 OPEN 打開游標；接著在循環(huán)中使用 FETCH 語句獲取游標中的記錄，如果沒有找到更多數(shù)據(jù)退出循環(huán)語句；變量 rec_emp 用于存儲游標中的記錄；最后使用 CLOSE 語句關閉游標，釋放資源。
游標是 PL/pgSQL 中的一個強大的數(shù)據(jù)處理功能，更多的使用方法可以參考官方文檔。
錯誤處理
報告錯誤和信息
PL/pgSQL 提供了 RAISE 語句，用于打印消息或者拋出錯誤：
RAISE level format;
不同的 level 代表了錯誤的不同嚴重級別，包括：
DEBUG
LOG
NOTICE
INFO
WARNING
EXCEPTION
在上文示例中，我們經(jīng)常使用 NOTICE 輸出一些信息。如果不指定 level，默認為 EXCEPTION，將會拋出異常并且終止代碼運行。
format 是一個用于提供信息內(nèi)容的字符串，可以使用百分號（%）占位符接收參數(shù)的值， 兩個連寫的百分號（%%）表示輸出百分號自身。
以下是一些 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 %
從結果可以看出，并非所有的消息都會打印到客戶端和服務器日志中。這個可以通過配置參數(shù) 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 語句中已經(jīng)包含了 format 字符串，不能再使用該選項。
DETAIL，指定錯誤詳細信息。
HINT，設置一個提示信息。
ERRCODE，指定一個錯誤碼（SQLSTATE）?？梢允俏臋n中的條件名稱或者五個字符組成的 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 語句，用于調(diào)試存儲過程和函數(shù)：
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 只適用于代碼調(diào)試；輸出錯誤信息使用 RAISE 語句。
捕獲異常
默認情況下，PL/pgSQL 遇到錯誤時會終止代碼執(zhí)行，同時撤銷事務。我們也可以在代碼塊中使用 EXCEPTION 捕獲錯誤并繼續(xù)事務：
[ <<label>> ]
[ DECLARE
declarations ]
BEGIN
statements
EXCEPTION
WHEN condition [ OR condition ... ] THEN
handler_statements
[ WHEN condition [ OR condition ... ] THEN
handler_statements
... ]
END;如果代碼執(zhí)行出錯，程序?qū)M入 EXCEPTION 模塊；依次匹配 condition，找到第一個匹配的分支并執(zhí)行相應的 handler_statements；如果沒有找到任何匹配的分支，繼續(xù)拋出錯誤。
以下是一個除零錯誤的示例：
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 語句，具體可以參考官方文檔。
自定義函數(shù)
要創(chuàng)建一個自定義的 PL/pgSQL 函數(shù)，可以使用 CREATE FUNCTION 語句：
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ù)名；括號內(nèi)是參數(shù)，多個參數(shù)使用逗號分隔；argmode 可以是 IN（輸入）、OUT（輸出）、INOUT（輸入輸出）或者 VARIADIC（數(shù)量可變），默認為 IN；argname 是參數(shù)名稱；argtype 是參數(shù)的類型；default_expr 是參數(shù)的默認值；rettype 是返回數(shù)據(jù)的類型；AS 后面是函數(shù)的定義，和上文中的匿名塊相同；最后，LANGUAGE 指定函數(shù)實現(xiàn)的語言，也可以是其他過程語言。
以下示例創(chuàng)建一個函數(shù) get_emp_count，用于返回指定部門中的員工數(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 語句中進行調(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ù)。例如，以下語句創(chuàng)建一個重載的函數(shù) get_emp_count，返回指定部門指定日期之后入職的員工數(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;
查詢每個部門 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|
...我們再來看一個 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 是一個數(shù)組，可以傳入任意多個參數(shù)；然后計算它們的和值。例如：
SELECT sum_num(1,2), sum_num(1,2,3);
sum_num|sum_num|
-------|-------|
3| 6|如果函數(shù)不需要返回結果，可以返回 void 類型；或者直接使用存儲過程。
存儲過程
PostgreSQL 11 增加了存儲過程，使用 CREATE PROCEDURE 語句創(chuàng)建：
CREATE [ OR REPLACE ] PROCEDURE
name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
AS $$
DECLARE
declarations
BEGIN
statements;
...
END; $$
LANGUAGE plpgsql;存儲過程的定義和函數(shù)主要的區(qū)別在于沒有返回值，其他內(nèi)容都類似。以下示例創(chuàng)建了一個存儲過程 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)用存儲過程使用 CALL 語句：
call update_emp(100, 25000, '515.123.4560');
事務管理
在存儲過程內(nèi)部，可以使用 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|只有偶數(shù)才會被最終提交。
歡迎大家評論和點贊，本篇大多內(nèi)容來自官網(wǎng)文檔的理解，以及本人的經(jīng)驗。若大家喜歡，將講解Oracle 存儲過程的內(nèi)容，謝謝關注！
到此這篇關于PostgreSQL 存儲過程的進階介紹（含游標、錯誤處理、自定義函數(shù)、事務）的文章就介紹到這了,更多相關PostgreSQL 存儲過程內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
PostgreSQL ERROR: invalid escape string 解決辦法
這篇文章主要介紹了PostgreSQL ERROR: invalid escape string 解決辦法,本文環(huán)境是JDBC+PostgreSQL,需要的朋友可以參考下2014-07-07
這篇文章主要給大家介紹了關于PostgreSQL中使用數(shù)組改進性能的相關資料，文中通過示例代碼以及圖文介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2018-12-12
這篇文章主要介紹了Postgresql 賦予用戶權限和撤銷權限的實例，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
這篇文章主要介紹了PostgreSQL 序列增刪改案例，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
這篇文章主要為大家介紹了如果在Windows下安裝PostgreSQL數(shù)據(jù)庫的方法，需要的朋友可以參考下2013-11-11
PostgreSQL數(shù)據(jù)庫視圖及子查詢使用操作
這篇文章主要為大家介紹了PostgreSQL數(shù)據(jù)庫視圖及子查詢的使用操作，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步早日升職加薪2022-04-04
在PostgreSQL中經(jīng)常碰到OID和XID，剛才不明白這些東西是干什么的。2009-09-09
這篇文章主要介紹了查詢PostgreSQL占多大內(nèi)存的操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
這篇文章主要介紹了PostgreSQL 自動Vacuum配置方式，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
這篇文章主要介紹了postgresql 利用xlog進行熱備操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01