# 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允许你什么也不写。