PostgreSQL教程(二十)：PL/pgSQL過(guò)程語(yǔ)言
一、概述：
PL/pgSQL函數(shù)在第一次被調(diào)用時(shí)，其函數(shù)內(nèi)的源代碼(文本)將被解析為二進(jìn)制指令樹(shù)，但是函數(shù)內(nèi)的表達(dá)式和SQL命令只有在首次用到它們的時(shí)候，PL/pgSQL解釋器才會(huì)為其創(chuàng)建一個(gè)準(zhǔn)備好的執(zhí)行規(guī)劃，隨后對(duì)該表達(dá)式或SQL命令的訪問(wèn)都將使用該規(guī)劃。如果在一個(gè)條件語(yǔ)句中，有部分SQL命令或表達(dá)式?jīng)]有被用到，那么PL/pgSQL解釋器在本次調(diào)用中將不會(huì)為其準(zhǔn)備執(zhí)行規(guī)劃，這樣的好處是可以有效地減少為PL/pgSQL函數(shù)里的語(yǔ)句生成分析和執(zhí)行規(guī)劃的總時(shí)間，然而缺點(diǎn)是某些表達(dá)式或SQL命令中的錯(cuò)誤只有在其被執(zhí)行到的時(shí)候才能發(fā)現(xiàn)。
由于PL/pgSQL在函數(shù)里為一個(gè)命令制定了執(zhí)行計(jì)劃，那么在本次會(huì)話中該計(jì)劃將會(huì)被反復(fù)使用，這樣做往往可以得到更好的性能，但是如果你動(dòng)態(tài)修改了相關(guān)的數(shù)據(jù)庫(kù)對(duì)象，那么就有可能產(chǎn)生問(wèn)題，如：
CREATE FUNCTION populate() RETURNS integer AS $$
DECLARE
-- 聲明段
BEGIN
PERFORM my_function();
END;
$$ LANGUAGE plpgsql;
在調(diào)用以上函數(shù)時(shí)，PERFORM語(yǔ)句的執(zhí)行計(jì)劃將引用my_function對(duì)象的OID。在此之后，如果你重建了my_function函數(shù)，那么populate函數(shù)將無(wú)法再找到原有my_function函數(shù)的OID。要解決該問(wèn)題，可以選擇重建populate函數(shù)，或者重新登錄建立新的會(huì)話，以使PostgreSQL重新編譯該函數(shù)。要想規(guī)避此類(lèi)問(wèn)題的發(fā)生，在重建my_function時(shí)可以使用CREATE OR REPLACE FUNCTION命令。
鑒于以上規(guī)則，在PL/pgSQL里直接出現(xiàn)的SQL命令必須在每次執(zhí)行時(shí)均引用相同的表和字段，換句話說(shuō)，不能將函數(shù)的參數(shù)用作SQL命令的表名或字段名。如果想繞開(kāi)該限制，可以考慮使用PL/pgSQL中的EXECUTE語(yǔ)句動(dòng)態(tài)地構(gòu)造命令，由此換來(lái)的代價(jià)是每次執(zhí)行時(shí)都要構(gòu)造一個(gè)新的命令計(jì)劃。
使用PL/pgSQL函數(shù)的一個(gè)非常重要的優(yōu)勢(shì)是可以提高程序的執(zhí)行效率，由于原有的SQL調(diào)用不得不在客戶端與服務(wù)器之間反復(fù)傳遞數(shù)據(jù)，這樣不僅增加了進(jìn)程間通訊所產(chǎn)生的開(kāi)銷(xiāo)，而且也會(huì)大大增加網(wǎng)絡(luò)IO的開(kāi)銷(xiāo)。
二、PL/pgSQL的結(jié)構(gòu)：
PL/pgSQL是一種塊結(jié)構(gòu)語(yǔ)言，函數(shù)定義的所有文本都必須在一個(gè)塊內(nèi)，其中塊中的每個(gè)聲明和每條語(yǔ)句都是以分號(hào)結(jié)束，如果某一子塊在另外一個(gè)塊內(nèi)，那么該子塊的END關(guān)鍵字后面必須以分號(hào)結(jié)束，不過(guò)對(duì)于函數(shù)體的最后一個(gè)END關(guān)鍵字，分號(hào)可以省略，如：
[ <<label>> ]
[ DECLARE declarations ]
BEGIN
statements
END [ label ];
在PL/pgSQL中有兩種注釋類(lèi)型，雙破折號(hào)(--)表示單行注釋。/* */表示多行注釋，該注釋類(lèi)型的規(guī)則等同于C語(yǔ)言中的多行注釋。
在語(yǔ)句塊前面的聲明段中定義的變量在每次進(jìn)入語(yǔ)句塊(BEGIN)時(shí)都會(huì)將聲明的變量初始化為它們的缺省值，而不是每次函數(shù)調(diào)用時(shí)初始化一次。如：
CREATE FUNCTION somefunc() RETURNS integer AS $$
DECLARE
quantity integer := 30;
BEGIN
RAISE NOTICE 'Quantity here is %', quantity; --在這里的數(shù)量是30
quantity := 50;
--
-- 創(chuàng)建一個(gè)子塊
--
DECLARE
quantity integer := 80;
BEGIN
RAISE NOTICE 'Quantity here is %', quantity; --在這里的數(shù)量是80
END;
RAISE NOTICE 'Quantity here is %', quantity; --在這里的數(shù)量是50
RETURN quantity;
END;
$$ LANGUAGE plpgsql;
#執(zhí)行該函數(shù)以進(jìn)一步觀察其執(zhí)行的結(jié)果。
postgres=# select somefunc();
NOTICE: Quantity here is 30
NOTICE: Quantity here is 80
NOTICE: Quantity here is 50
somefunc
----------
50
(1 row)
最后需要說(shuō)明的是，目前版本的PostgreSQL并不支持嵌套事務(wù)，函數(shù)中的事物總是由外層命令(函數(shù)的調(diào)用者)來(lái)控制的，它們本身無(wú)法開(kāi)始或提交事務(wù)。
三、聲明：
所有在塊里使用的變量都必須在塊的聲明段里先進(jìn)行聲明，唯一的例外是FOR循環(huán)里的循環(huán)計(jì)數(shù)變量，該變量被自動(dòng)聲明為整型。變量聲明的語(yǔ)法如下：
variable_name [ CONSTANT ] variable_type [ NOT NULL ] [ { DEFAULT | := } expression ];
1). SQL中的數(shù)據(jù)類(lèi)型均可作為PL/pgSQL變量的數(shù)據(jù)類(lèi)型，如integer、varchar和char等。
2). 如果給出了DEFAULT子句，該變量在進(jìn)入BEGIN塊時(shí)將被初始化為該缺省值，否則被初始化為SQL空值。缺省值是在每次進(jìn)入該塊時(shí)進(jìn)行計(jì)算的。因此，如果把now()賦予一個(gè)類(lèi)型為timestamp的變量，那么該變量的缺省值將為函數(shù)實(shí)際調(diào)用時(shí)的時(shí)間，而不是函數(shù)預(yù)編譯時(shí)的時(shí)間。
3). CONSTANT選項(xiàng)是為了避免該變量在進(jìn)入BEGIN塊后被重新賦值，以保證該變量為常量。
4). 如果聲明了NOT NULL，那么賦予NULL數(shù)值給該變量將導(dǎo)致一個(gè)運(yùn)行時(shí)錯(cuò)誤。因此所有聲明為NOT NULL的變量也必須在聲明時(shí)定義一個(gè)非空的缺省值。
1. 函數(shù)參數(shù)的別名：
傳遞給函數(shù)的參數(shù)都是用$1、$2這樣的標(biāo)識(shí)符來(lái)表示的。為了增加可讀性，我們可以為其聲明別名。之后別名和數(shù)字標(biāo)識(shí)符均可指向該參數(shù)值，見(jiàn)如下示例：
1). 在函數(shù)聲明的同時(shí)給出參數(shù)變量名。
CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$
BEGIN
RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;
2). 在聲明段中為參數(shù)變量定義別名。
CREATE FUNCTION sales_tax(REAL) RETURNS real AS $$
DECLARE
subtotal ALIAS FOR $1;
BEGIN
RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;
3). 對(duì)于輸出參數(shù)而言，我們?nèi)匀豢梢宰袷?)和2)中的規(guī)則。
CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$
BEGIN
tax := subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;
4). 如果PL/pgSQL函數(shù)的返回類(lèi)型為多態(tài)類(lèi)型(anyelement或anyarray)，那么函數(shù)就會(huì)創(chuàng)建一個(gè)特殊的參數(shù)：$0。我們?nèi)匀豢梢詾樵撟兞吭O(shè)置別名。
CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement)
RETURNS anyelement AS $$
DECLARE
result ALIAS FOR $0;
BEGIN
result := v1 + v2 + v3;
RETURN result;
END;
$$ LANGUAGE plpgsql;
2. 拷貝類(lèi)型：
見(jiàn)如下形式的變量聲明：
variable%TYPE
%TYPE表示一個(gè)變量或表字段的數(shù)據(jù)類(lèi)型，PL/pgSQL允許通過(guò)該方式聲明一個(gè)變量，其類(lèi)型等同于variable或表字段的數(shù)據(jù)類(lèi)型，見(jiàn)如下示例：
user_id users.user_id%TYPE;
在上面的例子中，變量user_id的數(shù)據(jù)類(lèi)型等同于users表中user_id字段的類(lèi)型。
通過(guò)使用%TYPE，一旦引用的變量類(lèi)型今后發(fā)生改變，我們也無(wú)需修改該變量的類(lèi)型聲明。最后需要說(shuō)明的是，我們可以在函數(shù)的參數(shù)和返回值中使用該方式的類(lèi)型聲明。
3. 行類(lèi)型：
見(jiàn)如下形式的變量聲明：
name table_name%ROWTYPE;
name composite_type_name;
table_name%ROWTYPE表示指定表的行類(lèi)型，我們?cè)趧?chuàng)建一個(gè)表的時(shí)候，PostgreSQL也會(huì)隨之創(chuàng)建出一個(gè)與之相應(yīng)的復(fù)合類(lèi)型，該類(lèi)型名等同于表名，因此，我們可以通過(guò)以上兩種方式來(lái)聲明行類(lèi)型的變量。由此方式聲明的變量，可以保存SELECT返回結(jié)果中的一行。如果要訪問(wèn)變量中的某個(gè)域字段，可以使用點(diǎn)表示法，如rowvar.field，但是行類(lèi)型的變量只能訪問(wèn)自定義字段，無(wú)法訪問(wèn)系統(tǒng)提供的隱含字段，如OID等。對(duì)于函數(shù)的參數(shù)，我們只能使用復(fù)合類(lèi)型標(biāo)識(shí)變量的數(shù)據(jù)類(lèi)型。最后需要說(shuō)明的是，推薦使用%ROWTYPE的聲明方式，這樣可以具有更好的可移植性，因?yàn)樵贠racle的PL/SQL中也存在相同的概念，其聲明方式也為%ROWTYPE。見(jiàn)如下示例：
CREATE FUNCTION merge_fields(t_row table1) RETURNS text AS $$
DECLARE
t2_row table2%ROWTYPE;
BEGIN
SELECT * INTO t2_row FROM table2 WHERE id = 1 limit 1;
RETURN t_row.f1 || t2_row.f3 || t_row.f5 || t2_row.f7;
END;
$$ LANGUAGE plpgsql;
4. 記錄類(lèi)型：
見(jiàn)如下形式的變量聲明：
name RECORD;
記錄變量類(lèi)似于行類(lèi)型變量，但是它們沒(méi)有預(yù)定義的結(jié)構(gòu)，只能通過(guò)SELECT或FOR命令來(lái)獲取實(shí)際的行結(jié)構(gòu)，因此記錄變量在被初始化之前無(wú)法訪問(wèn)，否則將引發(fā)運(yùn)行時(shí)錯(cuò)誤。
注：RECORD不是真正的數(shù)據(jù)類(lèi)型，只是一個(gè)占位符。
四、基本語(yǔ)句：
1. 賦值：
PL/pgSQL中賦值語(yǔ)句的形式為：identIFier := expression，等號(hào)兩端的變量和表達(dá)式的類(lèi)型或者一致，或者可以通過(guò)PostgreSQL的轉(zhuǎn)換規(guī)則進(jìn)行轉(zhuǎn)換，否則將會(huì)導(dǎo)致運(yùn)行時(shí)錯(cuò)誤，見(jiàn)如下示例：
user_id := 20;
tax := subtotal * 0.06;
2. SELECT INTO：
通過(guò)該語(yǔ)句可以為記錄變量或行類(lèi)型變量進(jìn)行賦值，其表現(xiàn)形式為：SELECT INTO target select_expressions FROM ...，該賦值方式一次只能賦值一個(gè)變量。表達(dá)式中的target可以表示為是一個(gè)記錄變量、行變量，或者是一組用逗號(hào)分隔的簡(jiǎn)單變量和記錄/行字段的列表。select_expressions以及剩余部分和普通SQL一樣。
如果將一行或者一個(gè)變量列表用做目標(biāo)，那么選出的數(shù)值必需精確匹配目標(biāo)的結(jié)構(gòu)，否則就會(huì)產(chǎn)生運(yùn)行時(shí)錯(cuò)誤。如果目標(biāo)是一個(gè)記錄變量，那么它自動(dòng)將自己構(gòu)造成命令結(jié)果列的行類(lèi)型。如果命令返回零行，目標(biāo)被賦予空值。如果命令返回多行，那么將只有第一行被賦予目標(biāo)，其它行將被忽略。在執(zhí)行SELECT INTO語(yǔ)句之后，可以通過(guò)檢查內(nèi)置變量FOUND來(lái)判斷本次賦值是否成功，如：
SELECT INTO myrec * FROM emp WHERE empname = myname;
IF NOT FOUND THEN
RAISE EXCEPTION 'employee % not found', myname;
END IF;
要測(cè)試一個(gè)記錄/行結(jié)果是否為空，可以使用IS NULL條件進(jìn)行判斷，但是對(duì)于返回多條記錄的情況則無(wú)法判斷，如：
DECLARE
users_rec RECORD;
BEGIN
SELECT INTO users_rec * FROM users WHERE user_id = 3;
IF users_rec.homepage IS NULL THEN
RETURN 'http://';
END IF;
END;
3. 執(zhí)行一個(gè)沒(méi)有結(jié)果的表達(dá)式或者命令：
在調(diào)用一個(gè)表達(dá)式或執(zhí)行一個(gè)命令時(shí)，如果對(duì)其返回的結(jié)果不感興趣，可以考慮使用PERFORM語(yǔ)句：PERFORM query，該語(yǔ)句將執(zhí)行PERFORM之后的命令并忽略其返回的結(jié)果。其中query的寫(xiě)法和普通的SQL SELECT命令是一樣的，只是把開(kāi)頭的關(guān)鍵字SELECT替換成PERFORM，如：
PERFORM create_mv('cs_session_page_requests_mv', my_query);
4. 執(zhí)行動(dòng)態(tài)命令：
如果在PL/pgSQL函數(shù)中操作的表或數(shù)據(jù)類(lèi)型在每次調(diào)用該函數(shù)時(shí)都可能會(huì)發(fā)生變化，在這樣的情況下，可以考慮使用PL/pgSQL提供的EXECUTE語(yǔ)句：EXECUTE command-string [ INTO target ]，其中command-string是用一段文本表示的表達(dá)式，它包含要執(zhí)行的命令。而target是一個(gè)記錄變量、行變量或者一組用逗號(hào)分隔的簡(jiǎn)單變量和記錄/行域的列表。這里需要特別注意的是，該命令字符串將不會(huì)發(fā)生任何PL/pgSQL變量代換，變量的數(shù)值必需在構(gòu)造命令字符串時(shí)插入到該字符串中。
和所有其它PL/pgSQL命令不同的是，一個(gè)由EXECUTE語(yǔ)句運(yùn)行的命令在服務(wù)器內(nèi)并不會(huì)只prepare和保存一次。相反，該語(yǔ)句在每次運(yùn)行的時(shí)候，命令都會(huì)prepare一次。因此命令字符串可以在函數(shù)里動(dòng)態(tài)的生成以便于對(duì)各種不同的表和字段進(jìn)行操作，從而提高函數(shù)的靈活性。然而由此換來(lái)的卻是性能上的折損。見(jiàn)如下示例：
EXECUTE 'UPDATE tbl SET ' || quote_ident(columnname) || ' = ' || quote_literal(newvalue);
五、控制結(jié)構(gòu)：
1. 函數(shù)返回：
1). RETURN expression
該表達(dá)式用于終止當(dāng)前的函數(shù)，然后再將expression的值返回給調(diào)用者。如果返回簡(jiǎn)單類(lèi)型，那么可以使用任何表達(dá)式，同時(shí)表達(dá)式的類(lèi)型也將被自動(dòng)轉(zhuǎn)換成函數(shù)的返回類(lèi)型，就像我們?cè)谫x值中描述的那樣。如果要返回一個(gè)復(fù)合類(lèi)型的數(shù)值，則必須讓表達(dá)式返回記錄或者匹配的行變量。
2). RETURN NEXT expression
如果PL/pgSQL函數(shù)聲明為返回SETOF sometype，其行記錄是通過(guò)RETURN NEXT命令進(jìn)行填充的，直到執(zhí)行到不帶參數(shù)的RETURN時(shí)才表示該函數(shù)結(jié)束。因此對(duì)于RETURN NEXT而言，它實(shí)際上并不從函數(shù)中返回，只是簡(jiǎn)單地把表達(dá)式的值保存起來(lái)，然后繼續(xù)執(zhí)行PL/pgSQL函數(shù)里的下一條語(yǔ)句。隨著RETURN NEXT命令的迭代執(zhí)行，結(jié)果集最終被建立起來(lái)。該類(lèi)函數(shù)的調(diào)用方式如下：
SELECT * FROM some_func();
它被放在FROM子句中作為數(shù)據(jù)源使用。最后需要指出的是，如果結(jié)果集數(shù)量很大，那么通過(guò)該種方式來(lái)構(gòu)建結(jié)果集將會(huì)導(dǎo)致極大的性能損失。
2. 條件：
在PL/pgSQL中有以下三種形式的條件語(yǔ)句。
1). IF-THEN
IF boolean-expression THEN
statements
END IF;
2). IF-THEN-ELSE
IF boolean-expression THEN
statements
ELSE
statements
END IF;
3). IF-THEN-ELSIF-ELSE
IF boolean-expression THEN
statements
ELSIF boolean-expression THEN
statements
ELSIF boolean-expression THEN
statements
ELSE
statements
END IF;
關(guān)于條件語(yǔ)句，這里就不在做過(guò)多的贅述了。
3. 循環(huán)：
1). LOOP
LOOP
statements
END LOOP [ label ];
LOOP定義一個(gè)無(wú)條件的循環(huán)，直到由EXIT或者RETURN語(yǔ)句終止。可選的label可以由EXIT和CONTINUE語(yǔ)句使用，用于在嵌套循環(huán)中聲明應(yīng)該應(yīng)用于哪一層循環(huán)。
2). EXIT
EXIT [ label ] [ WHEN expression ];
如果沒(méi)有給出label，就退出最內(nèi)層的循環(huán)，然后執(zhí)行跟在END LOOP后面的語(yǔ)句。如果給出label，它必須是當(dāng)前或更高層的嵌套循環(huán)塊或語(yǔ)句塊的標(biāo)簽。之后該命名塊或循環(huán)就會(huì)終止，而控制則直接轉(zhuǎn)到對(duì)應(yīng)循環(huán)/塊的END語(yǔ)句后面的語(yǔ)句上。
如果聲明了WHEN，EXIT命令只有在expression為真時(shí)才被執(zhí)行，否則將直接執(zhí)行EXIT后面的語(yǔ)句。見(jiàn)如下示例：
LOOP
-- do something
EXIT WHEN count > 0;
END LOOP;
3). CONTINUE
CONTINUE [ label ] [ WHEN expression ];
如果沒(méi)有給出label，CONTINUE就會(huì)跳到最內(nèi)層循環(huán)的開(kāi)始處，重新進(jìn)行判斷，以決定是否繼續(xù)執(zhí)行循環(huán)內(nèi)的語(yǔ)句。如果指定label，則跳到該label所在的循環(huán)開(kāi)始處。如果聲明了WHEN，CONTINUE命令只有在expression為真時(shí)才被執(zhí)行，否則將直接執(zhí)行CONTINUE后面的語(yǔ)句。見(jiàn)如下示例：
LOOP
-- do something
EXIT WHEN count > 100;
CONTINUE WHEN count < 50;
END LOOP;
4). WHILE
[ <<label>> ]
WHILE expression LOOP
statements
END LOOP [ label ];
只要條件表達(dá)式為真，其塊內(nèi)的語(yǔ)句就會(huì)被循環(huán)執(zhí)行。條件是在每次進(jìn)入循環(huán)體時(shí)進(jìn)行判斷的。見(jiàn)如下示例：
WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
--do something
END LOOP;
5). FOR
[ <<label>> ]
FOR name IN [ REVERSE ] expression .. expression LOOP
statements
END LOOP [ label ];
變量name自動(dòng)被定義為integer類(lèi)型，其作用域僅為FOR循環(huán)的塊內(nèi)。表示范圍上下界的兩個(gè)表達(dá)式只在進(jìn)入循環(huán)時(shí)計(jì)算一次。每次迭代name值自增1，但如果聲明了REVERSE，name變量在每次迭代中將自減1，見(jiàn)如下示例：
FOR i IN 1..10 LOOP
--do something
RAISE NOTICE 'i IS %', i;
END LOOP;
FOR i IN REVERSE 10..1 LOOP
--do something
END LOOP;
4. 遍歷命令結(jié)果：
[ <<label>> ]
FOR record_or_row IN query LOOP
statements
END LOOP [ label ];
這是另外一種形式的FOR循環(huán)，在該循環(huán)中可以遍歷命令的結(jié)果并操作相應(yīng)的數(shù)據(jù)，見(jiàn)如下示例：
FOR rec IN SELECT * FROM some_table LOOP
PERFORM some_func(rec.one_col);
END LOOP;
PL/pgSQL還提供了另外一種遍歷命令結(jié)果的方式，和上面的方式相比，唯一的差別是該方式將SELECT語(yǔ)句存于字符串文本中，然后再交由EXECUTE命令動(dòng)態(tài)的執(zhí)行。和前一種方式相比，該方式的靈活性更高，但是效率較低。
[ <<label>> ]
FOR record_or_row IN EXECUTE text_expression LOOP
statements
END LOOP [ label ];
5. 異常捕獲：
在PL/pgSQL函數(shù)中，如果沒(méi)有異常捕獲，函數(shù)會(huì)在發(fā)生錯(cuò)誤時(shí)直接退出，與其相關(guān)的事物也會(huì)隨之回滾。我們可以通過(guò)使用帶有EXCEPTION子句的BEGIN塊來(lái)捕獲異常并使其從中恢復(fù)。見(jiàn)如下聲明形式：
[ <<label>> ]
[ DECLARE
declarations ]
BEGIN
statements
EXCEPTION
WHEN condition [ OR condition ... ] THEN
handler_statements
WHEN condition [ OR condition ... ] THEN
handler_statements
END;
如果沒(méi)有錯(cuò)誤發(fā)生，只有BEGIN塊中的statements會(huì)被正常執(zhí)行，然而一旦這些語(yǔ)句中有任意一條發(fā)生錯(cuò)誤，其后的語(yǔ)句都將被跳過(guò)，直接跳轉(zhuǎn)到EXCEPTION塊的開(kāi)始處。此時(shí)系統(tǒng)將搜索異常條件列表，尋找匹配該異常的第一個(gè)條件，如果找到匹配，則執(zhí)行相應(yīng)的handler_statements，之后再執(zhí)行END的下一條語(yǔ)句。如果沒(méi)有找到匹配，該錯(cuò)誤就會(huì)被繼續(xù)向外拋出，其結(jié)果與沒(méi)有EXCEPTION子句完全等同。如果此時(shí)handler_statements中的語(yǔ)句發(fā)生新錯(cuò)誤，它將不能被該EXCEPTION子句捕獲，而是繼續(xù)向外傳播，交由其外層的EXCEPTION子句捕獲并處理。見(jiàn)如下示例：
INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');
BEGIN
UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';
x := x + 1;
y := x / 0;
EXCEPTION
WHEN division_by_zero THEN
RAISE NOTICE 'caught division_by_zero';
RETURN x;
END;
當(dāng)以上函數(shù)執(zhí)行到y(tǒng) := x / 0語(yǔ)句時(shí)，將會(huì)引發(fā)一個(gè)異常錯(cuò)誤，代碼將跳轉(zhuǎn)到EXCEPTION塊的開(kāi)始處，之后系統(tǒng)會(huì)尋找匹配的異常捕捉條件，此時(shí)division_by_zero完全匹配，這樣該條件內(nèi)的代碼將會(huì)被繼續(xù)執(zhí)行。需要說(shuō)明的是，RETURN語(yǔ)句中返回的x值為x := x + 1執(zhí)行后的新值，但是在除零之前的update語(yǔ)句將會(huì)被回滾，BEGIN之前的insert語(yǔ)句將仍然生效。
六、游標(biāo)：
1. 聲明游標(biāo)變量：
在PL/pgSQL中對(duì)游標(biāo)的訪問(wèn)都是通過(guò)游標(biāo)變量實(shí)現(xiàn)的，其數(shù)據(jù)類(lèi)型為refcursor。 創(chuàng)建游標(biāo)變量的方法有以下兩種：
1). 和聲明其他類(lèi)型的變量一樣，直接聲明一個(gè)游標(biāo)類(lèi)型的變量即可。
2). 使用游標(biāo)專有的聲明語(yǔ)法，如：
name CURSOR [ ( arguments ) ] FOR query;
其中arguments為一組逗號(hào)分隔的name datatype列表，見(jiàn)如下示例：
curs1 refcursor;
curs2 CURSOR FOR SELECT * FROM tenk1;
curs3 CURSOR (key integer) IS SELECT * FROM tenk1 WHERE unique1 = key;
在上面三個(gè)例子中，只有第一個(gè)是未綁定游標(biāo)，剩下兩個(gè)游標(biāo)均已被綁定。
2. 打開(kāi)游標(biāo)：
游標(biāo)在使用之前必須先被打開(kāi)，在PL/pgSQL中有三種形式的OPEN語(yǔ)句，其中兩種用于未綁定的游標(biāo)變量，另外一種用于綁定的游標(biāo)變量。
1). OPEN FOR:
其聲明形式為：
OPEN unbound_cursor FOR query;
該形式只能用于未綁定的游標(biāo)變量，其查詢語(yǔ)句必須是SELECT，或其他返回記錄行的語(yǔ)句，如EXPLAIN。在PostgreSQL中，該查詢和普通的SQL命令平等對(duì)待，即先替換變量名，同時(shí)也將該查詢的執(zhí)行計(jì)劃緩存起來(lái)，以供后用。見(jiàn)如下示例：
OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;
2). OPEN FOR EXECUTE
其聲明形式為：
OPEN unbound_cursor FOR EXECUTE query-string;
和上面的形式一樣，該形式也僅適用于未綁定的游標(biāo)變量。EXECUTE將動(dòng)態(tài)執(zhí)行其后以文本形式表示的查詢字符串。
OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);
3). 打開(kāi)一個(gè)綁定的游標(biāo)
其聲明形式為：
OPEN bound_cursor [ ( argument_values ) ];
該形式僅適用于綁定的游標(biāo)變量，只有當(dāng)該變量在聲明時(shí)包含接收參數(shù)，才能以傳遞參數(shù)的形式打開(kāi)該游標(biāo)，這些參數(shù)將被實(shí)際代入到游標(biāo)聲明的查詢語(yǔ)句中，見(jiàn)如下示例：
OPEN curs2;
OPEN curs3(42);
3. 使用游標(biāo)：
游標(biāo)一旦打開(kāi)，就可以按照以下方式進(jìn)行讀取。然而需要說(shuō)明的是，游標(biāo)的打開(kāi)和讀取必須在同一個(gè)事物內(nèi)，因?yàn)樵赑ostgreSQL中，如果事物結(jié)束，事物內(nèi)打開(kāi)的游標(biāo)將會(huì)被隱含的關(guān)閉。
1). FETCH
其聲明形式為：
FETCH cursor INTO target;
FETCH命令從游標(biāo)中讀取下一行記錄的數(shù)據(jù)到目標(biāo)中，其中目標(biāo)可以是行變量、記錄變量，或者是一組逗號(hào)分隔的普通變量的列表，讀取成功與否，可通過(guò)PL/pgSQL內(nèi)置變量FOUND來(lái)判斷，其規(guī)則等同于SELECT INTO。見(jiàn)如下示例：
FETCH curs1 INTO rowvar; --rowvar為行變量
FETCH curs2 INTO foo, bar, baz;
2). CLOSE
其聲明形式為：
CLOSE cursor;
關(guān)閉當(dāng)前已經(jīng)打開(kāi)的游標(biāo)，以釋放其占有的系統(tǒng)資源，見(jiàn)如下示例：
CLOSE curs1;
七、錯(cuò)誤和消息：
在PostgreSQL中可以利用RAISE語(yǔ)句報(bào)告信息和拋出錯(cuò)誤，其聲明形式為：
RAISE level 'format' [, expression [, ...]];
這里包含的級(jí)別有DEBUG(向服務(wù)器日志寫(xiě)信息)、LOG(向服務(wù)器日志寫(xiě)信息，優(yōu)先級(jí)更高)、INFO、NOTICE和WARNING(把信息寫(xiě)到服務(wù)器日志以及轉(zhuǎn)發(fā)到客戶端應(yīng)用，優(yōu)先級(jí)逐步升高)和EXCEPTION拋出一個(gè)錯(cuò)誤(通常退出當(dāng)前事務(wù))。某個(gè)優(yōu)先級(jí)別的信息是報(bào)告給客戶端還是寫(xiě)到服務(wù)器日志，還是兩個(gè)均有，是由log_min_messages和client_min_messages這兩個(gè)系統(tǒng)初始化參數(shù)控制的。
在format部分中，%表示為占位符，其實(shí)際值僅在RAISE命令執(zhí)行時(shí)由后面的變量替換，如果要在format中表示%自身，可以使用%%的形式表示，見(jiàn)如下示例：
RAISE NOTICE 'Calling cs_create_job(%)',v_job_id; --v_job_id變量的值將替換format中的%。
RAISE EXCEPTION 'Inexistent ID --> %',user_id;
相關(guān)文章
PostgreSQL創(chuàng)建自增序列、查詢序列及使用序列代碼示例
數(shù)據(jù)庫(kù)中主鍵的生成一般是通過(guò)序列來(lái)生成,下面這篇文章主要給大家介紹了關(guān)于PostgreSQL創(chuàng)建自增序列、查詢序列及使用序列的相關(guān)資料,文中通過(guò)代碼介紹的非常詳細(xì),需要的朋友可以參考下2023-11-11
淺談postgresql數(shù)據(jù)庫(kù)varchar、char、text的比較
這篇文章主要介紹了淺談postgresql數(shù)據(jù)庫(kù)varchar、char、text的比較，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2020-12-12
基于PostgreSQL和mysql數(shù)據(jù)類(lèi)型對(duì)比兼容
這篇文章主要介紹了基于PostgreSQL和mysql數(shù)據(jù)類(lèi)型對(duì)比兼容，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2020-12-12
PostgreSQL 實(shí)現(xiàn)登錄及修改密碼操作
這篇文章主要介紹了PostgreSQL 實(shí)現(xiàn)登錄及修改密碼操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01
PostgreSQL實(shí)時(shí)查看數(shù)據(jù)庫(kù)實(shí)例正在執(zhí)行的SQL語(yǔ)句實(shí)例詳解
在任何數(shù)據(jù)庫(kù)中,分析和優(yōu)化SQL的執(zhí)行,最重要的工作就是執(zhí)行計(jì)劃的解讀,而說(shuō)到執(zhí)行計(jì)劃得先了解postgresql的查詢執(zhí)行過(guò)程,下面這篇文章主要給大家介紹了關(guān)于PostgreSQL實(shí)時(shí)查看數(shù)據(jù)庫(kù)實(shí)例正在執(zhí)行的SQL語(yǔ)句的相關(guān)資料,需要的朋友可以參考下2023-01-01
postgresql中wal_level的三個(gè)參數(shù)用法說(shuō)明
這篇文章主要介紹了postgresql中wal_level的三個(gè)參數(shù)用法說(shuō)明，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2020-12-12
PostgreSQL 實(shí)現(xiàn)sql放入文件批量執(zhí)行
這篇文章主要介紹了PostgreSQL 實(shí)現(xiàn)sql放入文件批量執(zhí)行，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-02-02
postgresql 切換 log、xlog日志的實(shí)現(xiàn)
這篇文章主要介紹了postgresql 切換 log、xlog日志的實(shí)現(xiàn)方式，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01
PostgreSQL教程(四)：數(shù)據(jù)類(lèi)型詳解
這篇文章主要介紹了PostgreSQL教程(四)：數(shù)據(jù)類(lèi)型詳解,本文講解了數(shù)值類(lèi)型、字符類(lèi)型、布爾類(lèi)型、位串類(lèi)型、數(shù)組、復(fù)合類(lèi)型等數(shù)據(jù)類(lèi)型,需要的朋友可以參考下2015-05-05
這篇文章主要介紹了postgresql 啟動(dòng)與停止操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01