PL/pgSQL 的結構 - Amazon Redshift

本文為英文版的機器翻譯版本,如內容有任何歧義或不一致之處,概以英文版為準。

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 支援的資料類型,還有RECORDrefcursor。如需有關 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; $$;