支援的 PL/pgSQL 陳述式 - Amazon Redshift
指派SELECT INTO無操作動態 SQL傳回條件式：IF 條件式：CASE迴圈游標RAISE交易控制

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

支援的 PL/pgSQL 陳述式

PL/pgSQL 陳述式以程序性建構 (包括迴圈和條件式表達式) 來擴增 SQL 命令，以控制邏輯流程。可使用大部分 SQL 命令，包括資料處理語言 (DML) (例如 COPY、UNLOAD 和 INSERT) 和資料定義語言 (DDL) (例如 CREATE TABLE)。如需完整 SQL 命令的清單，請參閱 SQL 命令。此外，Amazon Redshift 還支援下列 PL/pgSQL 陳述式。

指派

指派陳述式可指派值給變數。表達式必須傳回單一值。

identifier := expression;

也接受使用非標準的 = 來指派 (而不是 :=)。

如果表達式的資料類型不符合變數的資料類型，或變數具有大小或精確度，則會隱含轉換結果值。

如下列範例所示。

customer_number := 20;
tip := subtotal * 0.15;

SELECT INTO

SELECT INTO 陳述式將多欄 (但只有一列) 的結果指派給一個記錄變數或一個純量變數清單。

SELECT INTO target select_expressions FROM ...;

在上述語法中，target 可以是記錄變數，或簡單變數和記錄欄位的逗號分隔清單。select_expressions 清單和命令的剩餘部分與一般 SQL 中相同。

如果以變數清單作為 target，選取的值必須完全符合目標的結構，否則會發生執行時間錯誤。當記錄變數為目標時，它本身會自動設定為查詢結果欄的列類型。

INTO 子句幾乎可出現在 SELECT 陳述式中的任意處。通常就出現在 SELECT 子句之後，或就在 FROM 子句之前。亦即，就出現在 select_expressions 清單之前或之後。

如果查詢未傳回任何列，NULL 值會指派給 target。如果查詢傳回多列，第一列會指派給 target，其餘捨棄。除非陳述式包含 ORDER BY，否則無法確定第一列。

若要判斷指派是否傳回至少一列，請使用特殊的 FOUND 變數。

SELECT INTO customer_rec * FROM cust WHERE custname = lname;
IF NOT FOUND THEN
  RAISE EXCEPTION 'employee % not found', lname;
END IF;

若要測試記錄結果是否為空值，您可以使用 IS NULL 條件。無法判斷是否已捨棄任何其他列。下列範例處理未傳回任何列的情況。

CREATE OR REPLACE PROCEDURE select_into_null(return_webpage OUT varchar(256))
AS $$
DECLARE
  customer_rec RECORD;
BEGIN
  SELECT INTO customer_rec * FROM users WHERE user_id=3;
  IF customer_rec.webpage IS NULL THEN
    -- user entered no webpage, return "http://"
    return_webpage = 'http://';
  END IF;
END;
$$ LANGUAGE plpgsql;

無操作

無操作陳述式 (NULL;) 是不執行任何動作的預留位置陳述式。無操作陳述式可以表示 IF-THEN-ELSE 鏈的一個分支是空的。

NULL;

動態 SQL

若要產生動態命令，以便每次從 PL/pgSQL 預存程序執行時，都可涉及不同的資料表或不同的資料類型，請使用 EXECUTE 陳述式。

EXECUTE command-string [ INTO target ];

在上述語法中，command-string 是產生字串 (文字類型) 的表達式，而此字串包含要執行的命令。此 command-string 值會傳送到 SQL 引擎。在命令字串上不會替換 PL/pgSQL 變數。您必須在建構命令字串時插入變數的值。

注意

您無法從動態 SQL 內使用 COMMIT 和 ROLLBACK 陳述式。如需在預存程序內使用 COMMIT 和 ROLLBACK 陳述式的相關資訊，請參閱管理交易。

使用動態命令時，您通常需要處理單引號逸出。建議使用 $ 符號引用來圍住函數主體中放在引號內的固定字串。在建構的查詢中要插入的動態值需要特別處理，因為動態值本身可能包含引號。下列範例對整個函數採用 $ 符號引用，因此引號不需要加倍。

EXECUTE 'UPDATE tbl SET '
  || quote_ident(colname)
  || ' = '
  || quote_literal(newvalue)
  || ' WHERE key = '
  || quote_literal(keyvalue);

上述範例顯示函數 quote_ident(text) 和 quote_literal(text)。此範例將包含欄和資料表識別符的變數傳給 quote_ident 函數。也將包含所建構命令中常值字串的變數傳給 quote_literal 函數。這兩個函數都採取適當步驟，傳回分別以雙引號或單引號括住的輸入文字，並適當地逸出任何內嵌的特殊字元。

$ 符號引用僅適用於括住固定文字。請勿將上述範例寫成下列格式。

EXECUTE 'UPDATE tbl SET '
  || quote_ident(colname)
  || ' = $$'
  || newvalue
  || '$$ WHERE key = '
  || quote_literal(keyvalue);

不能這樣做，因為如果 newvalue 的內容碰巧包含 $$，則範例會失敗。您可能選擇的任何其他 $ 符號引用分隔符號也有相同問題。若要安心地括住無法事先得知的文字，請使用 quote_literal 函數。

傳回

RETURN 陳述式從預存程序傳回到發起人。

RETURN;

下列顯示一個範例。

CREATE OR REPLACE PROCEDURE return_example(a int)
AS $$  
BEGIN
  FOR b in 1..10 LOOP
    IF b < a THEN
      RAISE INFO 'b = %', b;
    ELSE
      RETURN;
    END IF;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

條件式：IF

在 Amazon Redshift 所使用的 PL/pgSQL 語言中，IF 條件式陳述式有下列形式：

  • IF ... THEN

    IF boolean-expression THEN
  statements
END IF;

    下列顯示一個範例。

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

  • IF ... THEN ... ELSE

    IF boolean-expression THEN
  statements
ELSE
  statements
END IF;

    下列顯示一個範例。

    IF parentid IS NULL OR parentid = ''
THEN
  return_name = fullname;
  RETURN;
ELSE
  return_name = hp_true_filename(parentid) || '/' || fullname;
  RETURN;
END IF;

  • IF ... THEN ... ELSIF ... THEN ... ELSE

    關鍵字 ELSIF 也可以拼寫為 ELSEIF。

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

    下列顯示一個範例。

    IF number = 0 THEN
  result := 'zero';
ELSIF number > 0 THEN
  result := 'positive';
ELSIF number < 0 THEN
  result := 'negative';
ELSE
  -- the only other possibility is that number is null
  result := 'NULL';
END IF;

條件式：CASE

在 Amazon Redshift 所使用的 PL/pgSQL 語言中，CASE 條件式陳述式有下列形式：

  • 簡單 CASE 

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

    簡單 CASE 陳述式可根據運算元等式而有條件地執行。

    search-expression 值會評估一次，然後連續地與 WHEN 子句中的每個 expression 相比較。如果發現相符，則對應的 statements 會執行，接著控制權就移轉到 END CASE 之後的下一個陳述式。不會評估後續的 WHEN expressions。如果找不到相符，則 ELSE statements 會執行。不過，如果 ELSE 不存在，則會引發 CASE_NOT_FOUND 例外狀況。

    下列顯示一個範例。

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

  • 搜尋的 CASE 

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

    搜尋形式的 CASE 可根據布林值表達式的真實性而有條件地執行。

    每個 WHEN 子句的 boolean-expression 會依次評估，直到發現產生 true 為止。然後對應的陳述式會執行，接著控制權就移轉到 END CASE 之後的下一個陳述式。不會評估後續的 WHEN expressions。如果找不到 true 結果，則 ELSE statements 會執行。不過，如果 ELSE 不存在，則會引發 CASE_NOT_FOUND 例外狀況。

    下列顯示一個範例。

    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;

迴圈

在 Amazon Redshift 所使用的 PL/pgSQL 語言中，迴圈陳述式有下列形式：

  • 簡單迴圈 

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

    簡單迴圈定義無條件的迴圈，將會無限期重複，直到由 EXIT 或 RETURN 陳述式終止為止。巢狀迴圈內的 EXIT 和 CONTINUE 陳述式可使用選用標籤，以指定 EXIT 和 CONTINUE 陳述式所指的迴圈。

    下列顯示一個範例。

    CREATE OR REPLACE PROCEDURE simple_loop()
LANGUAGE plpgsql
AS $$
BEGIN
  <<simple_while>>
  LOOP
    RAISE INFO 'I am raised once';  
    EXIT simple_while;
    RAISE INFO 'I am not raised';
  END LOOP;
  RAISE INFO 'I am raised once as well';
END;
$$;

  • 結束迴圈

    EXIT [ label ] [ WHEN expression ];

    如果 label 不存在，最內層迴圈會終止，END LOOP 之後的陳述式會接下去執行。如果 label 存在，它必須是巢狀迴圈或區塊的目前或某個外層的標籤。然後，具名迴圈或區塊會終止，控制權會延續到迴圈或區塊相對應 END 之後的陳述式。

    如果指定 WHEN，只有在 expression 為 true 時，迴圈才會結束。否則，控制權會移轉到 EXIT 之後的陳述式。

    您可以對所有類型的迴圈使用 EXIT；不限於用於無條件的迴圈。

    與 BEGIN 區塊一起使用時，EXIT 會將控制權移轉到區塊結束之後的下一個陳述式。為此，必須使用標籤。無標籤的 EXIT 絕不可能有對稱的 BEGIN 區塊。

    下列顯示一個範例。

    CREATE OR REPLACE PROCEDURE simple_loop_when(x int)
LANGUAGE plpgsql
AS $$
DECLARE i INTEGER := 0;
BEGIN
  <<simple_loop_when>>
  LOOP
    RAISE INFO 'i %', i;
    i := i + 1;
    EXIT simple_loop_when WHEN (i >= x);
  END LOOP;
END;
$$;

  • 繼續迴圈 

    CONTINUE [ label ] [ WHEN expression ];

    如果未提供 label，執行會跳到最內層迴圈的下一次反覆運算。亦即會略過迴圈主體中剩餘的所有陳述式。然後，控制權會返回到迴圈控制表達式 (如果有的話)，以決定是否需要再一次迴圈反覆運算。如果 label 存在，它指定繼續執行的迴圈的標籤。

    如果指定 WHEN，只有在 expression 為 true 時，迴圈的下一次反覆運算才會開始。否則，控制權會移轉到 CONTINUE 之後的陳述式。

    您可以對所有類型的迴圈使用 CONTINUE；不限於用於無條件的迴圈。

    CONTINUE mylabel;

  • WHILE 迴圈 

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

    只要 boolean-expression 評估為 true，WHILE 陳述式會重複一連串陳述式。就在進入迴圈主體之前會檢查表達式。

    下列顯示一個範例。

    WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
  -- some computations here
END LOOP;

WHILE NOT done LOOP
  -- some computations here
END LOOP;

  • FOR 迴圈 (整數變體) 

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

    FOR 迴圈 (整數變數) 建立迴圈對整數值範圍反覆運算。變數名稱會自動定義為整數類型，並且只在迴圈內結束。迴圈內會忽略變數名稱的任何現有定義。定義範圍下限和上限的兩個表達式會在進入迴圈時評估一次。如果您指定 REVERSE，則每一次反覆運算後會減去間距值，而不是相加。

    如果下限大於上限 (或在 REVERSE 情況下是小於)，迴圈主體不會執行。不會引發錯誤。

    如果標籤附加到 FOR 迴圈，則您可以使用該標籤，以限定名稱來參考整數迴圈變數。

    下列顯示一個範例。

    FOR i IN 1..10 LOOP
  -- i will take on the values 1,2,3,4,5,6,7,8,9,10 within the loop
END LOOP;

FOR i IN REVERSE 10..1 LOOP
  -- i will take on the values 10,9,8,7,6,5,4,3,2,1 within the loop
END LOOP;

  • FOR 迴圈 (結果集變體) 

    [<<label>>]
FOR target IN query LOOP
  statements
END LOOP [ label ];

    target 是記錄變數，或純量變數的逗號分隔清單。從查詢產生的每一列會連續指派給目標，而每一列會執行一次迴圈主體。

    FOR 迴圈 (結果集變體) 可讓預存程序逐一查看查詢的結果，並相應地操作該資料。

    下列顯示一個範例。

    CREATE PROCEDURE cs_refresh_reports() AS $$
DECLARE
  reports RECORD;
BEGIN
  FOR reports IN SELECT * FROM cs_reports ORDER BY sort_key LOOP
    -- Now "reports" has one record from cs_reports
    EXECUTE 'TRUNCATE TABLE ' || quote_ident(reports.report_name);
    EXECUTE 'INSERT INTO ' || quote_ident(reports.report_name) || ' ' || reports.report_query;
  END LOOP;
  RETURN;
END;
$$ LANGUAGE plpgsql;

  • FOR 迴圈搭配動態 SQL

    [<<label>>]
FOR record_or_row IN EXECUTE text_expression LOOP 
  statements
END LOOP;

    FOR 迴圈搭配動態 SQL 可讓預存程序逐一查看動態查詢的結果，並相應地操作該資料。

    下列顯示一個範例。

    CREATE OR REPLACE PROCEDURE for_loop_dynamic_sql(x int)
LANGUAGE plpgsql
AS $$
DECLARE
  rec RECORD;
  query text;
BEGIN
  query := 'SELECT * FROM tbl_dynamic_sql LIMIT ' || x;
  FOR rec IN EXECUTE query
  LOOP
    RAISE INFO 'a %', rec.a;
  END LOOP;
END;
$$;

游標

您可以設定游標，而不要一次執行整個查詢。cursor 封裝查詢，每次在查詢結果中讀取幾列。這麼做的一個理由是當結果包含大量的列時，避免記憶體溢位。另一個理由是為了傳回預存程序已建立之游標的參考，此游標可讓發起人讀取列。此方法可以很有效率地從預存程序傳回很大的列集。

若要在 NONATOMIC 預存程序中使用游標，請將游標迴圈置於 START TRANSACTION...COMMIT 之間。

若要設定游標，首先要宣告游標變數。在 PL/pgSQL 中存取游標完全是透過游標變數，該變數一定是特殊資料類型 refcursor。refcursor 資料類型只是保留游標的參考。

您可以將變數宣告為 refcursor 類型，以建立游標變數。或者，您可以使用如下的游標宣告語法。

name CURSOR [ ( arguments ) ] FOR query ;

在上述語法中，arguments (如有指定) 是 name datatype 組的逗號分隔清單，每一組定義要由 query 中的參數值所取代的名稱。稍後開啟游標時指定用來替換這些名稱的實際值。

如下列範例所示。

DECLARE
  curs1 refcursor;
  curs2 CURSOR FOR SELECT * FROM tenk1;
  curs3 CURSOR (key integer) IS SELECT * FROM tenk1 WHERE unique1 = key;

這三個變數全部都是資料類型 refcursor，但第一個可用於任何查詢。相反地，第二個已繫結完整指定的查詢，而最後一個已繫結參數化查詢。開啟游標時，key 值會換成整數參數值。變數 curs1 可說成未繫結，因為沒有繫結至任何特定查詢。

游標必須先開始，才能用來擷取列。PL/pgSQL 有三種形式的 OPEN 陳述式，其中兩種使用未繫結的游標變數，第三種使用繫結的游標變數：

  • 開啟以選取：開啟游標變數，並給予要執行的指定查詢。游標不能已開啟。另外，還必須已宣告為未繫結的游標 (亦即，宣告為簡單的 refcursor 變數)。SELECT 查詢的處理方式與 PL/pgSQL 中的其他 SELECT 陳述式相同。

    OPEN cursor_name FOR SELECT ...;

    下列顯示一個範例。

    OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;

  • 開啟以執行：開啟游標變數，並給予要執行的指定查詢。游標不能已開啟。另外，還必須已宣告為未繫結的游標 (亦即，宣告為簡單的 refcursor 變數)。將查詢指定為字串表達式的方式與 EXECUTE 命令中相同。此方法很有彈性，可讓查詢隨著每一次執行而變化。

    OPEN cursor_name FOR EXECUTE query_string;

    下列顯示一個範例。

    OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);

  • 開啟繫結的游標：這種 OPEN 用於開啟已在宣告時繫結查詢的游標變數。游標不能已開啟。僅當游標宣告為接受引數時，實際引數值表達式的清單才必須出現。查詢中會替換這些值。

    OPEN bound_cursor_name [ ( argument_values ) ];

    下列顯示一個範例。

    OPEN curs2;
OPEN curs3(42);

開啟游標後，您可以利用下述的陳述式來使用它。這些陳述式不一定要出現在開啟游標的同一預存程序中。您可以從預存程序傳回 refcursor 值，並讓發起人繼續操作游標。交易結束時，所有入口會隱含關閉。因此，只有在交易結束後，您才可以使用 refcursor 值來參考開啟的游標。

  • FETCH 從游標中將下一列擷取到目標。此目標可以是列變數、記錄變數，或簡單變數的逗號分隔清單，如同 SELECT INTO 一樣。如同 SELECT INTO 一樣，您可以檢查特殊變數 FOUND，查明是否已取得一列。

    FETCH cursor INTO target;

    下列顯示一個範例。

    FETCH curs1 INTO rowvar;

  • CLOSE 會將已開啟的游標的基礎入口關閉。在交易結束之前，您可以使用此陳述式來提早釋放資源。您也可以使用此陳述式來釋放游標變數，供再次開啟。

    CLOSE cursor;

    下列顯示一個範例。

    CLOSE curs1;

RAISE

使用 RAISE level 陳述式來報告訊息和引發錯誤。

RAISE level 'format' [, variable [, ...]];

可能的等級包括 NOTICE、INFO、LOG、WARNING 和 EXCEPTION。EXCEPTION 會引發錯誤，通常會取消目前的交易。其他等級只產生不同優先等級的訊息。

在格式字串內，% 會換成下一個選用引數的字串表示法。撰寫 %% 以發出常值 %。目前，選用引數必須是簡單變數，不是表達式，而格式必須是簡單字串常值。

在下列範例中，v_job_id 的值會取代字串中的 %。

RAISE NOTICE 'Calling cs_create_job(%)', v_job_id;

使用 RAISE 陳述式重新擲出例外狀況處理區塊捕獲的例外狀況。這個陳述式僅在 NONATOMIC 模式預存程序的例外狀況處理區塊中有效。

RAISE;

交易控制

您可以在 Amazon Redshift 所使用的 PL/pgSQL 語言中使用交易控制陳述式。如需在預存程序內使用 COMMIT、ROLLBACK 和 TRUNCATE 陳述式的相關資訊，請參閱管理交易。

在 NONATOMIC 模式預存程序中，使用 START TRANSACTION 啟動交易區塊。

START TRANSACTION;
注意

PL/pgSQL 陳述式 START TRANSACTION 與 SQL 命令 START TRANSACTION 有以下不同：

  • 在預存程序中，START TRANSACTION 與 BEGIN 不同義。

  • PL/pgSQL 陳述式不支援選擇性的隔離層級和存取許可關鍵字。