PostgreSQL學習手冊(PL/pgSQL過程語言)

一、概述：

    PL/pgSQL函式在第一次被呼叫時，其函式內的原始碼(文字)將被解析為二進位制指令樹，但是函式內的表示式和SQL命令只有在首次用到它們的時 候，PL/pgSQL直譯器才會為其建立一個準備好的執行規劃，隨後對該表示式或SQL命令的訪問都將使用該規劃。如果在一個條件語句中，有部分SQL命 令或表示式沒有被用到，那麼PL/pgSQL直譯器在本次呼叫中將不會為其準備執行規劃，這樣的好處是可以有效地減少為PL/pgSQL函式裡的語句生成 分析和執行規劃的總時間，然而缺點是某些表示式或SQL命令中的錯誤只有在其被執行到的時候才能發現。
    由於PL/pgSQL在函式裡為一個命令制定了執行計劃，那麼在本次會話中該計劃將會被反覆使用，這樣做往往可以得到更好的效能，但是如果你動態修改了相關的資料庫物件，那麼就有可能產生問題，如：
    CREATE FUNCTION populate() RETURNS integer AS $$
    DECLARE
        -- 宣告段
    BEGIN
        PERFORM my_function();
    END;
    $$ LANGUAGE plpgsql;
    在呼叫以上函式時，PERFORM語句的執行計劃將引用my_function物件的OID。在此之後，如果你重建了my_function函式，那麼 populate函式將無法再找到原有my_function函式的OID。要解決該問題，可以選擇重建populate函式，或者重新登入建立新的會 話，以使PostgreSQL重新編譯該函式。要想規避此類問題的發生，在重建my_function時可以使用CREATE OR REPLACE FUNCTION命令。
    鑑於以上規則，在PL/pgSQL裡直接出現的SQL命令必須在每次執行時均引用相同的表和欄位，換句話說，不能將函式的引數用作SQL命令的表名或欄位 名。如果想繞開該限制，可以考慮使用PL/pgSQL中的EXECUTE語句動態地構造命令，由此換來的代價是每次執行時都要構造一個新的命令計劃。
    使用PL/pgSQL函式的一個非常重要的優勢是可以提高程式的執行效率，由於原有的SQL呼叫不得不在客戶端與伺服器之間反覆傳遞資料，這樣不僅增加了程式間通訊所產生的開銷，而且也會大大增加網路IO的開銷。

二、PL/pgSQL的結構：

    PL/pgSQL是一種塊結構語言，函式定義的所有文字都必須在一個塊內，其中塊中的每個宣告和每條語句都是以分號結束，如果某一子塊在另外一個塊內，那麼該子塊的END關鍵字後面必須以分號結束，不過對於函式體的最後一個END關鍵字，分號可以省略，如：
    [ <> ]
    [ DECLARE declarations ]
    BEGIN
        statements
    END [ label ];
    在PL/pgSQL中有兩種註釋型別，雙破折號(--)表示單行註釋。/* */表示多行註釋，該註釋型別的規則等同於C語言中的多行註釋。
    在語句塊前面的宣告段中定義的變數在每次進入語句塊(BEGIN)時都會將宣告的變數初始化為它們的預設值，而不是每次函式呼叫時初始化一次。如：
    CREATE FUNCTION somefunc() RETURNS integer AS $$
    DECLARE
       quantity integer := 30;
    BEGIN
       RAISE NOTICE 'Quantity here is %', quantity;      --在這裡的數量是30
       quantity := 50;
       --
       -- 建立一個子塊
       --
       DECLARE
          quantity integer := 80;
       BEGIN
          RAISE NOTICE 'Quantity here is %', quantity;   --在這裡的數量是80
       END;
       RAISE NOTICE 'Quantity here is %', quantity;      --在這裡的數量是50   
       RETURN quantity;
    END;
    $$ LANGUAGE plpgsql;
    #執行該函式以進一步觀察其執行的結果。
    postgres=# select somefunc();
    NOTICE:  Quantity here is 30
    NOTICE:  Quantity here is 80
    NOTICE:  Quantity here is 50
     somefunc
    ----------
           50
    (1 row)
    最後需要說明的是，目前版本的PostgreSQL並不支援巢狀事務，函式中的事物總是由外層命令(函式的呼叫者)來控制的，它們本身無法開始或提交事務。

三、宣告：

    所有在塊裡使用的變數都必須在塊的宣告段裡先進行宣告，唯一的例外是FOR迴圈裡的迴圈計數變數，該變數被自動宣告為整型。變數宣告的語法如下：
    variable_name [ CONSTANT ] variable_type [ NOT NULL ] [ { DEFAULT | := } expression ];
    1). SQL中的資料型別均可作為PL/pgSQL變數的資料型別，如integer、varchar和char等。
    2). 如果給出了DEFAULT子句，該變數在進入BEGIN塊時將被初始化為該預設值，否則被初始化為SQL空值。預設值是在每次進入該塊時進行計算的。因 此，如果把now()賦予一個型別為timestamp的變數，那麼該變數的預設值將為函式實際呼叫時的時間，而不是函式預編譯時的時間。
    3). CONSTANT選項是為了避免該變數在進入BEGIN塊後被重新賦值，以保證該變數為常量。
    4). 如果宣告瞭NOT NULL，那麼賦予NULL數值給該變數將導致一個執行時錯誤。因此所有宣告為NOT NULL的變數也必須在宣告時定義一個非空的預設值。

    1. 函式引數的別名：
    傳遞給函式的引數都是用$1、$2這樣的識別符號來表示的。為了增加可讀性，我們可以為其宣告別名。之後別名和數字識別符號均可指向該引數值，見如下示例：
    1). 在函式宣告的同時給出引數變數名。
    CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$
    BEGIN
        RETURN subtotal * 0.06;
    END;
    $$ LANGUAGE plpgsql;
    2). 在宣告段中為引數變數定義別名。
    CREATE FUNCTION sales_tax(REAL) RETURNS real AS $$
    DECLARE
        subtotal ALIAS FOR $1;
    BEGIN
        RETURN subtotal * 0.06;
    END;
    $$ LANGUAGE plpgsql;
    3). 對於輸出引數而言，我們仍然可以遵守1)和2)中的規則。
    CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$
    BEGIN
        tax := subtotal * 0.06;
    END;
    $$ LANGUAGE plpgsql;    
    4). 如果PL/pgSQL函式的返回型別為多型型別(anyelement或anyarray)，那麼函式就會建立一個特殊的引數：$0。我們仍然可以為該變數設定別名。
    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. 拷貝型別：
    見如下形式的變數宣告：
    variable%TYPE
    %TYPE表示一個變數或表欄位的資料型別，PL/pgSQL允許通過該方式宣告一個變數，其型別等同於variable或表欄位的資料型別，見如下示例：
    user_id users.user_id%TYPE;
    在上面的例子中，變數user_id的資料型別等同於users表中user_id欄位的型別。
    通過使用%TYPE，一旦引用的變數型別今後發生改變，我們也無需修改該變數的型別宣告。最後需要說明的是，我們可以在函式的引數和返回值中使用該方式的型別宣告。

    3. 行型別：
    見如下形式的變數宣告：
    name table_name%ROWTYPE;
    name composite_type_name;
    table_name%ROWTYPE表示指定表的行型別，我們在建立一個表的時候，PostgreSQL也會隨之建立出一個與之相應的複合型別，該類 型名等同於表名，因此，我們可以通過以上兩種方式來宣告行型別的變數。由此方式宣告的變數，可以儲存SELECT返回結果中的一行。如果要訪問變數中的某 個域欄位，可以使用點表示法，如rowvar.field，但是行型別的變數只能訪問自定義欄位，無法訪問系統提供的隱含欄位，如OID等。對於函式的參 數，我們只能使用複合型別標識變數的資料型別。最後需要說明的是，推薦使用%ROWTYPE的宣告方式，這樣可以具有更好的可移植性，因為在Oracle 的PL/SQL中也存在相同的概念，其宣告方式也為%ROWTYPE。見如下示例：
    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. 記錄型別：
    見如下形式的變數宣告：
    name RECORD;
    記錄變數類似於行型別變數，但是它們沒有預定義的結構，只能通過SELECT或FOR命令來獲取實際的行結構，因此記錄變數在被初始化之前無法訪問，否則將引發執行時錯誤。
    注：RECORD不是真正的資料型別，只是一個佔位符。

四、基本語句：

    1. 賦值：
    PL/pgSQL中賦值語句的形式為：identIFier := expression，等號兩端的變數和表示式的型別或者一致，或者可以通過PostgreSQL的轉換規則進行轉換，否則將會導致執行時錯誤，見如下示例：
    user_id := 20;
    tax := subtotal * 0.06;
    
    2. SELECT INTO：
    通過該語句可以為記錄變數或行型別變數進行賦值，其表現形式為：SELECT INTO target select_expressions FROM ...，該賦值方式一次只能賦值一個變數。表示式中的target可以表示為是一個記錄變數、行變數，或者是一組用逗號分隔的簡單變數和記錄/行欄位的列表。select_expressions以及剩餘部分和普通SQL一樣。
    如果將一行或者一個變數列表用做目標，那麼選出的數值必需精確匹配目標的結構，否則就會產生執行時錯誤。如果目標是一個記錄變數，那麼它自動將自己構造 成命令結果列的行型別。如果命令返回零行，目標被賦予空值。如果命令返回多行，那麼將只有第一行被賦予目標，其它行將被忽略。在執行SELECT INTO語句之後，可以通過檢查內建變數FOUND來判斷本次賦值是否成功，如：
    SELECT INTO myrec * FROM emp WHERE empname = myname;
    IF NOT FOUND THEN
        RAISE EXCEPTION 'employee % not found', myname;
    END IF;
    要測試一個記錄/行結果是否為空，可以使用IS NULL條件進行判斷，但是對於返回多條記錄的情況則無法判斷，如：
    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. 執行一個沒有結果的表示式或者命令：
    在呼叫一個表示式或執行一個命令時，如果對其返回的結果不感興趣，可以考慮使用PERFORM語句：PERFORM. query，該語句將執行PERFORM之後的命令並忽略其返回的結果。其中query的寫法和普通的SQL SELECT命令是一樣的，只是把開頭的關鍵字SELECT替換成PERFORM，如：
    PERFORM. create_mv('cs_session_page_requests_mv', my_query);

    4. 執行動態命令：
    如果在PL/pgSQL函式中操作的表或資料型別在每次呼叫該函式時都可能會發生變化，在這樣的情況下，可以考慮使用PL/pgSQL提供的EXECUTE語句：EXECUTE command-string [ INTO target ]， 其中command-string是用一段文字表示的表示式，它包含要執行的命令。而target是一個記錄變數、行變數或者一組用逗號分隔的簡單變數和 記錄/行域的列表。這裡需要特別注意的是，該命令字串將不會發生任何PL/pgSQL變數代換，變數的數值必需在構造命令字串時插入到該字串中。
    和所有其它PL/pgSQL命令不同的是，一個由EXECUTE語句執行的命令在伺服器內並不會只prepare和儲存一次。相反，該語句在每次執行的 時候，命令都會prepare一次。因此命令字串可以在函式裡動態的生成以便於對各種不同的表和欄位進行操作，從而提高函式的靈活性。然而由此換來的卻 是效能上的折損。見如下示例：
    EXECUTE 'UPDATE tbl SET ' || quote_ident(columnname) || ' = ' || quote_literal(newvalue);

五、控制結構：

    1. 函式返回：
    1). RETURN expression
    該表示式用於終止當前的函式，然後再將expression的值返回給呼叫者。如果返回簡單型別，那麼可以使用任何表示式，同時表示式的型別也將被自動 轉換成函式的返回型別，就像我們在賦值中描述的那樣。如果要返回一個複合型別的數值，則必須讓表示式返回記錄或者匹配的行變數。
    2). RETURN NEXT expression
    如果PL/pgSQL函式宣告為返回SETOF sometype，其行記錄是通過RETURN NEXT命令進行填充的，直到執行到不帶引數的RETURN時才表示該函式結束。因此對於RETURN NEXT而言，它實際上並不從函式中返回，只是簡單地把表示式的值儲存起來，然後繼續執行PL/pgSQL函式裡的下一條語句。隨著RETURN NEXT命令的迭代執行，結果集最終被建立起來。該類函式的呼叫方式如下：
    SELECT * FROM some_func();
    它被放在FROM子句中作為資料來源使用。最後需要指出的是，如果結果集數量很大，那麼通過該種方式來構建結果集將會導致極大的效能損失。

    2. 條件：
    在PL/pgSQL中有以下三種形式的條件語句。
    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;    
    關於條件語句，這裡就不在做過多的贅述了。

    3. 迴圈：
    1). LOOP
    LOOP
        statements
    END LOOP [ label ];
    LOOP定義一個無條件的迴圈，直到由EXIT或者RETURN語句終止。可選的label可以由EXIT和CONTINUE語句使用，用於在巢狀迴圈中宣告應該應用於哪一層迴圈。
    2). EXIT
    EXIT [ label ] [ WHEN expression ];
    如果沒有給出label，就退出最內層的迴圈，然後執行跟在END LOOP後面的語句。如果給出label，它必須是當前或更高層的巢狀迴圈塊或語句塊的標籤。之後該命名塊或迴圈就會終止，而控制則直接轉到對應迴圈/塊的END語句後面的語句上。
    如果宣告瞭WHEN，EXIT命令只有在expression為真時才被執行，否則將直接執行EXIT後面的語句。見如下示例：
    LOOP
        -- do something
        EXIT WHEN count > 0;
    END LOOP;
    3). CONTINUE
    CONTINUE [ label ] [ WHEN expression ];
    如果沒有給出label，CONTINUE就會跳到最內層迴圈的開始處，重新進行判斷，以決定是否繼續執行迴圈內的語句。如果指定label，則跳到該 label所在的迴圈開始處。如果宣告瞭WHEN，CONTINUE命令只有在expression為真時才被執行，否則將直接執行CONTINUE後面 的語句。見如下示例：
    LOOP
        -- do something
        EXIT WHEN count > 100;
        CONTINUE WHEN count < 50;
    END LOOP;    
    4). WHILE
    [ <> ]
    WHILE expression LOOP
        statements
    END LOOP [ label ];
    只要條件表示式為真，其塊內的語句就會被迴圈執行。條件是在每次進入迴圈體時進行判斷的。見如下示例：
    WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
        --do something
    END LOOP;
    5). FOR
    [ <> ]
    FOR name IN [ REVERSE ] expression .. expression LOOP
        statements
    END LOOP [ label ];
    變數name自動被定義為integer型別，其作用域僅為FOR迴圈的塊內。表示範圍上下界的兩個表示式只在進入迴圈時計算一次。每次迭代name值自增1，但如果宣告瞭REVERSE，name變數在每次迭代中將自減1，見如下示例：
    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. 遍歷命令結果：
    [ <> ]
    FOR record_or_row IN query LOOP
        statements
    END LOOP [ label ];
    這是另外一種形式的FOR迴圈，在該迴圈中可以遍歷命令的結果並操作相應的資料，見如下示例：
    FOR rec IN SELECT * FROM some_table LOOP
        PERFORM. some_func(rec.one_col);
    END LOOP;
    PL/pgSQL還提供了另外一種遍歷命令結果的方式，和上面的方式相比，唯一的差別是該方式將SELECT語句存於字串文字中，然後再交由EXECUTE命令動態的執行。和前一種方式相比，該方式的靈活性更高，但是效率較低。
    [ <> ]
    FOR record_or_row IN EXECUTE text_expression LOOP
        statements
    END LOOP [ label ];
    
    5. 異常捕獲：
    在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子句捕獲並處理。見如下示例：
    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;
    當以上函式執行到y := x / 0語句時，將會引發一個異常錯誤，程式碼將跳轉到EXCEPTION塊的開始處，之後系統會尋找匹配的異常捕捉條件，此時division_by_zero 完全匹配，這樣該條件內的程式碼將會被繼續執行。需要說明的是，RETURN語句中返回的x值為x := x + 1執行後的新值，但是在除零之前的update語句將會被回滾，BEGIN之前的insert語句將仍然生效。
  
六、遊標：

    1. 宣告遊標變數：
    在PL/pgSQL中對遊標的訪問都是通過遊標變數實現的，其資料型別為refcursor。 建立遊標變數的方法有以下兩種：
    1). 和宣告其他型別的變數一樣，直接宣告一個遊標型別的變數即可。
    2). 使用遊標專有的宣告語法，如：
    name CURSOR [ ( arguments ) ] FOR query;
    其中arguments為一組逗號分隔的name datatype列表，見如下示例：
    curs1 refcursor;
    curs2 CURSOR FOR SELECT * FROM tenk1;
    curs3 CURSOR (key integer) IS SELECT * FROM tenk1 WHERE unique1 = key;
    在上面三個例子中，只有第一個是未繫結遊標，剩下兩個遊標均已被繫結。

    2. 開啟遊標：
    遊標在使用之前必須先被開啟，在PL/pgSQL中有三種形式的OPEN語句，其中兩種用於未繫結的遊標變數，另外一種用於繫結的遊標變數。
    1). OPEN FOR:
    其宣告形式為：
    OPEN unbound_cursor FOR query;
    該形式只能用於未繫結的遊標變數，其查詢語句必須是SELECT，或其他返回記錄行的語句，如EXPLAIN。在PostgreSQL中，該查詢和普通的SQL命令平等對待，即先替換變數名，同時也將該查詢的執行計劃快取起來，以供後用。見如下示例：
    OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;
    2). OPEN FOR EXECUTE
    其宣告形式為：
    OPEN unbound_cursor FOR EXECUTE query-string;   
    和上面的形式一樣，該形式也僅適用於未繫結的遊標變數。EXECUTE將動態執行其後以文字形式表示的查詢字串。
    OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);
    3). 開啟一個繫結的遊標
    其宣告形式為：
    OPEN bound_cursor [ ( argument_values ) ];   
    該形式僅適用於繫結的遊標變數，只有當該變數在宣告時包含接收引數，才能以傳遞引數的形式開啟該遊標，這些引數將被實際代入到遊標宣告的查詢語句中，見如下示例：
    OPEN curs2;
    OPEN curs3(42);    

    3. 使用遊標：
    遊標一旦開啟，就可以按照以下方式進行讀取。然而需要說明的是，遊標的開啟和讀取必須在同一個事物內，因為在PostgreSQL中，如果事物結束，事物內開啟的遊標將會被隱含的關閉。
    1). FETCH
    其宣告形式為：
    FETCH cursor INTO target;
    FETCH命令從遊標中讀取下一行記錄的資料到目標中，其中目標可以是行變數、記錄變數，或者是一組逗號分隔的普通變數的列表，讀取成功與否，可通過PL/pgSQL內建變數FOUND來判斷，其規則等同於SELECT INTO。見如下示例：
    FETCH curs1 INTO rowvar;  --rowvar為行變數
    FETCH curs2 INTO foo, bar, baz;
    2). CLOSE
    其宣告形式為：
    CLOSE cursor;
    關閉當前已經開啟的遊標，以釋放其佔有的系統資源，見如下示例：
    CLOSE curs1;

七、錯誤和訊息：

    在PostgreSQL中可以利用RAISE語句報告資訊和丟擲錯誤，其宣告形式為：
    RAISE level 'format' [, expression [, ...]];
    這裡包含的級別有DEBUG(向伺服器日誌寫資訊)、LOG(向伺服器日誌寫資訊，優先順序更高)、INFO、NOTICE和WARNING(把資訊寫到伺服器日誌以及轉發到客戶端應用，優先順序逐步升高)和EXCEPTION丟擲一個錯誤(通常退出當前事務)。某個優先順序別的資訊是報告給客戶端還是寫到伺服器日誌，還是兩個均有，是由log_min_messages和client_min_messages這兩個系統初始化引數控制的。
    在format部分中，%表示為佔位符，其實際值僅在RAISE命令執行時由後面的變數替換，如果要在format中表示%自身，可以使用%%的形式表示，見如下示例：
    RAISE NOTICE 'Calling cs_create_job(%)',v_job_id;  --v_job_id變數的值將替換format中的%。
    RAISE EXCEPTION 'Inexistent ID --&gt %',user_id;   

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9521459/viewspace-759318/，如需轉載，請註明出處，否則將追究法律責任。