postgreSQL之存儲過程發布於 6 個月前 (10月23日) - 1524 次檢閱

2020 年 4 月 9 日
筆記

以下是自己結合postgreSQL文檔手冊總結整理的入門手札，如有不嚴謹，請到官方文檔手冊查看：http://www.postgres.cn/docs/10/index.html

基於SQL的存儲過程

什麼是sql函數?

SQL函數包體是一些可執行的SQL語言。同時包含1條以上的查詢，但是函數只返回最後一個查詢(必須是SELECT)的結果。
簡單情況下，返回最後一條查詢結果的第一行。
如果最後一個查詢正好根本不返回行，將會返回空值。
除非SQL函數聲明為返回void，否則最後一條語句必須是SELECT
如果需要該函數返回最後一條SELECT語句的所有行，可以將函數的返回值定義為集合，即SETOF sometype，或者等效地聲明它為RETURNS TABLE(columns)

創建sql函數，如：

CREATE OR REPLACE FUNCTION function_name([ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ])  [RETURNS rettype]  AS $$      $BODY$;  $$ LANGUAGE SQL;

接下來用一個例子，如最簡單的SQL函數沒有參數並且簡單地返回一個基本類型：

CREATE or replace FUNCTION one()  	RETURNS  integer AS  $body$      SELECT 1;  $body$  LANGUAGE SQL;  ----------  select one();  ------  one  1

如創建一個add函數，返回兩者的入參值：

CREATE or replace FUNCTION add(a integer,b integer)  	RETURNS  integer AS  $body$      SELECT a+b;  $body$  LANGUAGE SQL;  ------  select add(1,2)  ----  add  3

還有另外一種方式，即能省掉參數名而使用數字$n,$n這樣的標識符來使用參數,如：

CREATE or replace FUNCTION add(a integer,b integer)  	RETURNS  integer AS  $body$      SELECT $1+$2;  $body$  LANGUAGE SQL;

但個人覺得這種方式不怎麼友好，就是可讀性雞巴的差~

上面的兩個例子，都是只有入參，而沒出參，接下來這個例子將同時包含輸入參數和輸出參數;由於存在輸出參數；這裡不需要returns部分，如：

CREATE OR REPLACE FUNCTION add1(in a integer, in b integer,out c integer)  AS $body$      SELECT a+b;  $body$  LANGUAGE SQL;  -----------------  select add1(1,2);  ---------------  add1  3

還記得之前sql函數的介紹嗎?sql函數包體是一些可執行的SQL語言。同時包含1條以上的查詢，但是函數只返回最後一個查詢(必須是SELECT)的結果。所以，在sql函數包體中，不一定是SELECT語句，可以是其它任意合法的SQL。但最後一條SQL必須是SELECT語句並且該SQL的結果將作為該函數的輸出結果。接下來就做一個測試，如：

CREATE OR REPLACE FUNCTION add2(in a integer, in b integer,out c integer,out d integer)  AS  $body$  	SELECT a+b,a*b;  	delete from test_array where id =1;  	SELECT a+b+a,a*b*a;    $body$  LANGUAGE SQL;  --------  select * from add2(1,2);  ---------  c	d  4	2

繼續上面的sql函數介紹-->除非SQL函數聲明為返回void，否則最後一條語句必須是SELECT,接下來我這邊就直接返回void，所以就可以沒有select，如：

CREATE OR REPLACE FUNCTION delete_array(id integer)  RETURNS void AS  $body$  	delete from test_array where id =id;  $body$  LANGUAGE SQL;

還有就是，當一個表裡面含有多條記錄咋辦呢?帶著疑問看例子吧騷年:

CREATE OR REPLACE FUNCTION select_array_er()  RETURNS integer AS  $body$  	select id from test_array_er;  $body$  LANGUAGE SQL;  ------  select  select_array_er();//我這邊id就只有兩條，1和2  -------  select_array_er  2

所以，實踐的真理告訴我們，是返回最後結果行。等等，你肯定會問，那麼我要返回所有id怎麼弄?來，叔叔帶你去看金魚：

CREATE OR REPLACE FUNCTION select_array_er2()  RETURNS setof INTEGER AS  $body$  	select id from test_array_er;  $body$  LANGUAGE SQL;  -----  select  select_array_er2();  -----  select_array_er2  2  1

所以，如果需要返回該函數最後一條SELECT語句的所有行，可以將函數的返回值定義為集合，即SETOF sometype。

基於PL/PgSQL的存儲過程

PL/pgSQL是一種用於PostgreSQL資料庫系統的可載入的過程語言。PL/pgSQL的設計目標是創建一種這樣的可載入過程語言：

可以被用來創建函數和觸發器過程
對SQL語言增加控制結構
可以執行複雜計算
繼承所有用戶定義類型、函數和操作符
可以被定義為受伺服器信任
便於使用

PL/PgSQL的存儲過程的塊結構

PL/pgSQL是一個塊結構語言。函數定義的所有文本都必須是一個塊。一個塊用下面的方法定義：

PL/pgSQL程式由三個部分組成，即：
- 聲明部分
- 執行部分
- 異常處理部分

PL/pgSQ結構如：

[ <<label>> ]  [ DECLARE      declarations ]      --聲明部分: 在此聲明PL/SQL用到的變數,類型及游標.  BEGIN      statements      -- 執行部分:  過程及SQL語句,即程式的主要部分  EXCEPTION     -- 執行異常部分: 錯誤處理      END [ label ];

在一個塊中的每一個聲明和每一個語句都由一個分號終止。如上所示，出現在另一個塊中的塊必須有一個分號在END之後。不過最後一個結束函數體的END不需要一個分號。

PL/PgSQL的存儲過程的變數聲明

變數聲明的一般語法是：

name [ CONSTANT ] type [ COLLATE collation_name ] [ NOT NULL ] [ { DEFAULT | := | = } expression ];

PL/pgSQL變數可以是任意 SQL 數據類型，例如integer、varchar和char。

這跟oracle中的存儲過程聲明變數是一樣的例如：

user_id integer;  quantity numeric(5);  url varchar;  myrow tablename%ROWTYPE;--行類型  myfield tablename.columnname%TYPE;--複製類型  arow RECORD;--記錄類型

PL/PgSQL的存儲過程的參數

聲明一個參數的語法如下:

name [in|out|in out] type

如果只指定輸入參數類型，不指定參數名，則函數體里一般用$1，$n這樣的標識符來使用參數。如：

CREATE OR REPLACE FUNCTION demo(NUMERIC)  RETURNS NUMERIC  AS $$  BEGIN      RETURN $1 * 0.8;  END;  $$  LANGUAGE PLPGSQL;

但該方法可讀性不好,最後還是在聲明參數類型時同時聲明相應的參數名，如：

CREATE OR REPLACE FUNCTION demo(total NUMERIC)  RETURNS NUMERIC  AS $$  BEGIN      RETURN total * 0.8;  END;  $$  LANGUAGE PLPGSQL;

PL/PgSQL的存儲過程的注釋

在PL/SQL里，可以使用兩種符號來寫注釋，即：

第一種是使用雙-(雙減號)它的作用範圍是只能在一行有效(類似java 單行注釋//)
第二種就是一行或多行注釋，使用的是/* */表示

簡單的一個PL/PgSQL的存儲過程例子

CREATE OR REPLACE FUNCTION somefunc()  	RETURNS integer AS $body$  DECLARE      somenum integer := 30;  BEGIN      -- 這是單行注釋      RAISE NOTICE '這是一條提示語 %', somenum;      somenum := 50;  	/*  		這是多行注釋  	*/      RAISE NOTICE 'somenum here is %', somenum;      RETURN somenum;  	--錯誤捕獲  	EXCEPTION  		WHEN others THEN      		RAISE EXCEPTION '(%)', SQLERRM;  END;  $body$  LANGUAGE plpgsql;

PL/PgSQL的存儲過程的異常錯誤處理

在上面例子，其實已經有了處理異常錯誤的語句了。具體它是幹嘛的呢?

其實阿，在PL/pgSQL函數中，如果沒有異常捕獲，函數會在發生錯誤時直接退出，與其相關的事物也會隨之回滾。我們可以通過使用帶有EXCEPTION子句的BEGIN塊來捕獲異常並使其從中恢復。見如下聲明形式：

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

如果沒有錯誤發生，只有BEGIN塊中的statements會被正常執行，然而一旦這些語句中有任意一條發生錯誤，其後的語句都將被跳過，直接跳轉到 EXCEPTION塊的開始處。此時系統將搜索異常條件列表，尋找匹配該異常的第一個條件，如果找到匹配，則執行相應的 handler_statements，之後再執行END的下一條語句。如果沒有找到匹配，該錯誤就會被繼續向外拋出，其結果與沒有EXCEPTION子句完全等同。如果此時handler_statements中的語句發生新錯誤，它將不能被該EXCEPTION子句捕獲，而是繼續向外傳播，交由其外層的EXCEPTION子句捕獲並處理。

在PostgreSQL中可以利用RAISE語句報告資訊和拋出錯誤，其聲明形式為：

RAISE level 'format' [, expression [, ...]];

例如：

raise notice 'My name is %, I am a %.', 'glj', 'Handsome boy';

更多可到：https://199604.com/1339 查看

Tags: