PostgreSQL
上一頁 第十章. PL/pgSQL - SQL 程序語言 下一頁

描述

PL/pgSQL 的結構

PL/pgSQL 語言是大小寫不敏感的語言。所有關鍵字和標識都可以混合大小寫使用。

PL/pgSQL 是一種面向塊的語言。一個塊像下面這樣定義 

[<<label>>]
[DECLARE
    declarations]
BEGIN
    statements
END;
在一個語句塊（statement section）裡面可以有任意個子語句塊。子語句塊可以用於屏蔽語句塊外面的變數。在語句塊前面的聲明段（declarations section）裡定義的變數在每次進入語句塊時都初始化為它們的預設值，而不是每次函數調用時初始化一次。

我們一定不要把 PL/pgSQL 裡的分組語句 BEGIN/END 和用於事務控制的資料庫命令搞混了。函數和觸發器不能開始或提交一個事務並且Postgres 也沒有嵌套事務的用法。

注釋

在 PL/pgSQL 裡有兩種型態的注釋。雙破折號 '--' 引出到該行行尾的單行注釋。一個 '/*' 開始一個塊注釋，一直延伸到下一個 '*/'出現。塊注釋不能嵌套使用，但是雙破折號注釋可以封裝在一個塊注釋裡面並且雙破折號注釋可以隱藏一個塊注釋分隔符 '/*' 和 '*/'。

聲明

在一個語句塊或者它的子語句塊裡用到的所有變數，行和記錄都必須在一個語句塊的聲明段裡定義 －－ 除了 FOR 循環裡說明循環範圍的整數循環變數以外。賦予一個 PL/pgSQL 函數的參數自動用常用的標識 $n 定義。定義有下面語法：
name [ CONSTANT ] >type> [ NOT NULL ] [ DEFAULT | := value ];

 

 

定義一個指明了的基本型態的變數。如果變數被定義為 CONSTANT，該變數值就不能修改。如果聲明了 NOT NULL，則賦予一個 NULL 值給該變數將導致一個運行時錯誤。因為所有變數的預設值都是 SQL NULL，因而所有定義為 NOT NULL 的變數都必須有指明的預設值。

預設值在每次函數調用時都被計算。所以賦予 'now' 給一個型態為 datetime 的變數將導致變數擁有實際函數調用時的時間，而不是函數被編譯為字節碼的時間。
 

name class%ROWTYPE;

 

 

定義一個帶有指定表的結構的行。表必須是一個資料庫中現存的表或視圖的名稱。行的字段是通過點定義來訪問的。函數的參數可以是復合型態（表裡面完整的行）。這時，對應的標識 $n 將是一個行型態（rowtype），但是它必須用下面描述的 ALIAS 命令取個別名。在一行裡只有用戶字段可以被訪問，而 OID 或其他系統字段都不可訪問（因為行可能來自一個視圖，而視圖的行並沒有有用的系統字段）。

行型態（rowtype）字段繼承表中 char() 等數據型態的字段尺寸或精度。
 

name RECORD;

 

 

記錄（Records）類似於行型態（rowtypes），但它們沒有預定義的結構。它們在選擇和 FOR 循環中使用，用於從一個 SELECT 操作中保存一個具體的資料庫的行。該條記錄可以在不同的選擇中使用。當沒有實際的行存在於其中時試圖訪問一條記錄或賦予某個變數的值將導致一個運行時錯誤產生。

觸發器裡的 NEW 和 OLD 行是作為記錄傳給程序的。這一點是必要的，因為 Postgres 裡同樣的觸發器程序可以為不同的表控制觸發器事件。
 

name ALIAS FOR $n;

 

 

為了讓程式碼更具可讀性，我們可以為函數的位置參數定義一個別名。
 

當把復合型態做為參數傳遞給一個函數時要求這個別名。在 SQL 函數裡的點表示法 （$1.salary）在 PL/pgSQL 裡是不允許的。
RENAME oldname TO newname;

 

 

改變變數，記錄或者行的名稱。這一點當 NEW 或 OLD 在觸發器裡要被做為另一個名字引用時很有用。

數據型態

變數型態可以是任意資料庫裡現有的基本型態。上面聲明段裡的 type 是這樣定義的： variable 是變數名，事先在同一函數裡定義，在這一點上可見.

class 是現存表或視圖，field 是一個字段的名稱。

使用 class.field%TYPE 導致 PL/pgSQL 在後端的生存期裡第一次調用該函數時查看該字段的定義。如果我們有一個表有一個字段定義為 char(20) 而且一些 PL/pgSQL 函數在局部變數裡處理該字段的內容。現在我們發現 char(20) 不夠長，把表倒出來，刪除，重新把有問題的字段定義為 char(40) 然後恢復數據。哈－他忘了函數。函數裡的運算將把數值裁斷成20個字符。但是如果他們用 class.field%TYPE 做定義，它們將自動控制字段的長度的變化或新表把該字段定義為 text 型態。

表達式

在 PL/pgSQL 語句裡的所有表達式都用後端的執行器進行處理。包含約束的表達式可能實際上需要運行時計算（ 例如。datetime 型態的'now'），所以對於 PL/pgSQL 分析器而言，除了 NULL 關鍵字以外它是不可能識別真正常數的值的。所有表達式都通過用 SPI 管理器在內部運行查詢 
      SELECT expression
來計算。在表達式裡出現的變數標識都被參數和放在參數數組裡傳入執行器的變數實際值所代替。所有在 PL/pgSQL 函數裡用到的表達式都只準備和儲存一次。

Postgres 的主分析器做的型態檢查對轉換常數有一些副作用。詳細說來，下面的兩個函數有一些區別 

CREATE FUNCTION logfunc1 (text) RETURNS datetime AS '
    DECLARE
        logtxt ALIAS FOR $1;
    BEGIN
        INSERT INTO logtable VALUES (logtxt, ''now'');
        RETURN ''now'';
    END;
' LANGUAGE 'plpgsql';
和 
CREATE FUNCTION logfunc2 (text) RETURNS datetime AS '
    DECLARE
        logtxt ALIAS FOR $1;
        curtime datetime;
    BEGIN
        curtime := ''now'';
        INSERT INTO logtable VALUES (logtxt, curtime);
        RETURN curtime;
    END;
' LANGUAGE 'plpgsql';
在 logfunc1() 裡，Postgres 的主分析器在準備 INSERT 的規劃時認為，字符串 'now' 應該解釋為 datetime，因為 logtable 的目標字段是該型態。因此，這時它將從刈谹成一個常數並且在所有後端生存期內所有對 logfunc1() 的調用時使用該常數。不用說這可不是程式員希望的。

在 logfunc2() 裡，Postgres 的主分析器並不知道 'now' 應該轉換的型態，因此它返回一個包含字符串 'now' 的 text 數據型態。在給局部變數 curtime 賦值時，PL/pgSQL 解釋器通過調用 text_out()和 datetime_in() 把這個字符串轉換成 datetime 型態的變數。

這個 Postgres 主分析器的型態檢查是在 PL/pgSQL 接近完成的時候實現的。在版本 6.3 和版本 6.4 之間有所不同並且影響所有使用 SPI 管理器的規劃特性的函數。使用上面提到的局部變數的方法是目前能讓 PL/pgSQL 對那些數值正確解釋的唯一方法。

如果在表達式或語句裡用到記錄（record）字段，字段的數據型態在同一個表達式的不同調用中不應該改變。書寫控制多個表的觸發器程序時應該注意這一點。

語句

任何像下面聲明的 PL/pgSQL 分析器不能理解的東西將被放到查詢裡並發送給資料庫引擎執行。產生的查詢應該不返回任何數據。
賦值

 

 

給一個變數或行/記錄賦值用下面方法 

         identifier := expression;
如果表達式的結果數據型態和變數數據型態不一致，或者變數具有已知的尺寸/精度（例如 char(20)），結果值將隱含的被 PL/pgSQL 字節碼解釋器用結果型態的輸出函數和變數型態的輸入函數轉換。要注意這樣做可能潛在地導致型態輸入函數產生的運行時錯誤。

下面方法可以把一個完整的選擇放到一條記錄或者行裡 

SELECT expressions INTO target FROM ...;
target 可以是一條記錄，一行變數或一個逗號分隔的變數列表和記錄/行字段（域）。

如果一行或者一個變數列表當做目標，選擇的數值必須與目標列的結構完全一樣，否則會產生一條運行時錯誤。FORM 關鍵字可以跟隨著任意可在 SELECT 語句裡使用的有效的資格（條件），分組，排序等。

一個名為 FOUND 的特殊的布爾型態的變數可以在 SELECT INTO 之後立刻用於檢查賦值是否成功。 

SELECT * INTO myrec FROM EMP WHERE empname = myname;
IF NOT FOUND THEN
    RAISE EXCEPTION ''employee % not found'', myname;
END IF;
如果選擇返回多行，只有第一行被放到目標域裡面。其他所有都被悄悄地丟棄掉了。
 
調用其他函數

 

 

所有在 Prostgres 資料庫裡的函數返回一個值。因此，通常的調用函數的方法是執行一條 SELECT 查詢或者做一個賦值（導致一個 PL/pgSQL 的內部 SELECT ）。但是有時候我們對函數結果並不感興趣。 

PERFORM query
將在 SPI 管理器上執行一個 'SELECT query' 然後丟棄結果。像局部變數這樣的標識仍然代入參數。
從函數返回
RETURN expression
函數結束並且表達式 expression 的值將返回給上層執行器。函數的返回值不能取消定義。如果控制到達一個函數體的最頂層而沒有碰到一個 RETURN 語句，將產生一個運行時錯誤。

表達式的結果將被自動轉換成函數返回型態－－像我們在賦值裡描述的那樣。
 

退出和消息

 

 

像上面的例子指明的那樣，有一個 RAISE 語句可以丟出一條資訊到 Postgres elog 機制裡面。 

RAISE level ''format'' [, identifier [...]];
在格式 （ format ）裡面，“%” 用做一個後面跟隨的逗號分隔的標識的占位符。可能的級別（level）是 DEBUG （安靜地停止運行的資料庫），NOTICE （向資料庫書寫日誌並向前端客戶應用發送資訊）和 EXCEPTION （向資料庫書寫日誌並且退出事務 ）。
 
條件
IF expression THEN
    statements
[ELSE
    statements]
END IF;
表達式 expression 必須返回一個最起碼可以轉換成布爾型態的數值。
 
循環

 

 

有許多循環的型態。 

[<<label>>]
LOOP
    statements
END LOOP;
一個無條件循環必須用一條 EXIT 語句顯式終止。可選的標記 label 可以被 EXIT 用於嵌套循環用於聲明應該結束哪一層循環。 
[<<label>>]
WHILE expression LOOP
    statements
END LOOP;
一個條件循環在表達式 expression 為真時執行。 
[<<label>>]
FOR name IN [ REVERSE ] expression .. expression LOOP
    statements
END LOOP;
一個在某一整數範圍內執行的循環，變數 name 自動做為整數型態創建，並且只存在於循環內部。兩個表達式給出循環範圍的區間，它們只是在進入循環後才被計算出數值。循環步進值總是 1。 
[<<label>>]
FOR record | row IN select_clause LOOP
    statements
END LOOP;
記錄或行被賦予了 select 子句來的所有行，並且語句 statement 對每一條記錄/行都運行一次。如果用 EXIT 語句退出循環，最後賦值的行仍然在循環後可以被訪問。 
EXIT [ label ] [ WHEN expression ];
如果沒有標記 label ，最內層的循環將被結束並且 END LOOP 後面的語句將被接著執行。如果給出了標記 label ，該標記必須是當前或者上層嵌套的循環塊。那麼命名的循環或者語句塊被終止並且控制落到循環/語句塊的對應 END 的後面一條語句。

觸發器程序

PL/pgSQL 可以用於定義觸發器程序。它們通過通常的 CREATE FUNCTION 命令創建為沒有參數並且返回 OPAQUE 型態的函數。
做為觸發器程序的函數有一些 Postgres 特有的細節說明。
首先他們有一些在頂層的聲明段裡自動定義的特殊變數。有如下這些
 
NEW

 
數據型態是 RECORD﹔該變數保存著行（ROW）一級的觸發器在 INSERT/UPDATE 操作時的新的資料庫行。
OLD

 
數據型態是 RECORD﹔該變數保存著行（ROW）一級的觸發器在 INSERT/UPDATE 操作時的舊的資料庫行。
TG_NAME

 
數據型態是 name﹔該變數包含實際觸發的觸發器名。
TG_WHEN
數據型態是 text﹔是一個由觸發器定義決定的字符串，要麼是 'BEFORE' 要麼是 'AFTER'。
TG_LEVEL

 
數據型態是 text﹔是一個由觸發器定義決定的字符串，要麼是 'ROW' 要麼是 'STATEMENT'。
TG_OP

 
數據型態是 text﹔是一個說明觸發器實際進行的操作的字符串，可以是 'INSERT'，'UPDATE' 或者 'DELETE'。
TG_RELID

 
數據型態是 oid﹔是導致觸發器調用的表的對像標識（OID）。
TG_RELNAME

 
數據型態是 name﹔是激活觸發器調用的表的名稱。
TG_NARGS
數據型態是 integer﹔是在 CREATE TRIGGER 語句裡面賦予觸發器程序的參數的個數。
TG_ARGV[]

 

 

數據型態是 text 的數組﹔是 CREATE TRIGGER 語句裡的參數。下標從 0 開始記數，並且可以由一個表達式來表示。非法下標（< 0 或 >= tg_nargs）導致一個 NULL 值的返回。

其次，它們必須返回 NULL 或者是一個與導致觸發器運行的表的記錄/行完全一樣的結構的數據。AFTER 型態的觸發器可以總是返回一個沒有意義的 NULL 值。BEFORE 類的觸發器如果返回一個 NULL，將發送一個信號給觸發器管理器忽略對實際行的操作。否則，返回的記錄/行將代替插入/更新操作中的行。我們可能用一個值直接代替 NEW 裡的數值並且返回之或者我們也可以構建一個完全新的返回記錄/行。

例外

Postgres 不具有一個很好的例外處理模組。當分析器，規劃器（調度器）/最佳化器或者執行器認為一個語句不能在處理下去了，整個事務都退出並且系統跳回主循環等待從客戶應用過來的下一個查詢。

我們可以'鉤'在錯誤機制上來提示這種情況的發生。但是目前我們沒有能力告訴（用戶）是什麼導致了退出（輸入/輸出轉換錯誤，浮點數錯誤，分析錯誤）。並且此時的資料庫後端可能處在一種不連貫的狀態，所以退回到上層執行器或執行更多的命令可能摧毀整個資料庫。而且此時事務退出的資訊可能已經發送給了客戶端應用，所以繼續操作沒有任何意義。

因此，PL/pgSQL 在函數或觸發器操作時遇到退出的唯一一項操作是在 DEBUG 級別運行時輸出一些附加的日誌資訊，報告在哪個函數和在那裡（行號和語句型態）出了錯。

上一頁 首頁 下一頁
PL/pgSQL - SQL 程序語言 開頭 例子