前面介紹了?PostgreSQL 數(shù)據(jù)類型和運算符、常用函數(shù)、鎖操作、執(zhí)行計劃、視圖與觸發(fā)器相關(guān)的知識點，今天我將詳細的為大家介紹?PostgreSQL 存儲過程相關(guān)知識，希望大家能夠從中收獲多多！如有幫助，請點在看、轉(zhuǎn)發(fā)支持一波?。?！
工作中可能會存在業(yè)務(wù)比較復雜，重復性工作比較多，需要批量處理數(shù)據(jù)的情況，此時使用存儲過程會方便很多，存儲過程的執(zhí)行效率也會快很多，能幫助我們節(jié)省很多代碼和時間。
并且，將需要的sql寫成存儲過程并設(shè)置成定時任務(wù)，那樣在任意時刻，需要執(zhí)行任意次數(shù)都可以根據(jù)你的設(shè)定執(zhí)行，哪怕你不在工位上，減少你的工作量，能讓你更愉快的摸魚(不是)。
PostgreSQL 概述
在 PostgreSQL 中，除了標準 SQL 語句之外，通過創(chuàng)建復雜的過程和函數(shù)來滿足程序需要，我們稱為存儲過程和自定義函數(shù)（User-Defined Function）。它有助于您執(zhí)行通常在數(shù)據(jù)庫中的單個函數(shù)中進行多次查詢和往返操作的操作。
PL/pgSQL 簡單易學，無論是否具有編程基礎(chǔ)都能夠很快學會。PL/pgSQL 存儲過程，它和 Oracle PL/SQL 非常類似，是 PostgreSQL默認支持的存儲過程，下面針對優(yōu)缺點給大家做了簡要分析。
優(yōu)點
-
減少應(yīng)用和數(shù)據(jù)庫之間的網(wǎng)絡(luò)傳輸。所有的 SQL 語句都存儲在數(shù)據(jù)庫服務(wù)器中，應(yīng)用程序只需要發(fā)送函數(shù)調(diào)用并獲取除了結(jié)果，避免了發(fā)送多個 SQL 語句并等待結(jié)果。
-
提高應(yīng)用的性能。因為自定義函數(shù)和存儲過程進行了預編譯并存儲在數(shù)據(jù)庫服務(wù)器中。
-
可重用性。存儲過程和函數(shù)的功能可以被多個應(yīng)用同時使用。
-
作為腳本使用，如產(chǎn)品的 liquibase 中， 清理或修復數(shù)據(jù)將非常好用。
缺點
-
導致軟件開發(fā)緩慢。因為存儲過程需要單獨學習，而且很多開發(fā)人員并不具備這種技能。
-
不易進行版本管理和代碼調(diào)試。
-
不同數(shù)據(jù)庫管理系統(tǒng)之間無法移植，語法存在較大的差異。
-
更多關(guān)于大數(shù)據(jù) PostgreSQL 系列的學習文章，請參閱：PostgreSQL 數(shù)據(jù)庫，本系列持續(xù)更新中。
存儲過程基本結(jié)構(gòu)
定義一個函數(shù)
CREATE?[?OR?REPLACE?]?FUNCTION
????name?(?[?[?argmode?]?[?argname?]?argtype?[?{?DEFAULT?|?=?}?default_expr?]?[,?...]?]?)
????[?RETURNS?rettype
??????|?RETURNS?TABLE?(?column_name?column_type?[,?...]?)?]
??{?LANGUAGE?lang_name
????|?TRANSFORM?{?FOR?TYPE?type_name?}?[,?...?]
????|?WINDOW
????|?IMMUTABLE?|?STABLE?|?VOLATILE?|?[?NOT?]?LEAKPROOF
????|?CALLED?ON?NULL?INPUT?|?RETURNS?NULL?ON?NULL?INPUT?|?STRICT
????|?[?EXTERNAL?]?SECURITY?INVOKER?|?[?EXTERNAL?]?SECURITY?DEFINER
????|?PARALLEL?{?UNSAFE?|?RESTRICTED?|?SAFE?}
????|?COST?execution_cost
????|?ROWS?result_rows
????|?SUPPORT?support_function
????|?SET?configuration_parameter?{?TO?value?|?=?value?|?FROM?CURRENT?}
????|?AS?'definition'
????|?AS?'obj_file',?'link_symbol'
??}?...
由官方文檔得到的定義一個函數(shù)的語法，當然現(xiàn)實中不需要所有的要素都要定義到?，F(xiàn)在就常用的要素做出解釋。
-
CREATE FUNCTION定義一個新函數(shù)。CREATE OR REPLACE FUNCTION將創(chuàng)建一個新函數(shù)或者替換一個現(xiàn)有的函數(shù)
-
name：表示要創(chuàng)建的函數(shù)名
-
argmode：一個參數(shù)的模式：IN、OUT、INOUT或者VARIADIC。如果省略，默認為IN。只有OUT參數(shù)能跟在一個VARIADIC參數(shù)后面。還有，OUT和INOUT參數(shù)不能和RETURNS TABLE符號一起使用。
-
argname：一個參數(shù)的名稱
-
argtype：該函數(shù)參數(shù)的數(shù)據(jù)類型
-
default_expr：如果參數(shù)沒有被指定值時要用作默認值的表達式
-
rettype：返回的數(shù)據(jù)類型，如果該函數(shù)不會返回一個值，可以指定返回類型為void。(后面詳細講)
-
column_name:RETURNS TABLE語法中一個輸出列的名稱
-
culumn_type:RETURNS TABLE語法中的輸出列的數(shù)據(jù)類型
注意：定義函數(shù)的時候，參數(shù)可以是空，但是哪怕不修改函數(shù)體只修改參數(shù)，它會得到一個新的函數(shù)。更多關(guān)于大數(shù)據(jù) PostgreSQL 系列的學習文章，請參閱：PostgreSQL 數(shù)據(jù)庫，本系列持續(xù)更新中。
PL/pgSQL 的結(jié)構(gòu)
[?<<label>>?]
[?DECLARE
????declarations?]
BEGIN
????statements
END?[?label?];
PL/pgSQL是一種塊結(jié)構(gòu)的語言。一個函數(shù)體的完整文本必須是一個塊。存儲過程的語法如上所示。
-
在一個塊中的每一個聲明和每一個語句都由一個分號終止。
-
所有的關(guān)鍵詞都是大小寫無關(guān)的。除非被雙引號引用，標識符會被隱式地轉(zhuǎn)換為小寫形式，就像它們在普通 SQL 命令中。
-
PL/pgSQL代碼中的注釋和普通 SQL 中的一樣。一個雙連字符（–）開始一段注釋，它延伸到該行的末尾。一個/* 開始一段塊注釋，它會延伸到匹配*/出現(xiàn)的位置。塊注釋可以嵌套。
賦值語法
聲明變量賦值
具體可看官方文檔
name?[?CONSTANT?]?type?[?COLLATE?collation_name?]?[?NOT?NULL?]?[?{?DEFAULT?|?:=?|?=?}?expression?];
在自定義函數(shù)中聲明一個變量，并給這個變量賦值的時候可以用這個方法。示例如下：
--?1
declare?a?integer?default?32;
--?2
declare?a?integer?:=32;
--?3
declare?a?integer;
a?:=32;
這三種方法都能將聲明一個變量a,并且將32賦值給a。若不給a賦值，就是方法三中沒有a:=32;也不會報錯，就是變量a初始化為sql空值。
-
constant：若是增加constant，則表示該變量的值無法修改
-
collate：給該變量指定一個排序規(guī)則
-
not null:如果給改變量賦值為空值會報錯
例如，以下方式就會報錯。
--?報錯1
--?加了constant，已經(jīng)無法修改a的值了。
declare?a?constant?integer?default?32;
a?:=1;
?
--?報錯2
--?在聲明變量的時候選擇了not?null,就應(yīng)該在聲明時賦值，否則哪怕后面賦值還是會報錯
declare?a?integer?not?null;
a?:=32;
?
動態(tài)賦值
具體可查看官方文檔
方式一：into子句
SELECT?select_expressions?INTO?[STRICT]?target?FROM?...;
INSERT?...?RETURNING?expressions?INTO?[STRICT]?target;
UPDATE?...?RETURNING?expressions?INTO?[STRICT]?target;
DELETE?...?RETURNING?expressions?INTO?[STRICT]?target;
舉個例子，如下：
--?這就表示把test表中的id字段的值賦值給a
--?其中?select?id?from?test就是基礎(chǔ)sql命令從test表中查詢id的值
--?通過?into?a?將查詢得到的值賦值給a
select?id?into?a?from?test;
方式二：動態(tài)命令
EXECUTE?command-string?[?INTO?[STRICT]?target?]?[?USING?expression?[,?...?]?];
舉個例子，如下：
--?和上面一樣，把id的值查詢出來賦值給a
execute?'select?id?from?test'?into?a;
更多關(guān)于大數(shù)據(jù) PostgreSQL 系列的學習文章，請參閱：PostgreSQL 數(shù)據(jù)庫，本系列持續(xù)更新中。
返回值類型
官方文檔：
?
returns返回類型可以是一種基本類型、組合類型或者域類型，也可以引用一個表列的類型。
當有OUT或者INOUT參數(shù)時，可以省略RETURNS子句。如果存在，該子句必須和輸出參數(shù)所表示的結(jié)果類型一致：如果有多個輸出參數(shù)，則為RECORD，否則與單個輸出參數(shù)的類型相同。
返回void
如果該函數(shù)不會返回一個值，可以指定返回類型為void。如果選擇返回returns void,那函數(shù)體最后就不用return了。
CREATE?OR?REPLACE?FUNCTION?"public"."func1"()
?RETURNS?"pg_catalog"."void"?AS?$BODY$????--?returns?void,在保存的時候自動會變成"pg_catalog"."void"
???
???BEGIN
???--?建表語句
???create?table?a(
???id?int4,
???name?varchar(50)
???);
???
END
$BODY$
?LANGUAGE?plpgsql?VOLATILE
?COST?100
?
?
返回基本類型
返回的結(jié)果是類似int4,float,text等這些基本數(shù)據(jù)類型都可以。示例：
CREATE?OR?REPLACE?FUNCTION?"public"."func1"()
?RETURNS?"pg_catalog"."text"?AS?$BODY$
???
???declare?val?text;
???BEGIN
???insert?into?a?values?(1,'小明')??returning?name?into?val;
???
???return?val;
END
$BODY$
?LANGUAGE?plpgsql?VOLATILE
?COST?100
?
這個函數(shù)的結(jié)果會返回val的值，如下圖：
?
?
并且在表a中會插入一條數(shù)據(jù)，如下圖。
?
?
這里敲黑板?。?！?這里通過into子句賦值給變量，返回的是結(jié)果的第一行或者null(查詢返回零行)，除非使用order by進行排序，否則第一行是不明確的，第一行之后所有的結(jié)果都會被丟棄。
如果加了strict選項，那么查詢結(jié)果必須是恰好一行，否則就會報錯。
舉個例子，現(xiàn)在在a表中插入數(shù)據(jù)，表a數(shù)據(jù)如下。
?
?
然后從表中查詢出name值賦值val。
CREATE?OR?REPLACE?FUNCTION?"public"."func1"()
??RETURNS?"pg_catalog"."text"?AS?$BODY$
?
?declare?val?text;
?
?BEGIN
?select?name?into?val?from?a?;
?
?return?val;
END
$BODY$
??LANGUAGE?plpgsql?VOLATILE
??COST?100
得到的結(jié)果如下顯示。返回一行數(shù)據(jù)，并且這個第一行的排序并不是表的第一行數(shù)據(jù)。
?
?
若是添加strict選項，結(jié)果返回的是多條數(shù)據(jù)就會報錯，如下顯示。
?
?
如果返回的結(jié)果剛好是一行數(shù)據(jù)的，則添加strict選項就可以顯示最終結(jié)果。
?
正常顯示結(jié)果并且返回。結(jié)果如下：
?
返回多條數(shù)據(jù)
到目前為止，現(xiàn)在返回的結(jié)果要不是返回空要不就是返回一個記錄，若是想要多條數(shù)據(jù)該怎么處理呢？
-
返回setof sometype
-
SETOF修飾符表示該函數(shù)將返回一個項的集合而不是一個單一項。當被返回setof sometype時，函數(shù)最后一個查詢執(zhí)行完后輸出的每一行都會被作為結(jié)果集的一個元素返回。
-
sometype可以是某一張已經(jīng)存在的表，也可以是record。也可以是某個字段類型。
-
上面那個例子，若是想要返回多條記錄，就可以修改如下。
CREATE?OR?REPLACE?FUNCTION?"public"."func1"()
??RETURNS?SETOF?"public"."a"?AS?$BODY$
?
?BEGIN
?return?query?select?a.id,a.name?from?a?limit?2;
?
?return;
END
$BODY$
??LANGUAGE?plpgsql?STABLE
??COST?100
??ROWS?1000
得到的結(jié)果如下圖。
?
?
期中return query還可以使用return next。具體語法是：
RETURN?NEXT?expression;
RETURN?QUERY?query;
RETURN?QUERY?EXECUTE?command-string?[?USING?expression?[,?...?]?];
詳細情況可以看查看官方文檔。更多關(guān)于大數(shù)據(jù) PostgreSQL 系列的學習文章，請參閱：PostgreSQL 數(shù)據(jù)庫，本系列持續(xù)更新中。
控制結(jié)構(gòu)
postgresql中可以使用的控制結(jié)構(gòu)，有條件結(jié)構(gòu)和循環(huán)結(jié)構(gòu)。
條件結(jié)構(gòu)
官方文檔：
IF語句:
-
IF … THEN … END IF
-
IF … THEN … ELSE … END IF
-
IF … THEN … ELSIF … THEN … ELSE … END IF
具體語法官方文檔鏈接中可查看，現(xiàn)在舉個小例子看一個
CREATE?OR?REPLACE?FUNCTION?"public"."func1"("a"?int4)
?RETURNS?"pg_catalog"."text"?AS?$BODY$
???
???declare?rel?varchar;
???
???BEGIN
?????IF?a>=90?THEN
??????rel:='優(yōu)秀';
?????elsif?a>=80?then
???????rel?:=?'良好';
?????elsif?a>=60?then
???????rel?:=?'及格';
?????ELSE
??????rel?:='不及格';
?????END?IF;
???return?rel;
END
$BODY$
?LANGUAGE?plpgsql?STABLE
?COST?100
?
?
CASE語句：
-
CASE … WHEN … THEN … ELSE … END CASE
-
CASE WHEN … THEN … ELSE … END CASE
同理上面的函數(shù)可以改成case when
CREATE?OR?REPLACE?FUNCTION?"public"."func1"("a"?int4)
??RETURNS?"pg_catalog"."text"?AS?$BODY$
?
?declare?rel?varchar;
?
?BEGIN
??case?when?a>=90?THEN
?????rel:='優(yōu)秀';
????when?a>=80?then
?????rel?:=?'良好';
????when?a>=60?then
?????rel?:=?'及格';
????ELSE
?????rel?:='不及格';
??END?case;
?return?rel;
END
$BODY$
??LANGUAGE?plpgsql?STABLE
??COST?100
循環(huán)結(jié)構(gòu)
官方文檔：
循環(huán)結(jié)構(gòu)有l(wèi)oop，exit,continue,while,for和foreach語句
-
loop
[?<<label>>?]
LOOP
???statements
END?LOOP?[?label?];
loop定義的是一個無條件循環(huán)，會無限重復直到被exit或return語句終止，所以
-
exit
EXIT?[?label?]?[?WHEN?boolean-expression?];
指定when，當boolean-expression為真時會退出循環(huán)。配合loop給個例子。
?LOOP
??raise?notice?'a?is?%',a;
??a?:=a-1;
??IF?a<=rel?THEN
???EXIT;?
??END?IF;?
?END?LOOP;
--?等同于
?LOOP
??raise?notice?'a?is?%',a;
??a?:=a-1;
??EXIT?when?a<=rel;??????--?這個相當于前面整個if判斷
?END?LOOP;
?
執(zhí)行函數(shù)，傳入?yún)?shù)為5，則顯示的結(jié)果如下：
?
-
continue
CONTINUE?[?label?]?[?WHEN?boolean-expression?];
-
while
[?<<label>>?]
WHILE?boolean-expression?LOOP
???statements
END?LOOP?[?label?];
當boolean-expression為真的時候，這個循環(huán)會執(zhí)行。舉例：
CREATE?OR?REPLACE?FUNCTION?"public"."loops"("a"?int4)
??RETURNS?"pg_catalog"."void"?AS?$BODY$
?
?declare?rel?integer?default?0;
?
?BEGIN
?
?WHILE?a>0?LOOP
??raise?info?'a=?%',a;
??rel?:=?rel+a;
??a:=a-1;
?END?LOOP;
?
?raise?info?'rel?=?%',rel;
?
END
$BODY$
??LANGUAGE?plpgsql?VOLATILE
??COST?100
?
執(zhí)行函數(shù)輸入?yún)?shù)5，得到信息如下：
?
-
for
整數(shù)范圍循環(huán)。
[?<<label>>?]
FOR?name?IN?[?REVERSE?]?expression?..?expression?[?BY?expression?]?LOOP
????statements
END?LOOP?[?label?];
for循環(huán)會創(chuàng)建一個整數(shù)返回進行迭代。此時下界臨界值小于上界臨界值，若是制定reverse，則上界臨界值寫在前，下界臨界值寫在后，默認步長為1，若是正向迭代，每次迭代數(shù)值都是加1，若是反向迭代都是減1。通過by，可以指定步長。
例如：
FOR?i?IN?1..10?LOOP
???--?我在循環(huán)中將取值?1,2,3,4,5,6,7,8,9,10?
END?LOOP;
FOR?i?IN?REVERSE?10..1?LOOP
???--?我在循環(huán)中將取值?10,9,8,7,6,5,4,3,2,1?
END?LOOP;
FOR?i?IN?REVERSE?10..1?BY?2?LOOP
???--?我在循環(huán)中將取值?10,8,6,4,2?
END?LOOP;
查詢結(jié)果循環(huán)
[?<<label>>?]
FOR?target?IN?query?LOOP
????statements
END?LOOP?[?label?];
target是一個記錄變量、行變量或者逗號分隔的標量變量列表。target被連續(xù)不斷被賦予來自query的每一行，并且循環(huán)體將為每一行執(zhí)行一次。下面是一個例子：
CREATE?OR?REPLACE?FUNCTION?"public"."loops"()
??RETURNS?"pg_catalog"."void"?AS?$BODY$
?
?declare?rel?record;
?
?BEGIN
?
?FOR?rel?IN?select?id,name?from?a?LOOP
??--?quote_ident()的作用是為字符串加上雙引號
??raise?notice?'a表中的用戶信息為id:?%,name:?%',rel.id,quote_ident(rel.name);
?
?END?LOOP;
END
$BODY$
??LANGUAGE?plpgsql?VOLATILE
??COST?100
結(jié)果顯示如下：
?
?
for-in-execute語句在行上迭代的另一種方式：
[?<<label>>?]
FOR?target?IN?EXECUTE?text_expression?[?USING?expression?[,?...?]?]?LOOP
????statements
END?LOOP?[?label?]
這個例子類似前面的形式，只不過源查詢被指定為一個字符串表達式，在每次進入FOR循環(huán)時都會計算它并且重新規(guī)劃。更多關(guān)于大數(shù)據(jù) PostgreSQL 系列的學習文章，請參閱：PostgreSQL 數(shù)據(jù)庫，本系列持續(xù)更新中。
在使用EXECUTE時，可以通過USING將參數(shù)值插入到動態(tài)命令中。
-
foreach
FOREACH循環(huán)很像一個FOR循環(huán)，但不是通過一個 SQL 查詢返回的行進行迭代，它通過一個數(shù)組值的元素來迭代。
[?<<label>>?]
FOREACH?target?[?SLICE?number?]?IN?ARRAY?expression?LOOP
????statements
END?LOOP?[?label?];
具體例子不再給出，看看官方文檔中的例子。
CREATE?FUNCTION?sum(int[])?RETURNS?int8?AS?$$
DECLARE
??s?int8?:=?0;
??x?int;
BEGIN
??FOREACH?x?IN?ARRAY?$1
??LOOP
????s?:=?s?+?x;
??END?LOOP;
??RETURN?s;
END;
$$?LANGUAGE?plpgsql;
提示信息
官方文檔：
使用raise語句報告消息以及拋出錯誤，上面給的示例中已經(jīng)有部分給出，語法是。
RAISE?[?level?]?'format'?[,?expression?[,?...?]]?[?USING?option?=?expression?[,?...?]?];
RAISE?[?level?]?condition_name?[?USING?option?=?expression?[,?...?]?];
RAISE?[?level?]?SQLSTATE?'sqlstate'?[?USING?option?=?expression?[,?...?]?];
RAISE?[?level?]?USING?option?=?expression?[,?...?];
RAISE?;
level選項指定了錯誤的嚴重性。允許的級別有DEBUG、LOG、INFO、NOTICE, WARNING以及EXCEPTION，默認級別是EXCEPTION。
raise?log?‘這是日志消息’;?–?輸出在日志文件中??
raise?inof?‘這是一個信息’;?–?以下信息打印在控制臺??
raise?notice?‘這個是提示消息’;??
raise?notice?warning?‘這是個警告’;??
raise?exception?‘這個異常消息’;
調(diào)用存儲過程
當存儲過程編譯出來后，我們該如何執(zhí)行或者調(diào)用存儲過程呢?語法如下。
select?function_name();
select?*?from?function_name();
select?*?from?function_name?where?篩選條件；????--?當返回的結(jié)果為多條數(shù)據(jù)的結(jié)果集的時候
select?*?from?function_name()?as?tablename(column?name,column?type[,...])?;???--動態(tài)返回結(jié)果集
游標
PL/pgSQL 游標允許我們封裝一個查詢，然后每次處理結(jié)果集中的一條記錄。游標可以將大結(jié)果集拆分成許多小的記錄，避免內(nèi)存溢出；另外，我們可以定義一個返回游標引用的函數(shù)，然后調(diào)用程序可以基于這個引用處理返回的結(jié)果集。
使用游標的步驟大體如下：
-
聲明游標變量；
-
打開游標；
-
從游標中獲取結(jié)果；
-
判斷是否存在更多結(jié)果。如果存在，執(zhí)行第 3 步；否則，執(zhí)行第 5 步；
-
關(guān)閉游標。
我們直接通過一個示例演示使用游標的過程：
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 語句關(guān)閉游標，釋放資源。
游標是 PL/pgSQL 中的一個強大的數(shù)據(jù)處理功能，更多的使用方法可以參考官方文檔。
事務(wù)管理
在存儲過程內(nèi)部，可以使用 COMMIT 或者 ROLLBACK 語句提交或者回滾事務(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ù)才會被最終提交。更多關(guān)于大數(shù)據(jù) PostgreSQL 系列的學習文章，請參閱：PostgreSQL 數(shù)據(jù)庫，本系列持續(xù)更新中。
案例
以上就是存儲過程的基本語法，接下來再給幾個具體實例鞏固一下。
案例一：無查詢結(jié)果時，不用select，用perform。
CREATE?OR?REPLACE?FUNCTION?"public"."fun_etc"()
??RETURNS?"pg_catalog"."void"?AS?$BODY$
?
?BEGIN
?perform?current_date;
END
$BODY$
??LANGUAGE?plpgsql?VOLATILE
??COST?100
?
案例二：for查詢結(jié)果循環(huán)，聲明語法拷貝類型(拷貝一個行變量)
拷貝行類型，語法聲明：v_value table_name%ROWTYPE?。若是拷貝一個已有的列數(shù)據(jù)類型，語法：v_value variable%TYPE,類似declare b a.name%TYPE。
CREATE?OR?REPLACE?FUNCTION?"public"."fun_etc"()
??RETURNS?"pg_catalog"."text"?AS?$BODY$
?
?declare?b?a%rowtype;??
?
?BEGIN
?for?b?in?select?id,name?from?a?loop
???raise?info?'b的值=%',b;
???
??end?loop;
?
return?b.id||'---'||b.name;
?
END
$BODY$
??LANGUAGE?plpgsql?VOLATILE
??COST?100
?
信息結(jié)果：
?
return返回結(jié)果：return返回只會返回執(zhí)行的最后一個結(jié)果。
?
案例三：當sql中的語句有變量的時候，可以用||拼接，最后execute執(zhí)行。
CREATE?OR?REPLACE?FUNCTION?"public"."fun_etc"()
??RETURNS?"pg_catalog"."void"?AS?$BODY$
?
?declare?ifexists?integer;
?declare?sqltext?text;??
?declare?b?record;
?
?BEGIN
?--?判斷表是否存在，存在值=1，不存在值=0
?sqltext:='
??select?count(1)??from?pg_class?where?relname=''a_'||to_char(CURRENT_DATE,'yyyy_mm_dd')||'''';
?execute?sqltext?into?ifexists;
??
?--?判斷表是否存在，不存在則建表
?IF?ifexists=0?then
?
??--?建立一張新表
??sqltext:='
??
???create?table?"a_'||to_char(CURRENT_DATE,'yyyy_mm_dd')||'"?
???(
???create_time?date,
???id?int4,
???name?varchar(50)
???);';
???
??execute?sqltext;
?
?END?IF;
?
?--?從表a中查詢數(shù)據(jù)插入這表中
?sqltext?:='
?insert?into?a_'||to_char(CURRENT_DATE,'yyyy_mm_dd')||'?
?select?CURRENT_DATE,id,name?from?a
?';
?
?execute?sqltext;
?--?查詢顯示數(shù)據(jù)
?sqltext:=?'select?create_time,id,name?from?a_'||to_char(CURRENT_DATE,'yyyy_mm_dd')||';';
?for?b?in?execute?sqltext?loop
??raise?info?'%',b;
??
?end?loop;
?
?return?;
?
END
$BODY$
??LANGUAGE?plpgsql?VOLATILE
??COST?100
?
?
?
案例四：為了使函數(shù)更加通用，以解決動態(tài)返回數(shù)據(jù)集的問題，將表名作為參數(shù)傳進去。
CREATE?OR?REPLACE?FUNCTION?"public"."fun_etc"("name"?varchar)
??RETURNS?SETOF?"pg_catalog"."record"?AS?$BODY$
?
?declare?b?record;
?
?BEGIN
?
??for?b?in?execute?'select?*?from?'||name?loop
?
???return?next?b;
?end?loop;
?
?return?;
END
$BODY$
??LANGUAGE?plpgsql?VOLATILE
??COST?100
??ROWS?1000
select?*?from?fun_etc('a')?as?a(id?int,name?varchar(50));
結(jié)果顯示：
?文章來源：http://www.zghlxwxcb.cn/news/detail-774353.html
select?*?from?fun_etc('a_2022_12_21')?as?a(create_time?date,id?int,name?varchar(50));
結(jié)果顯示：
?
?
其他需要注意的是，我這邊都是在一個函數(shù)上修改，若是參數(shù)都一樣的話，他是會在原函數(shù)上修改的，所以你們別這樣啊，否則都白寫了。文章來源地址http://www.zghlxwxcb.cn/news/detail-774353.html
到了這里，關(guān)于進階數(shù)據(jù)庫系列（十一）：PostgreSQL 存儲過程的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
