# 40.5\. 基本語句 本節以及隨后的一節里， 描述所有PL/pgSQL明確可以理解的語句類型。 任何無法識別為這樣類型的語句將被做為SQL命令看待， 并且被發送到主數據庫引擎執行， 正如在節[Section 40.5.2](#calibre_link-1574) 和[Section 40.5.3](#calibre_link-1540)中描述的那樣。 ## 40.5.1\. 賦值 給一個PL/pgSQL變量的賦值如下: ``` _variable_ := _expression_; ``` 如上所述，語句中的表達式是用一個發送到主數據庫引擎的`SELECT`命令計算的。 該表達式必須生成單一的數值。表達式必須只能生成一個值 （如果變量一個行或者record，那么該值可能是一個行）。 目標變量可以是一個簡單的變量（可以用一個block的名字來描述）， 行或record變量的字段，或者是一個簡單變量或字段的數組元素。 如果表達式的結果數據類型和變量數據類型不一致， 或者變量具有已知的尺寸/精度(比如`char(20)`)， 結果值將隱含地被PL/pgSQL解釋器用結果類型的輸出 函數和變量類型的輸入函數轉換。 注意，如果結果數值的字符串形式不是輸入函數可以接受的形式， 那么這樣做可能導致類型輸入函數產生的運行時錯誤。 例子： ``` tax := subtotal * 0.06; my_record.user_id := 20; ``` ## 40.5.2\. 執行一個沒有結果的查詢 對于不返回任何行的SQL命令，例如沒有`RETURNING`子句的`INSERT`， 你可以簡單的在PL/pgSQL函數內寫上該語句， 然后執行該函數即可。 出現在查詢文本中的任何PL/pgSQL變量名都會被參數符號代替， 并在運行時將參數值替換為變量的當前值。 就像之前描述的表達式進程，可以查看資料[Section 40.10.1](#calibre_link-1045)。 當以這種方式執行一條SQL命令，這條命令在PL/pgSQL 中緩存并且在執行規劃中重新使用。 正如在[Section 40.10.2](#calibre_link-1573)中討論的。 有時評估一個表達式或`SELECT`查詢但是丟棄其結果也是有用的， 例如，調用一個具有副作用的函數，但對它的結果不感興趣。 要在PL/pgSQL中這樣做，可以使用`PERFORM`語句: ``` PERFORM _query_; ``` 這將執行`_query_`并丟棄其結果。 用`SELECT`命令重寫`_query_`，并將`SELECT` 替換為`PERFORM`， 對于`WITH`查詢，使用`PERFORM`并且將查詢放在括號中（在這種情況下， 查詢只僅僅返回一行）。 這樣，PL/pgSQL變量將會在查詢中被照常替換。 另外，如果查詢生成至少一行結果的話， 特殊變量`FOUND`將會被設為真，否則將被設為假。 （查閱[Section 40.5.5](#calibre_link-1575)） > **Note:** 有些人可能期望直接寫`SELECT`就能同樣達到此目的， 但目前確實只有`PERFORM`一種方法。 諸如`SELECT`這樣返回行的查詢將會被當作錯誤拒絕， 除非其帶有一個下面將要討論的`INTO`子句。 例如： ``` PERFORM create_mv('cs_session_page_requests_mv', my_query); ``` ## 40.5.3\. 執行一個僅有單行結果的查詢 如果一個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/pgSQL變量將被照常代入查詢的其余部分， 適用于帶有`RETURNING`的`SELECT`, `INSERT`/`UPDATE`/`DELETE`， 以及返回行集合的命令(比如`EXPLAIN`)。 除`INTO`子句外， SQL命令與其在PL/pgSQL外面時完全相同。 > **Tip:** 請注意，上面帶有`INTO`的`SELECT`和 PostgreSQL普通的`SELECT INTO`命令是不一樣的， 后者的`INTO`目標是一個新創建的表。 如果你想在PL/pgSQL函數里從一個`SELECT`結果中創建表， 那么請使用`CREATE TABLE ... AS SELECT`語法。 如果將一行或者一個變量列表用做目標，那么查詢的結果必需作為數目或者數據類型精確匹配目標的結構， 否則就會產生運行時錯誤。如果目標是一個記錄變量，那么它自動將自己配置成命令結果列的行類型。 `INTO`子句幾乎可以出現在SQL命令的任何地方。 習慣上把它寫在`SELECT`命令的`_select_expressions_`列表的之前或之后， 對于其它命令則位于結尾。 我們建議你遵守這個約定，以防萬一PL/pgSQL分析器在未來的版本中變得更加嚴格。 如果沒有在INTO指定STRICT，那么target將被設為查詢返回結果的第一行或者 NULL(查詢返回零行)， 請注意，除非用ORDER BY進行排序，否則"the first row"是不明確的。 第一行之后的所有結果都將被丟棄。 你可以檢查特殊變量FOUND(參見Section 39.5.5)來判斷查詢是否至少返回一行。 ``` 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/pgSQL也會在返回多行時報錯。 這是因為沒有`ORDER BY`之類的選項用于確定究竟返回那一行。 > **Note:** `STRICT`兼容 Oracle PL/SQL的`SELECT INTO`行為以及相關語句。 對于如何處理一個SQL查詢中返回的多行，參見[Section 40.6.4](#calibre_link-1576)。 ## 40.5.4\. 執行動態命令 你經常會希望在你的PL/pgSQL函數里生成動態命令。 也就是那些每次執行的時候都會涉及不同表或不同數據類型的命令。在這樣的情況下， PL/pgSQL試圖為命令(正如[Section 40.10.2](#calibre_link-1573)討論的) 緩沖執行計劃的一般企圖將不再合適。 為了處理這樣的問題，提供了`EXECUTE`語句： ``` EXECUTE _command-string_ [ INTO [STRICT] `_target_` ] [ USING `_expression_` [, ... ] ]; ``` 這里的`_command-string_`是一個生成字符串(類型為`text`)的表達式， 該字符串包含要執行的命令。 而`_target_`是一個記錄變量、行變量、逗號分隔的簡單變量列表、 逗號分隔的記錄/行列表，來存儲命令的結果。通過使用`USING`表達式，將參數值插入到命令中。 請特別注意在該命令字符串里將不會發生任何PL/pgSQL變量代換。 變量的數值必需在構造的時候插入該字符串的值，或者也可以使用下面介紹的參數。 同時，對于通過`EXECUTE`執行的命令，沒有預先設置緩存計劃。 相反，在該語句每次運行的時候，命令都準備一次。 命令字符串可以在過程里動態地生成以便于對各種不同的表和字段進行操作。 `INTO`子句聲明SQL命令的結果應該傳遞到哪里。 如果提供了一個行變量或者一個變量列表， 那么它必須和查詢生成的結果的結構一樣(如果使用了記錄變量，那么它回自動調整為匹配結果的結構)。 如果返回了多行，那么只有第一行將被賦予`INTO`變量。 如果返回零行，那么將給`INTO`變量賦予NULL。 如果沒有聲明`INTO`子句，則拋棄查詢結果。 如果使用了`STRICT`選項，那么在查詢沒有恰好返回一個行的情況下將會報錯。 該命令可以使用那些在命令中被引用為`$1`, `$2`等的參數值。 這些標簽指向的是在`USING`子句中使用的值。 這樣做可以很好的將數據值以文本類型插入到命令字符串中： 避免了運行期間在數據值和文本類型之間轉換的開銷， 并且這種方法不是傾向于進行SQL-injection，因為沒有進行引用和轉義的必要。例如： ``` EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2' INTO c USING checked_user, checked_date; ``` 需要注意的是，參數標簽只能用于數據值— 如果想要使用動態的已知的表或列的名字， 那么必須將它們以文本字符串類型插入到命令中。 例如，當上面那個查詢需要在一個動態選擇的表上執行時，你可以這么做： ``` EXECUTE 'SELECT count(*) FROM ' || tabname::regclass || ' WHERE inserted_by = $1 AND inserted <= $2' INTO c USING checked_user, checked_date; ``` 另一個關于參數標簽的限制是， 它們只能在`SELECT`, `INSERT`, `UPDATE`和 `DELETE`命令中使用。 在另一種語法類型中，通常稱為通用語法中，可以將參數值以文本類型插入， 哪怕它們只是數據值。 如在上面第一個例子中的，帶有一個簡單常量字符串和`USING`參數的`EXECUTE`命令， 它在功能上等同于直接在PL/pgSQL中寫命令， 并且允許PL/pgSQL變量自動替換。 最重要的不同之處在于，`EXECUTE`會在每一次執行時，根據當前的參數值更新該命令計劃， 在這一點上，PL/pgSQL可能創建一個命令計劃，并將 其放于緩存中以待重新使用。 當命令計劃對參數值的依賴性很強時， 對于使用`EXECUTE`積極確保通用計劃不被選擇是很有幫助的。 `EXECUTE`命令目前不支持`SELECT INTO`， 但是支持一個純`SELECT`命令，并且聲明一個`INTO`作為命令本身的一部分。 > **Note:** PL/pgSQL中的`EXECUTE`語法與 PostgreSQL服務器支持的[EXECUTE](#calibre_link-107)語法無關。 服務器支持的`EXECUTE`語法不能 被PL/pgSQL函數直接使用（并且也沒有必要）。 **Example 40-1\. 動態查詢中的引用值** 使用動態命令的時候經常需要逃逸單引號。 建議使用美元符界定函數體內的固定文本。 如果你有沒有使用美元符界定的老代碼，請參考[Section 40.11.1](#calibre_link-1577)， 這樣在把老代碼轉換成更合理的結構時，會節省你的一些精力。 插入到構造出來的查詢中的動態數值也需要特殊處理， 因為他們自己可能包含引號字符。 一個例子(這里都假設你使用了美元符作為整體，所以引號標記不需要加倍)： ``` EXECUTE 'UPDATE tbl SET ' || quote_ident(colname) || ' = ' || quote_literal(newvalue) || ' WHERE key = ' || quote_literal(keyvalue); ``` 這個例子顯示了`quote_ident`和`quote_literal` 函數的使用（參閱[Section 9.4](#calibre_link-1578)）。 為了安全，包含字段和表標識符的變量應該傳遞給`quote_ident`函數。 那些包含數值的表達式，如果中的數值在構造出來的命令字符串里是文本字符串， 那么應該傳遞給`quote_literal`。 它們倆都會采取合適的步驟把輸入文本包圍在單或雙引號里， 并且對任何嵌入其中的特殊字符進行合適的逃逸處理。 因為`quote_literal`被標記為`STRICT`， 當發出帶有null參數的請求時， 往往會返回一個null。在上面的例子中，如果`newvalue`或者 `keyvalue`是null， 整個動態查詢字符串會變成null，最終`EXECUTE`會報錯。 可以通過使用`quote_nullable`函數來避免該錯誤， 除了當發出帶有null參數的請求時，往往會返回一個字符串NULL之外， 該函數與`quote_literal`一樣工作。例如： ``` EXECUTE 'UPDATE tbl SET ' || quote_ident(colname) || ' = ' || quote_nullable(newvalue) || ' WHERE key = ' || quote_nullable(keyvalue); ``` 如果處理的參數值是null，那么應該用`quote_nullable`來代替`quote_literal`。 通常，應該注意確保查詢中的null值返回意料之外的結果。例如： ``` 'WHERE key = ' || quote_nullable(keyvalue) ``` 如果`keyvalue`是null，那么該`WHERE`子句永遠不會成功， 因為當`=`操作符帶有null操作數，操作返回的結果往往是null。 如果想讓null同普通關鍵字一樣使用，那么將上面的命令修改如下： ``` 'WHERE key IS NOT DISTINCT FROM ' || quote_nullable(keyvalue) ``` 目前，`IS NOT DISTINCT FROM`處理效率不如`=`，因此如非必要， 不用這么做。關于null和`IS DISTINCT`的資料可參閱[Section 9.2](#calibre_link-1516)。 請注意美元符界定只對包圍固定文本有用。如果想像下面這樣做上面的例子，那就太糟糕了: ``` EXECUTE 'UPDATE tbl SET ' || quote_ident(colname) || ' = $$' || newvalue || '$$ WHERE key = ' || quote_literal(keyvalue); ``` 因為如果`newvalue`的內容碰巧含有`$$`，那么這段代碼就有毛病了。 同樣的問題可能出現在你選用的任何美元符界定分隔符上。 因此，要想安全地包圍事先不知道的文本， _必須_恰當的使用`quote_literal`， `quote_nullable`或者`quote_ident`。 動態SQL語句可以使用`format`函數安全構建 （參閱[Section 9.4](#calibre_link-1578)）。比如： ``` EXECUTE format('UPDATE tbl SET %I = %L WHERE key = %L', colname, newvalue, keyvalue); ``` 在`USING`子句連接中使用`format`函數： ``` EXECUTE format('UPDATE tbl SET %I = $1 WHERE key = $2', colname) USING newvalue, keyvalue; ``` 這種形式更有效，因為參數`newvalue` and `keyvalue` 不轉換為文本。 關于動態命令和`EXECUTE`的另一個例子 是[Example 40-9](#calibre_link-1579)， 這個例子制作并執行了一個定義新函數的`CREATE FUNCTION`命令。 ## 40.5.5\. 獲取結果狀態 有好幾種方法可以判斷一條命令的效果。 第一個方法是使用`GET DIAGNOSTICS`，它的形式如下： ``` GET [ CURRENT ] DIAGNOSTICS _variable_ = _item_ [ , ... ]; ``` 這條命令允許檢索系統狀態標識符。每個`_item_`是一個關鍵字， 表示一個將要賦予該特定變量的狀態值(該變量應該和要接收的數值類型相同)。 當前可用的狀態項有`ROW_COUNT`、最后一個SQL命 令發送到SQL引擎處理的行數量、`RESULT_OID`， 最后一條SQL命令插入的最后一行的OID。 請注意`RESULT_OID`只有在一個向包含OID的表中`INSERT` 的命令之后才有用。 例如： ``` GET DIAGNOSTICS integer_var = ROW_COUNT; ``` 另外一個判斷命令效果的方法是一個`boolean`類型的特殊變量`FOUND`， 它在每個PL/pgSQL函數調用中`FOUND`開始都為假。 并被下列語句設置： * 一個`SELECT INTO`語句如果返回一行則將`FOUND`設置為真， 如果沒有返回行則設置為假。 * 一個`PERFORM`語句如果生成(或拋棄)一行，則將`FOUND`設置為真， 如果沒有生成行則為假。 * 如果至少影響了一行，那么`UPDATE`, `INSERT`和`DELETE`語句 設置FOUND為真，如果沒有行受影響則為假。 * 一個`FETCH`語句如果返回行則設置`FOUND`為真，如果不返回行則為假 * 當成功定位游標的位置時，`MOVE`將`FOUND`設為真，反之為假。 * 一個`FOR`或者`FOREACH`語句如果迭代了一次或多次， 則設置`FOUND`真，否則為假。 只有在循環退出的時候才設置`FOUND`； 在循環執行的內部，`FOUND`不被循環語句修改， 但是在循環體里它可能被其它語句的執行而修改。 * 如果查詢結果返回至少一個行， `RETURN QUERY` and `RETURN QUERY EXECUTE`聲明 將`FOUND`設為真， 反之如果沒有返回行，則為假。 其他的PL/pgSQL聲明不會改變`FOUND`的位置。 尤其需要注意的一點是：`EXECUTE`會修改`GET DIAGNOSTICS`的輸出， 但不會修改`FOUND`的輸出。 `FOUND`是每個PL/pgSQL里的局部變量； 任何對它的任何修改只影響當前的函數。 ## 40.5.6\. 什么也不做 有時一個什么也不做的占位語句也是很有用的。 例如，用于if/then/else 的空分支。 可以使用`NULL`語句達到這個目的。 ``` NULL; ``` 比如，下面的兩段代碼是相等的： ``` BEGIN y := x / 0; EXCEPTION WHEN division_by_zero THEN NULL; -- ignore the error END; ``` ``` BEGIN y := x / 0; EXCEPTION WHEN division_by_zero THEN -- ignore the error END; ``` 究竟使用哪一個取決于個人的喜好。 > **Note:** 在Oracle的PL/SQL中，不允許出現空語句列， 所以在這種情況下必須使用`NULL`語句， 而PL/pgSQL允許你什么也不寫。