PostgreSQL 控制結(jié)構(gòu)

RETURN expression;

帶有一個(gè)表達(dá)式的RETURN用于終止函數(shù)并把expression的值返回給調(diào)用者。這種形式被用于不返回集合的PL/pgSQL函數(shù)。

如果一個(gè)函數(shù)返回一個(gè)標(biāo)量類型，表達(dá)式的結(jié)果將被自動(dòng)轉(zhuǎn)換成函數(shù)的返回類型。但是要返回一個(gè)復(fù)合（行）值，你必須寫一個(gè)正好產(chǎn)生所需列集合的表達(dá)式。這可能需要使用顯式造型。

如果你聲明帶輸出參數(shù)的函數(shù)，那么就只需要寫不帶表達(dá)式的RETURN。輸出參數(shù)變量的當(dāng)前值將被返回。

如果你聲明函數(shù)返回void，一個(gè)RETURN語(yǔ)句可以被用來(lái)提前退出函數(shù)；但是不要在RETURN后面寫一個(gè)表達(dá)式。

一個(gè)函數(shù)的返回值不能是未定義。如果控制到達(dá)了函數(shù)最頂層的塊而沒(méi)有碰到一個(gè)RETURN語(yǔ)句，那么會(huì)發(fā)生一個(gè)運(yùn)行時(shí)錯(cuò)誤。不過(guò)，這個(gè)限制不適用于帶輸出參數(shù)的函數(shù)以及返回void的函數(shù)。在這些情況中，如果頂層的塊結(jié)束，將自動(dòng)執(zhí)行一個(gè)RETURN語(yǔ)句。

一些例子：

-- 返回一個(gè)標(biāo)量類型的函數(shù)
RETURN 1 + 2;
RETURN scalar_var;

-- 返回一個(gè)組合類型的函數(shù)
RETURN composite_type_var;
RETURN (1, 2, 'three'::text);  -- 必須把列造型成正確的類型

RETURN NEXT expression;
RETURN QUERY query;
RETURN QUERY EXECUTE command-string [ USING expression [, ... ] ];

當(dāng)一個(gè)PL/pgSQL函數(shù)被聲明為返回SETOF sometype ，那么遵循的過(guò)程則略有不同。在這種情況下，要返回的個(gè)體項(xiàng)被用一個(gè)RETURN NEXT或者RETURN QUERY命令的序列指定，并且接著會(huì)用一個(gè)不帶參數(shù)的最終RETURN命令來(lái)指示這個(gè)函數(shù)已經(jīng)完成執(zhí)行。RETURN NEXT可以被用于標(biāo)量和復(fù)合數(shù)據(jù)類型；對(duì)于復(fù)合類型，將返回一個(gè)完整的結(jié)果 “表”。RETURN QUERY將執(zhí)行一個(gè)查詢的結(jié)果追加到一個(gè)函數(shù)的結(jié)果集中。在一個(gè)單一的返回集合的函數(shù)中，RETURN NEXT和RETURN QUERY可以被隨意地混合，這樣它們的結(jié)果將被串接起來(lái)。

RETURN NEXT和RETURN QUERY實(shí)際上不會(huì)從函數(shù)中返回 — 它們簡(jiǎn)單地向函數(shù)的結(jié)果集中追加零或多行。然后會(huì)繼續(xù)執(zhí)行PL/pgSQL函數(shù)中的下一條語(yǔ)句。隨著后繼的RETURN NEXT和RETURN QUERY命令的執(zhí)行，結(jié)果集就建立起來(lái)了。最后一個(gè) RETURN（應(yīng)該沒(méi)有參數(shù)）會(huì)導(dǎo)致控制退出該函數(shù)（或者你可以讓控制到達(dá)函數(shù)的結(jié)尾）。

RETURN QUERY有一種變體RETURN QUERY EXECUTE，它可以動(dòng)態(tài)指定要被執(zhí)行的查詢?？梢酝ㄟ^(guò)USING向計(jì)算出的查詢字符串插入?yún)?shù)表達(dá)式，這和在EXECUTE命令中的方式相同。

如果你聲明函數(shù)帶有輸出參數(shù)，只需要寫不帶表達(dá)式的RETURN NEXT。在每一次執(zhí)行時(shí)，輸出參數(shù)變量的當(dāng)前值將被保存下來(lái)用于最終返回為結(jié)果的一行。注意為了創(chuàng)建一個(gè)帶有輸出參數(shù)的集合返回函數(shù)，在有多個(gè)輸出參數(shù)時(shí)，你必須聲明函數(shù)為返回SETOF record；或者如果只有一個(gè)類型為sometype的輸出參數(shù)時(shí)，聲明函數(shù)為 SETOF sometype 。

下面是一個(gè)使用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 get_all_foo() RETURNS SETOF foo AS
$BODY$
DECLARE
    r foo%rowtype;
BEGIN
    FOR r IN
        SELECT * FROM foo WHERE fooid > 0
    LOOP
        -- 這里可以做一些處理
        RETURN NEXT r; -- 返回 SELECT 的當(dāng)前行
    END LOOP;
    RETURN;
END;
$BODY$
LANGUAGE plpgsql;

SELECT * FROM get_all_foo();

這里是一個(gè)使用RETURN QUERY的函數(shù)的例子：

CREATE FUNCTION get_available_flightid(date) RETURNS SETOF integer AS
$BODY$
BEGIN
    RETURN QUERY SELECT flightid
                   FROM flight
                  WHERE flightdate >= $1
                    AND flightdate < ($1 + 1);

    -- 因?yàn)閳?zhí)行還未結(jié)束，我們可以檢查是否有行被返回
    -- 如果沒(méi)有就拋出異常。
    IF NOT FOUND THEN
        RAISE EXCEPTION 'No flight at %.', $1;
    END IF;

    RETURN;
 END;
$BODY$
LANGUAGE plpgsql;

-- 返回可用的航班或者在沒(méi)有可用航班時(shí)拋出異常。
SELECT * FROM get_available_flightid(CURRENT_DATE);

IF boolean-expression THEN
    statements
END IF;

IF-THEN語(yǔ)句是IF的最簡(jiǎn)單形式。 如果條件為真，在THEN和END IF之間的語(yǔ)句將被執(zhí)行。否則，將忽略它們。

例子：

IF v_user_id <> 0 THEN
    UPDATE users SET email = v_email WHERE user_id = v_user_id;
END IF;

IF boolean-expression THEN
    statements
ELSE
    statements
END IF;

IF-THEN-ELSE語(yǔ)句對(duì)IF-THEN進(jìn)行了增加，它讓你能夠指定一組在條件不為真時(shí)應(yīng)該被執(zhí)行的語(yǔ)句（注意這也包括條件為 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;

IF boolean-expression THEN
    statements
[ ELSIF boolean-expression THEN
    statements
[ ELSIF boolean-expression THEN
    statements
    ...
]
]
[ ELSE
    statements ]
END IF;

有時(shí)會(huì)有多于兩種選擇。IF-THEN-ELSIF則提供了一個(gè)簡(jiǎn)便的方法來(lái)檢查多個(gè)條件。IF條件會(huì)被一個(gè)接一個(gè)測(cè)試，直到找到第一個(gè)為真的。然后執(zhí)行相關(guān)語(yǔ)句，然后控制會(huì)被交給END IF之后的下一個(gè)語(yǔ)句（后續(xù)的任何IF條件不會(huì)被測(cè)試）。如果沒(méi)有一個(gè) IF條件為真，那么ELSE塊（如果有）將被執(zhí)行。

這里有一個(gè)例子：

IF number = 0 THEN
    result := 'zero';
ELSIF number > 0 THEN
    result := 'positive';
ELSIF number < 0 THEN
    result := 'negative';
ELSE
    -- 嗯，唯一的其他可能性是數(shù)字為空
    result := 'NULL';
END IF;

關(guān)鍵詞ELSIF也可以被拼寫成ELSEIF。

另一個(gè)可以完成相同任務(wù)的方法是嵌套IF-THEN-ELSE語(yǔ)句，如下例：

IF demo_row.sex = 'm' THEN
    pretty_sex := 'man';
ELSE
    IF demo_row.sex = 'f' THEN
        pretty_sex := 'woman';
    END IF;
END IF;

不過(guò)，這種方法需要為每個(gè)IF都寫一個(gè)匹配的END IF，因此當(dāng)有很多選擇時(shí)，這種方法比使用ELSIF要麻煩得多。

CASE search-expression
    WHEN expression [, expression [ ... ]] THEN
      statements
  [ WHEN expression [, expression [ ... ]] THEN
      statements
    ... ]
  [ ELSE
      statements ]
END CASE;

CASE的簡(jiǎn)單形式提供了基于操作數(shù)等值判斷的有條件執(zhí)行。search-expression會(huì)被計(jì)算（一次）并且一個(gè)接一個(gè)地與WHEN子句中的每個(gè)expression比較。如果找到一個(gè)匹配，那么相應(yīng)的statements會(huì)被執(zhí)行，并且接著控制會(huì)被交給 END CASE之后的下一個(gè)語(yǔ)句（后續(xù)的WHEN表達(dá)式不會(huì)被計(jì)算）。如果沒(méi)有找到匹配，ELSE 語(yǔ)句會(huì)被執(zhí)行。但是如果ELSE不存在，將會(huì)拋出一個(gè)CASE_NOT_FOUND異常。

這里是一個(gè)簡(jiǎn)單的例子：

CASE x
    WHEN 1, 2 THEN
        msg := 'one or two';
    ELSE
        msg := 'other value than one or two';
END CASE;

CASE
    WHEN boolean-expression THEN
      statements
  [ WHEN boolean-expression THEN
      statements
    ... ]
  [ ELSE
      statements ]
END CASE;

CASE的搜索形式基于布爾表達(dá)式真假的有條件執(zhí)行。每一個(gè)WHEN子句的boolean-expression會(huì)被依次計(jì)算，直到找到一個(gè)得到真的。然后相應(yīng)的statements會(huì)被執(zhí)行，并且接下來(lái)控制會(huì)被傳遞給 END CASE之后的下一個(gè)語(yǔ)句（后續(xù)的WHEN表達(dá)式不會(huì)被計(jì)算）。如果沒(méi)有找到為真的結(jié)果，ELSE statements會(huì)被執(zhí)行。但是如果ELSE不存在，那么將會(huì)拋出一個(gè)CASE_NOT_FOUND異常。

這里是一個(gè)例子：

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;

這種形式的CASE整體上等價(jià)于IF-THEN-ELSIF，不同之處在于CASE到達(dá)一個(gè)被忽略的ELSE子句時(shí)會(huì)導(dǎo)致一個(gè)錯(cuò)誤而不是什么也不做。

[ <<label>> ]
LOOP
    statements
END LOOP [ label ];

LOOP定義一個(gè)無(wú)條件的循環(huán)，它會(huì)無(wú)限重復(fù)直到被EXIT或RETURN語(yǔ)句終止?？蛇x的label可以被EXIT和CONTINUE語(yǔ)句用在嵌套循環(huán)中指定這些語(yǔ)句引用的是哪一層循環(huán)。

EXIT [ label ] [ WHEN boolean-expression ];

如果沒(méi)有給出label，那么最內(nèi)層的循環(huán)會(huì)被終止，然后跟在END LOOP后面的語(yǔ)句會(huì)被執(zhí)行。如果給出了label，那么它必須是當(dāng)前或者更高層的嵌套循環(huán)或者語(yǔ)句塊的標(biāo)簽。然后該命名循環(huán)或塊就會(huì)被終止，并且控制會(huì)轉(zhuǎn)移到該循環(huán)/塊相應(yīng)的END之后的語(yǔ)句上。

如果指定了WHEN，只有boolean-expression為真時(shí)才會(huì)發(fā)生循環(huán)退出。否則，控制會(huì)轉(zhuǎn)移到EXIT之后的語(yǔ)句。

EXIT可以被用在所有類型的循環(huán)中，它并不限于在無(wú)條件循環(huán)中使用。

在和BEGIN塊一起使用時(shí)，EXIT會(huì)把控制交給塊結(jié)束后的下一個(gè)語(yǔ)句。需要注意的是，一個(gè)標(biāo)簽必須被用于這個(gè)目的；一個(gè)沒(méi)有被標(biāo)記的EXIT永遠(yuǎn)無(wú)法被認(rèn)為與一個(gè)BEGIN塊匹配（這種狀況從PostgreSQL 8.4 之前的發(fā)布就已經(jīng)開(kāi)始改變。這可能允許一個(gè)未被標(biāo)記的EXIT匹配一個(gè)BEGIN塊）。

例子：

LOOP
    -- 一些計(jì)算
    IF count > 0 THEN
        EXIT;  -- 退出循環(huán)
    END IF;
END LOOP;

LOOP
    -- 一些計(jì)算
    EXIT WHEN count > 0;  -- 和前一個(gè)例子相同的結(jié)果
END LOOP;

<<ablock>>
BEGIN
    -- 一些計(jì)算
    IF stocks > 100000 THEN
        EXIT ablock;  -- 導(dǎo)致從 BEGIN 塊中退出
    END IF;
    -- 當(dāng)stocks > 100000時(shí)，這里的計(jì)算將被跳過(guò)
END;

CONTINUE [ label ] [ WHEN boolean-expression ];

如果沒(méi)有給出label，最內(nèi)層循環(huán)的下一次迭代會(huì)開(kāi)始。也就是，循環(huán)體中剩余的所有語(yǔ)句將被跳過(guò)，并且控制會(huì)返回到循環(huán)控制表達(dá)式（如果有）來(lái)決定是否需要另一次循環(huán)迭代。如果label存在，它指定應(yīng)該繼續(xù)執(zhí)行的循環(huán)的標(biāo)簽。

如果指定了WHEN，該循環(huán)的下一次迭代只有在boolean-expression為真時(shí)才會(huì)開(kāi)始。否則，控制會(huì)傳遞給CONTINUE后面的語(yǔ)句。

CONTINUE可以被用在所有類型的循環(huán)中，它并不限于在無(wú)條件循環(huán)中使用。

例子：

LOOP
    -- 一些計(jì)算
    EXIT WHEN count > 100;
    CONTINUE WHEN count < 50;
    -- 一些用于 count IN [50 .. 100] 的計(jì)算
END LOOP;

[ <<label>> ]
WHILE boolean-expression LOOP
    statements
END LOOP [ label ];

只要boolean-expression被計(jì)算為真，WHILE語(yǔ)句就會(huì)重復(fù)一個(gè)語(yǔ)句序列。在每次進(jìn)入到循環(huán)體之前都會(huì)檢查該表達(dá)式。

例如：

WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
    -- 這里是一些計(jì)算
END LOOP;

WHILE NOT done LOOP
    -- 這里是一些計(jì)算
END LOOP;

[ <<label>> ]
FOR name IN [ REVERSE ] expression .. expression [ BY expression ] LOOP
    statements
END LOOP [ label ];

這種形式的FOR會(huì)創(chuàng)建一個(gè)在一個(gè)整數(shù)范圍上迭代的循環(huán)。變量name會(huì)自動(dòng)定義為類型integer并且只在循環(huán)內(nèi)存在（任何該變量名的現(xiàn)有定義在此循環(huán)內(nèi)都將被忽略）。給出范圍上下界的兩個(gè)表達(dá)式在進(jìn)入循環(huán)的時(shí)候計(jì)算一次。如果沒(méi)有指定BY子句，迭代步長(zhǎng)為 1，否則步長(zhǎng)是BY中指定的值，該值也只在循環(huán)進(jìn)入時(shí)計(jì)算一次。如果指定了REVERSE，那么在每次迭代后步長(zhǎng)值會(huì)被減除而不是增加。

整數(shù)FOR循環(huán)的一些例子：

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;

如果下界大于上界（或者在REVERSE情況下是小于），循環(huán)體根本不會(huì)被執(zhí)行。而且不會(huì)拋出任何錯(cuò)誤。

如果一個(gè)label被附加到FOR循環(huán)，那么整數(shù)循環(huán)變量可以用一個(gè)使用那個(gè)label的限定名引用。

異常處理器經(jīng)常被用來(lái)標(biāo)識(shí)發(fā)生的特定錯(cuò)誤。有兩種方法來(lái)得到PL/pgSQL中當(dāng)前異常的信息：特殊變量和GET STACKED DIAGNOSTICS命令。

在一個(gè)異常處理器內(nèi)，特殊變量SQLSTATE包含了對(duì)應(yīng)于被拋出異常的錯(cuò)誤代碼（可能的錯(cuò)誤代碼列表見(jiàn)表 A.1）。特殊變量SQLERRM包含與該異常相關(guān)的錯(cuò)誤消息。這些變量在異常處理器外是未定義的。

在一個(gè)異常處理器內(nèi)，我們也可以用GET STACKED DIAGNOSTICS命令檢索有關(guān)當(dāng)前異常的信息，該命令的形式為：

GET STACKED DIAGNOSTICS variable { = | := } item [ , ... ];

每個(gè)item是一個(gè)關(guān)鍵詞，它標(biāo)識(shí)一個(gè)被賦予給指定變量（應(yīng)該具有接收該值的正確數(shù)據(jù)類型）的狀態(tài)值。表 42.2中顯示了當(dāng)前可用的狀態(tài)項(xiàng)。

如果異常沒(méi)有為一個(gè)項(xiàng)設(shè)置值，將返回一個(gè)空字符串。

這里是一個(gè)例子：

DECLARE
  text_var1 text;
  text_var2 text;
  text_var3 text;
BEGIN
  -- 某些可能導(dǎo)致異常的處理
  ...
EXCEPTION WHEN OTHERS THEN
  GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,
                          text_var2 = PG_EXCEPTION_DETAIL,
                          text_var3 = PG_EXCEPTION_HINT;
END;