支援的 PL/pgSQL 陳述式 - Amazon Redshift

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

支援的 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

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

  • 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

在所使用的 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;

迴圈

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

  • 簡單迴圈

    [<<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 PERFORM cs_log('Refreshing reports...'); FOR reports IN SELECT * FROM cs_reports ORDER BY sort_key LOOP -- Now "reports" has one record from cs_reports PERFORM cs_log('Refreshing report ' || quote_ident(reports.report_name) || ' ...'); EXECUTE 'TRUNCATE TABLE ' || quote_ident(reports.report_name); EXECUTE 'INSERT INTO ' || quote_ident(reports.report_name) || ' ' || reports.report_query; END LOOP; PERFORM cs_log('Done refreshing reports.'); RETURN; END; $$ LANGUAGE plpgsql;

    該範例假設一個名為cs_log,這是虛構的。

  • 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 封裝查詢,每次在查詢結果中讀取幾列。這麼做的一個理由是當結果包含大量的列時,避免記憶體溢位。另一個理由是為了傳回預存程序已建立之游標的參考,此游標可讓發起人讀取列。此方法可以很有效率地從預存程序傳回很大的列集。

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

您可以將變數宣告為 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 陳述式來報告訊息和引發錯誤。

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

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

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

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

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

交易控制

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