PL/pgSQL 的結構

PL/pgSQL 是一種程序性語言，具有很多建構與其他程序性語言相同。

封鎖

PL/pgSQL 是區塊結構化語言。程序的整個主體都定義在區塊內，其中包含變數宣告和 PL/pgSQL 陳述式。陳述式也可以是巢狀區塊或子區塊。

以分號作為宣告和陳述式的結尾。在區塊或子區塊中的 END 關鍵字後面加上分號。請勿在關鍵字 DECLARE 和 BEGIN 後面使用分號。

您可以混合大小寫來撰寫所有關鍵字和識別符。識別符除非以雙引號括住，否則會隱含轉換為小寫。

雙連字號 (--) 表示註解開始，一直延續到行尾。/* 表示區塊註解開始，一直延續到出現下一個 */。您不能將區塊註解巢狀化。不過，您可以用區塊註解圍住雙連字號註解，雙連字號可以隱藏區塊註解分隔符號 /* 和 */。

在區塊的陳述式區段中，任何陳述式可以是子區塊。您可以使用子區塊進行邏輯分組，或將變數區域化給一小組陳述式。

[ <<label>> ]
[ DECLARE
  declarations ]
BEGIN
  statements
END [ label ];  

在區塊前面的宣告區段中所宣告的變數，在每次進入區塊時會初始化為其預設值。換言之，在每次函數呼叫時不只初始化一次。

下列顯示一個範例。

CREATE PROCEDURE update_value() AS $$
DECLARE
  value integer := 20;
BEGIN
  RAISE NOTICE 'Value here is %', value;  -- Value here is 20
  value := 50;
  --
  -- Create a subblock
  --
  DECLARE
    value integer := 80;
  BEGIN
    RAISE NOTICE 'Value here is %', value;  -- Value here is 80
  END;

  RAISE NOTICE 'Value here is %', value;  -- Value here is 50
END;
$$ LANGUAGE plpgsql;               

使用標籤來識別要在 EXIT 陳述式中使用的區塊，或限定區塊中宣告的變數名稱。

請勿混淆 PL/pgSQL 中用於陳述式分組的 BEGIN/END 與用於交易控制的資料庫命令。PL/pgSQL 中的 BEGIN 和 END 只用於分組。而無法用來啟動或結束交易。

變數宣告

在區塊的 DECLARE 區段中宣告區塊的所有變數，但迴圈變數除外。變數可以使用任何有效的 Amazon Redshift 資料類型。關於支援的資料類型，請參閱資料類型。

PL/pgSQL 變數可以是 Amazon Redshift 支援的任何資料類型，還有 RECORD 和 refcursor。如需有關 RECORD 的詳細資訊，請參閱 記錄類型。如需有關 refcursor 的詳細資訊，請參閱 游標。

DECLARE
name [ CONSTANT ] type [ NOT NULL ] [ { DEFAULT | := } expression ];

以下，您可以找到變數宣告範例。

customerID integer;
numberofitems numeric(6);
link varchar;
onerow RECORD;

對整數範圍反覆運算的 FOR 迴圈的迴圈變數，自動宣告為整數變數。

DEFAULT 子句 (如有提供) 指定進入區塊時指派給變數的初始值。如果未提供 DEFAULT 子句，變數會初始化為 SQL NULL 值。CONSTANT 選項防止指派變數，因此在整個區塊內，值維持固定。如果指定 NOT NULL，則指派空值會導致執行時間錯誤。所有宣告為 NOT NULL 的變數必須指定非空值的預設值。

每次進入區塊時會評估預設值。例如，假設指派 now() 給 timestamp 類型的變數，則變數會具有目前函數呼叫的時間，而非預先編譯函數時的時間。

quantity INTEGER DEFAULT 32;
url VARCHAR := 'http://mysite.com';
user_id CONSTANT INTEGER := 10;

refcursor 資料類型是預存程序內的游標變數的資料類型。預存程序可以傳回 refcursor 值。如需更多詳細資訊，請參閱 傳回結果集。

別名宣告

如果預存程序的簽章省略引數名稱，您可以宣告該引數的別名。

name ALIAS FOR $n;

內建變數

支援下列內建變數：

• FOUND

• SQLSTATE

• SQLERRM

• GET DIAGNOSTICS integer_var := ROW_COUNT;

FOUND 是布林值類型的特殊變數。在每個程序呼叫內，FOUND 剛開始為 false。FOUND 由下列類型的陳述式來設定：

• SELECT INTO

  將 FOUND 設為 true 表示傳回一列，設為 false 表示未傳回任何列。

• UPDATE、INSERT 和 DELETE

  將 FOUND 設為 true 表示至少一列受影響，設為 false 表示不影響任何列。

• FETCH

  將 FOUND 設為 true 表示傳回一列，設為 false 表示未傳回任何列。

• FOR 陳述式

  將 FOUND 設為 true 表示 FOR 陳述式反覆運算一或多次，否則為設為 false。這適用於 FOR 陳述式的所有三種變體：整數 FOR 迴圈、記錄集 FOR 迴圈，以及動態記錄集 FOR 迴圈。

  在 FOR 迴圈結束時設定 FOUND。在迴圈執行期內，FOR 陳述式不會修改 FOUND。不過，迴圈主體內的其他陳述式執行可能變更它。

下列顯示一個範例。

CREATE TABLE employee(empname varchar);
CREATE OR REPLACE PROCEDURE show_found()
AS  $$
DECLARE
  myrec record;
BEGIN
  SELECT INTO myrec * FROM employee WHERE empname = 'John';
  IF NOT FOUND THEN
    RAISE EXCEPTION 'employee John not found';
  END IF;
END;
$$ LANGUAGE plpgsql;

在例外處理常式內，特殊變數 SQLSTATE 包含錯誤碼，對應於已引發的例外狀況。特殊變數 SQLERRM 包含與例外狀況相關聯的錯誤訊息。這些變數在例外處理常式外未定義，如果使用，則會顯示錯誤。

下列顯示一個範例。

CREATE OR REPLACE PROCEDURE sqlstate_sqlerrm() AS
$$
BEGIN
  UPDATE employee SET firstname = 'Adam' WHERE lastname = 'Smith';
  EXECUTE 'select invalid';
  EXCEPTION WHEN OTHERS THEN
  RAISE INFO 'error message SQLERRM %', SQLERRM;
  RAISE INFO 'error message SQLSTATE %', SQLSTATE;
END;
$$ LANGUAGE plpgsql;               

ROW_COUNT 與 GET DIAGNOSTICS 命令搭配使用。它顯示傳送到 SQL 引擎的最後一個 SQL 命令所處理的列數。

下列顯示一個範例。

CREATE OR REPLACE PROCEDURE sp_row_count() AS
$$
DECLARE
  integer_var int;
BEGIN
  INSERT INTO tbl_row_count VALUES(1);
  GET DIAGNOSTICS integer_var := ROW_COUNT;
  RAISE INFO 'rows inserted = %', integer_var;
END;
$$ LANGUAGE plpgsql;               

記錄類型

RECORD 類型不是真的資料類型，只是預留位置。記錄類型變數採用 SELECT 或 FOR 命令期間指派給它們的列的實際列結構。記錄變數在每次被指派一個值時可能變更子結構。記錄變數在第一次被指派之前沒有子結構。嘗試存取其中的欄位會擲出執行時間錯誤。

name RECORD;

下列顯示一個範例。

CREATE TABLE tbl_record(a int, b int);
INSERT INTO tbl_record VALUES(1, 2);
CREATE OR REPLACE PROCEDURE record_example()
LANGUAGE plpgsql
AS $$
DECLARE
  rec RECORD;
BEGIN
  FOR rec IN SELECT a FROM tbl_record
  LOOP
    RAISE INFO 'a = %', rec.a;
  END LOOP;
END;
$$;