PolarDB：基本語句

賦值

為一個PL/SQL變數賦一個值可以被寫為：

    variable { := | = } expression;

正如以前所解釋的，這樣一個語句中的運算式被以一個 SQL SELECT命令被發送到主要資料庫引擎的方式計算。 該運算式必須得到一個單一值（如果該變數是一個行或記錄變數， 它可能是一個行值）。該目標變數可以是一個簡單變數（ 可以選擇用一個塊名限定）、一個行或記錄變數的域或是一個簡單變數或域的數組元素。 等號（=）可以被用來代替 PL/SQL-相容的 :=。

如果該運算式的結果資料類型不匹配變數的資料類型，該值將被強製為變數的類型，就好像做了賦值造型一樣。 如果沒有用於所涉及到的資料類型的賦值造型可用， PL/SQL解譯器將嘗試以文本的方式轉換結果值，也就是在應用結果類型的輸出函數之後再應用變數類型的輸入函數。如果結果值的字串形式無法被輸入函數所接受，這可能會導致由輸入函數產生的執行階段錯誤。

例子：

    tax := subtotal * 0.06;
    my_record.user_id := 20;

執行一個沒有結果的命令

對於任何不返回行的 SQL 命令（例如沒有一個RETURNING子句的INSERT），你可以通過把該命令直接寫在一個 PL/SQL 函數中執行它。

任何出現在該命令文本中的PL/SQL變數名被當作一個參數，並且接著該變數的當前值被提供為運行時該參數的值。這與早前描述的對錶達式的處理完全相似。

當以這種方式執行一個 SQL 命令時，PL/SQL會為該命令緩衝並重用執行計畫。

有時候計算一個運算式或SELECT查詢但拋棄其結果是有用的，例如調用一個有副作用但是沒有有用的結果值的函數。在PL/SQL中要這樣做，可使用PERFORM語句：

    PERFORM query;

這會執行query並且丟棄掉結果。以寫一個 SQL SELECT命令相同的方式寫該query，並且將初始的關鍵詞SELECT替換為PERFORM。對於WITH查詢，使用PERFORM並且接著把該查詢放在圓括弧中（在這種情況中，該查詢只能返回一行）。PL/SQL變數將被替換到該查詢中，正像對不返回結果的命令所做的那樣，並且計劃被以相同的方式被緩衝。還有，如果該查詢產生至少一行，特殊變數FOUND會被設定為真，而如果它不產生行則設定為假。

一個例子：

    PERFORM create_mv('cs_session_page_requests_mv', my_query);

執行一個有單一行結果的查詢

一個產生單一行（可能有多個列）的 SQL 命令的結果可以被賦值給一個記錄變數、行類型變數或標量變數列表。這通過書寫基礎 SQL 命令並增加一個INTO子句來達成。例如：

    SELECT select_expressions INTO [STRICT] target FROM ...;
    INSERT ... RETURNING expressions INTO [STRICT] target;
    UPDATE ... RETURNING expressions INTO [STRICT] target;
    DELETE ... RETURNING expressions INTO [STRICT] target;

其中target可以是一個記錄變數、一個行變數或一個有逗號分隔的簡單變數和記錄/行域列表。PL/SQL變數將被替換到該查詢的剩餘部分中，並且計劃會被緩衝，正如之前描述的對不返回行的命令所做的。這對SELECT、帶有RETURNING的INSERT/UPDATE/DELETE以及返回行集結果的工具命令（例如EXPLAIN）。除了INTO子句，SQL 命令和它在PL/SQL之外的寫法一樣。

如果一行或一個變數列表被用作目標，該查詢的結果列必須完全符合該結果的結構，包括數量和資料類型，否則會發生一個執行階段錯誤。當一個記錄變數是目標時，它會自動地把自身配置成查詢結果列組成的行類型。

INTO子句幾乎可以出現在 SQL 命令中的任何位置。通常它被寫成剛好在SELECT命令中的select_expressions列表之前或之後，或者在其他命令類型的命令最後。我們推薦你遵循這種慣例，以防PL/SQL的解析器在未來的版本中變得更嚴格。

如果STRICT沒有在INTO子句中被指定，那麼target將被設定為該查詢返回的第一個行，或者在該查詢不返回行時設定為空白（注意除非使用了ORDER BY，否則“第一行”的界定並不清楚）。第一行之後的任何結果行都會被拋棄。你可以檢查特殊的FOUND變數來確定是否返回了一行：

    SELECT * INTO myrec FROM emp WHERE empname = myname;
    IF NOT FOUND THEN
        RAISE EXCEPTION 'employee % not found', myname;
    END IF;

如果指定了STRICT選項，該查詢必須剛好返回一行或者將會報告一個執行階段錯誤，該錯誤可能是NO_DATA_FOUND（沒有行）或TOO_MANY_ROWS（多於一行）。如果你希望捕捉該錯誤，可以使用一個異常塊，例如：

    BEGIN
    SELECT * INTO STRICT myrec FROM emp WHERE empname = myname;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                RAISE EXCEPTION 'employee % not found', myname;
            WHEN TOO_MANY_ROWS THEN
                RAISE EXCEPTION 'employee % not unique', myname;
    END;

成功執行一個帶STRICT的命令總是會將FOUND置為真。

對於帶有RETURNING的INSERT/UPDATE/DELETE，即使沒有指定STRICT，PL/SQL也會針對多於一個返回行的情況報告一個錯誤。這是因為沒有類似於ORDER BY的選項可以用來決定應該返回哪個被影響的行。

如果為該函數啟用了 If print_strict_params，那麼當因為 STRICT的要求沒有被滿足而拋出一個錯誤時，該錯誤訊息的DETAIL將包括傳遞給該查詢的參數資訊。可以通過設定 plpgsql.print_strict_params為所有函數更改 print_strict_params設定，但是只有修改後被編譯的函數才會生效。也可以使用一個編譯器選項來為一個函數啟用它，例如：

    CREATE FUNCTION get_userid(username text) RETURN int
    IS
    #print_strict_params on
    DECLARE
    userid int;
    BEGIN
        SELECT users.userid INTO STRICT userid
            FROM users WHERE users.username = get_userid.username;
        RETURN userid;
    END;

失敗時，這個函數會產生一個這樣的錯誤訊息

    ERROR:  query returned no rows
    DETAIL:  parameters: $1 = 'nosuchuser'
    CONTEXT:  PL/SQL function get_userid(text) line 6 at SQL statement

執行動態命令

很多時候你將想要在PL/SQL函數中產生動態命令，也就是每次執行中會涉及到不同表或不同資料類型的命令。PL/SQL通常對於命令所做的緩衝計劃嘗試在這種情境下無法工作。要處理這一類問題，需要提供EXECUTE語句：

    EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] ];

其中command-string是一個能得到一個包含要被執行命令字串（類型text）的運算式。可選的target是一個記錄變數、一個行變數或者一個逗號分隔的簡單變數以及記錄/行域的列表，該命令的結果將儲存在其中。可選的USING運算式提供要被插入到該命令中的值。

在計算得到的命令字串中，不會做PL/SQL變數的替換。任何所需的變數值必須在命令字串被構造時被插入其中，或者你可以使用下面描述的參數。

還有，對於通過EXECUTE執行的命令不會有計劃被緩衝。該命令反而在每次運行時都會被做計劃。因此，該命令字串可以在執行不同表和列上動作的函數中被動態建立。

INTO子句指定一個返回行的 SQL 命令的結果應該被賦值到哪裡。如果提供了一個行或變數列表，它必須完全符合查詢結果的結構（當使用一個記錄變數時，它會自動把它自己配置為匹配結果結構）。如果返回多個行，只有第一個行會被賦值給INTO變數。如果沒有返回行，NULL 會被賦值給INTO變數。如果沒有指定INTO變數，該查詢結果會被拋棄。

如果給出了STRICT選項，除非該查詢剛好產生一行，否則將會報告一個錯誤

命令字串可以使用參數值，它們在命令中用$1、$2等引用。這些符號引用在USING子句中提供的值。這種方法常常更適合於把資料值作為文本插入到命令字串中：它避免了將該值轉換為文本以及轉換回來的運行時負荷，並且它更不容易被 SQL 插入式攻擊，因為不需要引用或轉義。一個例子是：

    EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2'
       INTO c
       USING checked_user, checked_date;

需要注意的是，參數符號只能用於資料值 — 如果想要使用動態決定的表名或列名，你必須將它們以文本形式插入到命令字串中。例如，如果前面的那個查詢需要在一個動態選擇的表上執行，你可以這麼做：

    EXECUTE 'SELECT count(*) FROM '
        || quote_ident(tabname)
        || ' WHERE inserted_by = $1 AND inserted <= $2'
       INTO c
       USING checked_user, checked_date;

一種更乾淨的方法是為表名或者列名使用format()的 %I規範（被新行分隔的字串會被串接起來）：

    EXECUTE format('SELECT count(*) FROM %I '
       'WHERE inserted_by = $1 AND inserted <= $2', tabname)
       INTO c
       USING checked_user, checked_date;

另一個關於參數符號的限制是，它們只能在SELECT、INSERT、UPDATE和DELETE命令中工作。在另一種語句類型（通常被稱為實用語句）中，即使值是資料值，你也必須將它們以文本形式插入。

在上面第一個例子中，帶有一個簡單的常量命令字串和一些USING參數的EXECUTE命令在功能上等效於直接用PL/SQL寫的命令，並且允許自動發生PL/SQL變數替換。重要的不同之處在於，EXECUTE會在每一次執行時根據當前的參數值重新規劃該命令，而PL/SQL則是建立一個通用計劃並且將其緩衝以便重用。在最佳計劃強依賴於參數值的情況中，使用EXECUTE來明確地保證不會選擇一個通用計劃是很有協助的。

EXECUTE目前不支援SELECT INTO。但是可以執行一個純的SELECT命令並且指定INTO作為EXECUTE本身的一部分。

在使用動態命令時經常不得不處理單引號的轉義。我們推薦在函數體中使用貨幣符號引用來引用固定的文本。

動態值需要被小心地處理，因為它們可能包含引號字元。一個使用 format()的例子（這假設你用貨幣符號引用了函數體，因此引號不需要被雙寫）：

    EXECUTE format('UPDATE tbl SET %I = $1 '
       'WHERE key = $2', colname) USING newvalue, keyvalue;

還可以直接調用引用函數：

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

這個例子展示了quote_ident和quote_literal函數的使用。為了安全，在進行一個動態查詢中的插入之前，包含列或表標識符的運算式應該通過quote_ident被傳遞。如果運算式包含在被構造出的命令中應該是字串的值時，它應該通過quote_literal被傳遞。這些函數採取適當的步驟來分別返回被封閉在雙引號或單引號中的文本，其中任何嵌入的特殊字元都會被正確地轉義。

因為quote_literal被標記為STRICT，當用一個空參數調用時，它總是會返回空。在上面的例子中，如果newvalue或keyvalue為空白，整個動態查詢字串會變成空，導致從EXECUTE得到一個錯誤。可以通過使用quote_nullable函數來避免這種問題，它工作起來和quote_literal相同，除了用空參數調用時會返回一個字串NULL。例如：

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

如果正在處理的參數值可能為空白，那麼通常應該用quote_nullable來代替quote_literal。

通常，必須小心地確保查詢中的空值不會遞送意料之外的結果。例如如果keyvalue為空白，下面的WHERE子句

    'WHERE key = ' || quote_nullable(keyvalue)

永遠不會成功，因為在=操作符中使用空運算元得到的結果總是為空白。如果想讓空和一個普通索引值一樣工作，你應該將上面的命令重寫成

    'WHERE key IS NOT DISTINCT FROM ' || quote_nullable(keyvalue)

請注意貨幣符號引用只對引用固定文本有用。嘗試寫出下面這個例子是一個非常糟糕的主意：

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

因為如果newvalue的內容碰巧含有$$，那麼這段代碼就會出問題。同樣的缺點可能適用於你選擇的任何其他貨幣符號引用定界符。因此，要想安全地引用事先不知道的文本，必須恰當地使用quote_literal、quote_nullable或quote_ident。

動態 SQL 陳述式也可以使用format函數來安全地構造。例如：

    EXECUTE format('UPDATE tbl SET %I = %L '
       'WHERE key = %L', colname, newvalue, keyvalue);

%I等效於quote_ident並且 %L等效於quote_nullable。 format函數可以和 USING子句一起使用：

    EXECUTE format('UPDATE tbl SET %I = $1 WHERE key = $2', colname)
       USING newvalue, keyvalue;

這種形式更好，因為變數被以它們天然的資料類型格式處理，而不是無條件地把它們轉換成文本並且通過%L引用它們。這樣效率更高。

獲得結果狀態

有好幾種方法可以判斷一條命令的效果。第一種方法是使用GET DIAGNOSTICS命令，其形式如下：

    GET [ CURRENT ] DIAGNOSTICS variable { = | := } item [ , ... ];

這條命令允許檢索系統狀態指示器。CURRENT是一個雜訊詞。每個item是一個關鍵字， 它標識一個要被賦予給指定變數的狀態值（變數應具有正確的資料類型來接收狀態值）。可用的診斷項表中展示了當前可用的狀態項。冒號等號（:=）可以被用來取代 SQL 標準的=符號。例如：

    GET DIAGNOSTICS integer_var = ROW_COUNT;

可用的診斷項

第二種判斷命令效果的方法是檢查一個名為FOUND的boolean類型的特殊變數。在每一次PL/SQL函數調用時，FOUND開始都為假。它的值會被下面的每一種類型的語句設定：

• 如果一個SELECT INTO語句賦值了一行，它將把FOUND設定為真，如果沒有返回行則將之設定為假。

• 如果一個PERFORM語句產生（並且拋棄）一行或多行，它將把FOUND設定為真，如果沒有產生行則將之設定為假。

• 如果UPDATE、INSERT以及DELETE語句影響了至少一行，它們會把FOUND設定為真，如果沒有影響行則將之設定為假。

• 如果一個FETCH語句返回了一行，它將把FOUND設定為真，如果沒有返回行則將之設定為假。

• 如果一個MOVE語句成功地重定位了遊標，它將會把FOUND設定為真，否則設定為假。

• 如果一個FOR或FOREACH語句迭代了一次或多次，它將會把FOUND設定為真，否則設定為假。當迴圈退出時，FOUND用這種方式設定；在迴圈執行中，儘管FOUND可能被迴圈體中的其他語句的執行所改變，但它不會被迴圈語句修改。

• 如果查詢返回至少一行，RETURN QUERY和RETURN QUERY EXECUTE語句會把FOUND設為真， 如果沒有返回行則設定為假。

其他的PL/SQL語句不會改變FOUND的狀態。尤其需要注意的一點是：EXECUTE會修改GET DIAGNOSTICS的輸出，但不會修改FOUND的輸出。

FOUND是每個PL/SQL函數的局部變數；任何對它的修改隻影響當前的函數。

什麼也不做

有時一個什麼也不做的佔位語句也很有用。例如，它能夠指示 if/then/else 鏈中故意留出的空分支。可以使用NULL語句達到這個目的：

    NULL;

例如，下面的兩段代碼是等價的：

    BEGIN
        y := x / 0;
    EXCEPTION
        WHEN division_by_zero THEN
            NULL;  -- 忽略錯誤
    END;

    BEGIN
        y := x / 0;
    EXCEPTION
        WHEN division_by_zero THEN  -- 忽略錯誤
    END;

究竟使用哪一種取決於各人的喜好。