?
本文檔使用
php中文網(wǎng)手冊 發(fā)布
39.6. 控制結(jié)構(gòu)
控制結(jié)構(gòu)可能是PL/pgSQL中最有用的(以及最重要)的部分了。 利用PL/pgSQL的控制結(jié)構(gòu)，你可以以非常靈活而且強大的方法操縱PostgreSQL的數(shù)據(jù)。
39.6.1. 從函數(shù)返回
有兩個命令可以用來從函數(shù)中返回數(shù)據(jù)：RETURN和RETURN NEXT。
39.6.1.1. RETURN
RETURN expression;
帶表達(dá)式的RETURN用于終止函數(shù)并把expression的值返回給調(diào)用者。 這種形式用于不返回集合的PL/pgSQL函數(shù)。
如果返回標(biāo)量類型，那么可以使用任何表達(dá)式。 表達(dá)式的類型將被自動轉(zhuǎn)換成函數(shù)的返回類型，就像在賦值中描述的那樣。 要返回一個復(fù)合(行)數(shù)值，你必須寫一個記錄或者行變量的expression。
如果你聲明函數(shù)帶輸出參數(shù)，那么就只需要寫無表達(dá)式的RETURN。 那么輸出參數(shù)變量的當(dāng)前值將被返回。
如果你聲明函數(shù)返回void，那么一個RETURN語句可以用于提前退出函數(shù)； 但是不要在RETURN后面寫一個表達(dá)式。
一個函數(shù)的返回值不能是未定義。 如果控制到達(dá)了函數(shù)最頂層的塊而沒有碰到一個RETURN語句，那么它就會發(fā)生一個錯誤。 不過，這個限制不適用于帶輸出參數(shù)的函數(shù)以及那些返回void的函數(shù)。 在這些例子里，如果頂層的塊結(jié)束，則自動執(zhí)行一個RETURN語句。
39.6.1.2. RETURN NEXT和RETURN QUERY
RETURN NEXT expression;
RETURN QUERY query;
RETURN QUERY EXECUTE command-string [ USING expression [, ... ] ];
如果一個PL/pgSQL函數(shù)聲明為返回SETOFsometype，那么遵循的過程則略有不同。 在這種情況下，要返回的獨立項是在 RETURN NEXT或RETURN QUERY命令里聲明的， 然后最后有一個不帶參數(shù)的RETURN命令用于告訴這個函數(shù)已經(jīng)完成執(zhí)行了。 RETURN NEXT可以用于標(biāo)量和復(fù)合數(shù)據(jù)類型；對于復(fù)合類型，將返回一個完整的結(jié)果"table"。 RETURN QUERY命令將一條查詢的結(jié)果追加到一個函數(shù)的結(jié)果集中。 RETURN NEXT和RETURN QUERY RETURN QUERY appends the results of executing a query to the function's result set. RETURN NEXT and RETURN QUERY can be freely intermixed in a single set-returning function, in which case their results will be concatenated.
RETURN NEXT和RETURN QUERY實際上不會從函數(shù)中返回，它們是將0或者多個行追加到函數(shù)的結(jié)果集中。 然后執(zhí)行繼續(xù)執(zhí)行PL/pgSQL函數(shù)里的下一條語句。 隨著后繼的RETURN NEXT和RETURN QUERY命令的執(zhí)行，結(jié)果集就建立起來了。 最后一個RETURN應(yīng)該沒有參數(shù)， 它導(dǎo)致控制退出該函數(shù)(或者你可以簡單地讓控制到達(dá)函數(shù)的結(jié)尾)。
RETURN QUERY has a variant RETURN QUERY EXECUTE, which specifies the query to be executed dynamically. Parameter expressions can be inserted into the computed query string via USING, in just the same way as in the EXECUTE command.
If you declared the function with output parameters, write just RETURN NEXT with no expression. On each execution, the current values of the output parameter variable(s) will be saved for eventual return as a row of the result. Note that you must declare the function as returning SETOF record when there are multiple output parameters, or SETOF sometype when there is just one output parameter of type sometype, in order to create a set-returning. function with output parameters. 如果你聲明函數(shù)帶有輸出參數(shù)，那么就只需要寫不帶表達(dá)式的 RETURN NEXT 。 輸出參數(shù)的當(dāng)前值將被保存，用于最終返回。 請注意如果有多個輸出參數(shù)， 比如聲明函數(shù)為返回 SETOF record 或者是在只有一個類型為 sometype 的輸出參數(shù)時聲明為 SETOF sometype ， 這樣才能創(chuàng)建一個帶有輸出參數(shù)的返回集合的函數(shù)。
下面是一個使用RETURN NEXT的函數(shù)例子:
CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT);
INSERT INTO foo VALUES (1, 2, 'three');
INSERT INTO foo VALUES (4, 5, 'six');
CREATE OR REPLACE FUNCTION getAllFoo() RETURNS SETOF foo AS
$body$
DECLARE
r foo%rowtype;
BEGIN
FOR r IN SELECT * FROM foo
WHERE fooid > 0
LOOP
-- can do some processing here
RETURN NEXT r; -- return current row of SELECT
END LOOP;
RETURN;
END
$body$
LANGUAGE 'plpgsql' ;
SELECT * FROM getallfoo();
Note: 目前RETURN NEXT和RETURN QUERY實現(xiàn)在從函數(shù)返回之前把整個結(jié)果集都保存起來，就像上面描述的那樣。 這意味著如果一個PL/pgSQL函數(shù)生成一個非常大的結(jié)果集，性能可能會很差： 數(shù)據(jù)將被寫到磁盤上以避免內(nèi)存耗盡，但是函數(shù)在完成整個結(jié)果集的生成之前不會退出。 將來的PL/pgSQL版本可能會允許用戶定義沒有這樣限制的返回集合的函數(shù)。 目前，數(shù)據(jù)開始向磁盤里寫的時刻是由配置變量work_mem控制的。 擁有足夠內(nèi)存的管理員如果想在內(nèi)存里存儲更大的結(jié)果集，則可以考慮把這個參數(shù)增大一些。
39.6.2. 條件
IF和CASE語句讓你可以根據(jù)某種條件執(zhí)行命令。PL/pgSQL有三種形式的IF：
IF ... THEN
IF ... THEN ... ELSE
IF ... THEN ... ELSIF ... THEN ... ELSE
以及兩種形式的CASE:
CASE ... WHEN ... THEN ... ELSE ... END CASE
CASE WHEN ... THEN ... ELSE ... END CASE
39.6.2.1. IF-THEN
IF boolean-expression THEN
statements
END IF;
IF-THEN語句是IF的最簡單形式。 如果條件為真，在THEN和END IF之間的語句將被執(zhí)行。 否則，將忽略它們。
例子：
IF v_user_id <> 0 THEN
UPDATE users SET email = v_email WHERE user_id = v_user_id;
END IF;
39.6.2.2. IF-THEN-ELSE
IF boolean-expression THEN
statements
ELSE
statements
END IF;
IF-THEN語句增加了add to，你可以聲明在條件為假的時候執(zhí)行的語句（包括條件為NULL的情況）。
例子：
IF parentid IS NULL OR parentid = ''
THEN
RETURN fullname;
ELSE
RETURN hp_true_filename(parentid) || '/' || fullname;
END IF;
IF v_count > 0 THEN
INSERT INTO users_count (count) VALUES (v_count);
RETURN 't';
ELSE
RETURN 'f';
END IF;
39.6.2.3. IF-THEN-ELSIF
IF boolean-expression THEN
statements
[ ELSIF boolean-expression THEN
statements
[ ELSIF boolean-expression THEN
statements
...]]
[ ELSE
statements ]
END IF;
有時不知兩個選擇。IF-THEN-ELSIF反過來提供了一個簡便的方法來檢查選擇條件。 IF判斷會陸續(xù)檢查，直到找到第一個為真的，然后執(zhí)行相關(guān)聲明，如此，直到END IF（不會檢測IF子查詢）。 如果沒有一個條件符合IF判斷，那么會接著執(zhí)行ELSE判斷。
例子：
IF number = 0 THEN
result := 'zero';
ELSIF number > 0 THEN
result := 'positive';
ELSIF number < 0 THEN
result := 'negative';
ELSE
-- hmm, the only other possibility is that number is null
result := 'NULL';
END IF;
ELSIF關(guān)鍵字也可以寫成ELSEIF.
另一個可以實現(xiàn)該目的的方法是使用IF-THEN-ELSE聲明，如下：
IF demo_row.sex = 'm' THEN
pretty_sex := 'man';
ELSE
IF demo_row.sex = 'f' THEN
pretty_sex := 'woman';
END IF;
END IF;
然而，這個方法需要為每個IF寫END IF，因此當(dāng)有很多選擇時，這種方法明顯比ELSIF繁瑣。
39.6.2.4. Simple CASE
CASE search-expression
WHEN expression [, expression [ ... ]] THEN
statements
[ WHEN expression [, expression [ ... ]] THEN
statements
... ]
[ ELSE
statements ]
END CASE;
The simple form of CASE provides conditional execution based on equality of operands. The search-expression is evaluated (once) and successively compared to each expression in the WHEN clauses. If a match is found, then the corresponding statements are executed, and then control passes to the next statement after END CASE. (Subsequent WHEN expressions are not evaluated.) If no match is found, the ELSE statements are executed; but if ELSE is not present, then a CASE_NOT_FOUND exception is raised.
例如：
CASE x
WHEN 1, 2 THEN
msg := 'one or two';
ELSE
msg := 'other value than one or two';
END CASE;
39.6.2.5. Searched CASE
CASE
WHEN boolean-expression THEN
statements
[ WHEN boolean-expression THEN
statements
... ]
[ ELSE
statements ]
END CASE;
The searched form of CASE provides conditional execution based on truth of Boolean expressions. Each WHEN clause's boolean-expression is evaluated in turn, until one is found that yields true. Then the corresponding statements are executed, and then control passes to the next statement after END CASE. (Subsequent WHEN expressions are not evaluated.) If no true result is found, the ELSE statements are executed; but if ELSE is not present, then a CASE_NOT_FOUND exception is raised.
Here is an example:
CASE
WHEN x BETWEEN 0 AND 10 THEN
msg := 'value is between zero and ten';
WHEN x BETWEEN 11 AND 20 THEN
msg := 'value is between eleven and twenty';
END CASE;
This form of CASE is entirely equivalent to IF-THEN-ELSIF, except for the rule that reaching an omitted ELSE clause results in an error rather than doing nothing.
39.6.3. 簡單循環(huán)
使用LOOP, EXIT,CONTINUE, WHILE,HE FOR語句， 可以控制PL/pgSQL函數(shù)重復(fù)一系列命令。
39.6.3.1. 循環(huán)
[ <<label>> ] LOOP statements END LOOP [ label ];
LOOP定義一個無條件的循環(huán)，無限循環(huán)，直到由EXIT或RETURN語句終止。 可選的label可以由EXIT和CONTINUE語句使用，用于在嵌套循環(huán)中聲明應(yīng)該應(yīng)用于哪一層循環(huán)。
39.6.3.2. 退出
EXIT [ label ] [ WHEN boolean-expression ];
如果沒有給出label，那么退出最內(nèi)層的循環(huán)，然后執(zhí)行跟在END LOOP后面的語句。 如果給出label，那么它必須是當(dāng)前或者更高層的嵌套循環(huán)塊或者語句塊的標(biāo)簽。 然后該命名塊或者循環(huán)就會終止，而控制落到對應(yīng)循環(huán)/塊的END語句后面的語句上。
如果聲明了WHEN，循環(huán)退出只有在boolean-expression為真的時候才發(fā)生， 否則控制會落到EXIT后面的語句上。
EXIT可以用于在所有的循環(huán)類型中，它并不僅僅限制于在無條件循環(huán)中使用。
在和BEGIN塊一起使用的時候，EXIT把控制交給塊結(jié)束后的下一個語句。 需要注意的是，一個標(biāo)簽必須用于這個目的；一個沒有標(biāo)記的EXIT永遠(yuǎn)無法與BEGIN進行匹配。
例如：
LOOP
-- some computations
IF count > 0 THEN
EXIT; -- exit loop
END IF;
END LOOP;
LOOP
-- some computations
EXIT WHEN count > 0; -- same result as previous example
END LOOP;
<<ablock>>
BEGIN
-- some computations
IF stocks > 100000 THEN
EXIT ablock; -- causes exit from the BEGIN block
END IF;
-- computations here will be skipped when stocks > 100000
END;
39.6.3.3. CONTINUE
CONTINUE [ label ] [ WHEN boolean-expression ];
如果沒有給出label，那么就開始最內(nèi)層循環(huán)的下一次執(zhí)行。 也就是說，控制傳遞回給循環(huán)控制表達(dá)式(如果有)，然后重新計算循環(huán)體。 如果出現(xiàn)了label，它聲明即將繼續(xù)執(zhí)行的循環(huán)的標(biāo)簽。
如果聲明了WHEN，那么循環(huán)的下一次執(zhí)行只有在boolean-expression為真的情況下才進行。 否則，控制傳遞給CONTINUE后面的語句。
CONTINUE可以用于所有類型的循環(huán)；它并不僅僅限于無條件循環(huán)。
例如：
LOOP
-- 一些計算
EXIT WHEN count > 100;
CONTINUE WHEN count < 50;
-- 一些count數(shù)值在[50 .. 100]之間的計算
END LOOP;
39.6.3.4. WHILE
[ <<label>> ] WHILE boolean-expression LOOP statements END LOOP [ label ];
只要條件表達(dá)式（boolean-expression）為真，WHILE語句就會不停的在一系列語句上進行循環(huán)， 條件是在每次進入循環(huán)體的時候檢查的。
例如：
WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
-- some computations here
END LOOP;
WHILE NOT done LOOP
-- some computations here
END LOOP;
39.6.3.5. FOR (integer variant)
[ <<label>> ] FOR name IN [ REVERSE ] expression .. expression [ BY expression ] LOOP statements END LOOP [ label ];
這種形式的FOR對一定范圍的整數(shù)進行迭代的循環(huán)。 變量name會自動定義為integer類型并且只在循環(huán)里存在(任何該變量名的現(xiàn)存定義在此循環(huán)內(nèi)都將被忽略)。 給出范圍上下界的兩個表達(dá)式在進入循環(huán)的時候計算一次。 BY子句指定迭代步長(缺省為 1)，但如果聲明了REVERSE步長將變?yōu)橄鄳?yīng)的負(fù)值。
一些整數(shù) FOR 循環(huán)的例子：
FOR i IN 1..10 LOOP
-- i will take on the values 1,2,3,4,5,6,7,8,9,10 within the loop
END LOOP;
FOR i IN REVERSE 10..1 LOOP
-- i will take on the values 10,9,8,7,6,5,4,3,2,1 within the loop
END LOOP;
FOR i IN REVERSE 10..1 BY 2 LOOP
-- i will take on the values 10,8,6,4,2 within the loop
END LOOP;
如果下界大于上界(或者是在REVERSE情況下是小于)，那么循環(huán)體將完全不被執(zhí)行。 而且不會拋出任何錯誤。
If a label is attached to the FOR loop then the integer loop variable can be referenced with a qualified name, using that label.
39.6.4. 遍歷命令結(jié)果
使用不同類型的FOR循環(huán)，你可以遍歷一個命令的結(jié)果并且對其進行相應(yīng)的操作。 語法是：
[ <<label>> ] FOR target IN query LOOP statements END LOOP [ label ];
target是一個記錄變量、行變量、逗號分隔的標(biāo)量變量列表 target被連續(xù)不斷被賦予所有來自query的行，并且循環(huán)體將為每行執(zhí)行一次。 下面是一個例子：
CREATE FUNCTION cs_refresh_mviews() RETURNS integer AS $$
DECLARE
mviews RECORD;
BEGIN
PERFORM cs_log('Refreshing materialized views...');
FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key LOOP
-- 現(xiàn)在"mviews"里有了一條來自 cs_materialized_views 的記錄
PERFORM cs_log('Refreshing materialized view '
|| quote_ident(mviews.mv_name) || ' ...');
EXECUTE 'TRUNCATE TABLE ' || quote_ident(mviews.mv_name);
EXECUTE 'INSERT INTO '
|| quote_ident(mviews.mv_name) || ' '
|| mviews.mv_query;
END LOOP;
PERFORM cs_log('Done refreshing materialized views.');
RETURN 1;
END;
$$ LANGUAGE plpgsql;
如果循環(huán)是用一個EXIT語句終止的，那么在循環(huán)之后你仍然可以訪問最后賦值的行
FOR語句中使用的這種query可以是任何返回行的SQL命令， 通常是SELECT，不過帶有RETURNING子句的INSERT, UPDATE,或DELETE也是可以的， 一些諸如EXPLAIN之類的命令也可以
PL/pgSQL variables are substituted into the query text, and the query plan is cached for possible re-use, as discussed in detail in Section 39.10.1 and Section 39.10.2. PL/pgSQL變量 正如在Section 39.10.1和Section 39.10.2中討論的那樣。
FOR-IN-EXECUTE語句是遍歷所有行的另外一種方法：
[ <<label>> ] FOR target IN EXECUTE text_expression [ USING expression [, ... ] ] LOOP statements END LOOP [ label ];
這個例子類似前面的形式，只不過源查詢語句聲明為了一個字符串表達(dá)式， 這樣它在每次進入FOR循環(huán)的時候都會重新計算和生成執(zhí)行計劃。 這樣就允許程序員在一個預(yù)先規(guī)劃好了的命令所獲得的速度和一個動態(tài)命令所獲得的靈活性 (就像一個簡單的EXECUTE語句那樣)之間進行選擇。 當(dāng)使用EXECUTE時，可以通過USING將參數(shù)值插入到動態(tài)命令中。
對于一個需要將結(jié)果迭代的查詢，另外一個聲明的方法是將它定義為游標(biāo)（cursor），可參閱Section 39.7.4。
39.6.5. Trapping Errors捕獲錯誤
缺省時，一個在PL/pgSQL函數(shù)里發(fā)生的錯誤退出函數(shù)的執(zhí)行， 并且實際上其周圍的事務(wù)也會退出。 你可以使用一個帶有EXCEPTION子句的BEGIN塊捕獲錯誤并且從中恢復(fù)。 其語法是正常的BEGIN塊語法的一個擴展：
[ <<label>> ] [ DECLARE declarations ] BEGIN statements EXCEPTION WHEN condition [ OR condition ... ] THEN handler_statements [ WHEN condition [ OR condition ... ] THEN handler_statements ... ] END;
如果沒有發(fā)生錯誤，這種形式的塊只是簡單地執(zhí)行所有statements， 然后轉(zhuǎn)到下一個END之后的語句。 但是如果在statements內(nèi)部發(fā)生了一個錯誤，則對statements的進一步處理將廢棄， 然后轉(zhuǎn)到 EXCEPTION 列表。 系統(tǒng)搜索這個列表，尋找匹配錯誤的第一個condition。 如果找到匹配，則執(zhí)行對應(yīng)的handler_statements，然后轉(zhuǎn)到END之后的下一個語句。 如果沒有找到匹配，該錯誤就會廣播出去， 就好像根本沒有EXCEPTION子句一樣：該錯誤可以被一個包圍塊用EXCEPTION捕獲， 如果沒有包圍塊，則退出函數(shù)的處理。
condition的名字可以是 附錄A里顯示的任何名字。 一個范疇名匹配任意該范疇里的錯誤。 特殊的條件名OTHERS匹配除了QUERY_CANCELED之外的所有錯誤類型。 可以用名字捕獲QUERY_CANCELED，不過通常是不明智的。條件名是大小寫無關(guān)的。 同時也可以通過SQLSTATE來聲明一個錯誤條件，例如：
WHEN division_by_zero THEN ... WHEN SQLSTATE '22012' THEN ...
如果在選中的handler_statements里發(fā)生了新錯誤， 那么它不能被這個EXCEPTION子句捕獲，而是傳播出去。 一個外層的EXCEPTION子句可以捕獲它。
如果一個錯誤被EXCEPTION捕獲，PL/pgSQL函數(shù)的局部變量保持錯誤發(fā)生時的原值， 但是所有該塊中想固化在數(shù)據(jù)庫中的狀態(tài)都回滾。 作為一個例子，讓我們看看下面片斷
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)控制到達(dá)給y賦值的地方時，它會帶著一個division_by_zero錯誤失敗。 這個錯誤將被EXCEPTION子句捕獲。 而在RETURN語句里返回的數(shù)值將是x的增量值。 但是UPDATE已經(jīng)被回滾。然而，在該塊之前的INSERT將不會回滾， 因此最終的結(jié)果是數(shù)據(jù)庫包含Tom Jones而不是Joe Jones。
Tip: 進入和退出一個包含EXCEPTION子句的塊要比不包含的塊開銷大的多。 因此，不必要的時候不要使用EXCEPTION。
在異常處理器中，SQLSTATE變量包含拋出錯誤對應(yīng)的錯誤代碼(參考Table A-1獲取可能的錯誤碼的列表)。 SQLSTATE變量包含與異常關(guān)聯(lián)的錯誤信息。 這些變量在異常處理器外面是未定義的。
Example 39-2. UPDATE/INSERT異常
這個例子根據(jù)使用異常處理器執(zhí)行恰當(dāng)?shù)?tt class="COMMAND">UPDATE或INSERT。
CREATE TABLE db (a INT PRIMARY KEY, b TEXT);
CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
LOOP
-- first try to update the key
UPDATE db SET b = data WHERE a = key;
IF found THEN
RETURN;
END IF;
-- not there, so try to insert the key
-- if someone else inserts the same key concurrently,
-- we could get a unique-key failure
BEGIN
INSERT INTO db(a,b) VALUES (key, data);
RETURN;
EXCEPTION WHEN unique_violation THEN
-- do nothing, and loop to try the UPDATE again
END;
END LOOP;
END;
$$
LANGUAGE plpgsql;
SELECT merge_db(1, 'david');
SELECT merge_db(1, 'dennis');