过程语言
1.过程语言
UXDB 允许用户在除 SQL 和 C 之外的其他语言中编写自定义函数。这些其他语言通常被称为过程性语言(PLs)。对于使用过程性语言编写的函数，数据库服务器没有内置的知识来解释函数的源代码。相反，任务被传递给一个特殊的处理程序，该处理程序了解语言的详细信息。处理程序可以自己完成所有解析、语法分析、执行等工作，也可以作为“粘合剂” 在 UXDB 和现有编程语言实现之间起到桥梁的作用。处理程序本身是一个 C语言函数，编译成共享对象并按需加载，就像任何其他C函数一样。
目前在标准的 UXDB 发行版中提供了四种过程性语言：PL/uxSQL、PL/Tcl、PL/Perl 和 PL/Python。还有其他可用的过程性语言未包含在核心发行版中。
1.1.安装过程语言
每个使用过程语言的数据库都必须将其“安装”到其中。但是，安装在数据库template1中的过程语言将自动在所有随后创建的数据库中可用，因为它们在template1中的条目将被CREATE DATABASE复制。因此，数据库管理员可以决定哪些语言在哪些数据库中可用，并可以根据需要默认提供一些语言。
对于标准发行版提供的语言，只需要执行CREATE EXTENSION language_name将语言安装到当前数据库中。下面描述的手动过程仅建议用于安装未打包为扩展的语言。
手动过程语言安装
过程语言在数据库中安装需要五个步骤，必须由数据库超级用户执行。在大多数情况下，所需的SQL命令应打包为“扩展”的安装脚本，以便可以使用CREATE EXTENSION来执行它们。
-
必须编译和安装语言处理程序的共享对象到适当的库目录中。这与使用常规用户定义的C函数构建和安装模块的方式相同。通常，语言处理程序将依赖于提供实际编程语言引擎的外部库；如果是这样，那么也必须安装它。
-
必须使用以下命令声明处理程序
CREATE FUNCTION handler_function_name() RETURNS language_handler AS 'path-to-shared-object' LANGUAGE C;language_handler的特殊返回类型告诉数据库系统，此函数不返回定义的任何 SQL 数据类型，因此不能直接在 SQL语句中使用。 -
可选地，语言处理程序可以提供一个“内联”处理程序函数，用于执行用此语言编写的匿名代码块（
DO命令）。如果语言提供了内联处理程序函数，则使用以下命令声明它：CREATE FUNCTION inline_function_name(internal) RETURNS void AS 'path-to-shared-object' LANGUAGE C; -
可选地，语言处理程序可以提供一个“验证器”函数，用于在不实际执行函数定义的情况下检查其正确性。如果语言提供了验证器函数，则在使用
CREATE FUNCTION命令时将调用该函数。如果语言提供了验证器函数，则使用以下命令声明它：CREATE FUNCTION validator_function_name(oid) RETURNS void AS 'path-to-shared-object' LANGUAGE C STRICT; -
最后，必须使用以下命令声明 PL：
CREATE [TRUSTED] LANGUAGE language_name HANDLER handler_function_name [INLINE inline_function_name] [VALIDATOR validator_function_name] ;可选关键字
TRUSTED指定语言不授予用户否则不具备的数据访问权限。可信任的语言适用于普通数据库用户（没有超级用户权限），并允许他们安全地创建函数和过程。由于PL 函数在数据库服务器内部执行，因此应该仅为不允许访问数据库服务器内部或文件系统的语言提供TRUSTED标志。语言PL/uxSQL、PL/Tcl 和 PL/Perl 被认为是可信任的；语言 PL/TclU、PL/PerlU 和 PL/PythonU旨在提供无限功能，不应标记为可信任。
示例 手动安装 PL/Perl显示了如何使用语言 PL/Perl 手动安装过程。
示例 手动安装 PL/Perl
以下命令告诉数据库服务器在哪里找到 PL/Perl 语言的调用处理程序函数的共享对象：
CREATE FUNCTION plperl_call_handler() RETURNS language_handler AS
'$libdir/plperl' LANGUAGE C;
PL/Perl 有一个内联处理程序函数和一个验证器函数，因此我们也声明它们：
CREATE FUNCTION plperl_inline_handler(internal) RETURNS void AS
'$libdir/plperl' LANGUAGE C STRICT;
CREATE FUNCTION plperl_validator(oid) RETURNS void AS
'$libdir/plperl' LANGUAGE C STRICT;
命令：
CREATE TRUSTED LANGUAGE plperl
HANDLER plperl_call_handler
INLINE plperl_inline_handler
VALIDATOR plperl_validator;
然后定义先前声明的函数应在语言属性为plperl的函数和过程中调用。
在默认的UXsinoDB安装中，PL/uxSQL语言的处理程序已构建并安装到“库”目录中；此外，PL/uxSQL语言已安装在所有数据库中。如果配置了Tcl支持，则PL/Tcl和PL/TclU的处理程序将构建并安装在库目录中，但默认情况下不会在任何数据库中安装该语言。同样，如果配置了Perl支持，则将构建和安装PL/Perl和PL/PerlU处理程序，如果配置了Python支持，则将安装PL/PythonU处理程序，但默认情况下不会安装这些语言。
2.PL/uxSQL — SQL过程化语言
2.1.概述
PL/uxSQL 是一个可加载的过程化语言，用于 UXDB 数据库系统。 PL/uxSQL的设计目标是创建一个可加载的过程化语言，它可以：
-
用于创建函数、过程和触发器，
-
为 SQL 语言添加控制结构，
-
执行复杂的计算，
-
继承所有用户定义的类型、函数、过程和运算符，
-
可以定义为服务器信任的，
-
易于使用。
使用 PL/uxSQL创建的函数可以在任何内置函数可以使用的地方使用。例如，可以创建复杂的条件计算函数，然后将它们用于定义运算符或在索引表达式中使用它们。
在UXsinoDB 2112及更高版本中，默认安装了PL/uxSQL。但是它仍然是一个可加载的模块，因此特别注重安全的管理员可以选择删除它。
2.1.1.使用 PL/uxSQL 的优点
SQL 是 UXDB 和大多数其他关系型数据库使用的查询语言。它是可移植且易于学习的。但是，每个SQL语句必须由数据库服务器单独执行。
这意味着您的客户端应用程序必须将每个查询发送到数据库服务器。数据库服务器，等待它被处理，接收和处理结果，进行一些计算，然后向服务器发送进一步的查询。所有这些都会产生进程间通信，并且如果客户端在与数据库服务器不同的机器上，则还会产生网络开销。
使用 PL/uxSQL，您可以在数据库服务器内部分组计算块和一系列查询，因此具有过程语言的功能和 SQL的易用性，但可以大大节省客户端/服务器通信开销。
-
消除了客户机和服务器之间的额外往返
-
客户端不需要的中间结果不必在服务器和客户端之间进行封送或传输
-
可以避免多轮查询解析
这可以与不使用存储函数的应用程序相比，导致相当大的性能提高。
此外，使用 PL/uxSQL，您可以使用SQL 的所有数据类型、运算符和函数。
2.1.2.支持的参数和结果数据类型
PL/uxSQL中编写的函数可以接受服务器支持的任何标量或数组数据类型作为参数，并且可以返回任何这些类型之一的结果。它们还可以接受或返回任何由名称指定的复合类型（行类型）。还可以声明PL/uxSQL 函数为接受 record，这意味着任何复合类型都可以作为输入，或者返回record，这意味着结果是一个行类型，其列由调用查询中的规范确定。
PL/uxSQL 函数可以通过使用 VARIADIC 标记声明为接受可变数量的参数。这与SQL函数完全相同。
PL/uxSQL 函数还可以声明为接受和返回多态类型，从而允许函数处理的实际数据类型因调用而异。示例出现在声明函数参数中。
PL/uxSQL函数还可以声明为返回任何可以作为单个实例返回的数据类型的“set”（或表）。这样的函数通过对每个所需结果集元素执行RETURN NEXT或使用RETURN QUERY来输出查询的结果来生成其输出。
最后，如果 PL/uxSQL函数没有有用的返回值，则可以声明为返回void。在这种情况下，它也可以编写为过程。
PL/uxSQL函数还可以在显式指定返回类型的位置使用输出参数进行声明。这不会为语言添加任何基本功能，但可以使函数更易于使用。通常很方便，特别是用于返回多个值。
RETURNS TABLE 表示法也可以用于替代 RETURNS SETOF。
2.2.PL/uxSQL的结构
通过执行CREATE FUNCTION命令，可以将PL/uxSQL中编写的函数定义到服务器中。这样的命令通常看起来像这样：
CREATE FUNCTION somefunc(integer, text) RETURNS integer
AS 'function body text'
LANGUAGE pluxsql;
就CREATE FUNCTION而言，函数体只是一个字符串字面量。通常使用美元符号引用来编写函数体比使用普通的单引号语法更有帮助。如果不使用美元符号引用，则函数体中的任何单引号或反斜杠都必须加倍转义。 本章中几乎所有的示例都使用美元符号引用的字面量来编写函数体。
PL/uxSQL是一种块结构语言。 函数体的完整文本必须是一个块。 块的定义如下：
[ <<label>> ]
[ DECLARE
declarations ]
BEGIN
statements
END [ label ];
块中的每个声明和每个语句都以分号结尾。出现在另一个块内的块必须在END之后加上分号，如上所示；但是，结束函数体的最终END不需要分号。
提示
常见的错误是在BEGIN后面立即写分号。 这是不正确的，会导致语法错误。
只有在需要在函数体中引用该块时才需要 label 。在EXIT语句中使用标签，或者限定块中声明的变量的名称。如果在END之后给出标签，则必须与块开头的标签匹配。
所有关键字都不区分大小写。标识符会隐式转换为小写，除非用双引号引用，就像在普通的SQL命令中一样。
在PL/uxSQL代码中，注释的工作方式与普通SQL相同。双破折号（--）开始一条注释，该注释延伸到行末。/*开始一个块注释，该注释延伸到匹配的*/出现。块注释可以嵌套。
块语句中的任何语句都可以是子块。子块可用于逻辑分组或将变量局部化到一小组语句中。在子块中声明的变量会遮盖外部块的同名变量，但是如果使用其块的标签限定其名称，则仍然可以访问外部变量。例如：
CREATE FUNCTION somefunc() RETURNS integer AS $$
<< outerblock >>
DECLARE
quantity integer := 30;
BEGIN
RAISE NOTICE 'Quantity here is %', quantity; -- Prints 30
quantity := 50;
--
-- Create a subblock
--
DECLARE
quantity integer := 80;
BEGIN
RAISE NOTICE 'Quantity here is %', quantity; -- Prints 80
RAISE NOTICE 'Outer quantity here is %', outerblock.quantity; -- Prints 50
END;
RAISE NOTICE 'Quantity here is %', quantity; -- Prints 50
RETURN quantity;
END;
$$ LANGUAGE pluxsql;
注意
实际上，任何PL/uxSQL函数的主体都被一个隐藏的“外部块”包围。该块提供函数参数（如果有）的声明，以及一些特殊变量，例如FOUND（请参见获取结果状态）。外部块带有函数的名称标签，这意味着参数和特殊变量可以带有函数的名称限定。
重要的是不要混淆在PL/uxSQL中使用BEGIN/END来分组语句与用于事务控制的类似命名的SQL命令。PL/uxSQL的BEGIN/END仅用于分组；它们不会开始或结束事务。有关在PL/uxSQL中管理事务的信息，请参见事务管理。此外，包含EXCEPTION子句的块实际上形成了一个子事务，可以回滚而不影响外部事务。有关更多信息，请参见捕获错误。
2.3.声明
块中使用的所有变量必须在块的声明部分中声明。(唯一的例外是，遍历整数值范围的FOR循环的循环变量被自动声明为整数变量，同样，遍历游标结果的FOR循环的循环变量被自动声明为记录变量。)
PL/uxSQL 变量可以具有任何 SQL 数据类型，例如 integer、varchar 和 char。
以下是一些变量声明的示例：
user_id integer;
quantity numeric(5);
url varchar;
myrow tablename%ROWTYPE;
myfield tablename.columnname%TYPE;
arow RECORD;
变量声明的一般语法如下：
name [ CONSTANT ] type [ COLLATE collation_name ] [ NOT NULL ] [ { DEFAULT | := | = } expression ];
如果给出了 DEFAULT 子句，则指定分配的初始值。当进入块时，变量的默认值会被评估并赋值给变量。如果未给出 DEFAULT子句，则变量将被初始化为 SQL 空值。 CONSTANT选项防止变量在初始化后被赋值，因此其值将在块的持续时间内保持不变。COLLATE选项指定要用于变量的排序规则（请参见PL/uxSQL变量的排序规则）。如果指定了NOT NULL，则将 null 值赋值给变量会导致运行时错误。所有声明为 NOT NULL的变量都必须指定非空默认值。等于符号（=）可以用于代替PL/SQL 兼容的 :=。
变量的默认值在每次进入块时都会被评估和赋值（而不仅仅是每次函数调用一次）。例如，将 now() 赋值给类型为 timestamp的变量会导致该变量具有当前函数调用的时间，而不是函数预编译时的时间。
示例：
quantity integer DEFAULT 32;
url varchar := 'http://mysite.com';
transaction_time CONSTANT timestamp with time zone := now();
一旦声明，变量的值可以在同一块中的后续初始化表达式中使用，例如：
DECLARE
x integer := 1;
y integer := x + 1;
2.3.1.声明函数参数
传递给函数的参数使用标识符 $1、$2 等命名。可选地，可以为 $n参数名称声明别名，以增加可读性。然后可以使用别名或数字标识符来引用参数值。
有两种方法可以创建别名。首选的方法是在 CREATE FUNCTION 命令中为参数指定名称，例如：
CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$
BEGIN
RETURN subtotal * 0.06;
END;
$$ LANGUAGE pluxsql;
另一种方法是使用声明语法显式声明别名：
name ALIAS FOR $n;
使用相同示例的语法如下：
CREATE FUNCTION sales_tax(real) RETURNS real AS $$
DECLARE
subtotal ALIAS FOR $1;
BEGIN
RETURN subtotal * 0.06;
END;
$$ LANGUAGE pluxsql;
注意
这两个示例并不完全等价。在第一种情况下，可以将 subtotal 引用为sales_tax.subtotal，但在第二种情况下则不能。（如果我们给内部块附加了标签，则可以使用该标签限定subtotal。）
更多示例：
CREATE FUNCTION instr(varchar, integer) RETURNS integer AS $$
DECLARE
v_string ALIAS FOR $1;
index ALIAS FOR $2;
BEGIN
-- some computations using v_string and index here
END;
$$ LANGUAGE pluxsql;
CREATE FUNCTION concat_selected_fields(in_t sometablename) RETURNS text AS $$
BEGIN
RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7;
END;
$$ LANGUAGE pluxsql;
当使用输出参数声明 PL/uxSQL函数时，输出参数会被赋予一个名称。$n个名称和可选别名与普通输入参数一样。输出参数实际上是一个起始值为NULL的变量；它应该在函数执行期间被赋值。参数的最终值是返回的值。例如，销售税的例子也可以这样做：
CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$
BEGIN
tax := subtotal * 0.06;
END;
$$ LANGUAGE pluxsql;
注意，我们省略了RETURNS real——我们可以包含它，但这是多余的。
调用带有OUT参数的函数时，在函数调用中省略输出参数：
SELECT sales_tax(100.00);
输出参数在返回多个值时最有用。一个微不足道的例子是：
CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$
BEGIN
sum := x + y;
prod := x * y;
END;
$$ LANGUAGE pluxsql;
SELECT * FROM sum_n_product(2, 4);
sum | prod
-----+------
6 | 8
这实际上为函数的结果创建了一个匿名记录类型。如果给出了RETURNS子句，则必须说RETURNS record。
这也适用于过程，例如：
CREATE PROCEDURE sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$
BEGIN
sum := x + y;
prod := x * y;
END;
$$ LANGUAGE pluxsql;
在调用过程时，必须指定所有参数。对于输出参数，在从普通SQL调用过程时可以指定NULL：
CALL sum_n_product(2, 4, NULL, NULL);
sum | prod
-----+------
6 | 8
但是，在从PL/uxSQL调用过程时，应该为任何输出参数编写一个变量；变量将接收调用的结果。有关详细信息，请参见调用过程。
另一种声明PL/uxSQL函数的方法是使用RETURNS TABLE，例如：
CREATE FUNCTION extended_sales(p_itemno int)
RETURNS TABLE(quantity int, total numeric) AS $$
BEGIN
RETURN QUERY SELECT s.quantity, s.quantity * s.price FROM sales AS s
WHERE s.itemno = p_itemno;
END;
$$ LANGUAGE pluxsql;
这与声明一个或多个OUT参数并指定RETURNS SETOFsometype完全相同。
当PL/uxSQL函数的返回类型声明为多态类型时，会创建一个特殊的参数$0。它的数据类型是从实际输入类型推断出的函数的实际返回类型。这允许函数像复制类型所示访问其实际返回类型。$0初始化为null，可以通过函数进行修改，因此可以用于保存返回值（如果需要），但这不是必需的。$0也可以被赋予别名。例如，此函数适用于具有+运算符的任何数据类型：
CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement)
RETURNS anyelement AS $$
DECLARE
result ALIAS FOR $0;
BEGIN
result := v1 + v2 + v3;
RETURN result;
END;
$$ LANGUAGE pluxsql;
可以通过声明一个或多个输出参数为多态类型来实现相同的效果。在这种情况下，不使用特殊的$0参数;输出参数本身具有相同的目的。例如：
CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement,
OUT sum anyelement)
AS $$
BEGIN
sum := v1 + v2 + v3;
END;
$$ LANGUAGE pluxsql;
实际上，使用anycompatible类型族声明多态函数可能更有用，以便自动将输入参数提升为公共类型。例如：
CREATE FUNCTION add_three_values(v1 anycompatible, v2 anycompatible, v3 anycompatible)
RETURNS anycompatible AS $$
BEGIN
RETURN v1 + v2 + v3;
END;
$$ LANGUAGE pluxsql;
使用此示例，例如调用
SELECT add_three_values（1，2，4.7）;
将起作用，自动将整数输入提升为数字。使用anyelement的函数将要求您手动将三个输入强制转换为相同的类型。
2.3.2.ALIAS
newname ALIAS FOR oldname;
ALIAS语法比前一节所示更通用：您可以为任何变量而不仅仅是函数参数声明别名。这个主要的实际用途是为具有预定名称的变量（例如触发器函数中的NEW或OLD）分配不同的名称。
示例：
DECLARE
prior ALIAS FOR old;
updated ALIAS FOR new;
由于ALIAS创建了两种不同的命名方式来命名相同的对象，因此不受限制的使用可能会令人困惑。最好仅用于覆盖预定名称的目的。
2.3.3.复制类型
variable%TYPE
%TYPE提供变量或表列的数据类型。您可以使用它来声明将保存数据库值的变量。例如，假设您的users表中有一个名为user_id的列。要声明与users.user_id相同数据类型的变量，您可以编写：
user_id users.user_id%TYPE;
通过使用%TYPE，您不需要知道您正在引用的结构的数据类型，最重要的是，如果将来引用项的数据类型更改（例如：将user_id的类型从integer更改为real），您可能不需要更改函数定义。
%TYPE在多态函数中特别有价值，因为内部变量所需的数据类型可能会从一次调用到另一次调用而变化。可以通过将％TYPE应用于函数的参数或结果占位符来创建适当的变量。
2.3.4.行类型
name table_name%ROWTYPE;
name composite_type_name;
复合类型的变量称为行变量（或行类型变量）。这样的变量可以保存一个SELECT或FOR查询结果的整行数据，只要该查询的列集与变量声明的类型匹配即可。行值的各个字段可以使用常规的点表示法访问，例如rowvar.field。
可以使用table_name%ROWTYPE表示法声明行变量具有与现有表或视图的行相同的类型；也可以通过给出复合类型的名称来声明行变量。由于每个表都有一个与其同名的关联复合类型，因此在UXsinoDB中写入 %ROWTYPE或不写都无所谓。但是，使用%ROWTYPE的形式更具可移植性。
函数的参数可以是复合类型（完整的表行）。在这种情况下，相应的标识符$n将是一个行变量，可以从中选择字段，例如$1.user_id。
下面是使用复合类型的示例。table1和table2是具有至少所述字段的现有表：
CREATE FUNCTION merge_fields(t_row table1) RETURNS text AS $$
DECLARE
t2_row table2%ROWTYPE;
BEGIN
SELECT * INTO t2_row FROM table2 WHERE ... ;
RETURN t_row.f1 || t2_row.f3 || t_row.f5 || t2_row.f7;
END;
$$ LANGUAGE pluxsql;
SELECT merge_fields(t.*) FROM table1 t WHERE ... ;
2.3.5.记录变量
name RECORD;
类似于行类型变量，但它们没有预定义的结构。它们在 SELECT 或 FOR 命令中被赋值时采用实际行结构。记录变量的子结构可以每次分配时更改。这意味着，在首次分配记录变量之前，它没有子结构，任何尝试访问其中的字段都会引发运行时错误。
请注意，RECORD不是真正的数据类型，只是一个占位符。还应该意识到，当一个PL/uxSQL函数声明返回类型为record时，这不是一个记录变量的概念，即使这样的函数可能使用记录变量来保存其结果。在两种情况下，当函数编写时，实际的行结构是未知的，但对于返回record的函数，实际结构在调用查询解析时确定，而记录变量可以在运行时更改其行结构。
2.3.6.集合类型
PL/UXSQL中有三种集合类型：关联数组（associative array）、可变数组（VARRAY）和嵌套表（nested table）。
2.3.6.1.关联数组类型
- 语法
TYPE table_type IS TABLE OF data_type [NOT NULL] INDEX BY index_type - 参数
-
able_type要定义的类型名。
-
data_type要创建的集合中成员的类型。
-
index_type创建集合索引的类型。
-
NOT NULL要创建的集合中成员不能为
NULL。
-
- 说明
-
在插入值时，变量会自动增长，访问越界会放回一个
NULL，不会报错。 -
在存储过程中定义的类型，其作用域仅在该存储过程中。
-
索引的类型仅支持
INTEGER和VARCHAR类型，其中VARCHAR的长度暂不约束。 -
data_type可以是基础类型或用户自定义类型，不可以为数组类型。 -
不支持该类型的变量作为函数的出入参。
-
不支持通过
RAISE NOTICE打印整个嵌套变量。 -
不支持下标使用
NULL给元素赋值。
-
2.3.6.2.可变数组类型
-
语法
TYPE varray_type IS {VARRAY | VARYING ARRAY}(size) OF data_type [NOT NULL] -
参数
-
varray_type要定义的类型名。
-
size取值为正整数，表示可以容纳的成员的最大数量。
-
data_type要创建的集合中成员的类型。
-
NOT NULL要创建的集合中成员不能为
NULL。
-
-
说明
-
在插入值时，变量会自动增长，访问越界会放回一个
NULL，不会报错。 -
在存储过程中定义的类型，其作用域仅在该存储过程中。
-
data_type可以是基础类型或用户自定义类型，不可以为数组类型、嵌套表类型和关联数组类型。 -
data_type可以是基础类型或用户自定义类型，不可以为数组类型。 -
不支持该类型的变量作为函数的出入参。
-
size仅语法支持，暂未支持功能。 -
不支持下标使用
NULL给元素赋值。
-
2.3.6.3.嵌套表类型
-
语法
TYPE table_type IS TABLE OF data_type [NOT NULL] -
参数
-
table_type要定义的类型名。
-
data_type要创建的集合中成员的类型。
-
NOT NULL要创建的集合中成员不能为
NULL。
-
-
说明
-
在插入值时，变量会自动增长，访问越界会放回一个
NULL，不会报错。 -
在存储过程中定义的类型，其作用域仅在该存储过程中。
-
data_type可以是基础类型或用户自定义类型，不可以为数组类型。 -
不支持该类型的变量作为函数的出入参。
-
不支持通过
RAISE NOTICE打印整个嵌套变量。 -
不支持下标使用
NULL给元素赋值。
-
2.3.6.4.使用方法
通过集合构造函数和集合变量赋值给变量初始化和赋值，然后使用集合方法进行调用。说明如下所示。
-
集合构造函数
集合构造函数是与集合类型同名的系统定义函数，它返回该类型的集合。
构造函数调用的语法如下所示。
collection_type ( [ value [, value ]... ] )如果参数列表为空，构造函数返回一个空集合；否则，构造函数返回一个包含指定值的集合。可以在变量声明和块的可执行部分中将返回的集合分配给集合变量。
-
集合变量赋值
可以通过如下方式为集合变量赋值。
-
调用构造函数创建集合并将其分配给集合变量（关联数组类型变量不支持）。
-
使用赋值语句将另一个现有集合变量的值赋给它。
要为集合变量的元素赋值，需要将该元素引用为
collection_variable_name(index)并为其赋值，赋值方法如下所示。-
使用赋值语句直接赋值或将另一个现有集合变量中的元素的值赋给它。
-
使用
INTO语句赋值。
-
-
集合方法
集合方法调用的基本语法如下所示。
collection_name.method语法 描述 DELETE从一个集合中删除元素。其中， DELETE/DELETE()会删除集合中的所有元素;DELETE(n)会删除集合中索引为n的元素，如果该元素不存在则不进行任何动作,使用DELETE(n)删除元素后，索引为n后面的元素都会前移一位。TRIM从一个集合的末尾删除元素，该方法对于索引类型为 VARCHAR的关联数组类型不支持。其中，TRIM/TRIM()会从集合的末尾删除一个元素，如果集合变量中没有元素，则不进行任何动作；TRIM(n)会从集合的末尾删除n个元素，如果集合变量中没有n个元素，则会把集合变量中的所有元素删除。EXTEND向一个集合的末尾添加元素，该方法对于索引类型为 VARCHAR的关联数组类型不支持。其中，EXTEND/EXTEND()向集合添加一个null元素；EXTEND(n)向集合添加n个null元素。EXISTSEXISTS是一个说明指定索引的元素在集合中是否存在的函数。对于可变数组和嵌套表变量而言，EXISTS(n)将会在第n个元素存在时返回TRUE，否则返回FALSE；对于关联数组变量而言，EXISTS(n)将会在索引为n的元素存在时返回TRUE，否则返回FALSE。如果n超过当前集合中的元素个数，将会返回FALSE。FIRST和LAST如果集合至少有一个元素，
FIRST和LAST返回集合中第一个和最后一个元素的索引。如果集合只有一个元素，FIRST和LAST返回相同的索引。如果集合为空，FIRST和LAST返回NULL。对于关联数组而言，如果索引类型为
INTEGER，第一个和最后一个元素分别是索引最小和最大的元素；如果索引为VARCHAR，第一个和最后一个元素分别是索引经过排序后最低和最高的元素。对于可变数组和嵌套表而言，如果变量中有元素，
FIRST总是返回1；LAST返回的值总是和COUNT返回的值相等。COUNT返回集合中元素的个数。 PRIOR和NEXTPRIOR和NEXT是允许在集合中向前和向后移动的函数，这些方法对于遍历稀疏集合非常有用。当给出一个索引后：
PRIOR返回集合中前一个元素的索引，如果元素不存在，则返回NULL；NEXT返回集合中后一个元素的索引，如果元素不存在，则返回NULL。对于任何集合c，c.prior(c.first)返回
NULL，c.next(c.last)返回NULL。 -
示例
DECLARE -- 定义一个元素类型为NUMERIC，索引类型为VARCHAR的关联数组类型 TYPE population IS TABLE OF NUMERIC INDEX BY VARCHAR(64); -- 定义关联数组变量 city_population population; i VARCHAR(64); BEGIN -- 通过指定下标给变量赋值 city_population('Smallville') := 2000; city_population('Midland') := 750000; city_population('Megalopolis') := 1000000; city_population('Smallville') := 2001; -- 通过FIRST和NEXT方法遍历整个变量 i := city_population.FIRST; WHILE i IS NOT NULL LOOP RAISE NOTICE 'Population of % is %', i, city_population(i); i := city_population.NEXT(i); END LOOP; END; /
2.3.7.PL/uxSQL变量的排序规则
当 PL/uxSQL 函数具有一个或多个可排序数据类型的参数时，每个函数调用都会根据分配给实际参数的排序规则确定排序规则。参数在排序规则下进行比较。如果成功确定了排序规则（即参数之间没有隐式排序规则的冲突），则所有可排序参数都被视为隐式具有该排序规则。这将影响函数内部的排序规则敏感操作的行为。例如，考虑以下代码：
CREATE FUNCTION less_than(a text, b text) RETURNS boolean AS $$
BEGIN
RETURN a < b;
END;
$$ LANGUAGE pluxsql;
SELECT less_than(text_field_1, text_field_2) FROM table1;
SELECT less_than(text_field_1, text_field_2 COLLATE "C") FROM table1;
第一次使用less_than将使用text_field_1和text_field_2的公共排序规则进行比较，而第二次使用将使用C排序规则。
此外，确定的排序规则也被视为可排序类型的任何本地变量的排序规则。因此，如果将该函数编写为：
CREATE FUNCTION less_than(a text, b text) RETURNS boolean AS $$
DECLARE
local_a text := a;
local_b text := b;
BEGIN
RETURN local_a < local_b;
END;
$$ LANGUAGE pluxsql;
如果没有可排序数据类型的参数，或者无法为它们确定公共排序规则，则参数和本地变量使用其数据类型的默认排序规则（通常是数据库的默认排序规则，但对于域类型的变量可能不同）。
可排序数据类型的本地变量可以通过在其声明中包含COLLATE选项来关联不同的排序规则，例如：
DECLARE
local_a text COLLATE "en_US";
此选项将覆盖根据上述规则给变量赋予的排序规则。
当然，如果需要强制使用特定的排序规则执行特定的操作，则可以在函数内部编写显式的COLLATE子句。例如：
CREATE FUNCTION less_than_c(a text, b text) RETURNS boolean AS $$
BEGIN
RETURN a < b COLLATE "C";
END;
$$ LANGUAGE pluxsql;
这将覆盖表列、参数或本地变量与表达式中使用的排序规则，就像在普通的SQL命令中一样。
2.4.表达式
所有在PL/uxSQL语句中使用的表达式都是使用服务器的主要SQL执行器进行处理的。例如，当您编写像这样的PL/uxSQL语句时：
IF expression THEN ...
PL/uxSQL将通过向主SQL引擎提供类似于以下查询的查询来评估表达式：
SELECT expression
在形成SELECT命令时，任何出现的PL/uxSQL变量名称都将被替换为查询参数，详细讨论请参见变量替换。这允许为SELECT准备查询计划，然后重用该计划以使用变量的不同值进行后续评估。因此，第一次使用表达式实际上是一个PREPARE命令。例如，如果我们声明了两个整数变量x和y，并编写了以下代码：
IF x < y THEN ...
在幕后发生的事情相当于：
PREPARE statement_name(integer, integer) AS SELECT $1 < $2;
然后，对于IF语句的每个执行，使用当前的PL/uxSQL变量值作为参数值执行该准备好的语句。通常，这些细节对于PL/uxSQL用户来说并不重要，但是在尝试诊断问题时它们是有用的。更多信息请参见计划缓存。
由于 expression 被转换为SELECT命令，因此它可以包含与普通SELECT相同的子句，只是它不能包含像ORDER BY或LIMIT之类的限制子句。
不能包含顶层的 UNION、INTERSECT 或 EXCEPT 子句。例如，可以使用以下语句测试表是否非空：
IF count(*) > 0 FROM my_table THEN ...
因为在 IF 和 THEN 之间的 expression 被解析为 SELECT count(*) > 0 FROM my_table。 SELECT 必须产生单个列，而且不能超过一行。（如果它不产生行，则结果被视为NULL。）
2.5.基本语句
在本节和接下来的几节中，我们描述了所有由PL/uxSQL显式理解的语句类型。任何不被识别为这些语句类型之一的内容都被认为是一个 SQL命令，并被发送到主数据库引擎执行，如执行SQL命令所述。
2.5.1.赋值
将值赋给PL/uxSQL变量的赋值语句写作：
variable { := | = } expression;
如前所述，这种语句中的表达式是通过发送一个 SQL SELECT命令到主数据库引擎来计算的。表达式必须产生单个值（如果变量是行或记录变量，则可能是行值）。目标变量可以是简单变量（可选地带有块名称）、行或记录目标的字段，或数组目标的元素或切片。等于号（=）可以用来代替PL/SQL 兼容的:=。
如果该表达式的结果数据类型不匹配变量的数据类型，该值将被强制为变量的类型，就好像做了赋值造型一样。如果没有用于所涉及到的数据类型的赋值造型可用，PL/uxSQL解释器将尝试以文本的方式转换结果值，也就是在应用结果类型的输出函数之后再应用变量类型的输入函数。注意如果结果值的字符串形式无法被输入函数所接受，这可能会导致由输入函数产生的运行时错误。
示例
tax := subtotal * 0.06;
my_record.user_id := 20;
my_array[j] := 20;
my_array[1:3] := array[1,2,3];
complex_array[n].realpart = 12.3;
如果表达式的结果数据类型与变量的数据类型不匹配，将尝试进行自动类型转换。如果无法进行自动类型转换，则会引发一个错误。
在赋值语句中，可以使用以下特殊变量：
$n
其中n是一个数字。这将被替换为当前块的第n个参数的值。参数编号从0开始。
在赋值语句中，可以使用以下特殊语法：
variable [ subscripts ] := expression;
其中subscripts是一个逗号分隔的表达式列表，用于指定数组元素的下标。数组下标从1开始。
在赋值语句中，可以使用以下特殊语法：
variable . field_name := expression;
其中field_name是记录类型的字段名。
在赋值语句中，可以使用以下特殊语法：
variable := expression DEFAULT default_expression;
如果表达式的结果为NULL，则使用默认表达式的结果。
在赋值语句中，可以使用以下特殊语法：
variable := expression OR default_expression;
如果表达式的结果为NULL，则使用默认表达式的结果。
在赋值语句中，可以使用以下特殊语法：
variable := expression NULLIF null_expression;
如果表达式的结果等于null_expression，则结果为NULL。
在赋值语句中，可以使用以下特殊语法：
variable := expression COALESCE coalesce_expression;
如果表达式的结果为NULL，则使用 coalesce_expression的结果。
在赋值语句中，可以使用以下特殊语法：
variable := expression || expression;
将两个字符串连接起来。
在赋值语句中，可以使用以下特殊语法：
variable := expression - expression;
将两个日期或时间戳相减，结果为时间间隔。
在赋值语句中，可以使用以下特殊语法：
variable := expression + interval_expression;
将日期或时间戳加上时间间隔。
在赋值语句中，可以使用以下特殊语法：
variable := expression - interval_expression;
将日期或时间戳减去时间间隔。
在赋值语句中，可以使用以下特殊语法：
variable := expression * expression;
将两个数相乘。
在赋值语句中，可以使用以下特殊语法：
variable := expression / expression;
将两个数相除。
在赋值语句中，可以使用以下特殊语法：
variable := expression % expression;
计算两个数的模数。
2.5.2.执行SQL命令
一般来说，任何不返回行的SQL命令都可以通过编写命令在PL/uxSQL函数中执行。例如，您可以通过编写以下命令来创建和填充表：
CREATE TABLE mytable（id int primary key，data text）; INSERT INTO mytable VALUES（1，'one'），（2，'two'）;
如果命令返回行（例如SELECT或INSERT/UPDATE/DELETE与RETURNING），则有两种方法可以继续进行。当命令最多返回一行或您只关心输出的第一行时，像往常一样编写命令，但添加INTO子句以捕获输出，如第执行具有单行结果的命令中所述。要处理所有输出行，请将命令编写为FOR循环的数据源，如循环遍历查询结果所述。
通常仅执行静态定义的SQL命令是不够的。通常，您需要使用不同的数据值，甚至以更基本的方式进行变化，例如在不同的时间使用不同的表名。根据情况，有两种方法可以继续进行。
PL/uxSQL变量值可以自动插入可优化的SQL命令中，这些命令是SELECT，INSERT，UPDATE，DELETE和某些包含其中之一的实用程序命令，例如EXPLAIN和CREATE TABLE ... AS SELECT。在这些命令中，出现在命令文本中的任何PL/uxSQL变量名称都将替换为查询参数，然后在运行时提供变量的当前值作为参数值。这与先前描述的表达式处理完全相同。有关详细信息，请参见变量替换。
以这种方式执行可优化的SQL命令时，PL/uxSQL可能会缓存并重用命令的执行计划，如计划缓存中所述。
不可优化的SQL命令（也称为实用程序命令）无法接受查询参数。因此，在此类命令中自动替换PL/uxSQL变量不起作用。要在执行此类命令时包含非常量文本，可以使用动态命令。有关详细信息，请参见执行具有单行结果的命令。
从PL/uxSQL，你必须将实用程序命令构建为字符串，然后像执行动态命令中讨论的那样EXECUTE它。
如果您想以某种方式修改命令（例如更改表名），也必须使用EXECUTE。有时候，评估表达式或SELECT查询但丢弃结果是有用的，例如在调用具有副作用但没有有用结果值的函数时。要在PL/uxSQL中执行此操作，请使用PERFORM语句：
PERFORM query;
这将执行query并丢弃结果。编写query的方式与编写SQL SELECT命令相同，但将初始关键字SELECT替换为PERFORM。对于WITH查询，请使用PERFORM，然后将查询放在括号中。（在这种情况下，查询只能返回一行。）PL/uxSQL变量将像上面描述的那样替换到查询中，并且计划将以相同的方式缓存。此外，特殊变量FOUND设置为true，如果查询至少生成一行，则为true，如果未生成行，则为false（请参见获取结果状态）。
注意
有人可能会认为直接编写SELECT会产生这种结果，但是目前唯一接受的方法是PERFORM。可以返回行的SQL命令（例如SELECT）将被拒绝为错误，除非它具有如下一节中所讨论的INTO子句。
一个例子：
PERFORM create_mv('cs_session_page_requests_mv', my_query);
2.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 可以是记录变量、行变量或逗号分隔的简单变量和记录/行字段的列表。在其余命令（即除INTO子句之外的所有内容）中将替换PL/uxSQL变量。
INTO 子句与上面描述的一样，计划以相同的方式缓存。这适用于带有 RETURNING 的SELECT、INSERT/UPDATE/DELETE，以及返回行集的某些实用命令，例如 EXPLAIN。除了INTO 子句外，SQL 命令与在 PL/uxSQL 外部编写的命令相同。
提示
请注意，SELECT 带有 INTO 的解释与 UXDB 的常规 SELECT INTO 命令完全不同，其中INTO 目标是新创建的表。如果您想在 PL/uxSQL 函数中从 SELECT 结果创建表，请使用语法 CREATE TABLE ... AS SELECT。
如果将行变量或变量列表用作目标，则命令的结果列必须与目标的结构完全匹配，包括数量和数据类型，否则会发生运行时错误。当记录变量是目标时，它会自动配置为命令的结果列的行类型。
INTO 子句几乎可以出现在 SQL 命令的任何位置。通常，在 SELECT 命令的 select_expressions 列表之前或之后，或在其他命令类型的命令末尾编写。建议您遵循此约定，以防将来版本的 PL/uxSQL 解析器变得更加严格。
如果在INTO 子句中未指定 STRICT，则 target 将设置为命令返回的第一行，或者如果命令未返回行，则设置为 null。（请注意，除非使用了 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设置为true。
对于带有RETURNING的INSERT/UPDATE/DELETE，即使未指定 STRICT，PL/uxSQL 也会报告多个返回行的错误。这是因为没有诸如ORDER BY的选项可以确定应返回哪个受影响的行。
如果为函数启用了print_strict_params，那么当因未满足STRICT的要求而抛出错误时，错误消息的DETAIL部分将包括有关传递给命令的参数的信息。您可以通过设置pluxsql.print_strict_params来更改所有函数的print_strict_params设置，但只有后续函数编译会受到影响。您还可以通过使用编译器选项在每个函数上启用它，例如：
CREATE FUNCTION get_userid(username text) RETURNS int
AS $$
#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;
$$ LANGUAGE pluxsql;
在失败时，此函数可能会生成以下错误消息：
ERROR: query returned no rows
DETAIL: parameters: $1 = 'nosuchuser'
CONTEXT: PL/uxSQL function get_userid(text) line 6 at SQL statement
注意
STRICT选项与Oracle PL/SQL的SELECT INTO和相关语句的行为相匹配。
2.5.4.执行动态命令
通常，您会希望在您的PL/uxSQL函数中生成动态命令，即每次执行时都涉及不同的表或不同的数据类型。PL/uxSQL对命令的计划缓存的正常尝试（如计划缓存中所述）将无法在这种情况下工作。为了处理这种问题，提供了EXECUTE语句：
EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] ];
其中command-string是产生包含要执行的命令的字符串（类型为text）的表达式。可选的target是一个记录变量、行变量或简单变量和记录/行字段的逗号分隔列表，用于存储命令的结果。可选的USING表达式提供要插入到命令中的值。
计算的命令字符串上不执行任何PL/uxSQL变量的替换。任何所需的变量值必须在构建命令字符串时插入；或者您可以像下面描述的那样使用参数。
此外，对于通过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;
（此示例依赖于SQL规则，即由换行符分隔的字符串字面量会被隐式连接。）
参数符号的另一个限制是它们只适用于可优化的SQL命令（SELECT、INSERT、UPDATE、DELETE、MERGE和包含其中之一的某些命令）。在其他语句类型（通称为实用程序语句）中，即使它们只是数据值，也必须以文本方式插入值。
带有简单常量命令字符串和一些USING参数的EXECUTE（如上面的第一个示例）在功能上等同于直接在PL/uxSQL中编写命令，并允许自动替换PL/uxSQL变量。重要的区别在于，EXECUTE将在每次执行时重新计划命令，生成一个特定于当前参数值的计划；而PL/uxSQL可能会创建一个通用计划并将其缓存以供重用。在最佳计划强烈依赖于参数值的情况下，使用EXECUTE可以有助于确保不选择通用计划。
SELECT INTO目前不支持在EXECUTE内部；相反，执行一个简单的SELECT命令，并将INTO指定为EXECUTE本身的一部分。
注意
PL/uxSQL的EXECUTE语句与UXsinoDB服务器支持的EXECUTE SQL语句无关。服务器的EXECUTE语句不能直接在PL/uxSQL函数中使用（也不需要）。
示例 动态查询中的值引用
在处理动态命令时，您经常需要处理单引号的转义。在函数体中引用固定文本的推荐方法是使用美元引用（dollar quoting）。（如果您有不使用美元引用的旧代码，请参阅引号处理的概述，这可以在将该代码转换为更合理的方案时节省一些工作。）
动态值需要仔细处理，因为它们可能包含引号字符。使用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，因此当使用null参数调用时，它将始终返回null。在上面的示例中，如果newvalue或keyvalue为null，则整个动态查询字符串将变为null，从而导致EXECUTE出错。您可以通过使用quote_nullable函数来避免此问题，该函数与quote_literal相同，只是当使用null参数调用时，它返回字符串NULL。例如，
EXECUTE 'UPDATE tbl SET '
|| quote_ident(colname)
|| ' = '
|| quote_nullable(newvalue)
|| ' WHERE key = '
|| quote_nullable(keyvalue);
如果您处理的值可能为null，则通常应使用quote_nullable代替quote_literal。
与往常一样，必须注意确保查询中的null值不会产生意外结果。例如，WHERE子句
'WHERE key = ' || quote_nullable(keyvalue)
如果keyvalue为null，则永远不会成功，因为使用等号运算符=与null操作数的结果始终为null。如果您希望null像普通键值一样工作，则需要将上述内容重写为
'WHERE key IS NOT DISTINCT FROM ' || quote_nullable(keyvalue)
目前，IS NOT DISTINCT FROM的处理效率远低于=，因此除非必须，否则不要这样做。
请注意，美元引用仅适用于引用固定文本。如果尝试将此示例编写为：
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 进行引用。它也更有效率。
动态命令和 EXECUTE 的一个更大的示例可以在示例 移植一个简单的函数从 PL/SQL 到PL/uxSQL中看到，该示例构建并执行一个CREATE FUNCTION 命令以定义一个新函数。
2.5.5.获取结果状态
有几种方法可以确定命令的效果。第一种方法是使用 GET DIAGNOSTICS 命令，其格式为：
GET [ CURRENT ] DIAGNOSTICS variable { = | := } item [ , ... ];
此命令允许检索系统状态指示器。 CURRENT是一个噪声词（但请参见获取有关错误的信息）。每个item都是一个关键字，用于标识要分配给指定的variable的状态值（它应该是正确的数据类型以接收它）。当前可用的状态项如可用诊断项所示。冒号等于（:=）可以用作SQL 标准的 = 标记。例如：
GET DIAGNOSTICS integer_var = ROW_COUNT;
可用诊断项
| 名称 | 类型 | 描述 |
|---|---|---|
ROW_COUNT |
bigint |
最近一个 SQL 命令处理的行数 |
UX_CONTEXT |
text |
描述当前调用堆栈的文本行 |
第二种确定命令效果的方法是检查名为 FOUND 的特殊变量，它是 boolean 类型。在每个PL/uxSQL函数调用中，FOUND 最初为 false。它由以下类型的语句设置：
-
SELECT INTO语句如果分配了一行，则将FOUND设置为 true，如果未返回行，则设置为 false。 -
PERFORM语句如果生成（并且丢弃）一个或多个行，则将FOUND设置为 true，如果未生成行，则设置为 false。 -
UPDATE、INSERT、DELETE和MERGE语句如果影响了至少一行，则将FOUND设置为true，如果未影响行，则设置为 false。 -
FETCH语句如果返回一行，则将FOUND设置为 true，如果未返回行，则设置为 false。 -
MOVE语句如果成功重新定位游标，则将FOUND设置为 true，否则设置为 false。 -
FOR或FOREACH语句如果迭代一次或多次，则将FOUND设置为 true，否则设置为false。当循环退出时，将以这种方式设置FOUND；在循环执行期间，循环语句不会修改FOUND，尽管循环体内的其他语句可能会更改它。 -
RETURN QUERY和RETURN QUERY EXECUTE语句如果查询返回至少一行，则将FOUND设置为true，如果未返回行，则设置为 false。
其他 PL/uxSQL 语句不会更改 FOUND 的状态。特别要注意的是，EXECUTE 更改 GET DIAGNOSTICS的输出，但不更改 FOUND。
FOUND 是每个 PL/uxSQL 函数中的局部变量；对它的任何更改仅影响当前函数。
2.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;
哪个更好取决于个人喜好。
注意
在 Oracle 的 PL/SQL 中，不允许空语句列表，因此必须使用 NULL 语句来处理这种情况。而 PL/uxSQL则允许你直接不写任何内容。
2.6.控制结构
控制结构可能是 PL/uxSQL 最有用（也是最重要）的部分。使用 PL/uxSQL 的控制结构，您可以以非常灵活和强大的方式操作UXsinoDB 数据。
2.6.1.从函数返回
有两个命令可用于从函数返回数据：RETURN 和 RETURN NEXT。
2.6.1.1.RETURN
RETURN expression;
使用带表达式的 RETURN 终止函数并将 expression 的值返回给调用者。此形式用于不返回集合的PL/uxSQL函数。
在返回标量类型的函数中，表达式的结果将自动转换为函数的返回类型，如赋值所述。但是，要返回复合（行）值，必须编写一个提供完全请求的列集的表达式。这可能需要使用显式转换。
如果您声明了具有输出参数的函数，请仅编写没有表达式的RETURN。输出参数变量的当前值将被返回。
如果您声明函数返回void，则可以使用RETURN语句提前退出函数；但是不要在RETURN后面写表达式。
函数的返回值不能未定义。如果控制在函数的顶级块结束时未触发RETURN语句，则会发生运行时错误。但是，这个限制不适用于具有输出参数和返回void的函数。在这些情况下，如果顶级块完成，则会自动执行RETURN语句。
以下是一些示例：
-- 返回标量类型的函数
RETURN 1 + 2;
RETURN scalar_var;
-- 返回复合类型的函数
RETURN composite_type_var;
RETURN (1, 2, 'three'::text); -- 必须将列转换为正确的类型
2.6.1.2.RETURN NEXT 和 RETURN QUERY
RETURN NEXT expression;
RETURN QUERY query;
RETURN QUERY EXECUTE command-string [ USING expression [, ... ] ];
当声明一个函数返回SETOF sometype时，要遵循的过程略有不同。在这种情况下，要返回的单个项由一系列RETURN NEXT或RETURN QUERY命令指定，然后使用没有参数的最终RETURN命令表示函数已经执行完毕。 RETURN NEXT可用于标量和复合数据类型；对于复合结果类型，将返回整个“表”的结果。RETURN QUERY将执行查询的结果附加到函数的结果集中。在单个返回集函数中，可以自由地混合使用RETURN NEXT和RETURN QUERY，在这种情况下，它们的结果将被连接。
RETURN NEXT和RETURN QUERY实际上并不返回函数，它们只是将零个或多个行附加到函数的结果集中。然后，执行将继续到PL/uxSQL函数中的下一条语句。随着执行连续的RETURN NEXT或RETURN QUERY命令，结果集被构建起来。最终的RETURN，它不应该有参数，会导致控制退出函数（或者您可以让控制到达函数的末尾）。
RETURN QUERY有一个变体RETURN QUERY EXECUTE 指定要动态执行的查询。可以通过 USING 将参数表达式插入到计算出的查询字符串中，就像在EXECUTE 命令中一样。
如果您声明了带有输出参数的函数，请只写 RETURN NEXT而不带表达式。在每次执行时，输出参数变量的当前值将被保存，以便作为结果的一行最终返回。请注意，当有多个输出参数时，必须将函数声明为返回SETOF record，或者当只有一个输出参数时，必须将其声明为返回 SETOF sometype，以创建带有输出参数的集合返回函数。
以下是使用 RETURN NEXT 的函数示例：
CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT);
INSERT INTO foo VALUES (1, 2, 'three');
INSERT INTO foo VALUES (4, 5, 'six');
CREATE OR REPLACE FUNCTION get_all_foo() RETURNS SETOF foo AS
$BODY$
DECLARE
r foo%rowtype;
BEGIN
FOR r IN
SELECT * FROM foo WHERE fooid > 0
LOOP
-- can do some processing here
RETURN NEXT r; -- return current row of SELECT
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE pluxsql;
SELECT * FROM get_all_foo();
以下是使用 RETURN QUERY 的函数示例：
CREATE FUNCTION get_available_flightid(date) RETURNS SETOF integer AS
$BODY$
BEGIN
RETURN QUERY SELECT flightid
FROM flight
WHERE flightdate >= $1
AND flightdate < ($1 + 1);
-- 由于执行尚未完成，因此我们可以检查是否返回了行，如果没有，则引发异常。
IF NOT FOUND THEN
RAISE EXCEPTION 'No flight at %.', $1;
END IF;
RETURN;
END;
$BODY$
LANGUAGE pluxsql;
-- 返回可用航班或如果没有可用航班则引发异常。
SELECT * FROM get_available_flightid(CURRENT_DATE);
注意
RETURN NEXT 和 RETURN QUERY 的当前实现在返回函数之前存储整个结果集，如上所述。这意味着，如果 PL/uxSQL函数生成非常大的结果集，则性能可能很差：数据将被写入磁盘以避免内存耗尽，但函数本身将不会返回，直到整个结果集已生成。未来版本的 PL/uxSQL可能允许用户定义不具有此限制的集合返回函数。当前，数据开始写入磁盘的点由work_mem配置变量控制。具有足够内存以在内存中存储较大结果集的管理员应考虑增加此参数。
2.6.2.从过程中返回
过程没有返回值。因此，过程可以在没有 RETURN 语句的情况下结束。如果您希望使用 RETURN 语句提前退出代码，请只写RETURN 而不带表达式。
如果过程具有输出参数，则输出参数变量的最终值将返回给调用者。
2.6.3.调用过程
PL/uxSQL函数、过程、DO块或CALL可以使用过程调用。输出参数的处理方式与普通SQL中CALL的工作方式不同。过程的每个OUT或INOUT参数必须对应于CALL语句中的一个变量，过程返回的任何内容在返回后都将被分配回该变量。例如：
CREATE PROCEDURE triple(INOUT x int)
LANGUAGE pluxsql
AS $$
BEGIN
x := x * 3;
END;
$$;
DO $$
DECLARE myvar int := 5;
BEGIN
CALL triple(myvar);
RAISE NOTICE 'myvar = %', myvar; -- prints 15
END;
$$;
与输出参数对应的变量可以是简单变量或复合类型变量的字段。目前，它不能是数组的元素。
2.6.4.条件语句
IF和CASE语句允许您根据某些条件执行替代命令。 PL/uxSQL有三种形式的IF：
-
IF ... THEN ... END IF -
IF ... THEN ... ELSE ... END IF -
IF ... THEN ... ELSIF ... THEN ... ELSE ... END IF
和两种形式的CASE：
-
CASE ... WHEN ... THEN ... ELSE ... END CASE -
CASE WHEN ... THEN ... ELSE ... END CASE
2.6.4.1.IF-THEN
IF boolean-expression THEN
statements
END IF;
IF-THEN语句是最简单的IF形式。如果条件为真，则在THEN和END IF之间的语句将被执行。否则，它们将被跳过。
例如：
IF v_user_id <> 0 THEN
UPDATE users SET email = v_email WHERE user_id = v_user_id;
END IF;
2.6.4.2.IF-THEN-ELSE
IF boolean-expression THEN
statements
ELSE
statements
END IF;
IF-THEN-ELSE语句通过让您指定一个备用语句集，以在条件不为真时执行，增加了IF-THEN。（请注意，这包括条件评估为NULL的情况。）
例如：
IF parentid IS NULL OR parentid = ''
THEN
RETURN fullname;
ELSE
RETURN hp_true_filename(parentid) || '/' || fullname;
END IF;
IF v_count > 0 THEN
INSERT INTO users_count (count) VALUES (v_count);
RETURN 't';
ELSE
RETURN 'f';
END IF;
2.6.4.3.IF-THEN-ELSIF
IF boolean-expression THEN
statements
[ ELSIF boolean-expression THEN
statements
[ ELSIF boolean-expression THEN
statements
...
]
]
[ ELSE
statements ]
END IF;
有时候有不止两个的选择。IF-THEN-ELSIF 提供了一种方便的方法来依次检查多个选择。IF条件会被依次测试，直到找到第一个为真的条件。然后执行相应的语句，之后控制权传递到 END IF 后面的语句。如果没有一个 IF 条件为真，则执行ELSE 块（如果有的话）。
以下是一个例子：
IF number = 0 THEN
result := 'zero';
ELSIF number > 0 THEN
result := 'positive';
ELSIF number < 0 THEN
result := 'negative';
ELSE
-- hmm, the only other possibility is that number is null
result := 'NULL';
END IF;
关键字 ELSIF 也可以拼写为ELSEIF。
另一种完成相同任务的方法是嵌套 IF-THEN-ELSE 语句，如下例所示：
IF demo_row.sex = 'm' THEN
pretty_sex := 'man';
ELSE
IF demo_row.sex = 'f' THEN
pretty_sex := 'woman';
END IF;
END IF;
如果; 但是，这种方法需要为每个 IF 编写匹配的 END IF，因此在有许多选择时使用 ELSIF要简单得多。
2.6.4.4.简单 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后面的下一条语句。如果没有找到匹配项，则执行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;
2.6.4.5.搜索型 CASE
CASE
WHEN boolean-expression THEN
statements
[ WHEN boolean-expression THEN
statements
... ]
[ ELSE
statements ]
END CASE;
搜索型的 CASE 根据布尔表达式的真值提供条件执行。每个 WHEN 子句的 boolean-expression 依次进行评估，直到找到一个返回 true 的子句。然后执行相应的 statements ，然后控制传递到END CASE 后的下一条语句。（后续的 WHEN 表达式不会被评估。）如果没有找到真值结果，则执行 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;
这种形式的 CASE 与 IF-THEN-ELSIF 完全等效，除了省略 ELSE 子句会导致错误而不是什么都不做的规则。
2.6.5.简单循环
使用 LOOP、EXIT、CONTINUE、WHILE、FOR、FORALL 和 FOREACH 语句，可以安排您的 PL/uxSQL函数重复一系列命令。
2.6.5.1.LOOP
[ <<label>> ]
LOOP
statements
END LOOP [ label ];
LOOP 定义一个无条件循环，直到通过 EXIT 或 RETURN 语句终止为止。可选的 label 可以由嵌套循环中的EXIT 和 CONTINUE 语句使用，以指定这些语句所指的循环。
2.6.5.2.EXIT
EXIT [ label ] [ WHEN boolean-expression ];
如果没有给出 label ，则终止最内层的循环，并在 END LOOP 后的语句中继续执行。如果给出了 label ，则终止指定的循环。如果给出了 WHEN 子句，则仅在布尔表达式的值为真时才终止循环。
如果指定了WHEN，则只有在布尔表达式为true时才会发生循环退出。否则，控件在 EXIT之后传递给语句。
EXIT可以用于所有类型的循环; 它不限于用于无条件循环。
当与 BEGIN 块一起使用时，EXIT 将控制传递给块结束后的下一个语句。注意，为此必须使用标签; 未标记的 EXIT 从不被认为与 BEGIN 块匹配。
示例：
LOOP
-- some computations
IF count > 0 THEN
EXIT; -- exit loop
END IF;
END LOOP;
LOOP
-- some computations
EXIT WHEN count > 0; -- same result as previous example
END LOOP;
<<ablock>>
BEGIN
-- some computations
IF stocks > 100000 THEN
EXIT ablock; -- causes exit from the BEGIN block
END IF;
-- computations here will be skipped when stocks > 100000
END;
2.6.5.3.CONTINUE
CONTINUE [ label ];
如果没有给出label，则开始最内层循环的下一次迭代。也就是说，跳过循环体中剩余的所有语句，并将控制返回到循环控制表达式(如果有的话) ，以确定是否需要进行另一次循环迭代。如果label存在，它指定将继续执行的循环的标签。
如果指定了WHEN，则仅当boolean-expression为 true 时才开始循环的下一次迭代。否则，控件将在 CONTINUE 之后传递到语句。
CONTINUE 可以用于所有类型的循环; 它不限于用于无条件循环。
示例：
LOOP
-- some computations
EXIT WHEN count > 100;
CONTINUE WHEN count < 50;
-- some computations for count IN [50 .. 100]
END LOOP;
2.6.5.4.WHILE
[ <<label>> ]
WHILE boolean-expression LOOP
statements
END LOOP [ label ];
WHILE 定义一个循环，只要boolean-expression的值为真，就重复执行。可选的 label 可以由嵌套循环中的 EXIT 和CONTINUE 语句使用，以指定这些语句所指的循环。
示例：
WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
-- some computations here
END LOOP;
WHILE NOT done LOOP
-- some computations here
END LOOP;
2.6.5.5.FOR（整数变量）
[ <<label>> ]
FOR name IN [ REVERSE ] expression .. expression [ BY expression ] LOOP
statements
END LOOP [ label ];
这种形式的 FOR 创建一个循环，迭代整数值的范围。变量 name 自动定义为类型integer，仅在循环内部存在（循环内忽略任何现有的变量名定义）。给出范围的下限和上限的两个表达式在进入循环时只计算一次。如果未指定 BY 子句，则迭代步骤为 1，否则为在 BY子句中指定的值，该值在循环进入时再次计算。如果指定了 REVERSE，则在每次迭代后将减去步长值，而不是加上步长值。
以下是一些整数 FOR 循环的示例：
FOR i IN 1..10 LOOP
-- i 将在循环中取值 1、2、3、4、5、6、7、8、9、10
END LOOP;
FOR i IN REVERSE 10..1 LOOP
-- i 将在循环中取值 10、9、8、7、6、5、4、3、2、1
END LOOP;
FOR i IN REVERSE 10..1 BY 2 LOOP
-- i 将在循环中取值 10、8、6、4、2
END LOOP;
如果下限大于上限（或在 REVERSE 的情况下小于上限），则根本不执行循环体。不会引发错误。
如果将 label 附加到 FOR 循环，则可以使用该 label 使用限定名称引用整数循环变量。
2.6.5.6.FORALL
[ <<label>> ]
FORALL name IN lower_bound .. upper_bound
dml_statement
| 参数 | 说明 |
|---|---|
| name | 一个无需声明的标识符，作为下标使用。 |
| lower_bound .. upper_bound | 数字表达式，来指定一组连续有效的索引数字下限和上限。 |
| dml_statement | sql语句，只支持update、insert、delete、merge、execute语句。 |
FORALL示例，如下所示。
FORALL i IN 1..100
insert into t1 values(i);
2.6.5.7.FOREACH
[ <<label>> ]
FOREACH loop-variable IN ARRAY array-expression LOOP
statements
END LOOP [ label ];
FOREACH 定义一个循环，其迭代变量在指定的数组中循环。 loop-variable 是一个变量名， array-expression 是一个表达式，它返回一个数组。在每次迭代期间，将迭代变量设置为数组中的下一个元素，并执行循环体中的语句。可选的 label 可以由嵌套循环中的 EXIT 和 CONTINUE 语句使用，以指定这些语句所指的循环。
2.6.6.循环遍历查询结果
使用不同类型的 FOR 循环，可以迭代查询结果并相应地操作该数据。语法如下：
[ <<label>> ]
FOR target IN query LOOP
statements
END LOOP [ label ];
target 是记录变量、行变量或逗号分隔的标量变量列表。 target 依次分配每行结果，该结果是由 query 产生的，对于每行执行循环体。以下是一个示例：
CREATE FUNCTION refresh_mviews() RETURNS integer AS $$
DECLARE
mviews RECORD;
BEGIN
RAISE NOTICE 'Refreshing all materialized views...';
FOR mviews IN
SELECT n.nspname AS mv_schema,
c.relname AS mv_name,
ux_catalog.ux_get_userbyid(c.relowner) AS owner
FROM ux_catalog.ux_class c
LEFT JOIN ux_catalog.ux_namespace n ON (n.oid = c.relnamespace)
WHERE c.relkind = 'm'
ORDER BY 1
LOOP
-- Now "mviews" has one record with information about the materialized view
RAISE NOTICE 'Refreshing materialized view %.% (owner: %)...',
quote_ident(mviews.mv_schema),
quote_ident(mviews.mv_name),
quote_ident(mviews.owner);
EXECUTE format('REFRESH MATERIALIZED VIEW %I.%I', mviews.mv_schema, mviews.mv_name);
END LOOP;
RAISE NOTICE 'Done refreshing materialized views.';
RETURN 1;
END;
$$ LANGUAGE pluxsql;
如果循环由EXIT语句终止，则最后一个 分配的行值仍然可以在循环之后访问。
在此类型的FOR中使用的 query 语句可以是返回行给调用者的任何SQL命令：SELECT是最常见的情况，但您也可以使用INSERT，UPDATE或 DELETE与RETURNING子句。 一些实用程序命令，如EXPLAIN也可以使用。
PL/uxSQL 变量由查询参数替换， 查询计划被缓存以供可能的重用，如讨论所述变量替换和计划缓存。
FOR-IN-EXECUTE语句是另一种迭代的方式 行：
[ <<label>> ]
FOR target IN EXECUTE text_expression [ USING expression [, ... ] ] LOOP
statements
END LOOP [ label ];
这类似于前面的形式，只是源查询 指定为字符串表达式，该表达式在每次进入时进行评估和重新计划 到FOR循环。这使程序员可以选择预先计划的查询的速度或动态的灵活性 查询，就像使用普通的EXECUTE语句一样。 与EXECUTE一样，可以插入参数值 通过USING动态命令。
指定应迭代其结果的查询的另一种方法 通过将其声明为游标。这是在中描述的循环遍历游标结果。
2.6.7.通过数组循环
FOREACH循环很像FOR循环，但是，它不是迭代由SQL查询返回的行，它迭代数组值的元素。（通常，FOREACH用于循环 复合值表达式的组件;循环的变体 通过除数组之外的复合体循环，可能会在未来添加。） 循环遍历数组的FOREACH语句是：
[ << label >> ]
FOREACH target [ SLICE number ] IN ARRAY expression LOOP
statements
END LOOP [ label ];
没有使用SLICE，或者如果指定了SLICE 0，循环将遍历通过评估 expression 产生的数组的各个元素。 target 变量按顺序分配每个元素的值，并且循环体对每个元素执行。以下是遍历整数数组元素的示例：
CREATE FUNCTION sum(int[]) RETURNS int8 AS $$
DECLARE
s int8 := 0;
x int;
BEGIN
FOREACH x IN ARRAY $1
LOOP
s := s + x;
END LOOP;
RETURN s;
END;
$$ LANGUAGE pluxsql;
无论数组维度的数量如何，元素都按存储顺序访问。虽然 target 通常只是单个变量，但在循环遍历复合值（记录）数组时，它可以是变量列表。在这种情况下，对于每个数组元素，变量从复合值的连续列中分配。
使用正数SLICE值，FOREACH遍历数组的切片而不是单个元素。SLICE值必须是不大于数组维度数的整数常量。 target 变量必须是数组，并且它接收数组值的连续切片，其中每个切片的维度数由SLICE指定。以下是遍历一维切片的示例：
CREATE FUNCTION scan_rows(int[]) RETURNS void AS $$
DECLARE
x int[];
BEGIN
FOREACH x SLICE 1 IN ARRAY $1
LOOP
RAISE NOTICE 'row = %', x;
END LOOP;
END;
$$ LANGUAGE pluxsql;
SELECT scan_rows(ARRAY[[1,2,3],[4,5,6],[7,8,9],[10,11,12]]);
NOTICE: row = {1,2,3}
NOTICE: row = {4,5,6}
NOTICE: row = {7,8,9}
NOTICE: row = {10,11,12}
2.6.8.捕获错误
默认情况下，PL/uxSQL函数中发生的任何错误都会中止函数的执行和周围的事务。您可以使用带有EXCEPTION子句的BEGIN块来捕获错误并从中恢复。语法是BEGIN块的正常语法的扩展：
[ <<label>> ]
[ DECLARE
declarations ]
BEGIN
statements
EXCEPTION
WHEN condition [ OR condition ... ] THEN
handler_statements
[ WHEN condition [ OR condition ... ] THEN
handler_statements
... ]
END;
如果没有错误发生，则此形式的块仅执行所有 statements ，然后控制传递到END之后的下一个语句。但是，如果在 statements 中发生错误，则会放弃进一步处理 statements ，并将控制传递到EXCEPTION列表。列表搜索第一个与发生的错误匹配的 condition 。如果找到匹配项，则对应的 handler_statements 将被执行，然后控制权将传递到END之后的下一条语句。如果没有匹配项，则错误会像没有EXCEPTION子句一样传播出去：错误可以被具有EXCEPTION的封闭块捕获，或者如果没有，则中止函数的处理。
condition 名称可以显示的任何名称。类别名称匹配其类别中的任何错误。特殊条件名称OTHERS匹配除QUERY_CANCELED和ASSERT_FAILURE之外的每种错误类型。（通过名称捕获这两种错误类型是可能的，但通常不明智。）条件名称不区分大小写。此外，可以通过SQLSTATE代码指定错误条件；例如，以下内容是等效的：
WHEN division_by_zero THEN ...
WHEN SQLSTATE '22012' THEN ...
如果在所选的 handler_statements 中发生新错误，则无法通过此EXCEPTION子句捕获该错误，但会传播出去。周围的EXCEPTION子句可以捕获它。
当EXCEPTION子句捕获错误时，PL/uxSQL函数的局部变量保持为错误发生时的状态，但是块内对持久数据库状态的所有更改都将被回滚。例如，请考虑以下片段：
INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');
BEGIN
UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';
x := x + 1;
y := x / 0;
EXCEPTION
WHEN division_by_zero THEN
RAISE NOTICE 'caught division_by_zero';
RETURN x;
END;
当控制权到达对y的赋值时，它将以division_by_zero错误失败。这将被EXCEPTION子句捕获。在RETURN语句中返回的值将是x的递增值，但是UPDATE命令的效果已被回滚。然而，前面的INSERT命令没有被回滚，因此最终结果是数据库包含Tom Jones而不是Joe Jones。
提示
包含EXCEPTION子句的块进入和退出的成本比没有EXCEPTION子句的块高得多。因此，不要没有必要就使用EXCEPTION。
示例 UPDATE/INSERT中的异常
此示例使用异常处理执行UPDATE或INSERT，视情况而定。建议应用程序使用带有ON CONFLICT DO UPDATE的INSERT，而不是实际使用此模式。此示例主要用于说明PL/uxSQL控制流结构的使用：
CREATE TABLE db (a INT PRIMARY KEY, b TEXT);
CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
LOOP
-- first try to update the key
UPDATE db SET b = data WHERE a = key;
IF found THEN
RETURN;
END IF;
-- not there, so try to insert the key
-- if someone else inserts the same key concurrently,
-- we could get a unique-key failure
BEGIN
INSERT INTO db(a,b) VALUES (key, data);
RETURN;
EXCEPTION WHEN unique_violation THEN
-- Do nothing, and loop to try the UPDATE again.
END;
END LOOP;
END;
$$
LANGUAGE pluxsql;
SELECT merge_db(1, 'david');
SELECT merge_db(1, 'dennis');
这段代码假设 unique_violation 错误是由 INSERT 引起的，而不是由表上的触发器函数中的 INSERT引起的。如果表上有多个唯一索引，则它可能会出现问题，因为它将无论哪个索引引起错误都会重试操作。通过使用下面讨论的功能来检查捕获的错误是否是预期的，可以获得更多的安全性。
2.6.8.1.获取有关错误的信息
异常处理程序经常需要识别出发生的特定错误。在 PL/uxSQL 中，有两种方法可以获取有关当前异常的信息：特殊变量和 GET STACKED DIAGNOSTICS 命令。
在异常处理程序中，特殊变量 SQLSTATE 包含对应于引发的异常的错误代码以获取可能的错误代码列表）。特殊变量 SQLERRM 包含与异常相关联的错误消息。这些变量在异常处理程序之外未定义。
在异常处理程序中，还可以使用 GET STACKED DIAGNOSTICS 命令检索有关当前异常的信息，其形式为：
GET STACKED DIAGNOSTICS variable { = | := } item [ , ... ];
每个 item 都是一个关键字，用于标识要分配给指定的 variable 的状态值（它应该是正确的数据类型以接收它）。当前可用的状态项如错误诊断项所示。
错误诊断项
| 名称 | 类型 | 描述 |
|---|---|---|
RETURNED_SQLSTATE |
text |
异常的 SQLSTATE 错误代码 |
COLUMN_NAME |
text |
与异常相关的列的名称 |
CONSTRAINT_NAME |
text |
与异常相关的约束的名称 |
UX_DATATYPE_NAME |
text |
与异常相关的数据类型的名称 |
MESSAGE_TEXT |
text |
异常的主要消息的文本 |
TABLE_NAME |
text |
与异常相关的表的名称 |
SCHEMA_NAME |
text |
与异常相关的模式的名称 |
UX_EXCEPTION_DETAIL |
text |
异常的详细消息的文本（如果有） |
UX_EXCEPTION_HINT |
text |
异常的提示消息的文本（如果有） |
UX_EXCEPTION_CONTEXT |
text |
在异常发生时描述调用堆栈的文本行 |
如果异常没有为某个项目设置值，则将返回空字符串。
以下是一个示例：
DECLARE
text_var1 text;
text_var2 text;
text_var3 text;
BEGIN
-- some processing which might cause an exception
...
EXCEPTION WHEN OTHERS THEN
GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,
text_var2 = UX_EXCEPTION_DETAIL,
text_var3 = UX_EXCEPTION_HINT;
END;
2.6.9.获取执行位置信息
GET DIAGNOSTICS 命令，前面在获取结果状态中已经介绍，检索有关当前执行状态的信息（而上面讨论的GET STACKED DIAGNOSTICS 命令报告有关先前错误的执行状态的信息）。它的 UX_CONTEXT状态项有助于识别当前执行位置。 UX_CONTEXT返回一个文本字符串，其中包含描述调用堆栈的文本行。第一行是当前函数和当前正在执行的GET DIAGNOSTICS 命令。第二行和任何后续行都是指调用堆栈中更高层次的调用函数。例如：
CREATE OR REPLACE FUNCTION outer_func() RETURNS integer AS $$
BEGIN
RETURN inner_func();
END;
$$ LANGUAGE pluxsql;
CREATE OR REPLACE FUNCTION inner_func() RETURNS integer AS $$
DECLARE
stack text;
BEGIN
GET DIAGNOSTICS stack = UX_CONTEXT;
RAISE NOTICE E'--- Call Stack ---\n%', stack;
RETURN 1;
END;
$$ LANGUAGE pluxsql;
SELECT outer_func();
NOTICE: --- Call Stack ---
PL/uxSQL function inner_func() line 5 at GET DIAGNOSTICS
PL/uxSQL function outer_func() line 3 at RETURN
CONTEXT: PL/uxSQL function outer_func() line 3 at RETURN
outer_func
------------
1
(1 row)
GET STACKED DIAGNOSTICS ... UX_EXCEPTION_CONTEXT 返回相同类型的堆栈跟踪，但描述了检测到错误的位置，而不是当前位置。
2.7.游标
可以设置一个封装查询的游标，然后一次读取几行查询结果，而不是一次执行整个查询。这样做的一个原因是，当结果包含大量行时，避免内存溢出。（然而，PL/uxSQL用户通常不需要担心这个问题，因为FOR循环会自动使用游标来避免内存问题。）更有趣的用法是返回函数创建的游标的引用，允许调用者读取行。这提供了一种从函数返回大型行集的高效方法。
2.7.1.声明游标变量
在PL/uxSQL中，所有对游标的访问都通过游标变量进行，游标变量始终是特殊数据类型refcursor。创建游标变量的一种方法是将其声明为refcursor类型的变量。另一种方法是使用游标声明语法，一般形式如下：
name [ [ NO ] SCROLL ] CURSOR [ ( arguments ) ] FOR query;
（FOR可以替换为Oracle兼容的IS。）如果指定了SCROLL，则游标将能够在结果集中向前和向后滚动。向后滚动；如果指定了NO SCROLL，则向后获取将被拒绝；如果没有出现任何规范，则查询依赖于是否允许向后获取。 arguments 如果指定，则是一对由逗号分隔的name datatype ，用于定义在给定查询中要替换为参数值的名称。实际要替换这些名称的值将在打开游标时指定。
一些例子：
DECLARE
curs1 refcursor;
curs2 CURSOR FOR SELECT * FROM tenk1;
curs3 CURSOR (key integer) FOR SELECT * FROM tenk1 WHERE unique1 = key;
这三个变量都具有数据类型refcursor，但第一个可以与任何查询一起使用，而第二个已经绑定了一个完全指定的查询，最后一个绑定了一个参数化查询。（在打开游标时，key将被替换为整数参数值。）变量curs1被称为未绑定，因为它没有绑定到任何特定的查询。
当游标的查询使用FOR UPDATE/SHARE时，无法使用SCROLL选项。此外，最好在涉及易失性函数的查询中使用NO SCROLL。SCROLL的实现假定重新读取查询的输出将给出一致的结果，而易失性函数可能不会这样做。
2.7.2.打开游标
在可以检索行的游标之前，必须打开它。（这相当于SQL命令DECLARECURSOR。）PL/uxSQL有三种形式的OPEN语句，其中两种使用未绑定的游标变量，而第三种使用绑定的游标变量。
注意
绑定的游标变量也可以在不显式打开游标的情况下使用，通过循环遍历游标结果中描述的FOR语句。
2.7.2.1.OPEN FOR query
OPEN unbound_cursorvar [ [ NO ] SCROLL ] FOR query;
打开游标变量并给定要执行的指定查询。游标不能已经打开，并且必须将其声明为未绑定的游标变量（即，作为简单的refcursor变量）。查询必须是SELECT或返回行的其他内容（例如EXPLAIN）。查询与PL/uxSQL中的其他SQL命令一样处理：PL/uxSQL变量名被替换，并且查询计划被缓存以供可能的重用。当将PL/uxSQL变量替换为游标查询时，替换的值是在OPEN时具有的值；对变量的后续更改不会影响游标的行为。SCROLL和NO SCROLL选项选项与绑定游标的含义相同。
例如：
OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;
2.7.2.2.OPEN FOR EXECUTE
OPEN unbound_cursorvar [ [ NO ] SCROLL ] FOR EXECUTE query_string
[ USING expression [, ... ] ];
打开游标变量并给定要执行的指定查询。游标不能已经打开，并且必须已声明为未绑定游标变量（即简单的refcursor 变量）。查询是作为字符串表达式指定的，方式与EXECUTE命令相同。通常，这样可以提供灵活性，因此查询计划可以从一次运行到下一次运行变化（参见计划缓存），并且这也意味着不对命令字符串进行变量替换。与 EXECUTE 一样，可以通过format()和USING 在动态命令中插入参数值 SCROLL 和 NO SCROLL 选项与绑定游标的含义相同。
例如：
OPEN curs1 FOR EXECUTE format('SELECT * FROM %I WHERE col1 = $1',tabname) USING keyvalue;
在本例中，表名通过 format ()插入到查询中。Col1的比较值是通过 USING 参数插入的，因此不需要引号。
2.7.2.3.打开一个已绑定的游标
OPEN bound_cursorvar [ ( [ argument_name := ] argument_value [, ...] ) ];
这种形式的OPEN被用于打开一个游标变量，它的查询是在声明时绑定的。该游标不能是已经打开的。当且仅当该游标被声明为接收参数时，才必需出现一个实际参数值表达式的列表。这些值将被替换到命令中。
一个已绑定游标的查询计划总是被认为是可缓存的，在这种情况中没有EXECUTE的等效形式。注意SCROLL和NO SCROLL不能在OPEN中指定，因为游标的滚动行为已经被确定。
使用位置或命名记号可以传递参数值。在位置记号中，所有参数都必须按照顺序指定。在命名记号中，每一个参数的名字被使用:=指定以将它和参数表达式分隔开。
示例（这些示例使用上面示例中的游标声明）。
OPEN curs2;
OPEN curs3(42);
OPEN curs3(key := 42);
因为在一个已绑定游标的查询上已经完成了变量替换，实际有两种方式将值传到游标中：给OPEN一个显式参数，或者在查询中隐式引用一个PL/SQL变量。不过，只有在已绑定游标之前声明的变量才将会被替换到游标中。在两种情况下，要被传递的值都是在OPEN时确定的。例如，得到上例中curs3相同效果的另一种方式，如下所示。
DECLARE
key integer;
curs4 CURSOR FOR SELECT * FROM tenk1 WHERE unique1 = key;
BEGIN
key := 42;
OPEN curs4;
2.7.3.使用游标
打开游标后，可以使用此处描述的语句进行操作。
这些操作不必发生在最初打开游标的函数中。可以从函数返回refcursor值，并让调用者操作游标。（在内部，refcursor值只是包含游标活动查询的所谓portal的字符串名称。可以传递此名称，分配给其他refcursor变量等，而不会干扰portal。）
所有portal在事务结束时都会被隐式关闭。因此，refcursor值仅在事务结束之前可用于引用打开的游标。
2.7.3.1.FETCH
FETCH [ direction { FROM | IN } ] cursor INTO target;
FETCH将游标的下一行检索到目标中，目标可以是行变量、记录变量或逗号分隔的简单变量列表，就像SELECT INTO一样。如果没有下一行，则将目标设置为NULL。与SELECT INTO一样，可以检查特殊变量FOUND以查看是否获取了行。
direction 子句可以是SQL FETCH 命令中允许的任何变体，除了可以获取多行的变体；即可以是NEXT、PRIOR、FIRST、LAST、ABSOLUTE count 、RELATIVE count 、FORWARD或BACKWARD。省略 direction 与指定NEXT相同。在使用 count 的形式中， count 可以是任何整数值表达式（与SQL FETCH命令不同，后者仅允许整数常量）。需要向后移动的 direction 值可能会失败，除非使用SCROLL选项声明或打开游标。
cursor 必须是引用打开游标portal的refcursor变量的名称。
例如：
FETCH curs1 INTO rowvar;
FETCH curs2 INTO foo, bar, baz;
FETCH LAST FROM curs3 INTO x, y;
FETCH RELATIVE -2 FROM curs4 INTO x;
2.7.3.2.MOVE
MOVE [ direction { FROM | IN } ] cursor;
从 curs4 中相对移动 -2 个位置到 x； MOVE 重新定位游标而不检索任何数据。MOVE 的工作方式与 FETCH命令完全相同，只是重新定位游标而不返回移动到的行。与 SELECT INTO 一样，可以检查特殊变量FOUND，以查看是否有下一行可移动。
示例：
MOVE curs1;
MOVE LAST FROM curs3;
MOVE RELATIVE -2 FROM curs4;
MOVE FORWARD 2 FROM curs4;
2.7.3.3.UPDATE/DELETE WHERE CURRENT OF
UPDATE table SET ... WHERE CURRENT OF cursor;
DELETE FROM table WHERE CURRENT OF cursor;
当游标定位在表行上时，可以使用游标标识该行来更新或删除该行。游标的查询有限制（特别是没有分组），最好在游标中使用FOR UPDATE。
示例：
UPDATE foo SET dataval = myval WHERE CURRENT OF curs1;
2.7.3.4.CLOSE
CLOSE
关闭打开的游标下面的门户。这可以用于比事务结束更早地释放资源，或者释放游标变量以便再次打开。
示例：
CLOSE curs1;
2.7.3.5.返回游标
PL/uxSQL函数可以向调用者返回游标。这对于返回多行或列特别是非常大的结果集很有用。为此，函数打开游标并将游标名称返回给调用者（或者仅使用由调用者指定或其他已知的门户名称打开游标）。然后调用者可以从游标中获取行。调用者可以关闭游标，或者在事务关闭时自动关闭游标。
用于游标的门户名称可以由程序员指定或自动生成。要指定门户名称，只需在打开它之前将字符串分配给 refcursor 变量。refcursor变量的字符串值将由 OPEN 用作底层门户的名称。但是，如果 refcursor 变量为空，则 OPEN会自动生成一个名称，该名称不与任何现有门户冲突，并将其分配给 refcursor 变量。
注意
绑定的游标变量初始化为表示其名称的字符串值，因此门户名称与游标变量名称相同，除非程序员覆盖它。在打开游标之前，可以通过分配来指定游标名称。但是，未绑定的游标变量最初默认为null值，因此它将接收自动生成的唯一名称，除非被覆盖。
以下示例展示了调用者提供游标名称的一种方式：
CREATE TABLE test (col text);
INSERT INTO test VALUES ('123');
CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
BEGIN
OPEN $1 FOR SELECT col FROM test;
RETURN $1;
END;
' LANGUAGE pluxsql;
BEGIN;
SELECT reffunc('funccursor');
FETCH ALL IN funccursor;
COMMIT;
以下示例使用自动生成的游标名称：
CREATE FUNCTION reffunc2() RETURNS refcursor AS '
DECLARE
ref refcursor;
BEGIN
OPEN ref FOR SELECT col FROM test;
RETURN ref;
END;
' LANGUAGE pluxsql;
-- need to be in a transaction to use cursors.
BEGIN;
SELECT reffunc2();
reffunc2
--------------------
<unnamed cursor 1>
(1 row)
FETCH ALL IN "<unnamed cursor 1>";
COMMIT;
以下示例展示了从单个函数返回多个游标的一种方式：
CREATE FUNCTION myfunc(refcursor, refcursor) RETURNS SETOF refcursor AS $$
BEGIN
OPEN $1 FOR SELECT * FROM table_1;
RETURN NEXT $1;
OPEN $2 FOR SELECT * FROM table_2;
RETURN NEXT $2;
END;
$$ LANGUAGE pluxsql;
-- need to be in a transaction to use cursors.
BEGIN;
SELECT * FROM myfunc('a', 'b');
FETCH ALL FROM a;
FETCH ALL FROM b;
COMMIT;
2.7.4.循环遍历游标结果
有一种变体的FOR语句，允许迭代游标返回的行。语法如下：
[ <<label>> ]
FOR recordvar IN bound_cursorvar [ ( [ argument_name := ] argument_value [, ...] ) ] LOOP
statements
END LOOP [ label ];
游标变量在声明时必须绑定到某个查询，并且不能已经打开。FOR语句自动打开游标，并在循环退出时关闭游标。如果游标声明为带参数，则必须出现实际参数值表达式列表。这些值将像在OPEN期间一样在查询中进行替换（请参见打开一个已绑定的游标）。
变量recordvar自动定义为类型record，并且仅在循环内部存在（循环内忽略任何现有的变量名定义）。游标返回的每一行都被依次分配给该记录变量，并执行循环体。
2.8.事务管理
在由CALL命令调用的过程中以及匿名代码块（DO命令）中，可以用命令COMMIT和ROLLBACK结束事务（ORACLE模式下，还可以在由SELECT命令调用的函数中使用）。使用这些命令结束事务后，会自动启动新的事务，因此没有单独的 STARTTRANSACTION 命令。（请注意，BEGIN 和 END 在 PL/uxSQL 中有不同的含义。）
以下是一个简单的示例：
CREATE PROCEDURE transaction_test1()
LANGUAGE pluxsql
AS $$
BEGIN
FOR i IN 0..9 LOOP
INSERT INTO test1 (a) VALUES (i);
IF i % 2 = 0 THEN
COMMIT;
ELSE
ROLLBACK;
END IF;
END LOOP;
END;
$$;
CALL transaction_test1();
新事务以默认的事务特性（如事务隔离级别）开始。在循环中提交事务时，可能希望自动以与上一个事务相同的特性开始新事务。命令 COMMIT AND CHAIN 和 ROLLBACK AND CHAIN 可以实现这一点。
事务控制仅在顶层或嵌套的 CALL 或 DO 调用中进行，而且没有其他干扰的命令。例如，如果调用堆栈是 CALL proc1() →CALL proc2() → CALL proc3()，则第二个和第三个过程可以执行事务控制操作。但是，如果调用堆栈是 CALL proc1() → SELECT func2() → CALL proc3()，则最后一个过程无法进行事务控制，因为中间有SELECT。
游标循环有特殊的考虑因素。考虑以下示例：
CREATE PROCEDURE transaction_test2()
LANGUAGE pluxsql
AS $$
DECLARE
r RECORD;
BEGIN
FOR r IN SELECT * FROM test2 ORDER BY x LOOP
INSERT INTO test1 (a) VALUES (r.x);
COMMIT;
END LOOP;
END;
$$;
CALL transaction_test2();
通常，游标在事务提交时会自动关闭。但是，像这样作为循环的一部分创建的游标会在第一个COMMIT或ROLLBACK时自动转换为可保持游标。这意味着游标在第一个COMMIT或ROLLBACK时会被完全评估，而不是逐行评估。游标仍然会在循环后自动删除，因此对用户来说这基本上是不可见的。
不允许在由非只读命令驱动的游标循环中使用事务命令（例如UPDATE ... RETURNING）。
不能在具有异常处理程序的块内结束事务。
2.9.错误和消息
2.9.1.报告错误和消息
使用 RAISE语句报告消息并引发错误。
RAISE [ level ] 'format' [, expression [, ... ]] [ USING option = expression [, ... ] ];
RAISE [ level ] condition_name [ USING option = expression [, ... ] ];
RAISE [ level ] SQLSTATE 'sqlstate' [ USING option = expression [, ... ] ];
RAISE [ level ] USING option = expression [, ... ];
RAISE ;
NOTICE，WARNING和EXCEPTION是三种不同的消息级别，其中EXCEPTION是默认级别。使用EXCEPTION会引发错误（通常会中止当前事务），而其他级别只会生成不同优先级的消息。是否向客户端报告特定优先级的消息、将其写入服务器日志或两者都有，由log_min_messages和client_min_messages配置变量控制。
在 level 之后，您可以指定一个 format 字符串（必须是简单的字符串文字，而不是表达式）。格式字符串指定要报告的错误消息文本。格式字符串可以后跟可选的参数表达式以插入到消息中。在格式字符串中，%将被下一个可选参数的值的字符串表示形式替换。写%%以发出文字%。参数的数量必须与格式字符串中%占位符的数量匹配，否则在函数编译期间会引发错误。
在此示例中，v_job_id的值将替换字符串中的%：
RAISE NOTICE 'Calling cs_create_job(%)', v_job_id;
您可以通过编写USING，后跟 option = expression 项，将附加信息附加到错误报告中。每个 expression 都可以是任何字符串值表达式。允许的 option 关键字如下：
-
MESSAGE
设置错误消息文本。在RAISE之前包含格式字符串的形式中无法使用此选项。 -
DETAIL
提供错误详细信息。 -
HINT
提供提示信息。 -
ERRCODE
指定要报告的错误代码（SQLSTATE），可以是条件名称或直接作为五个字符的SQLSTATE代码。 -
COLUMN
CONSTRAINT
DATATYPE
TABLE
SCHEMA
提供相关对象的名称。
此示例将使用给定的错误消息和提示中止事务：
RAISE EXCEPTION 'Nonexistent ID --> %', user_id
USING HINT = 'Please check your user ID';
这两个示例展示了设置SQLSTATE的等效方式：
RAISE 'Duplicate user ID: %', user_id USING ERRCODE = 'unique_violation';
RAISE 'Duplicate user ID: %', user_id USING ERRCODE = '23505';
有第二种RAISE语法，其中主要参数是要报告的条件名称或SQLSTATE，例如：
RAISE division_by_zero;
RAISE SQLSTATE '22012';
在这种语法中，USING可以用于提供自定义错误消息、详细信息或提示。另一种实现前面示例的方法是
RAISE unique_violation USING MESSAGE = 'Duplicate user ID: ' || user_id;
还有一种变体是编写RAISE USING或RAISE level USING，并将其他所有内容放入USING列表中。
RAISE的最后一种变体根本没有参数。这种形式只能在BEGIN块的EXCEPTION子句中使用；它会重新抛出当前正在处理的错误。
注意
在UXsinoDB之前版本中，没有参数的RAISE被解释为从包含活动异常处理程序的块中重新抛出错误。因此，即使RAISE在嵌套的EXCEPTION子句的块中，嵌套的EXCEPTION子句也无法捕获它。这被认为是令人惊讶的，而且与Oracle的PL/SQL不兼容。
如果在RAISEEXCEPTION命令中未指定条件名称或SQLSTATE，则默认使用ERRCODE_RAISE_EXCEPTION（P0001）。如果未指定消息文本，则默认使用条件名称或SQLSTATE作为消息文本。
注意
当通过SQLSTATE代码指定错误代码时，您不仅限于预定义的错误代码，而可以选择任何由五个数字和/或大写ASCII字母组成的错误代码，除了00000。建议避免抛出以三个零结尾的错误代码，因为这些是类别代码，只能通过捕获整个类别来捕获它们。
2.9.2.检查断言
ASSERT语句是在PL/uxSQL函数中插入调试检查的便捷方式。
ASSERT condition [ , message ];
condition 是一个布尔表达式，预计始终评估为true；如果是，则ASSERT语句不会再执行任何操作。如果结果为false或null，则会引发ASSERT_FAILURE异常。（如果在评估 condition 时发生错误，则会报告为正常错误。）
如果提供了可选的 message ，则它是一个表达式，其结果（如果不为null）将替换默认的错误消息文本“assertionfailed”，如果 condition 失败。在断言成功的正常情况下，不会评估 message 表达式。
可以通过配置文件启用或禁用断言测试。
参数pluxsql.check_asserts接受一个布尔值，默认值为on。如果将此参数设置为off，则ASSERT语句将不起作用。
请注意，ASSERT用于检测程序错误，而不是报告普通错误条件。对于报告普通错误条件，请使用上面描述的RAISE语句。
2.10.触发器函数
PL/uxSQL可用于定义数据更改或数据库事件的触发器函数。 触发器函数是使用CREATE FUNCTION命令创建的，将其声明为没有参数且返回类型为trigger（用于数据更改触发器）或event_trigger（用于数据库事件触发器）的函数。名为TG_something的特殊本地变量会自动定义，以描述触发调用的条件。
2.10.1.数据更改触发器
数据更改触发器被声明为没有参数且返回类型为trigger的函数。 请注意，即使函数期望接收在CREATE TRIGGER中指定的一些参数，该函数也必须声明为没有参数—这些参数通过TG_ARGV传递，如下所述。
当将PL/uxSQL函数作为触发器调用时，在顶层块中会自动创建几个特殊变量。它们是：
-
NEW
数据类型为RECORD；在行级触发器中，它保存INSERT/UPDATE操作的新数据库行。在语句级触发器和DELETE操作中，此变量为空。 -
OLD
数据类型为RECORD；在行级触发器中，它保存UPDATE/DELETE操作的旧数据库行。在语句级触发器和INSERT操作中，此变量为空。 -
TG_NAME
数据类型为NAME；包含触发器的名称。 -
TG_WHEN
数据类型为text；包含触发器的类型。可能的值是'BEFORE'、'AFTER'和'INSTEAD OF'。 -
TG_LEVEL
数据类型为text；包含触发器的级别。可能的值是'ROW'和'STATEMENT'。 -
TG_OP
数据类型为text；包含触发器的操作类型。可能的值是'INSERT'、'UPDATE'和'DELETE'。 -
TG_RELID
数据类型为OID；包含触发器所在表的对象标识符。 -
TG_TABLE_NAME
数据类型为NAME；包含触发器所在表的名称。 -
TG_TABLE_SCHEMA
数据类型为NAME；包含触发器所在表的模式名称。 -
TG_NARGS
数据类型为int；包含传递给触发器的参数数目。 -
TG_ARGV
数据类型为text[]；包含传递给触发器的参数值的数组。
触发器函数必须返回NULL或一个与触发器被触发的表具有完全相同结构的记录/行值。
触发器被触发前的行级触发器可以返回null，以向触发管理器发出信号，跳过此行的其余操作（即，不会触发后续触发器，也不会对此行进行INSERT/UPDATE/DELETE操作）。如果返回非空值，则操作将使用该行值继续进行。返回与NEW的原始值不同的行值会更改将要插入或更新的行。因此，如果触发器函数希望触发操作正常成功而不更改行值，则必须返回NEW（或等于其值的值）。要更改要存储的行，可以直接替换NEW中的单个值并返回修改后的NEW，或者构建一个完整的新记录/行以返回。在触发器之前的情况下在DELETE 中，返回值没有直接的影响，但必须是非空的，以允许触发器操作继续进行。请注意，在 DELETE 触发器中，NEW为空，因此返回该值通常是不明智的。在 DELETE 触发器中，通常的习惯用法是返回 OLD。
INSTEAD OF 触发器（始终是行级触发器，并且只能用于视图）可以返回null，以表示它们没有执行任何更新，并且应跳过此行的其余操作（即，不会触发后续触发器，并且该行不会计入周围INSERT/UPDATE/DELETE 的受影响行数）。否则，应返回非空值，以表示触发器执行了请求的操作。对于 INSERT和 UPDATE 操作，返回值应为 NEW，触发器函数可以修改它以支持 INSERT RETURNING 和 UPDATERETURNING（这也会影响传递给任何后续触发器的行值，或传递给带有 ON CONFLICT DO UPDATE 子句的INSERT 语句中的特殊 EXCLUDED 别名引用）。对于 DELETE 操作，返回值应为 OLD。
行级触发器触发的 AFTER 或语句级触发器触发的 BEFORE 或 AFTER的返回值始终被忽略；它可能为空。但是，这些类型的触发器仍然可能通过引发错误来中止整个操作。
示例 一个 PL/uxSQL 触发器函数显示了一个在 PL/uxSQL 中的触发器函数示例。
示例 一个 PL/uxSQL 触发器函数
此示例触发器确保每次在表中插入或更新行时，当前用户名称和时间戳都会被标记到行中。并检查是否给出了员工的姓名以及薪水是否为正值。
CREATE TABLE emp (
empname text,
salary integer,
last_date timestamp,
last_user text
);
CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
BEGIN
-- Check that empname and salary are given
IF NEW.empname IS NULL THEN
RAISE EXCEPTION 'empname cannot be null';
END IF;
IF NEW.salary IS NULL THEN
RAISE EXCEPTION '% cannot have null salary', NEW.empname;
END IF;
-- Who works for us when they must pay for it?
IF NEW.salary < 0 THEN
RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
END IF;
-- Remember who changed the payroll when
NEW.last_date := current_timestamp;
NEW.last_user := current_user;
RETURN NEW;
END;
$emp_stamp$ LANGUAGE pluxsql;
CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW EXECUTE FUNCTION emp_stamp();
记录表更改的另一种方法是创建一个新表，该表保存每次发生的插入、更新或删除的行。这种方法可以被视为审计表更改。示例 用于审计的 PL/uxSQL 触发器函数显示了一个在 PL/uxSQL 中的审计触发器函数示例。
示例 用于审计的 PL/uxSQL 触发器函数
此示例触发器确保在 emp 表中的任何行的插入、更新或删除都记录（即审计）在 emp_audit表中。当前时间和用户名与执行的操作类型一起记录到行中。
CREATE TABLE emp (
empname text NOT NULL,
salary integer
);
CREATE TABLE emp_audit(
operation char(1) NOT NULL,
stamp timestamp NOT NULL,
userid text NOT NULL,
empname text NOT NULL,
salary integer
);
CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
BEGIN
--
-- Create a row in emp_audit to reflect the operation performed on emp,
-- making use of the special variable TG_OP to work out the operation.
--
IF (TG_OP = 'DELETE') THEN
INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;
END IF;
RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$emp_audit$ LANGUAGE pluxsql;
CREATE TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW EXECUTE FUNCTION process_emp_audit();
前一个示例的变体使用了一个将主表与审计表连接起来的视图，以显示每个条目的最后修改时间。这种方法仍然记录表的完整审计跟踪，但也提供了一个简化的审计跟踪视图，仅显示每个条目的来自审计跟踪的最后修改时间。示例 用于审计的 PL/uxSQL 视图触发器函数显示了在 PL/uxSQL 中对视图进行审计的示例。
示例 用于审计的 PL/uxSQL 视图触发器函数
此示例使用视图上的触发器使其可更新，并确保在视图中的任何行的插入、更新或删除都记录（即审计）在 emp_audit表中。当前时间和用户名与执行的操作类型一起记录，并且视图显示每行的最后修改时间。
CREATE TABLE emp (
empname text PRIMARY KEY,
salary integer
);
CREATE TABLE emp_audit(
operation char(1) NOT NULL,
userid text NOT NULL,
empname text NOT NULL,
salary integer,
stamp timestamp NOT NULL
);
CREATE VIEW emp_view AS
SELECT e.empname,
e.salary,
max(ea.stamp) AS last_updated
FROM emp e
LEFT JOIN emp_audit ea ON ea.empname = e.empname
GROUP BY 1, 2;
CREATE OR REPLACE FUNCTION update_emp_view() RETURNS TRIGGER AS $$
BEGIN
--
-- Perform the required operation on emp, and create a row in emp_audit
-- to reflect the change made to emp.
--
IF (TG_OP = 'DELETE') THEN
DELETE FROM emp WHERE empname = OLD.empname;
IF NOT FOUND THEN RETURN NULL; END IF;
OLD.last_updated = now();
INSERT INTO emp_audit VALUES('D', user, OLD.*);
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
UPDATE emp SET salary = NEW.salary WHERE empname = OLD.empname;
IF NOT FOUND THEN RETURN NULL; END IF;
NEW.last_updated = now();
INSERT INTO emp_audit VALUES('U', user, NEW.*);
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO emp VALUES(NEW.empname, NEW.salary);
NEW.last_updated = now();
INSERT INTO emp_audit VALUES('I', user, NEW.*);
RETURN NEW;
END IF;
END;
$$ LANGUAGE pluxsql;
CREATE TRIGGER emp_audit
INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_view
FOR EACH ROW EXECUTE FUNCTION update_emp_view();
触发器的一个用途是维护另一个表的汇总表。生成的汇总可以用于某些查询的原始表格，通常可以大大减少运行时间。这种技术通常用于数据仓库中，其中测量或观察数据的表（称为事实表）可能非常大。示例 用于维护汇总表的PL/uxSQL触发器函数，该处方案部分基于 Ralph Kimball 的《数据仓库工具包》中的“杂货店”示例。
示例 用于维护汇总表的PL/uxSQL触发器函数
这里详细描述的模式部分基于 Ralph Kimball 的数据仓库工具包中的 Grocery Store 示例。
--
-- Main tables - time dimension and sales fact.
--
CREATE TABLE time_dimension (
time_key integer NOT NULL,
day_of_week integer NOT NULL,
day_of_month integer NOT NULL,
month integer NOT NULL,
quarter integer NOT NULL,
year integer NOT NULL
);
CREATE UNIQUE INDEX time_dimension_key ON time_dimension(time_key);
CREATE TABLE sales_fact (
time_key integer NOT NULL,
product_key integer NOT NULL,
store_key integer NOT NULL,
amount_sold numeric(12,2) NOT NULL,
units_sold integer NOT NULL,
amount_cost numeric(12,2) NOT NULL
);
CREATE INDEX sales_fact_time ON sales_fact(time_key);
--
-- Summary table - sales by time.
--
CREATE TABLE sales_summary_bytime (
time_key integer NOT NULL,
amount_sold numeric(15,2) NOT NULL,
units_sold numeric(12) NOT NULL,
amount_cost numeric(15,2) NOT NULL
);
CREATE UNIQUE INDEX sales_summary_bytime_key ON sales_summary_bytime(time_key);
--
-- Function and trigger to amend summarized column(s) on UPDATE, INSERT, DELETE.
--
CREATE OR REPLACE FUNCTION maint_sales_summary_bytime() RETURNS TRIGGER
AS $maint_sales_summary_bytime$
DECLARE
delta_time_key integer;
delta_amount_sold numeric(15,2);
delta_units_sold numeric(12);
delta_amount_cost numeric(15,2);
BEGIN
-- Work out the increment/decrement amount(s).
IF (TG_OP = 'DELETE') THEN
delta_time_key = OLD.time_key;
delta_amount_sold = -1 * OLD.amount_sold;
delta_units_sold = -1 * OLD.units_sold;
delta_amount_cost = -1 * OLD.amount_cost;
ELSIF (TG_OP = 'UPDATE') THEN
-- forbid updates that change the time_key -
-- (probably not too onerous, as DELETE + INSERT is how most
-- changes will be made).
IF ( OLD.time_key != NEW.time_key) THEN
RAISE EXCEPTION 'Update of time_key : % -> % not allowed',
OLD.time_key, NEW.time_key;
END IF;
delta_time_key = OLD.time_key;
delta_amount_sold = NEW.amount_sold - OLD.amount_sold;
delta_units_sold = NEW.units_sold - OLD.units_sold;
delta_amount_cost = NEW.amount_cost - OLD.amount_cost;
ELSIF (TG_OP = 'INSERT') THEN
delta_time_key = NEW.time_key;
delta_amount_sold = NEW.amount_sold;
delta_units_sold = NEW.units_sold;
delta_amount_cost = NEW.amount_cost;
END IF;
-- Insert or update the summary row with the new values.
<<insert_update>>
LOOP
UPDATE sales_summary_bytime
SET amount_sold = amount_sold + delta_amount_sold,
units_sold = units_sold + delta_units_sold,
amount_cost = amount_cost + delta_amount_cost
WHERE time_key = delta_time_key;
EXIT insert_update WHEN found;
BEGIN
INSERT INTO sales_summary_bytime (
time_key,
amount_sold,
units_sold,
amount_cost)
VALUES (
delta_time_key,
delta_amount_sold,
delta_units_sold,
delta_amount_cost
);
EXIT insert_update;
EXCEPTION
WHEN UNIQUE_VIOLATION THEN
-- do nothing
END;
END LOOP insert_update;
RETURN NULL;
END;
$maint_sales_summary_bytime$ LANGUAGE pluxsql;
CREATE TRIGGER maint_sales_summary_bytime
AFTER INSERT OR UPDATE OR DELETE ON sales_fact
FOR EACH ROW EXECUTE FUNCTION maint_sales_summary_bytime();
INSERT INTO sales_fact VALUES(1,1,1,10,3,15);
INSERT INTO sales_fact VALUES(1,2,1,20,5,35);
INSERT INTO sales_fact VALUES(2,2,1,40,15,135);
INSERT INTO sales_fact VALUES(2,3,1,10,1,13);
SELECT * FROM sales_summary_bytime;
DELETE FROM sales_fact WHERE product_key = 1;
SELECT * FROM sales_summary_bytime;
UPDATE sales_fact SET units_sold = units_sold * 2;
SELECT * FROM sales_summary_bytime;
AFTER 触发器还可以使用转换表来检查触发语句更改的整个行集。CREATE TRIGGER命令将名称分配给一个或两个转换表，然后函数可以引用这些名称，就好像它们是只读临时表一样。
示例 使用转换表进行审核
这个示例产生与示例 用于审计的 PL/uxSQL 触发器函数相同的结果，但是在收集转换表中的相关信息之后，它不使用每行触发一次的触发器，而是使用每条语句触发一次的触发器。当调用语句修改了许多行时，这比行触发器方法快得多。注意，我们必须为每种类型的事件分别声明一个触发器，因为REFERENCING子句对于每种情况都必须是不同的。但是，如果我们选择，这并不能阻止我们使用单一触发函数。(实际上，最好使用三个单独的函数，避免在TG_OP上进行运行时测试。)
CREATE TABLE emp (
empname text NOT NULL,
salary integer
);
CREATE TABLE emp_audit(
operation char(1) NOT NULL,
stamp timestamp NOT NULL,
userid text NOT NULL,
empname text NOT NULL,
salary integer
);
CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
BEGIN
--
-- Create rows in emp_audit to reflect the operations performed on emp,
-- making use of the special variable TG_OP to work out the operation.
--
IF (TG_OP = 'DELETE') THEN
INSERT INTO emp_audit
SELECT 'D', now(), user, o.* FROM old_table o;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO emp_audit
SELECT 'U', now(), user, n.* FROM new_table n;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO emp_audit
SELECT 'I', now(), user, n.* FROM new_table n;
END IF;
RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$emp_audit$ LANGUAGE pluxsql;
CREATE TRIGGER emp_audit_ins
AFTER INSERT ON emp
REFERENCING NEW TABLE AS new_table
FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();
CREATE TRIGGER emp_audit_upd
AFTER UPDATE ON emp
REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();
CREATE TRIGGER emp_audit_del
AFTER DELETE ON emp
REFERENCING OLD TABLE AS old_table
FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();
2.10.2.事件触发器
PL/uxSQL 可以用于定义事件触发器。 UXsinoDB要求作为事件触发器调用的函数必须声明为一个没有参数并且返回类型为 event_trigger 的函数。
当一个 PL/uxSQL 函数被调用作为事件触发器时，在顶层块中会自动创建几个特殊变量。它们是：
-
TG_EVENT
数据类型为text；表示触发器触发的事件的字符串。 -
TG_TAG
数据类型为text；包含触发器触发的命令标记。
示例 一个 PL/uxSQL 事件触发器函数展示了一个 PL/uxSQL 中的事件触发器函数的例子。
示例 一个 PL/uxSQL 事件触发器函数
这个示例触发器每次执行支持的命令时都会简单地引发一个 NOTICE 消息。
CREATE OR REPLACE FUNCTION snitch() RETURNS event_trigger AS $$
BEGIN
RAISE NOTICE 'snitch: % %', tg_event, tg_tag;
END;
$$ LANGUAGE pluxsql;
CREATE EVENT TRIGGER snitch ON ddl_command_start EXECUTE FUNCTION snitch();
2.11.PL/uxSQL底层实现
本节讨论了一些实现细节，这些细节对于PL/uxSQL用户来说通常很重要。
2.11.1.变量替换
PL/uxSQL函数中的SQL语句和表达式可以引用函数的变量和参数。在幕后，PL/uxSQL会为这些引用替换查询参数。查询参数只会在语法上允许的地方进行替换。作为一个极端的例子，考虑下面这个编程风格很差的例子：
INSERT INTO foo (foo) VALUES (foo(foo));
第一个foo的语法必须是一个表名，因此即使函数有一个名为foo的变量，它也不会被替换。第二个foo必须是该表的一个列名，因此它也不会被替换。同样，第三个foo必须是一个函数名，因此它也不会被替换。只有最后一个foo是PL/uxSQL函数的变量引用的候选项。
另一种理解方式是，变量替换只能将数据值插入到SQL命令中；它不能动态地改变命令引用的数据库对象。如果你想这样做，你必须动态地构建一个命令字符串，如执行动态命令所述。
由于变量的名称在语法上与表列的名称没有区别，因此在同时引用表列和变量的语句中可能会存在歧义。表格：给定的名称是指表格列还是变量？让我们将前面的示例更改为
INSERT INTO dest (col) SELECT foo + bar FROM src;
这里，dest 和 src 必须是表格名称，而 col 必须是 dest 的列，但是 foo 和 bar可以合理地是函数的变量或者是 src 的列。
默认情况下，PL/uxSQL 会报告一个错误，如果一个名称在 SQL 语句中既可以是变量也可以是表格列。您可以通过重命名变量或列，或者限定模糊引用，或者告诉PL/uxSQL 偏好哪种解释来解决此类问题。
最简单的解决方案是重命名变量或列。 一个常见的编码规则是为 PL/uxSQL 变量使用不同的命名约定，而不是为列名使用。例如，如果您一贯地将函数变量命名为 v_something，而您的 列名没有一个以 v_开头，就不会发生冲突。
或者，您可以限定模糊引用以使其清晰明了。 在上面的示例中，src.foo 将是对表格列的明确引用。 要创建对变量的明确引用，在一个带标签的块中声明它并使用该块的标签（参见PL/uxSQL的结构）。例如，
<<block>>
DECLARE
foo int;
BEGIN
foo := ...;
INSERT INTO dest (col) SELECT block.foo + bar FROM src;
这里，block.foo 意味着变量，即使在 src 中有一个列 foo。函数参数以及 FOUND 等特殊变量可以由函数的名称限定，因为它们是在外部块中隐式声明的 标记为函数名称。
有时，在大量的 PL/uxSQL 代码中修复所有模糊引用是不切实际的。 在这种情况下，您可以指定 PL/uxSQL 应该将模糊引用解析为变量（与PL/uxSQL之前的行为兼容）或表格列（与其他一些系统如 Oracle 兼容）。
要在系统范围内更改此行为，请将配置参数 pluxsql.variable_conflict 设置为error、use_variable 或 use_column 之一（其中 error 是出厂默认值）。 此参数影响后续编译
在 PL/uxSQL 函数中的语句，但不影响已经编译的语句 在当前会话中。 因为更改此设置 可能会导致 PL/uxSQL函数的行为出现意外变化， 所以只有超级用户才能更改它。
您还可以按函数逐个设置行为， 在函数文本的开头插入以下特殊命令之一：
#variable_conflict error
#variable_conflict use_variable
#variable_conflict use_column
这些命令仅影响它们所编写的函数，并覆盖 pluxsql.variable_conflict的设置。例如，
CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$
#variable_conflict use_variable
DECLARE
curtime timestamp := now();
BEGIN
UPDATE users SET last_modified = curtime, comment = comment
WHERE users.id = id;
END;
$$ LANGUAGE pluxsql;
如果需要将可变值插入这样的命令中，请将其作为构建字符串值的一部分，或者使用 USING，如执行动态命令所示。
目前，变量替换仅适用于 SELECT、INSERT、UPDATE、DELETE 和包含这些命令之一的命令（例如 EXPLAIN 和 CREATE TABLE ... AS SELECT），因为主 SQL 引擎仅允许在这些命令中使用查询参数。要在其他语句类型（通常称为实用程序语句）中使用非常量名称或值，必须将实用程序语句构造为字符串并执行它。
2.11.2.计划缓存
PL/uxSQL 解释器在每个会话中首次调用函数时解析函数的源文本并生成内部二进制指令树。指令树完全翻译了 PL/uxSQL 语句结构，但是函数中使用的单个 SQL 表达式和 SQL 命令不会立即被翻译。
当在函数中首次执行每个表达式和 SQL 命令时，PL/uxSQL 解释器解析和分析该命令以创建一个准备好的语句，使用 SPI 管理器的 SPI_prepare 函数。随后访问该表达式或命令将重用准备好的语句。因此，很少访问的条件代码路径的函数永远不会遭受在当前会话中从未执行的命令的分析开销。缺点是特定表达式或命令中的错误无法在执行之前被检测到。（微不足道的语法错误将在初始解析通行证中被检测到，但是任何更深的错误直到执行时才会被检测到。）
PL/uxSQL（更准确地说，SPI 管理器）还可以尝试缓存与任何特定预处理语句相关联的执行计划。如果未使用缓存计划，则在每次访问语句时生成新的执行计划，并且可以使用当前参数值（即PL/uxSQL变量值）来优化所选计划。如果语句没有参数或被执行多次，则 SPI 管理器将考虑创建一个不依赖于特定参数值的通用计划，并将其缓存以供重复使用。通常，仅当执行计划对其中引用的PL/uxSQL变量的值不太敏感时才会发生这种情况。如果是这样，每次生成计划都是一个净胜利。
由于PL/uxSQL以这种方式保存预处理语句和有时的执行计划，因此直接出现在PL/uxSQL函数中的 SQL 命令必须在每次执行时引用相同的表和列；也就是说，您不能将参数用作 SQL 命令中表或列的名称。为了解决这个限制，您可以使用PL/uxSQL的EXECUTE语句构造动态命令，但需要在每次执行时执行新的解析分析和构造新的执行计划。
记录变量的可变性质在这种情况下还会带来另一个问题。当记录变量的字段用于表达式或语句时，字段的数据类型不能从函数的一个调用到下一个调用发生变化，因为每个表达式将使用在首次到达表达式时存在的数据类型进行分析。必要时可以使用EXECUTE来解决此问题。
如果同一个函数用作多个表的触发器，则PL/uxSQL将独立地为每个这样的表准备并缓存语句；也就是说，不仅为每个函数，而且为每个触发器函数和表组合都有一个缓存。这缓解了某些不同数据类型的问题；例如，触发器函数将能够成功地使用名为key的列，即使它在不同的表中具有不同的类型。
同样，具有多态参数类型的函数对于它们已被调用的每个实际参数类型组合都有一个单独的语句缓存，因此数据类型差异不会导致意外故障。
语句缓存有时可能会对时间敏感值的解释产生意想不到的影响。例如，这两个函数之间存在差异：
CREATE FUNCTION logfunc1(logtxt text) RETURNS void AS $$
BEGIN
INSERT INTO logtable VALUES (logtxt, 'now');
END;
$$ LANGUAGE pluxsql;
和：
CREATE FUNCTION logfunc2(logtxt text) RETURNS void AS $$
DECLARE
curtime timestamp;
BEGIN
curtime := 'now';
INSERT INTO logtable VALUES (logtxt, curtime);
END;
$$ LANGUAGE pluxsql;
在logfunc1的情况下，UXsinoDB主解析器在分析INSERT时知道字符串'now'应解释为timestamp，因为logtable的目标列是该类型。因此，在分析INSERT时，'now'将转换为timestamp常量，然后在所有执行期间都使用。在会话的生命周期中调用logfunc1。不用说，这不是程序员想要的。更好的方法是使用 now() 或 current_timestamp 函数。
对于 logfunc2，UXsinoDB 的主解析器不知道 'now' 应该成为什么类型，因此它返回一个数据类型为 text的数据值，其中包含字符串 now。在随后的赋值给本地变量 curtime 时，PL/uxSQL 解释器通过调用textout 和 timestamp_in 函数将此字符串转换为 timestamp类型。因此，计算的时间戳在每次执行时都会更新，正如程序员所期望的那样。即使这样可以按预期工作，但效率并不是特别高，因此使用now() 函数仍然是更好的选择。
2.12.PL/uxSQL开发提示
在PL/uxSQL中进行开发的一种好方法是使用您选择的文本编辑器创建函数，并在另一个窗口中使用uxsql加载和测试这些函数。如果您是这样做的，最好使用CREATE OR REPLACE FUNCTION编写函数。这样，您可以重新加载文件以更新函数定义。例如：
CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $$
....
$$ LANGUAGE pluxsql;
在运行uxsql时，您可以使用以下命令加载或重新加载此类函数定义文件：
\i filename.sql
然后立即发出SQL命令以测试函数。
在PL/uxSQL中进行开发的另一种好方法是使用GUI数据库访问工具，该工具可以促进过程化语言的开发。这种工具的一个例子是uxAdmin，尽管还有其他工具。这些工具通常提供方便的功能，例如转义单引号并更轻松地重新创建和调试函数。
2.12.1.引号处理
一个PL/uxSQL函数的代码在一个CREATE FUNCTION中被指定为一个字符串。如果用通常的方式把该字符串写在单引号中间，那么该函数体中的任何单引号都必须被双写；同样任何反斜线也必须被双写（假定使用了转义字符串语法）。双写引号最多有点冗长，并且在更复杂的情况中代码会变得完全无法理解，因为很容易发现需要半打或者更多相邻的引号。推荐把函数体写成一个“美元引用”的字符串。在美元引用方法中，从不需要双写任何引号。但是要注意为需要的每一层嵌套选择一个不同的美元引用定界符。例如，CREATE FUNCTION命令如下所示。
CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $PROC$
....
$PROC$ LANGUAGE pluxsql;
在这里面，可以在 SQL 命令中为简单字符串使用引号并且用$$来界定被组装成字符串的 SQL 命令片段。如果需要引用包括$$的文本，可以使用$Q$等等。
下列图表展示了在写没有美元引用的引号时需要做什么。在将之前用美元引用的代码翻译成更容易理解的代码时，它们会有所帮助。
-
1 个引号
用来开始和结束函数体，如下所示。
CREATE FUNCTION foo() RETURNS integer AS ' .... ' LANGUAGE pluxsql;在一个单引号引用的函数体中的任何位置，引号必须成对出现。
-
2 个引号
用于函数体内的字符串，如下所示。
a_output := ''Blah''; SELECT * FROM users WHERE f_name=''foobar'';在美元引用方法中，如下所示。
a_output := 'Blah'; SELECT * FROM users WHERE f_name='foobar';这恰好就是PL/uxSQL在两种情况中会看到的。
-
4 个引号
当函数内的一个字符串常量中需要一个单引号时，如下所示。
a_output := a_output || '' AND name LIKE ''''foobar'''' AND xyz''实际会被追加到a_output的值将是： AND name LIKE 'foobar' AND xyz。
使用美元引用方法，要注意在这周围的任何美元引用定界符不只是$$，如下所示。
a_output := a_output || $$ AND name LIKE 'foobar' AND xyz$$ -
6 个引号
当在函数体内的一个字符串中的一个单引号与该字符串常量末尾相邻，如下所示。
a_output := a_output || '' AND name LIKE ''''foobar''''''被追加到a_output的值则将是： AND name LIKE 'foobar'。
在美元引用方法中，进行如下使用。
a_output := a_output || $$ AND name LIKE 'foobar'$$ -
10个引号
当在一个字符串常量（占 8 个引号）中有两个单引号时并且这会挨着该字符串常量的末尾（另外 2 个）。如果正在写一个产生其他函数的函数，将很可能只需要这种。如下所示。
a_output := a_output || '' if v_'' || referrer_keys.kind || '' like '''''''''' || referrer_keys.key_string || '''''''''' then return '''''' || referrer_keys.referrer_type || ''''''; end if;'';a_output的值将是下面内容。
if v_... like ''...'' then return ''...''; end if;在美元引用方法中，进行如下使用。
a_output := a_output || $$ if v_$$ || referrer_keys.kind || $$ like '$$ || referrer_keys.key_string || $$' then return '$$ || referrer_keys.referrer_type || $$'; end if;$$;这里假定只需要把单引号放在a_output中，因为在使用前它将被再引用。
2.12.2.额外的编译时和运行时检查
为了辅助用户在一些简单但常见的问题产生危害之前找到它们，PL/uxSQL提供了额外的检查。当被启用时，根据配置，它们可以在一个函数的编译期间被用来发出WARNING或者ERROR。一个已经收到了WARNING的函数可以被继续执行而不会产生进一步的消息，因此建议在一个单独的开发环境中进行测试。
根据需要设置 pluxsql.extra_warnings 或 pluxsql.extra_errors，适当情况下，在开发和/或测试环境中可以设置为 "all"。
这些附加检查通过配置变量启用， pluxsql.extra_warnings用于警告，pluxsql.extra_errors 用于错误。 两者都可以设置为逗号分隔的检查列表，"none" 或 "all"。 默认值为"none"。当前可用的检查列表如下所示。
-
shadowed_variables
检查声明是否遮蔽了先前定义的变量。 -
strict_multi_assignment
一些PL/UxSQL命令允许将值分配给多个变量，例如SELECT INTO。通常，目标变量的数量和源变量的数量应该匹配，尽管PL/UxSQL将使用NULL用于缺少的值，多余的变量将被忽略。启用此检查将导致PL/UxSQL在目标变量的数量和源变量的数量不同时抛出WARNING或ERROR。 -
too_many_rows
启用此检查将导致PL/UxSQL检查是否在使用INTO子句时给定的查询返回多行。由于INTO语句只会使用一行，因此查询返回多行通常是低效和/或不确定性的，因此可能是错误的。
以下示例显示了将pluxsql.extra_warnings设置为shadowed_variables的效果：
SET pluxsql.extra_warnings TO 'shadowed_variables';
CREATE FUNCTION foo(f1 int) RETURNS int AS $$
DECLARE
f1 int;
BEGIN
RETURN f1;
END;
$$ LANGUAGE pluxsql;
下面的示例显示了将pluxsql.extra_warnings 设置为strict_multi_assignment：
SET pluxsql.extra_warnings TO 'strict_multi_assignment';
CREATE OR REPLACE FUNCTION public.foo()
RETURNS void
LANGUAGE pluxsql
AS $$
DECLARE
x int;
y int;
BEGIN
SELECT 1 INTO x, y;
SELECT 1, 2 INTO x, y;
SELECT 1, 2, 3 INTO x, y;
END;
$$;
SELECT foo();
WARNING: number of source and target fields in assignment does not match
DETAIL: strict_multi_assignment check of extra_warnings is active.
HINT: Make sure the query returns the exact list of columns.
WARNING: number of source and target fields in assignment does not match
DETAIL: strict_multi_assignment check of extra_warnings is active.
HINT: Make sure the query returns the exact list of columns.
foo
-----
(1 row)
2.13.从 Oracle PL/SQL 迁移
本节介绍了Oracle到 UXDB 的应用程序迁移时， PL/uxSQL语言与Oracle的PL/SQL语言之间的差异。
PL/uxSQL 在许多方面与 PL/SQL 相似。 它是一种块结构、命令式语言，所有变量都必须声明。 赋值、循环和条件语句也很相似。 从PL/SQL 迁移到 PL/uxSQL 时，您应该注意以下主要差异：
-
如果 SQL 命令中使用的名称既可以是命令中使用的表的列名，也可以是函数变量的引用， 则 PL/SQL 将其视为列名。默认情况下，PL/uxSQL 会抛出错误，指出名称不明确。 您可以指定
pluxsql.variable_conflict=use_column以更改此行为，使其与 PL/SQL 相匹配，如变量替换所述。通常最好避免这种歧义，但如果您必须迁移依赖于此行为的大量代码，则设置variable_conflict可能是最佳解决方案。 -
在 UXsinoDB中，函数体必须被写成字符串字面量。因此，您需要使用美元引用或在函数体中转义单引号（请参见引号处理）。
-
数据类型名称通常需要翻译。例如，在 Oracle 中，字符串值通常被声明为类型
varchar2，这是一种非 SQL 标准类型。 -
在UXsinoDB 中，使用类型
varchar或text代替。同样，将类型number替换为numeric，或者如果有更合适的数字数据类型，则使用其他数字数据类型。 使用模式来组织函数，而不是包。 -
由于没有包，也没有包级变量。这有点烦人。您可以将每个会话状态保存在临时表中。
-
带有
REVERSE的整数FOR循环的工作方式不同： PL/SQL 从第二个数字向下计数到第一个数字，而PL/uxSQL从第一个数字向下计数到第二个数字，需要在移植时交换循环边界。这种不兼容性很不幸，但不太可能改变。（请参见FOR（整数变量））。 -
查询（除游标之外）的
FOR循环也有不同的工作方式：目标变量必须已声明，而 PL/SQL总是隐式声明它们。这样做的一个优点是，在循环退出后，变量值仍然可以访问。 -
使用游标变量的符号表示法有各种不同之处。
2.13.1.移植示例
示例 移植一个简单的函数从 PL/SQL 到PL/uxSQL显示了如何将一个简单的函数从 PL/SQL 移植到 PL/uxSQL。
示例 移植一个简单的函数从 PL/SQL 到PL/uxSQL
这是一个 Oracle PL/SQL 函数：
CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar2,
v_version varchar2)
RETURN varchar2 IS
BEGIN
IF v_version IS NULL THEN
RETURN v_name;
END IF;
RETURN v_name || '/' || v_version;
END;
/
show errors;
让我们看看这个函数与 PL/uxSQL 相比的区别：
-
类型名称
varchar2必须更改为varchar或text。在本节的示例中，我们将使用varchar，但如果您不需要特定的字符串长度限制，则text通常是更好的选择。 -
函数原型中的
RETURN关键字（而不是函数体）在 UXDB 中变为RETURNS。此外，IS变成了AS，并且需要添加一个LANGUAGE子句，因为 PL/uxSQL 不是唯一可能的函数语言。 -
在 UXDB 中，函数体被视为字符串字面量，因此需要在其周围使用引号或美元符号引用。这代替了 Oracle 方法中的终止符
/。 -
show errors命令在 UXDB 中不存在，也不需要，因为错误会自动报告。
将此函数移植到 UXDB 后，它将如下所示：
CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar,
v_version varchar)
RETURNS varchar AS $$
BEGIN
IF v_version IS NULL THEN
RETURN v_name;
END IF;
RETURN v_name || '/' || v_version;
END;
$$ LANGUAGE pluxsql;
示例 从 PL/SQL 移植创建另一个函数的函数并处理引号问题展示了如何移植一个创建另一个函数并处理随之而来的引号问题的函数。
示例 从 PL/SQL 移植创建另一个函数的函数并处理引号问题
以下过程从 SELECT 语句中获取行并构建一个大函数，其中包含 IF 语句，以提高效率。
这是 Oracle 版本：
CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc IS
CURSOR referrer_keys IS
SELECT * FROM cs_referrer_keys
ORDER BY try_order;
func_cmd VARCHAR(4000);
BEGIN
func_cmd := 'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host IN VARCHAR2,
v_domain IN VARCHAR2, v_url IN VARCHAR2) RETURN VARCHAR2 IS BEGIN';
FOR referrer_key IN referrer_keys LOOP
func_cmd := func_cmd ||
' IF v_' || referrer_key.kind
|| ' LIKE ''' || referrer_key.key_string
|| ''' THEN RETURN ''' || referrer_key.referrer_type
|| '''; END IF;';
END LOOP;
func_cmd := func_cmd || ' RETURN NULL; END;';
EXECUTE IMMEDIATE func_cmd;
END;
/
show errors;
在 UXDB 中，该函数将如下所示：
CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc() AS $func$
DECLARE
referrer_keys CURSOR IS
SELECT * FROM cs_referrer_keys
ORDER BY try_order;
func_body text;
func_cmd text;
BEGIN
func_body := 'BEGIN';
FOR referrer_key IN referrer_keys LOOP
func_body := func_body ||
' IF v_' || referrer_key.kind
|| ' LIKE ' || quote_literal(referrer_key.key_string)
|| ' THEN RETURN ' || quote_literal(referrer_key.referrer_type)
|| '; END IF;' ;
END LOOP;
func_body := func_body || ' RETURN NULL; END;';
func_cmd :=
'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host varchar,
v_domain varchar,
v_url varchar)
RETURNS varchar AS '
|| quote_literal(func_body)
|| ' LANGUAGE pluxsql;' ;
EXECUTE func_cmd;
END;
$func$ LANGUAGE pluxsql;
请注意，函数体是单独构建的，并通过 quote_literal传递，以使其中的任何引号都加倍。这种技术是必需的，因为我们不能安全地使用美元符号引用来定义新函数：我们无法确定从
referrer_key.key_string 字段中插入哪些字符串。（我们在此假设 referrer_key.kind 始终可以信任为host、domain 或url。）
url，但referrer_key.key_string可能是任何内容，特别是可能包含美元符号。）这个函数实际上是对Oracle原始版本的改进，因为当referrer_key.key_string或referrer_key.referrer_type包含引号时，它不会生成错误的代码。
示例 将带有字符串操作和OUT参数的过程从PL/SQL移植到PL/uxSQL展示了如何移植一个带有OUT参数和字符串操作的函数。UXsinoDB没有内置的instr函数，但是您可以使用其他函数的组合来创建一个。在附录中，有一个PL/uxSQL实现的instr，您可以使用它来使移植更容易。
示例 将带有字符串操作和OUT参数的过程从PL/SQL移植到PL/uxSQL
以下Oracle PL/SQL过程用于解析URL并返回多个元素（主机、路径和查询）。
这是Oracle版本：
CREATE OR REPLACE PROCEDURE cs_parse_url(
v_url IN VARCHAR2,
v_host OUT VARCHAR2, -- This will be passed back
v_path OUT VARCHAR2, -- This one too
v_query OUT VARCHAR2) -- And this one
IS
a_pos1 INTEGER;
a_pos2 INTEGER;
BEGIN
v_host := NULL;
v_path := NULL;
v_query := NULL;
a_pos1 := instr(v_url, '//');
IF a_pos1 = 0 THEN
RETURN;
END IF;
a_pos2 := instr(v_url, '/', a_pos1 + 2);
IF a_pos2 = 0 THEN
v_host := substr(v_url, a_pos1 + 2);
v_path := '/';
RETURN;
END IF;
v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2);
a_pos1 := instr(v_url, '?', a_pos2 + 1);
IF a_pos1 = 0 THEN
v_path := substr(v_url, a_pos2);
RETURN;
END IF;
v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
v_query := substr(v_url, a_pos1 + 1);
END;
/
show errors;
这是可能的PL/uxSQL翻译：
CREATE OR REPLACE FUNCTION cs_parse_url(
v_url IN VARCHAR,
v_host OUT VARCHAR, -- This will be passed back
v_path OUT VARCHAR, -- This one too
v_query OUT VARCHAR) -- And this one
AS $$
DECLARE
a_pos1 INTEGER;
a_pos2 INTEGER;
BEGIN
v_host := NULL;
v_path := NULL;
v_query := NULL;
a_pos1 := instr(v_url, '//');
IF a_pos1 = 0 THEN
RETURN;
END IF;
a_pos2 := instr(v_url, '/', a_pos1 + 2);
IF a_pos2 = 0 THEN
v_host := substr(v_url, a_pos1 + 2);
v_path := '/';
RETURN;
END IF;
v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2);
a_pos1 := instr(v_url, '?', a_pos2 + 1);
IF a_pos1 = 0 THEN
v_path := substr(v_url, a_pos2);
RETURN;
END IF;
v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
v_query := substr(v_url, a_pos1 + 1);
END;
$$ LANGUAGE pluxsql;
可以像这样使用此函数：
SELECT * FROM cs_parse_url('http://foobar.com/query.cgi?baz');
示例 从PL/SQL移植一个过程到PL/uxSQL展示了如何移植一个使用许多Oracle特定功能的过程。以下是将此过程移植到PL/uxSQL的方法：
示例 从PL/SQL移植一个过程到PL/uxSQL
Oracle版本如下。
CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id IN INTEGER) IS
a_running_job_count INTEGER;
BEGIN
LOCK TABLE cs_jobs IN EXCLUSIVE MODE;
SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;
IF a_running_job_count > 0 THEN
COMMIT; -- free lock
raise_application_error(-20000,
'Unable to create a new job: a job is currently running.');
END IF;
DELETE FROM cs_active_job;
INSERT INTO cs_active_job(job_id) VALUES (v_job_id);
BEGIN
INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, now());
EXCEPTION
WHEN dup_val_on_index THEN NULL; -- don't worry if it already exists
END;
COMMIT;
END;
/
show errors
如何将这个过程移植到PL/uxSQL，如下所示。
CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id integer) AS $$
DECLARE
a_running_job_count integer;
BEGIN
LOCK TABLE cs_jobs IN EXCLUSIVE MODE;
SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;
IF a_running_job_count > 0 THEN
COMMIT; -- free lock
RAISE EXCEPTION 'Unable to create a new job: a job is currently running'; -- (1)
END IF;
DELETE FROM cs_active_job;
INSERT INTO cs_active_job(job_id) VALUES (v_job_id);
BEGIN
INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, now());
EXCEPTION
WHEN unique_violation THEN -- (2)
-- don't worry if it already exists
END;
COMMIT;
END;
$$ LANGUAGE pluxsql;
-
RAISE的语法与 Oracle 的语句相当不同，尽管基本的形式RAISE exception_name工作起来是相似的。
-
PL/uxSQL所支持的异常名称不同于 Oracle。内建的异常名称集合要更大。目前没有办法声明用户定义的异常名称，尽管能够抛出用户选择的 SQLSTATE 值。
2.13.2.其他要关注的事项
这一节解释了在移植 Oracle PL/SQL函数到UXsinoDB中时要关注的一些其他问题。
2.13.2.1.异常后隐式回滚
在PL/uxSQL，当一个异常被EXCEPTION子句捕获之后，从该块的BEGIN以来的所有数据库改变都会被自动回滚。也就是，该行为等效于在 Oracle 中用下面的代码得到的效果。
BEGIN
SAVEPOINT s1;
... code here ...
EXCEPTION
WHEN ... THEN
ROLLBACK TO s1;
... code here ...
WHEN ... THEN
ROLLBACK TO s1;
... code here ...
END;
如果正在翻译一个使用这种风格的SAVEPOINT以及ROLLBACK TO的 Oracle 过程，只要忽略掉SAVEPOINT以及ROLLBACK TO。如果 Oracle 过程是以不同的方法使用SAVEPOINT以及ROLLBACK TO。
2.13.2.2.EXECUTE
EXECUTE 的PL/uxSQL版本与 PL/SQL 版本类似，但您必须记住使用执行动态命令中描述的 quote_literal 和 quote_ident。如果不使用这些函数，则类似于 EXECUTE 'SELECT *FROM $1'; 的结构将无法可靠地工作。
2.13.2.3.优化 PL/uxSQL 函数
UXsinoDB 为您提供了两个函数创建修饰符以优化执行： “volatility”（当给定相同参数时函数是否始终返回相同结果）和“strictness”（如果任何参数为空，则函数是否返回 null）。有关详细信息，请参阅 CREATE FUNCTION参考页面。
在使用这些优化属性时，您的 CREATE FUNCTION 语句可能如下所示：
CREATE FUNCTION foo(...) RETURNS integer AS $$
...
$$ LANGUAGE pluxsql STRICT IMMUTABLE;
2.13.3.附录
本节包含一组模仿 Oracle 对应函数的 instr 函数代码，可用于简化移植工作。
--
-- instr 函数，模仿 Oracle 的对应函数
-- 语法：instr(string1, string2 [, n [, m]])
-- 其中 [] 表示可选参数。
--
-- 在第 n 个字符处开始搜索 string1 中的第 m 个出现位置的 string2。
-- 如果 n 为负数，则从 string1 的倒数第 abs(n) 个字符开始向后搜索。
-- 如果未传递 n，则假定为 1（搜索从第一个字符开始）。
-- 如果未传递 m，则假定为 1（查找第一个出现位置）。
-- 返回 string2 在 string1 中的起始索引，如果未找到 string2，则返回 0。
--
CREATE FUNCTION instr(varchar, varchar) RETURNS integer AS $$
BEGIN
RETURN instr($1, $2, 1);
END;
$$ LANGUAGE pluxsql STRICT IMMUTABLE;
CREATE FUNCTION instr(string varchar, string_to_search_for varchar,
beg_index integer)
RETURNS integer AS $$
DECLARE
pos integer NOT NULL DEFAULT 0;
temp_str varchar;
beg integer;
length integer;
ss_length integer;
BEGIN
IF beg_index > 0 THEN
temp_str := substring(string FROM beg_index);
pos := position(string_to_search_for IN temp_str);
IF pos = 0 THEN
RETURN 0;
ELSE
RETURN pos + beg_index - 1;
END IF;
ELSIF beg_index < 0 THEN
ss_length := char_length(string_to_search_for);
length := char_length(string);
beg := length + 1 + beg_index;
WHILE beg > 0 LOOP
temp_str := substring(string FROM beg FOR ss_length);
IF string_to_search_for = temp_str THEN
RETURN beg;
END IF;
beg := beg - 1;
END LOOP;
RETURN 0;
ELSE
RETURN 0;
END IF;
END;
$$ LANGUAGE pluxsql STRICT IMMUTABLE;
CREATE FUNCTION instr(string varchar, string_to_search_for varchar,
beg_index integer, occur_index integer)
RETURNS integer AS $$
DECLARE
pos integer NOT NULL DEFAULT 0;
occur_number integer NOT NULL DEFAULT 0;
temp_str varchar;
beg integer;
i integer;
length integer;
ss_length integer;
BEGIN
IF occur_index <= 0 THEN
RAISE 'argument ''%'' is out of range', occur_index
USING ERRCODE = '22003';
END IF;
IF beg_index > 0 THEN
beg := beg_index - 1;
FOR i IN 1..occur_index LOOP
temp_str := substring(string FROM beg + 1);
pos := position(string_to_search_for IN temp_str);
IF pos = 0 THEN
RETURN 0;
END IF;
beg := beg + pos;
END LOOP;
RETURN beg;
ELSIF beg_index < 0 THEN
ss_length := char_length(string_to_search_for);
length := char_length(string);
beg := length + 1 + beg_index;
WHILE beg > 0 LOOP
temp_str := substring(string FROM beg FOR ss_length);
IF string_to_search_for = temp_str THEN
occur_number := occur_number + 1;
IF occur_number = occur_index THEN
RETURN beg;
END IF;
END IF;
beg := beg - 1;
END LOOP;
RETURN 0;
ELSE
RETURN 0;
END IF;
END;
$$ LANGUAGE pluxsql STRICT IMMUTABLE;
3.PL/Tcl — Tcl过程语言
PL/Tcl是一个可加载的过程语言，用于 UXsinoDB数据库系统，它使得Tcl语言可以用于编写 UXsinoDB函数和过程。
3.1.概述
imPL/Tcl提供了大部分C语言函数编写者所具备的能力，但有一些限制，并且还增加了可用于Tcl的强大字符串处理库。
一个引人注目的限制是，所有内容都是在Tcl解释器的安全上下文中执行的。除了安全Tcl的有限命令集外，只有少数命令可用于通过SPI访问数据库并通过elog()引发消息。PL/Tcl没有提供访问数据库服务器内部或以UXsinoDB服务器进程权限获得操作系统级别访问的方法，就像C函数可以做的那样。因此，非特权数据库用户可以信任使用此语言；它不会给予他们无限的权限。
另一个值得注意的实现限制是，Tcl函数不能用于创建新数据类型的输入/输出函数。
有时希望编写不受安全Tcl限制的Tcl函数。例如，可能需要一个发送电子邮件的Tcl函数。为了处理这些情况，有一种名为PL/TclU（不受信任的Tcl）的PL/Tcl变体。这完全是相同的语言，只是使用了完整的Tcl解释器。如果使用PL/TclU，必须将其安装为不受信任的过程语言，以便只有数据库超级用户可以在其中创建函数。PL/TclU函数的编写者必须注意，函数不能用于执行任何不需要的操作，因为它将能够执行任何数据库管理员登录用户可以执行的操作。
如果在安装过程的配置步骤中指定了Tcl支持，则PL/Tcl和PL/TclU调用处理程序的共享对象代码将自动构建并安装在UXsinoDB库目录中。要在特定数据库中安装PL/Tcl和/或PL/TclU，请使用CREATE EXTENSION命令，例如：CREATE EXTENSION pltcl 或 CREATE EXTENSION pltclu。
3.2.PL/Tcl 函数和参数
要在 PL/Tcl 语言中创建函数，请使用标准的 CREATE FUNCTION 语法：
CREATE FUNCTION funcname (argument-types) RETURNS return-type AS $$
# PL/Tcl function body
$$ LANGUAGE pltcl;
PL/TclU 也是一样的，只是需要将语言指定为 pltclu。
函数体只是一段 Tcl 脚本。当调用函数时，参数值将作为名为 1 ... n 的变量传递给 Tcl 脚本。结果将以通常的方式从 Tcl代码中返回，使用 return 语句。在过程中，将忽略 Tcl 代码的返回值。
例如，可以定义一个返回两个整数值中较大值的函数：
CREATE FUNCTION tcl_max(integer, integer) RETURNS integer AS $$
if {$1 > $2} {return $1}
return $2
$$ LANGUAGE pltcl STRICT;
注意，这里有一个 STRICT 子句，它使我们不必考虑空输入值：如果传递了空值，函数将不会被调用，而是自动返回一个空结果。
在非严格函数中，如果参数的实际值为 null，则相应的 $n 变量将被设置为空字符串。要检测特定参数是否为 null，请使用函数argisnull。例如，假设我们希望 tcl_max 在一个 null 和一个非 null 参数的情况下返回非 null 参数，而不是null：
CREATE FUNCTION tcl_max(integer, integer) RETURNS integer AS $$
if {[argisnull 1]} {
if {[argisnull 2]} { return_null }
return $2
}
if {[argisnull 2]} { return $1 }
if {$1 > $2} {return $1}
return $2
$$ LANGUAGE pltcl;
如上所示，要从 PL/Tcl 函数返回 null 值，请执行return_null。
无论函数是否严格，都可以这样做。复合类型参数作为Tcl数组传递给函数。数组的元素名称是复合类型的属性名称。如果传递的行中的属性具有null值，则不会出现在数组中。以下是一个示例：
CREATE TABLE employee (
name text,
salary integer,
age integer
);
CREATE FUNCTION overpaid(employee) RETURNS boolean AS $$
if {200000.0 < $1(salary)} {
return "t"
}
if {$1(age) < 30 && 100000.0 < $1(salary)} {
return "t"
}
return "f"
$$ LANGUAGE pltcl;
PL/Tcl函数也可以返回复合类型结果。为此，Tcl代码必须返回与预期结果类型匹配的列名称/值对列表。从列表中省略的任何列名称都将返回为null，并且如果存在意外的列名称，则会引发错误。以下是一个示例：
CREATE FUNCTION square_cube(in int, out squared int, out cubed int) AS $$
return [list squared [expr {$1 * $1}] cubed [expr {$1 * $1 * $1}]]
$$ LANGUAGE pltcl;
过程的输出参数也以相同的方式返回，例如：
CREATE PROCEDURE tcl_triple(INOUT a integer, INOUT b integer) AS $$
return [list a [expr {$1 * 3}] b [expr {$2 * 3}]]
$$ LANGUAGE pltcl;
CALL tcl_triple(5, 10);
提示
结果列表可以使用array get Tcl命令从所需元组的数组表示中制作。例如：
CREATE FUNCTION raise_pay(employee, delta int) RETURNS employee AS $$
set 1(salary) [expr {$1(salary) + $2}]
return [array get 1]
$$ LANGUAGE pltcl;
PL/Tcl函数可以返回集合。为此，Tcl代码应该调用return_next一次返回一行，当返回标量类型时传递适当的值，或者当返回复合类型时传递列名称/值对的列表。以下是返回标量类型的示例：
CREATE FUNCTION sequence(int, int) RETURNS SETOF int AS $$
for {set i $1} {$i < $2} {incr i} {
return_next $i
}
$$ LANGUAGE pltcl;
这是返回复合类型的示例：
CREATE FUNCTION table_of_squares(int, int) RETURNS TABLE (x int, x2 int) AS $$
for {set i $1} {$i < $2} {incr i} {
return_next [list x $i x2 [expr {$i * $i}]]
}
$$ LANGUAGE pltcl;
3.3.PL/Tcl中的数据值
传递给 PL/Tcl 函数代码的参数值只是将输入参数转换为文本形式（就像它们已经被 SELECT 语句显示一样）。相反，return 和return_next 命令将接受任何符合函数声明结果类型或复合结果类型指定列的可接受输入格式的字符串。
3.4.PL/Tcl中的全局数据
有时候，在两次函数调用之间或不同函数之间共享一些全局数据是很有用的。在 PL/Tcl 中，这很容易实现，但必须理解一些限制。
出于安全原因，PL/Tcl 为任何一个 SQL 角色调用的函数在一个单独的 Tcl 解释器中执行。这可以防止一个用户意外或恶意地干扰另一个用户的PL/Tcl 函数的行为。每个这样的解释器都将有其自己的任何“全局”Tcl 变量的值。因此，仅当它们由相同的 SQL 角色执行时，两个PL/Tcl 函数才会共享相同的全局变量。在一个单个会话下通过 SECURITY DEFINER 函数、使用 SET ROLE等方式执行多个SQL 角色的代码的应用程序中，您可能需要采取明确的步骤来确保 PL/Tcl函数可以共享数据。为此，请确保应该通信的函数由同一用户拥有，并标记它们为SECURITY DEFINER。当然，您必须小心，这样的函数不能用于执行任何意外的操作。
在一个会话中使用的所有 PL/TclU 函数都在同一个 Tcl 解释器中执行，当然，这个解释器与用于 PL/Tcl函数的解释器不同。因此，全局数据会自动在 PL/TclU函数之间共享。这不被认为是安全风险，因为所有 PL/TclU函数都以数据库超级用户的信任级别执行。
为了帮助保护 PL/Tcl 函数不会意外地相互干扰，通过 upvar命令为每个函数提供了一个全局数组。该变量的全局名称是函数的内部名称，本地名称为GD。建议将 GD 用于函数的持久私有数据。仅将常规 Tcl 全局变量用于您明确希望在多个函数之间共享的值。（请注意，GD数组仅在特定解释器内是全局的，因此它们不会绕过上述安全限制。）
下面是使用 GD 的示例，出现在下面的 spi_execp 示例中。
3.5.从PL/Tcl访问数据库
在本节中，我们遵循Tcl的惯例，使用问号而不是方括号来表示语法概要中的可选元素。以下命令可用于从PL/Tcl函数体中访问数据库：
-
spi_exec?-countn? ? -arrayname?command?loop-body?执行作为字符串给出的SQL命令。命令中的错误会导致错误被引发。否则，
spi_exec的返回值是命令处理的行数（选择、插入、更新或删除），如果命令是一个实用程序语句，则返回零。此外，如果命令是一个SELECT语句，则所选列的值将按照下面的描述放置在Tcl变量中。可选的
-count值告诉spi_exec在命令中处理的最大行数。这个效果类似于将查询设置为游标，然后说FETCH n。如果命令是一个
SELECT语句，则结果列的值将被放置在以列命名的Tcl变量中。如果给出了-array选项，则列值将被存储到命名的关联数组的元素中，其中列名用作数组索引。此外，结果中的当前行号（从零开始计数）将存储在名为“.tupno”的数组元素中，除非该名称在结果中用作列名。如果命令是一个
SELECT语句，并且没有给出loop-body脚本，则只有结果的第一行被存储到Tcl变量或数组元素中；如果有剩余的行，则会被忽略。如果查询没有返回任何行，则不会发生存储。（可以通过检查spi_exec的结果来检测此情况。）例如：spi_exec "SELECT count(*) AS cnt FROM ux_proc"将把Tcl变量
$cnt设置为ux_proc系统目录中的行数。如果给出了可选的
loop-body参数，则它是一段Tcl脚本，每个查询结果中的每一行都会执行一次。（如果给定的命令不是SELECT，则忽略loop-body。）在每次迭代之前，当前行的列的值将存储到Tcl变量或数组元素中。例如：spi_exec -array C "SELECT * FROM ux_class" { elog DEBUG "have table $C(relname)" }将为
ux_class的每一行打印一条日志消息。此功能类似于其他Tcl循环结构；特别是continue和break在循环体内的工作方式与通常相同。如果查询结果的某个列为null，则其目标变量为“unset”而不是被设置。
-
spi_preparequerytypelist准备并保存查询计划以供以后执行。保存的计划将保留当前会话的生命周期。
查询可以使用参数，即在实际执行计划时提供值的占位符。在查询字符串中，通过符号
$1...$n引用参数。如果查询使用参数，则必须将参数类型的名称作为Tcl列表给出。（如果不使用参数，则写一个空列表typelist。）spi_prepare的返回值是一个查询ID，用于后续调用spi_execp。请参见spi_execp的示例。 -
spi_execp?-countn? ?-arrayname? ?-nullsstring?queryid?value-list? ?loop-body?执行先前使用
spi_prepare准备的查询。queryid是spi_prepare返回的ID。如果查询引用参数，则必须提供value-list。这是参数的实际值的Tcl列表。列表的长度必须与先前给出的参数类型列表相同。如果查询没有参数，则省略value-list。-nulls的可选值是一个由空格和'n'字符组成的字符串，告诉spi_execp哪些参数是null值。如果给出，则它必须与value-list完全相同。如果没有给出，则所有参数值都是非空的。除了查询及其参数的指定方式不同外，
spi_execp的工作方式与spi_exec相同。-count、-array和loop-body选项相同，结果值也相同。以下是一个使用准备计划的 PL/Tcl 函数示例：
CREATE FUNCTION t1_count(integer, integer) RETURNS integer AS $$ if {![ info exists GD(plan) ]} { # prepare the saved plan on the first call set GD(plan) [ spi_prepare \ "SELECT count(*) AS cnt FROM t1 WHERE num >= \$1 AND num <= \$2" \ [ list int4 int4 ] ] } spi_execp -count 1 $GD(plan) [ list $1 $2 ] return $cnt $$ LANGUAGE pltcl;我们需要在传递给
spi_prepare的查询字符串中使用反斜杠，以确保$n标记将原样传递给spi_prepare，而不是被Tcl 变量替换。 -
subtransactioncommand在 SQL 子事务中执行包含在
command中的 Tcl 脚本。如果脚本返回错误，则在将错误返回到周围的 Tcl代码之前，整个子事务将回滚。有关详细信息和示例，请参见PL/Tcl 中的显式子事务。 -
quotestring在给定字符串中加倍所有单引号和反斜杠字符的出现次数。这可用于安全地引用要插入到给定给
spi_exec或spi_prepare的SQL 命令中的字符串。例如，考虑一个类似于以下 SQL 命令字符串："SELECT '$val' AS ret"其中 Tcl 变量
val实际上包含doesn't。这将导致最终的命令字符串：SELECT 'doesn't' AS ret这将在
spi_exec或spi_prepare中导致解析错误。为了正常工作，提交的命令应包含：SELECT 'doesn''t' AS ret可以使用以下方式在 PL/Tcl 中形成：
"SELECT '[ quote $val ]' AS ret"spi_execp的一个优点是您不必像这样引用参数值，因为参数永远不会被解析为 SQL 命令字符串的一部分。 -
eloglevelmsg发出日志或错误消息。可能的级别是
DEBUG、LOG、INFO、NOTICE、WARNING、ERROR和FATAL。 ERROR 引发错误条件；如果周围的 Tcl代码未捕获此错误，则错误会传播到调用查询，导致当前事务或子事务中止。这实际上与Tclerror命令相同。FATAL中止事务并导致当前会话关闭。（在 PL/Tcl函数中使用此错误级别可能没有好的理由，但它提供了）其他级别只会生成不同优先级的消息。特定优先级的消息是否报告给客户端、写入服务器日志或两者都有，由log_min_messages和client_min_messages配置变量控制。
3.6.在PL/Tcl中的触发器函数
触发器函数可以用 PL/Tcl 编写。 UXDB 要求作为触发器调用的函数必须声明为没有参数的函数，并且返回类型为trigger。
触发器管理器的信息通过以下变量传递给函数体：
-
$TG_name
CREATE TRIGGER语句中触发器的名称。 -
$TG_relid
引起触发器函数被调用的表的对象 ID。 -
$TG_table_name
引起触发器函数被调用的表的名称。 -
$TG_table_schema
引起触发器函数被调用的表的模式。 -
$TG_relatts
表列名的 Tcl 列表，以空列表元素为前缀。因此，使用 Tcl 的lsearch命令在列表中查找列名返回从第一列开始的元素编号，与在UXsinoDB 中列的编号方式相同。 （空列表元素也出现在已删除的列的位置，因此属性编号对于它们右侧的列是正确的。） -
$TG_when
字符串BEFORE、AFTER或INSTEAD OF，取决于触发器事件的类型。 -
$TG_level
字符串ROW或STATEMENT，取决于触发器事件的类型。 -
$TG_op
根据触发器事件类型，字符串INSERT、UPDATE、DELETE或TRUNCATE。 -
$NEW
关联数组，包含INSERT或UPDATE操作的新表行的值，或对于DELETE为空。该数组由列名索引。空值的列不会出现在数组中。 对于语句级触发器，此项未设置。 -
$OLD
关联数组，包含UPDATE或DELETE操作的旧表行的值，或对于INSERT为空。该数组由列名索引。空值的列不会出现在数组中。 对于语句级触发器，此项未设置。 -
$args
Tcl列表，包含在CREATE TRIGGER语句中给出的函数参数。这些参数也可以在函数体中作为$1...$n访问。
触发器函数的返回值可以是字符串OK或SKIP，或列名/值对的列表。如果返回值是OK，触发器触发的操作（INSERT/UPDATE/DELETE）将正常进行。SKIP告诉触发器管理器在此行中静默抑制操作。如果返回列表，则告诉PL/Tcl向触发器管理器返回修改后的行；列表中的列名和值指定了修改后的行的内容。未在列表中提到的任何列都将设置为null。返回修改后的行仅对于行级BEFORE INSERT或UPDATE触发器有意义，对于这些触发器，修改后的行将被插入，而不是给定的$NEW；或对于行级INSTEAD OF INSERT或UPDATE触发器，返回的行将用作INSERT RETURNING或UPDATE RETURNING子句的源数据。 在行级BEFORE DELETE或INSTEAD OF DELETE触发器中，返回修改后的行与返回OK具有相同的效果，即操作继续进行。对于所有其他类型的触发器，触发器返回值将被忽略。
提示
结果列表可以使用array get Tcl命令从修改后的元组的数组表示中生成。
这是一个小例子触发器函数，它强制在表中的整数值跟踪对行执行的更新次数。对于插入的新行，该值初始化为0，然后在每个更新操作上递增。
CREATE FUNCTION trigfunc_modcount() RETURNS trigger AS $$
switch $TG_op {
INSERT {
set NEW($1) 0
}
UPDATE {
set NEW($1) $OLD($1)
incr NEW($1)
}
default {
return OK
}
}
return [array get NEW]
$$ LANGUAGE pltcl;
CREATE TABLE mytab (num integer, description text, modcnt integer);
CREATE TRIGGER trig_mytab_modcount BEFORE INSERT OR UPDATE ON mytab
FOR EACH ROW EXECUTE FUNCTION trigfunc_modcount('modcnt');
请注意，触发器函数本身不知道列名；这是从触发器参数中提供的。这使得触发器函数可以在不同的表中重复使用。
3.7.PL/Tcl中的事件触发器函数
事件触发器函数可以用 PL/Tcl 编写。 UXDB 要求作为事件触发器调用的函数必须声明为没有参数且返回类型为event_trigger 的函数。
触发器管理器中的信息通过以下变量传递到函数体中：
-
$TG_event
触发器触发的事件名称。 -
$TG_tag
触发器触发的命令标记。
触发器函数的返回值将被忽略。
下面是一个简单的事件触发器函数示例，每次执行支持的命令时都会触发一个 NOTICE 消息：
CREATE OR REPLACE FUNCTION tclsnitch() RETURNS event_trigger AS $$
elog NOTICE "tclsnitch: $TG_event $TG_tag"
$$ LANGUAGE pltcl;
CREATE EVENT TRIGGER tcl_a_snitch ON ddl_command_start EXECUTE FUNCTION tclsnitch();
3.8.PL/Tcl中的错误处理
在 PL/Tcl 函数内部或从中调用的 Tcl 代码可能会通过执行一些无效操作或使用 Tcl 的 error 命令或 PL/Tcl 的elog 命令生成错误。这些错误可以使用 Tcl 的 catch 命令在 Tcl 中捕获。如果错误未被捕获，但允许传播到 PL/Tcl函数执行的顶层，则会在调用查询中报告为 SQL 错误。
相反，发生在 PL/Tcl 的 spi_exec、spi_prepare 和 spi_execp 命令中的 SQL 错误将报告为Tcl 错误，因此可以使用 Tcl 的 catch 命令捕获。(这些 PL/Tcl 命令中的每个命令都在子事务中运行其 SQL操作，在错误时回滚，因此任何部分完成的操作都会自动清理。)同样，如果错误传播到顶层而未被捕获，则会转换回 SQL错误。
Tcl 提供了一个 errorCode 变量，可以表示关于错误的附加信息，以易于 Tcl 程序解释的形式。内容以 Tcl列表格式呈现，第一个单词标识报告错误的子系统或库；除此之外，其余内容留给各个子系统或库。对于由PL/Tcl 命令报告的数据库错误，第一个单词是 UXDB，第二个单词是 UXsinoDB版本号，其他单词是提供有关错误详细信息的字段名/值对。始终提供的字段包括SQLSTATE、condition 和 message。可能存在的字段包括detail、hint、context，schema、table、column、datatype、constraint、statement、cursor_position、filename、lineno和funcname。
一种方便的处理 PL/Tcl 的 errorCode 信息的方法是将其加载到数组中，使字段名称成为数组下标。处理代码可能如下所示：
if {[catch { spi_exec $sql_command }]} {
if {[lindex $::errorCode 0] == "UXDB"} {
array set errorArray $::errorCode
if {$errorArray(condition) == "undefined_table"} {
# deal with missing table
} else {
# deal with some other type of SQL error
}
}
}
（双冒号明确指定 errorCode 是全局变量。）
3.9.PL/Tcl中的显式子事务
如PL/Tcl 中的错误处理所述，由于数据库访问引起的错误可能导致一些操作成功执行，然后一个操作失败，从而在恢复错误后数据处于不一致状态。PL/Tcl提供了显式子事务的解决方案来解决这个问题。
考虑一个实现两个账户之间转账的函数：
CREATE FUNCTION transfer_funds() RETURNS void AS $$
if [catch {
spi_exec "UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe'"
spi_exec "UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary'"
} errormsg] {
set result [format "error transferring funds: %s" $errormsg]
} else {
set result "funds transferred successfully"
}
spi_exec "INSERT INTO operations (result) VALUES ('[quote $result]')"
$$ LANGUAGE pltcl;
如果第二个UPDATE语句导致异常被抛出，这个函数将记录失败，但是第一个UPDATE的结果仍然会被提交。换句话说，资金将从 Joe的账户中提取，但不会转移到 Mary 的账户中。这是因为每个spi_exec都是一个单独的子事务，只有其中一个子事务被回滚。
为了处理这种情况，您可以将多个数据库操作包装在一个显式子事务中，它将作为一个整体成功或回滚。PL/Tcl提供了一个subtransaction命令来管理这个过程。我们可以将函数重写为：
CREATE FUNCTION transfer_funds2() RETURNS void AS $$
if [catch {
subtransaction {
spi_exec "UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe'"
spi_exec "UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary'"
}
} errormsg] {
set result [format "error transferring funds: %s" $errormsg]
} else {
set result "funds transferred successfully"
}
spi_exec "INSERT INTO operations (result) VALUES ('[quote $result]')"
$$ LANGUAGE pltcl;
请注意，仍然需要使用catch来达到此目的。否则，错误将传播到函数的顶层，从而阻止将数据插入operations表的操作。subtransaction命令不会捕获错误，它只是确保在其范围内执行的所有数据库操作在报告错误时一起回滚。
显式子事务的回滚发生在包含的Tcl代码报告任何错误时，不仅限于源自数据库访问的错误。因此，在subtransaction命令中引发的常规Tcl异常也会导致子事务回滚。但是，从包含的Tcl代码中退出（例如，由于return）不会导致回滚。
3.10.事务管理
在从顶层调用的过程或从顶层调用的匿名代码块（DO 命令）中，可以控制事务。要提交当前事务，请调用 commit命令。要回滚当前事务，请调用 rollback 命令。（请注意，不可能通过
spi_exec 或类似函数运行 SQL 命令 COMMIT 或ROLLBACK。必须使用这些函数来完成。）事务结束后，会自动启动一个新事务，因此没有单独的命令。
下面是一个示例：
CREATE PROCEDURE transaction_test1()
LANGUAGE pltcl
AS $$
for {set i 0} {$i < 10} {incr i} {
spi_exec "INSERT INTO test1 (a) VALUES ($i)"
if {$i % 2 == 0} {
commit
} else {
rollback
}
}
$$;
CALL transaction_test1();
当存在显式子事务时，无法结束事务。
3.11.PL/Tcl配置
本节列出影响 PL/Tcl 的配置参数。
-
pltcl.start_proc(string)如果将此参数设置为非空字符串，则指定一个无参数的 PL/Tcl 函数（可能带有模式限定名称），该函数将在为PL/Tcl创建新的Tcl解释器时执行。这样的函数可以执行每个会话的初始化，例如加载其他 Tcl 代码。当在数据库会话中首次执行 PL/Tcl函数或因新的 SQL 角色调用 PL/Tcl 函数而必须创建附加解释器时，将创建新的 Tcl 解释器。
引用的函数必须用
pltcl语言编写，并且不能标记为SECURITY DEFINER。（这些限制确保它在它应该初始化的解释器中运行。）当前用户也必须有调用它的权限。如果函数因错误而失败，则会中止导致创建新解释器的函数调用并传播到调用查询，导致当前事务或子事务中止。在 Tcl中已经执行的任何操作都不会被撤消；但是，该解释器将不再使用。如果再次使用该语言，则将在新的Tcl 解释器中再次尝试初始化。
只有超级用户可以更改此设置。虽然可以在会话中更改此设置，但这些更改不会影响已经创建的 Tcl 解释器。
-
pltclu.start_proc(string)此参数与
pltcl.start_proc完全相同，只是适用于 PL/TclU。引用的函数必须用pltclu语言编写。
3.12.Tcl过程名称
在UXsinoDB中，只要参数数量或类型不同，就可以使用相同的函数名称来表示不同的函数定义。然而，Tcl要求所有过程名称都必须不同。PL/Tcl通过将系统表ux_proc中的函数对象ID作为其名称的一部分，使内部Tcl过程名称包含此信息。因此，具有相同名称但参数类型不同的UXsinoDB函数也将是不同的Tcl过程。对于PL/Tcl程序员来说，这通常不是一个问题，但在调试时可能会出现。
4.PL/Perl — Perl过程语言
PL/Perl是一种可加载的过程语言，它使您能够使用Perl编程语言编写UXsinoDB函数和过程。
使用PL/Perl的主要优点是，它允许在存储的函数和过程中使用Perl提供的众多“字符串操作”运算符和函数。使用Perl解析复杂字符串可能比使用PL/uxSQL提供的字符串函数和控制结构更容易。
要在特定数据库中安装PL/Perl，请使用CREATE EXTENSION plperl。
提示
如果将语言安装到template1中，则所有随后创建的数据库都将自动包含该语言。
注意
源代码包的用户必须在安装过程中特别启用 PL/Perl的构建。二进制包的用户可能会在单独的子包中找到PL/Perl。
4.1.PL/Perl 函数和参数
要在 PL/Perl 语言中创建函数，请使用标准的CREATE FUNCTION语法：
CREATE FUNCTION funcname (argument-types)
RETURNS return-type
-- 可以在此处添加函数属性
AS $$
# PL/Perl function body goes here
$$ LANGUAGE plperl;
函数体是普通的 Perl 代码。实际上，PL/Perl 粘合代码将其包装在 Perl 子例程中。PL/Perl函数以标量上下文调用，因此无法返回列表。您可以通过返回引用来返回非标量值（数组、记录和集合），如下所述。
在 PL/Perl 过程中，来自 Perl 代码的任何返回值都将被忽略。
PL/Perl 还支持使用DO语句调用的匿名代码块：
DO $$
# PL/Perl code
$$ LANGUAGE plperl;
匿名代码块不接收任何参数，并且它可能返回的任何值都将被丢弃。否则，它的行为就像一个函数。
注意
在 Perl 中使用命名的嵌套子例程是危险的，特别是如果它们引用了封闭范围中的词法变量。因为 PL/Perl函数被包装在一个子例程中，所以您放置在其中的任何命名子例程都将被嵌套。一般来说，创建通过coderef 调用的匿名子例程更安全。有关更多信息，请参见 perldiag 手册中的 Variable "%s" will not stay shared 和 Variable "%s" is not available 条目，或在互联网上搜索 “perl nested named subroutine”。
CREATE FUNCTION 命令的语法要求函数体编写为字符串常量。通常情况下，可以使用最方便的方法是使用美元引用来表示字符串常量。如果选择使用转义字符串语法E''，则必须在函数体中使用的任何单引号（'）和反斜杠（\）都要加倍。
参数和结果的处理方式与任何其他Perl子例程相同：参数传递在@_中进行，结果值通过return或在函数中最后一个表达式中返回。
例如，可以定义一个返回两个整数值中较大值的函数：
CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $$
if ($_[0] > $_[1]) { return $_[0]; }
return $_[1];
$$ LANGUAGE plperl;
注意
参数将从数据库的编码转换为UTF-8以在PL/Perl中使用，然后在返回时将从UTF-8转换回数据库编码。
如果将SQL空值传递给函数，则参数值将显示为Perl中的“undefined”。上述函数定义将不会对空输入进行很好的处理（实际上，它将像零一样运行）。我们可以向函数定义中添加STRICT，以使UXsinoDB执行更合理的操作：如果传递了空值，则不会调用函数，而只会自动返回空结果。或者，我们可以在函数体中检查未定义的输入。例如，假设我们希望perl_max具有一个空值和一个非空值的参数，返回非空值而不是空值：
CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $$
my ($x, $y) = @_;
if (not defined $x) {
return undef if not defined $y;
return $y;
}
return $x if not defined $y;
return $x if $x > $y;
return $y;
$$ LANGUAGE plperl;
如上所示，要从PL/Perl函数返回SQL空值，请返回未定义的值。无论函数是否严格，都可以这样做。
函数参数中的任何非引用内容都是字符串，它是相关数据类型的标准UXsinoDB外部文本表示形式。对于普通数字或文本类型，Perl将自动处理，程序员通常不必担心。但是，在其他情况下，参数需要转换为更可用于Perl的形式。例如，decode_bytea函数可用于将bytea类型的参数转换为未转义的二进制数据。
类似地，传回给UXsinoDB的值必须采用外部文本表示格式。例如，encode_bytea函数可用于为bytea类型的返回值转义二进制数据。
一个特别重要的情况是布尔值。正如刚才所述，bool值的默认行为是将它们作为文本传递给Perl，因此为't'或'f'。这是有问题的，因为Perl不会将'f'视为false！可以通过使用“转换”来改善情况。适当的转换由bool_plperl扩展提供。要使用它，请安装扩展：
CREATE EXTENSION bool_plperl; -- or bool_plperlu for PL/PerlU
然后为接受或返回bool的PL/Perl函数使用TRANSFORM函数属性，例如：
CREATE FUNCTION perl_and(bool, bool) RETURNS bool
TRANSFORM FOR TYPE bool
AS $$
my ($a, $b) = @_;
return $a && $b;
$$ LANGUAGE plperl;
应用此转换时，Perl将把bool参数视为1或空，因此正确地为真或假。如果函数结果是bool类型，则根据Perl是否将返回值评估为真，它将为真或假。类似的转换也适用于函数内执行的布尔查询参数和SPI查询结果（从 PL/Perl 访问数据库）。
Perl可以将UXsinoDB数组作为Perl数组的引用返回。以下是一个示例：
CREATE OR REPLACE function returns_array()
RETURNS text[][] AS $$
return [['a"b','c,d'],['e\\f','g']];
$$ LANGUAGE plperl;
select returns_array();
Perl将UXsinoDB数组作为一个受保护的UXsinoDB::InServer::ARRAY对象传递。该对象可以被视为数组引用或字符串，从而允许向后兼容早于之前版本的UXsinoDB版本的Perl代码运行。例如：
CREATE OR REPLACE FUNCTION concat_array_elements(text[]) RETURNS TEXT AS $$
my $arg = shift;
my $result = "";
return undef if (!defined $arg);
# as an array reference
for (@$arg) {
$result .= $_;
}
# also works as a string
$result .= $arg;
return $result;
$$ LANGUAGE plperl;
SELECT concat_array_elements(ARRAY['PL','/','Perl']);
注意
多维数组以每个Perl程序员都熟悉的方式表示为低维数组的引用。
复合类型参数作为对哈希的引用传递给函数。哈希的键是复合类型的属性名称。以下是一个示例：
CREATE TABLE employee (
name text,
basesalary integer,
bonus integer
);
CREATE FUNCTION empcomp(employee) RETURNS integer AS $$
my ($emp) = @_;
return $emp->{basesalary} + $emp->{bonus};
$$ LANGUAGE plperl;
SELECT name, empcomp(employee.*) FROM employee;
PL/Perl函数可以使用相同的方法返回复合类型结果：返回具有所需属性的哈希的引用。例如：
CREATE TYPE testrowperl AS (f1 integer, f2 text, f3 text);
CREATE OR REPLACE FUNCTION perl_row() RETURNS testrowperl AS $$
return {f2 => 'hello', f1 => 1, f3 => 'world'};
$$ LANGUAGE plperl;
SELECT * FROM perl_row();
在声明的结果数据类型中未出现的列将作为null值返回。
类似地，过程的输出参数可以作为哈希引用返回：
CREATE PROCEDURE perl_triple(INOUT a integer, INOUT b integer) AS $$
my ($a, $b) = @_;
return {a => $a * 3, b => $b * 3};
$$ LANGUAGE plperl;
CALL perl_triple(5, 10);
PL/Perl函数还可以返回标量或复合类型的集合。通常，您会希望逐行返回行，以加快启动时间并避免排队。将整个结果集存储在内存中。您可以使用下面所示的return_next来实现这一点。请注意，在最后一个return_next之后，必须放置return或（更好的是）return undef。
CREATE OR REPLACE FUNCTION perl_set_int(int)
RETURNS SETOF INTEGER AS $$
foreach (0..$_[0]) {
return_next($_);
}
return undef;
$$ LANGUAGE plperl;
SELECT * FROM perl_set_int(5);
CREATE OR REPLACE FUNCTION perl_set()
RETURNS SETOF testrowperl AS $$
return_next({ f1 => 1, f2 => 'Hello', f3 => 'World' });
return_next({ f1 => 2, f2 => 'Hello', f3 => 'UXsinoDB' });
return_next({ f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' });
return undef;
$$ LANGUAGE plperl;
对于小型结果集，您可以返回一个引用，该引用包含标量、数组引用或哈希引用，用于简单类型、数组类型和复合类型。以下是将整个结果集作为数组引用返回的一些简单示例：
CREATE OR REPLACE FUNCTION perl_set_int(int) RETURNS SETOF INTEGER AS $$
return [0..$_[0]];
$$ LANGUAGE plperl;
SELECT * FROM perl_set_int(5);
CREATE OR REPLACE FUNCTION perl_set() RETURNS SETOF testrowperl AS $$
return [
{ f1 => 1, f2 => 'Hello', f3 => 'World' },
{ f1 => 2, f2 => 'Hello', f3 => 'UXsinoDB' },
{ f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' }
];
$$ LANGUAGE plperl;
SELECT * FROM perl_set();
如果您希望在代码中使用strict pragma，您有几个选项。对于临时全局使用，您可以将SET``plperl.use_strict设置为true。这将影响后续编译的PL/Perl函数，但不会影响当前会话中已编译的函数。对于永久全局使用，您可以在uxsinodb.conf文件中将plperl.use_strict设置为true。
对于特定函数的永久使用，您只需在函数体的顶部放置：
use strict;
feature pragma也可用于use，如果您的Perl版本为5.10.0或更高版本。
4.2.PL/Perl中的数据值
PL/Perl 函数代码中提供的参数值只是将输入参数转换为文本形式（就像通过 SELECT 语句显示一样）。相反，return 和return_next 命令将接受任何符合函数声明的返回类型的可接受输入格式的字符串。
如果这种行为对于特定情况不方便，可以使用转换来改进，就像已经为 bool 值所示的那样。 UXsinoDB分发中包含了几个转换模块的示例。
4.3.内置函数
4.3.1.从 PL/Perl 访问数据库
通过以下函数可以从 Perl 函数访问数据库本身：
spi_exec_query(query[,max-rows])
spi_exec_query 执行 SQL 命令并将整个行集作为哈希引用数组的引用返回。只有在您知道结果集相对较小时才应使用此命令。以下是带有可选最大行数的查询（SELECT 命令）的示例：
$rv = spi_exec_query('SELECT * FROM my_table', 5);
这将从表 my_table 返回最多 5 行。 如果 my_table 有一个名为 my_column的列，则可以像这样从结果的第 $i 行获取该值：
$foo = $rv->{rows}[$i]->{my_column};
可以像这样访问从 SELECT 查询返回的总行数：
$nrows = $rv->{processed}
以下是使用不同命令类型的示例：
$query = "INSERT INTO my_table VALUES (1, 'test')";
$rv = spi_exec_query($query);
然后可以像这样访问命令状态（例如 SPI_OK_INSERT）：
$res = $rv->{status};
要获取受影响的行数，请执行以下操作：
$nrows = $rv->{processed};
以下是完整示例：
CREATE TABLE test (
i int,
v varchar
);
INSERT INTO test (i, v) VALUES (1, 'first line');
INSERT INTO test (i, v) VALUES (2, 'second line');
INSERT INTO test (i, v) VALUES (3, 'third line');
INSERT INTO test (i, v) VALUES (4, 'immortal');
CREATE OR REPLACE FUNCTION test_munge() RETURNS SETOF test AS $$
my $rv = spi_exec_query('select i, v from test;');
my $status = $rv->{status};
my $nrows = $rv->{processed};
foreach my $rn (0 .. $nrows - 1) {
my $row = $rv->{rows}[$rn];
$row->{i} += 200 if defined($row->{i});
$row->{v} =~ tr/A-Za-z/a-zA-Z/ if (defined($row->{v}));
return_next($row);
}
return undef;
$$ LANGUAGE plperl;
SELECT * FROM test_munge();
-
spi_query(command)
spi_fetchrow(cursor)
spi_cursor_close(cursor)spi_query（command）和spi_fetchrow（cursor）一起工作，用于可能很大的行集，或者在您希望返回行时使用。spi_fetchrow仅与spi_query一起使用。以下示例说明了如何将它们一起使用：
CREATE TYPE foo_type AS (the_num INTEGER, the_text TEXT); CREATE OR REPLACE FUNCTION lotsa_md5 (INTEGER) RETURNS SETOF foo_type AS $$ use Digest::MD5 qw(md5_hex); my $file = '/usr/share/dict/words'; my $t = localtime; elog(NOTICE, "opening file $file at $t" ); open my $fh, '<', $file # ooh, it's a file access! or elog(ERROR, "cannot open $file for reading: $!"); my @words = <$fh>; close $fh; $t = localtime; elog(NOTICE, "closed file $file at $t"); chomp(@words); my $row; my $sth = spi_query("SELECT * FROM generate_series(1,$_[0]) AS b(a)"); while (defined ($row = spi_fetchrow($sth))) { return_next({ the_num => $row->{a}, the_text => md5_hex($words[rand @words]) }); } return; $$ LANGUAGE plperlu; SELECT * from lotsa_md5(500);通常，应重复使用spi_fetchrow，直到它返回undef，表示没有更多的行可读取。由spi_query返回的游标在spi_fetchrow返回undef时自动释放。如果您不想读取所有行，请调用spi_cursor_close而不是释放游标。否则会导致内存泄漏。
-
spi_prepare（command，argument types）
spi_query_prepared（plan，arguments）
spi_exec_prepared（plan[，attributes]，arguments）
spi_freeplan（plan）spi_prepare、spi_query_prepared、spi_exec_prepared和spi_freeplan实现了与预处理查询相同的功能。spi_prepare接受一个带有编号参数占位符（$1、$2等）的查询字符串和一个字符串类型的参数列表：
$plan = spi_prepare('SELECT * FROM test WHERE id > $1 AND name = $2', 'INTEGER', 'TEXT');一旦通过调用
spi_prepare准备好查询计划，该计划可以代替字符串查询使用，无论是在spi_exec_prepared中，其结果与spi_exec_query返回的结果相同，还是在spi_query_prepared中，它返回一个游标，就像spi_query一样，稍后可以将其传递给spi_fetchrow。spi_exec_prepared的可选第二个参数是属性的哈希引用；目前仅支持的属性是limit，它设置查询返回的最大行数。预处理查询的优点是可以使用一个准备好的计划来执行多个查询。在计划不再需要时，可以使用
spi_freeplan释放计划：CREATE OR REPLACE FUNCTION init() RETURNS VOID AS $$ $_SHARED{my_plan} = spi_prepare('SELECT (now() + $1)::date AS now', 'INTERVAL'); $$ LANGUAGE plperl; CREATE OR REPLACE FUNCTION add_time( INTERVAL ) RETURNS TEXT AS $$ return spi_exec_prepared( $_SHARED{my_plan}, $_[0] )->{rows}->[0]->{now}; $$ LANGUAGE plperl; CREATE OR REPLACE FUNCTION done() RETURNS VOID AS $$ spi_freeplan( $_SHARED{my_plan}); undef $_SHARED{my_plan}; $$ LANGUAGE plperl; SELECT init(); SELECT add_time('1 day'), add_time('2 days'), add_time('3 days'); SELECT done(); add_time | add_time | add_time -------+------------+------------ 2005-12-10 | 2005-12-11 | 2005-12-12请注意，
spi_prepare中的参数下标是通过$1、$2、$3等定义的，因此避免在双引号中声明查询字符串，这可能会导致难以捕捉的错误。另一个示例说明了在
spi_exec_prepared中使用可选参数的用法：CREATE TABLE hosts AS SELECT id, ('192.168.1.'||id)::inet AS address FROM generate_series(1,3) AS id; CREATE OR REPLACE FUNCTION init_hosts_query() RETURNS VOID AS $$ $_SHARED{plan} = spi_prepare('SELECT * FROM hosts WHERE address << $1', 'inet'); $$ LANGUAGE plperl; CREATE OR REPLACE FUNCTION query_hosts(inet) RETURNS SETOF hosts AS $$ return spi_exec_prepared( $_SHARED{plan}, {limit => 2}, $_[0] )->{rows}; $$ LANGUAGE plperl; CREATE OR REPLACE FUNCTION release_hosts_query() RETURNS VOID AS $$ spi_freeplan($_SHARED{plan}); undef $_SHARED{plan}; $$ LANGUAGE plperl; SELECT init_hosts_query(); SELECT query_hosts('192.168.1.0/30'); SELECT release_hosts_query(); query_hosts ----------------- (1,192.168.1.1) (2,192.168.1.2) (2 rows) -
spi_commit()
spi_rollback()
提交或回滚当前事务。这只能在从顶层调用的过程或匿名代码块（DO命令）中调用。（请注意，无法运行SQL命令COMMIT或ROLLBACK）通过spi_exec_query 或类似函数执行事务。在事务结束后，会自动启动一个新的事务，因此没有单独的函数用于启动事务。
以下是一个示例：
CREATE PROCEDURE transaction_test1()
LANGUAGE plperl
AS $$
foreach my $i (0..9) {
spi_exec_query("INSERT INTO test1 (a) VALUES ($i)");
if ($i % 2 == 0) {
spi_commit();
} else {
spi_rollback();
}
}
$$;
CALL transaction_test1();
4.3.2.PL/Perl 中的工具函数
-
elog(level,msg)发出一个日志或者错误消息。可用的级别有DEBUG、LOG、INFO、NOTICE、WARNING以及ERROR。ERROR产生一种错误情况，如果它没有被周围的 Perl 代码捕获，错误会传播到调用查询中，导致当前事务或者子事务被中止。这实际上和 Perl 的die 命令相同。其他级别只产生不同优先级的消息。特定优先级的消息是被报告给客户端、写到服务器日志或者两者都做由配置变量log_min_messages和 client_min_messages控制。
-
quote_literal(string)返回给定字符串的被适当引用后的形式，这种形式能被用作 SQL 语句字符串中的字符串。嵌入的引号和反斜线会被正确地双写。注意对 undef 输入quote_literal会返回 undef。如果参数可能是 undef，quote_nullable通常更合适。
-
quote_nullable(string)返回给定字符串的被适当引用后的形式，这种形式能被用作 SQL 语句字符串中的字符串。或者在参数为 undef 时，返回未引用的串 "NULL"。嵌入的引号和反斜线会被正确地双写。
-
quote_ident(string)返回给定字符串的被适当引用后的形式，这种形式能被用作 SQL 语句字符串中的标识符。只有在必要时才增加引号（即，如果串包含非标识符字符或者是大小写折叠的）。嵌入的引号会被正确地双写。
-
decode_bytea(string)返回由给定串的内容（应该用bytea编码）表示的未转义二进制数据。
-
encode_bytea(string)返回给定串的二进制数据内容的bytea编码形式。
-
encode_array_literal(array)
encode_array_literal(array,delimiter)以数组文字格式返回引用数组的内容。如果参数值不是对数组的引用，则返回未更改的参数值。 如果未指定分隔符或为undef，则在数组文字中元素之间使用的分隔符默认为“
,”。 -
encode_typed_literal(value,typename)将Perl变量转换为作为第二个参数传递的数据类型的值，并返回此值的字符串表示形式。 正确处理嵌套数组和复合类型的值。
-
encode_array_constructor(array)以数组构造器格式返回引用数组的内容。使用
quote_nullable引用单个值。 如果参数值不是对数组的引用，则返回使用quote_nullable引用的参数值。 -
looks_like_number(string)如果给定字符串的内容看起来像数字，则根据Perl返回true值，否则返回false。如果参数为undef，则返回undef。忽略前导和尾随空格。将
Inf和Infinity视为数字。 -
is_array_ref(argument)如果给定的参数可以被视为数组引用，即如果参数的ref为
ARRAY或UXsinoDB::InServer::ARRAY，则返回true值。否则返回false。
4.4.PL/Perl 中的全局值
您可以使用全局哈希表 %_SHARED在当前会话的生命周期内存储数据，包括代码引用。
下面是一个简单的共享数据示例：
CREATE OR REPLACE FUNCTION set_var(name text, val text) RETURNS text AS $$
if ($_SHARED{$_[0]} = $_[1]) {
return 'ok';
} else {
return "cannot set shared variable $_[0] to $_[1]";
}
$$ LANGUAGE plperl;
CREATE OR REPLACE FUNCTION get_var(name text) RETURNS text AS $$
return $_SHARED{$_[0]};
$$ LANGUAGE plperl;
SELECT set_var('sample', 'Hello, PL/Perl! How''s tricks?');
SELECT get_var('sample');
下面是一个稍微复杂一些的示例，使用了代码引用：
CREATE OR REPLACE FUNCTION myfuncs() RETURNS void AS $$
$_SHARED{myquote} = sub {
my $arg = shift;
$arg =~ s/(['\\])/\\$1/g;
return "'$arg'";
};
$$ LANGUAGE plperl;
SELECT myfuncs(); /* initializes the function */
/* Set up a function that uses the quote function */
CREATE OR REPLACE FUNCTION use_quote(TEXT) RETURNS text AS $$
my $text_to_quote = shift;
my $qfunc = $_SHARED{myquote};
return &$qfunc($text_to_quote);
$$ LANGUAGE plperl;
（您可以用一行代码替换上面的代码，return $_SHARED{myquote}->($_[0]);，但这会降低可读性。）
出于安全原因，PL/Perl 在单个 SQL 角色调用的函数中使用单独的 Perl 解释器执行。这可以防止一个用户意外或恶意地干扰另一个用户的PL/Perl 函数行为。每个这样的解释器都有自己的 %_SHARED 变量和其他全局状态。因此，仅当它们由同一 SQL 角色执行时，两个PL/Perl 函数才会共享 %_SHARED 的相同值。在一个应用程序中，一个会话通过 SECURITY DEFINER 函数、使用SET ROLE 等方式执行多个 SQL 角色的代码时，您可能需要采取明确的步骤，以确保 PL/Perl 函数可以通过 %_SHARED共享数据。为此，请确保应该通信的函数归属于同一用户，并标记它们为 SECURITYDEFINER。当然，您必须小心，以确保不会出现安全问题。这样的函数不能用于执行任何意外的操作。
4.5.可信和不可信的PL/Perl
通常，PL/Perl被安装为名为plperl的“可信”编程语言。在这种设置下，为了保证安全性，某些Perl操作被禁用。一般来说，被限制的操作是与环境交互的操作。这包括文件句柄操作、require和use（用于外部模块）。无法访问数据库服务器进程的内部或以服务器进程的权限获得操作系统级别的访问权限，就像C函数可以做的那样。因此，任何非特权数据库用户都可以使用此语言。
以下是一个由于安全原因不允许文件系统操作而无法工作的函数示例：
CREATE FUNCTION badfunc() RETURNS integer AS $$
my $tmpfile = "/tmp/badfile";
open my $fh, '>', $tmpfile
or elog(ERROR, qq{could not open the file "$tmpfile": $!});
print $fh "Testing writing to a file\n";
close $fh or elog(ERROR, qq{could not close the file "$tmpfile": $!});
return 1;
$$ LANGUAGE plperl;
由于验证器会捕捉到其使用了被禁止的操作，因此创建此函数将失败。
有时需要编写不受限制的Perl函数。例如，可能需要一个发送电子邮件的Perl函数。为了处理这些情况，PL/Perl也可以安装为一个“不可信”语言（通常称为PL/PerlU）。在这种情况下，完整的Perl语言是可用的。安装语言时，语言名称plperlu将选择不可信的PL/Perl变体。
PL/PerlU函数的编写者必须注意，函数不能用于执行任何不需要的操作，因为它将能够执行任何数据库管理员登录用户可以执行的操作。请注意，数据库系统只允许数据库超级用户在不可信的语言中创建函数。
如果上述函数是由超级用户使用语言plperlu创建的，则执行将成功。
同样地，如果将语言指定为plperlu而不是plperl，则在Perl中编写的匿名代码块可以使用受限操作，但调用者必须是超级用户。
注意
虽然每个SQL角色的PL/Perl函数在单独的Perl解释器中运行，但在给定会话中执行的所有PL/PerlU函数都在单个Perl解释器中运行（不是任何一个用于PL/Perl函数的解释器）。这允许PL/PerlU函数自由共享数据，但PL/Perl和PL/PerlU函数之间不能进行通信。
注意
除非使用了适当的标志（即usemultiplicity或useithreads），否则Perl无法在一个进程中支持多个解释器。（除非您实际需要使用线程，否则建议使用usemultiplicity。有关更多详细信息，请参见perlembed手册。）如果PL/Perl与未构建此方式的Perl副本一起使用，则每个会话只能有一个Perl解释器，因此任何一个会话只能执行PL/PerlU函数或由同一SQL角色调用的PL/Perl函数。
4.6.PL/Perl 触发器
PL/Perl 可用于编写触发器函数。在触发器函数中，哈希引用 $_TD 包含有关当前触发器事件的信息。$_TD是一个全局变量，每次调用触发器时都会获得一个单独的本地值。 $_TD 哈希引用的字段包括：
-
$_TD->{new}{foo}列
foo的NEW值 -
$_TD->{old}{foo}列
foo的OLD值 -
$_TD->{name}调用的触发器的名称
-
$_TD->{event}触发器事件：
INSERT、UPDATE、DELETE、TRUNCATE或UNKNOWN -
$_TD->{when}触发器被调用的时间：
BEFORE、AFTER、INSTEAD OF或UNKNOWN -
$_TD->{level}触发器级别：
ROW、STATEMENT或UNKNOWN -
$_TD->{relid}触发器触发的表的 OID
-
$_TD->{table_name}触发器触发的表的名称
-
$_TD->{relname}触发器触发的表的名称。这已被弃用，并可能在未来的版本中被删除。请改用 $_TD->{table_name}。
-
$_TD->{table_schema}触发器所在表的模式名称
-
$_TD->{argc}触发器函数的参数数量
-
@{$_TD->{args}}触发器函数的参数。如果
$_TD->{argc}为0，则不存在。
行级触发器可以返回以下之一：
-
return;执行操作
-
"SKIP"不执行操作
-
"MODIFY"指示
NEW行已被触发器函数修改
以下是一个触发器函数的示例，说明了上述一些内容：
CREATE TABLE test (
i int,
v varchar
);
CREATE OR REPLACE FUNCTION valid_id() RETURNS trigger AS $$
if (($_TD->{new}{i} >= 100) || ($_TD->{new}{i} <= 0)) {
return "SKIP"; # skip INSERT/UPDATE command
} elsif ($_TD->{new}{v} ne "immortal") {
$_TD->{new}{v} .= "(modified by trigger)";
return "MODIFY"; # modify row and execute INSERT/UPDATE command
} else {
return; # execute INSERT/UPDATE command
}
$$ LANGUAGE plperl;
CREATE TRIGGER test_valid_id_trig
BEFORE INSERT OR UPDATE ON test
FOR EACH ROW EXECUTE FUNCTION valid_id();
4.7.PL/Perl 事件触发器
PL/Perl 可以用于编写事件触发器函数。在事件触发器函数中，哈希引用 $_TD 包含有关当前触发事件的信息。 $_TD是一个全局变量，每次触发都会获得一个单独的本地值。 $_TD 哈希引用的字段包括：
-
$_TD->{event}触发器触发的事件名称。
-
$_TD->{tag}触发器触发的命令标记。
触发器函数的返回值将被忽略。
以下是一个事件触发器函数的示例，说明了上述一些内容：
CREATE OR REPLACE FUNCTION perlsnitch() RETURNS event_trigger AS $$
elog(NOTICE, "perlsnitch: " . $_TD->{event} . " " . $_TD->{tag} . " ");
$$ LANGUAGE plperl;
CREATE EVENT TRIGGER perl_a_snitch
ON ddl_command_start
EXECUTE FUNCTION perlsnitch();
4.8.PL/Perl Under the Hood
4.8.1.配置
本节列出了影响 PL/Perl 的配置参数。
plperl.on_init(string)
指定在 Perl 解释器首次初始化时执行的 Perl 代码，执行之前，该解释器尚未专门用于 plperl 或 plperlu。在执行此代码时，SPI 函数不可用。 如果代码执行失败，则会中止解释器的初始化并传播到调用查询，导致当前事务或子事务中止。
Perl 代码仅限于单个字符串。可以将更长的代码放入模块中，并由 on_init 字符串加载。 例如：
plperl.on_init = 'require "plperlinit.pl"'
plperl.on_init = 'use lib "/my/app"; use MyApp::UxInit;'
由 plperl.on_init 直接或间接加载的任何模块都可供 plperl使用。这可能会创建安全风险。要查看已加载的模块，可以使用：
DO 'elog(WARNING, join ", ", sort keys %INC)' LANGUAGE plperl;
如果在shared_preload_libraries中包含了 plperl 库，则初始化将在 uxmaster 中发生，此时应特别考虑破坏 uxmaster的风险。使用此功能的主要原因是，由 plperl.on_init 加载的 Perl模块需要在 uxmaster 中使用。
此参数仅可在uxsinodb.conf文件或服务器命令行中设置。
-
plperl.on_plperl_init(string)
plperl.on_plperlu_init(string)这些参数指定在专门为
plperl或plperlu特化Perl解释器时要执行的Perl代码。当在数据库会话中首次执行PL/Perl或PL/PerlU函数，或者因为调用其他语言或新SQL角色调用PL/Perl函数而必须创建其他解释器时，将发生这种情况。这遵循plperl.on_init执行的任何初始化。在执行此代码时，SPI函数不可用。在plperl.on_plperl_init中的Perl代码在“锁定”解释器后执行，因此它只能执行受信任的操作。如果代码出现错误，它将中止初始化并传播到调用查询，导致当前事务或子事务中止。在Perl中已经执行的任何操作都不会被撤消；但是，不会再次使用该解释器。如果再次使用该语言，则将在新的Perl解释器中尝试重新初始化。
只有超级用户才能更改这些设置。尽管可以在会话中更改这些设置，但此类更改不会影响已用于执行函数的Perl解释器。
-
plperl.use_strict(boolean)当设置为true时，后续PL/Perl函数的编译将启用
strict编译指示。此参数不影响当前会话中已编译的函数。
4.8.2.限制和缺失功能
目前，PL/Perl缺少以下功能，但它们将成为受欢迎的贡献。
-
PL/Perl函数不能直接相互调用。
-
SPI尚未完全实现。
-
如果使用
spi_exec_query获取非常大的数据集，则应该意识到这些数据集都将进入内存。您可以通过使用前面介绍的spi_query/spi_fetchrow来避免这种情况。如果返回集合的函数通过
return将大量行传递回UXsinoDB，则会出现类似的问题。您也可以通过使用先前显示的return_next返回每个返回的行来避免此问题。 -
当一个会话正常结束时，不是由于致命错误，任何已定义的
END块都会被执行。目前没有执行其他操作。具体来说，文件句柄不会自动刷新，对象也不会自动销毁。
5.PL/Python — Python过程语言
PL/Python过程语言允许在UXsinoDB中编写函数和过程，使用Python语言编写。
要在特定数据库中安装 PL/Python，请使用 CREATE EXTENSION plpython3u。
提示
如果将语言安装到 template1 中，则所有随后创建的数据库都将自动安装该语言。
PL/Python 仅作为一个“不受信任的”语言提供，这意味着它不提供任何限制用户在其中执行的操作的方式，因此它被命名为plpython3u。如果 Python 中开发出了一种安全的执行机制，那么可信的变体 plpython 可能会在未来推出。在不受信任的PL/Python中编写函数的编写者必须注意，该函数不能用于执行任何不需要的操作，因为它将能够执行任何数据库管理员登录用户可以执行的操作。只有超级用户才能创建不受信任语言（如plpython3u）中的函数。
注意
源代码包的用户必须在安装过程中特别启用 PL/Python 的构建。（有关更多信息，请参阅安装说明。）二进制包的用户可能会在单独的子包中找到PL/Python。
5.1.PL/Python 函数
通过标准的CREATE FUNCTION语法声明PL/Python 中的函数：
CREATE FUNCTION funcname (argument-list)
RETURNS return-type
AS $$
# PL/Python function body
$$ LANGUAGE plpython3u;
函数体就是一个 Python 脚本。当函数被调用时，它的参数作为列表args的元素传递；命名参数也作为普通变量传递给 Python脚本。使用命名参数通常更易读。结果以通常的方式从 Python代码中返回，使用return或yield（在结果集语句的情况下）。如果您没有提供返回值，Python返回默认值None。PL/Python将 Python 的None转换为 SQL 的 null 值。在过程中，Python代码的结果必须是None（通常通过在过程结束时不使用return语句或使用没有参数的return语句来实现）；否则，将引发错误。
例如，可以定义一个返回两个整数中较大值的函数：
CREATE FUNCTION pymax (a integer, b integer)
RETURNS integer
AS $$
if a > b:
return a
return b
$$ LANGUAGE plpython3u;
作为函数定义体给出的 Python 代码将转换为 Python 函数。例如，上述代码将转换为：
def __plpython_procedure_pymax_23456():
if a > b:
return a
return b
其中 23456 是由UXsinoDB分配给该函数的 OID。
参数被设置为全局变量。由于 Python的作用域规则，这具有一个微妙的后果，即参数变量不能在函数内部被重新分配为涉及变量名本身的表达式的值，除非变量在块中被重新声明为全局变量。例如，以下代码将无法工作：
CREATE FUNCTION pystrip(x text)
RETURNS text
AS $$
x = x.strip() # error
return x
$$ LANGUAGE plpython3u;
因为将值赋给 x 会使 x 成为整个块的局部变量，因此赋值语句右侧的 x 引用了一个尚未赋值的局部变量 x，而不是PL/Python 函数参数。使用 global 语句可以解决这个问题：
CREATE FUNCTION pystrip(x text)
RETURNS text
AS $$
global x
x = x.strip() # ok now
return x
$$ LANGUAGE plpython3u;
但最好不要依赖 PL/Python 的这个实现细节。最好将函数参数视为只读。
5.2.数据值
一般来说，PL/Python 的目标是提供 UXDB 和 Python 世界之间的“自然”映射。这决定了下面所描述的数据映射规则。
5.2.1.数据类型映射
当调用 PL/Python 函数时，其参数将从其 UXDB 数据类型转换为相应的 Python 类型：
-
UXsinoDB 的
boolean转换为 Python 的bool。 -
UXsinoDB 的
smallint、int、bigint和oid转换为 Python 的int。 -
UXsinoDB 的
real和double转换为 Python 的float。 -
UXsinoDB 的
numeric转换为 Python 的Decimal。如果可用，此类型将从cdecimal包中导入。否则，将使用标准库中的decimal.Decimal。在 Python 3.3 及以上版本中，cdecimal已被整合到标准库中，名称为decimal，因此不再有区别。 -
UXsinoDB中的
bytea类型会被转换为Python中的bytes类型。 -
所有其他数据类型，包括UXsinoDB字符字符串类型，都会被转换为Python中的
str类型（像所有Python字符串一样，是Unicode编码）。 -
对于非标量数据类型，请参见下面的内容。
当PL/Python函数返回时，其返回值将按以下方式转换为函数声明的UXsinoDB返回数据类型：
-
当UXsinoDB返回类型为
boolean时，返回值将根据Python规则进行真值评估。也就是说，0和空字符串为假，但值为'f'的字符串为真。 -
当UXsinoDB返回类型为
bytea时，返回值将使用相应的Python内置函数转换为Pythonbytes，并将结果转换为bytea。 -
对于所有其他UXsinoDB返回类型，返回值将使用Python内置函数
str转换为字符串，并将结果传递给UXsinoDB数据类型的输入函数。（如果Python值是float，则使用内置的repr而不是str进行转换，以避免精度损失。）当字符串传递给UXsinoDB时，它们会自动转换为UXsinoDB服务器编码。
-
对于非标量数据类型，请参见下面的内容。
请注意，声明的UXsinoDB返回类型与实际返回对象的Python数据类型之间的逻辑不匹配不会被标记；无论如何都会进行转换。
5.2.2.Null、None
如果将SQL null值传递给函数，则参数值将在Python中显示为None。例如，在PL/Python 函数中显示的pymax函数定义将为null输入返回错误的答案。我们可以在函数定义中添加STRICT，以使UXsinoDB执行更合理的操作：如果传递了null值，则不会调用函数，而只会自动返回null结果。或者，我们可以在函数体中检查null输入：
CREATE FUNCTION pymax (a integer, b integer)
RETURNS integer
AS $$
if (a is None) or (b is None):
return None
if a > b:
return a
return b
$$ LANGUAGE plpython3u;
如上所示，要从PL/Python函数返回SQL null值，请返回值None。无论函数是否严格，都可以这样做。
5.2.3.数组、列表
SQL数组值作为Python列表传递到PL/Python中。要从PL/Python函数返回SQL数组值，请返回Python列表：
CREATE FUNCTION return_arr()
RETURNS int[]
AS $$
return [1, 2, 3, 4, 5]
$$ LANGUAGE plpython3u;
SELECT return_arr();
return_arr
-------------
{1,2,3,4,5}
(1 row)
多维数组作为嵌套Python列表传递到PL/Python中。例如，2维数组是一个列表的列表。当从PL/Python函数返回多维SQL数组时，内部列表应该是嵌套的。每个级别的列表必须具有相同的大小。例如：
CREATE FUNCTION test_type_conversion_array_int4(x int4[]) RETURNS int4[] AS $$
plpy.info(x, type(x))
return x
$$ LANGUAGE plpython3u;
SELECT * FROM test_type_conversion_array_int4(ARRAY[[1,2,3],[4,5,6]]);
INFO: ([[1, 2, 3], [4, 5, 6]], <type 'list'>)
test_type_conversion_array_int4
---------------------------------
{{1,2,3},{4,5,6}}
(1 row)
其他Python序列，如元组，也被接受以向后兼容UXsinoDB版本，当时不支持多维数组。但是，它们始终被视为一维数组，因为它们与复合类型不明确。出于同样的原因，当复合类型用于多维数组时，必须用元组而不是列表表示。
请注意，在Python中，字符串是序列，可能会对Python程序员熟悉的不良影响：
CREATE FUNCTION return_str_arr()
RETURNS varchar[]
AS $$
return "hello"
$$ LANGUAGE plpython3u;
SELECT return_str_arr();
return_str_arr
----------------
{h,e,l,l,o}
(1 row)
5.2.4.复合类型
复合类型参数作为Python映射传递给函数。映射的元素名称是复合类型的属性名称。如果传递的行中的属性具有空值，则在映射中具有值None。这是一个例子：
CREATE TABLE employee (
name text,
salary integer,
age integer
);
CREATE FUNCTION overpaid (e employee)
RETURNS boolean
AS $$
if e["salary"] > 200000:
return True
if (e["age"] < 30) and (e["salary"] > 100000):
return True
return False
$$ LANGUAGE plpython3u;
有多种方法可以从Python函数返回行或复合类型。以下示例假定我们有：
CREATE TYPE named_value AS (
name text,
value integer
);
可以将复合结果返回为：
-
序列类型（元组或列表，但不是集合，因为它不可索引）
返回的序列对象必须具有与复合结果类型具有的字段相同的项目数。索引为0的项目分配给复合类型的第一个字段，1分配给第二个字段，依此类推。例如：
CREATE FUNCTION make_pair (name text, value integer) RETURNS named_value AS $$ return ( name, value ) # or alternatively, as list: return [ name, value ] $$ LANGUAGE plpython3u;要返回任何列的SQL null，请在相应位置插入
None。当返回复合类型的数组时，它不能作为列表返回，因为Python列表表示复合类型或另一个数组维度是不明确的。
-
映射（字典）
每个结果类型列的值都从具有列名称作为键的映射中检索。例如：
CREATE FUNCTION make_pair (name text, value integer) RETURNS named_value AS $$ return { "name": name, "value": value } $$ LANGUAGE plpython3u;任何额外的字典键/值对都将被忽略。缺少的键被视为错误。 要为任何列返回SQL null值，请使用相应的列名称作为键插入
None。 -
对象（提供方法
__getattr__的任何对象）这与映射相同。例如：
CREATE FUNCTION make_pair (name text, value integer) RETURNS named_value AS $$ class named_value: def __init__ (self, n, v): self.name = n self.value = v return named_value(name, value) # or simply class nv: pass nv.name = name nv.value = value return nv $$ LANGUAGE plpython3u;
带有 OUT 参数的函数也是被支持的。例如：
CREATE FUNCTION multiout_simple(OUT i integer, OUT j integer) AS $$
return (1, 2)
$$ LANGUAGE plpython3u;
SELECT * FROM multiout_simple();
过程的输出参数也是以同样的方式传回的。例如：
CREATE PROCEDURE python_triple(INOUT a integer, INOUT b integer) AS $$
return (a * 3, b * 3)
$$ LANGUAGE plpython3u;
CALL python_triple(5, 10);
5.2.5.返回集合的函数
PL/Python函数也可以返回标量或复合类型的集合。有几种方法可以实现这一点，因为返回的对象在内部被转换为迭代器。以下示例假定我们有复合类型：
CREATE TYPE greeting AS (
how text,
who text
);
可以从以下方式中返回集合结果：
-
序列类型（元组、列表、集合）
CREATE FUNCTION greet (how text) RETURNS SETOF greeting AS $$ # return tuple containing lists as composite types # all other combinations work also return ( [ how, "World" ], [ how, "UXsinoDB" ], [ how, "PL/Python" ] ) $$ LANGUAGE plpython3u; -
迭代器（任何提供
__iter__和next方法的对象）CREATE FUNCTION greet (how text) RETURNS SETOF greeting AS $$ class producer: def __init__ (self, how, who): self.how = how self.who = who self.ndx = -1 def __iter__ (self): return self def next (self): self.ndx += 1 if self.ndx == len(self.who): raise StopIteration return ( self.how, self.who[self.ndx] ) return producer(how, [ "World", "UXsinoDB", "PL/Python" ]) $$ LANGUAGE plpython3u; -
生成器（
yield）CREATE FUNCTION greet (how text) RETURNS SETOF greeting AS $$ for who in [ "World", "UXsinoDB", "PL/Python" ]: yield ( how, who ) $$ LANGUAGE plpython3u;
带有 OUT 参数的返回集合的函数（使用 RETURNS SETOF record）也是被支持的。例如：
CREATE FUNCTION multiout_simple_setof(n integer, OUT integer, OUT integer) RETURNS SETOF record AS $$
return [(1, 2)] * n
$$ LANGUAGE plpython3u;
SELECT * FROM multiout_simple_setof(3);
5.3.共享数据
全局字典SD可用于在重复调用同一函数之间存储私有数据。全局字典GD是公共数据，可在会话中的所有Python函数中使用；请谨慎使用。
每个函数在Python解释器中都有自己的执行环境，因此myfunc的全局数据和函数参数对myfunc2不可用。唯一的例外是上述GD字典中的数据。
5.4.匿名代码块
PL/Python还支持使用DO语句调用的匿名代码块：
DO $$
# PL/Python code
$$ LANGUAGE plpython3u;
匿名代码块不接收任何参数，它可能返回的任何值都会被丢弃。否则，它的行为就像一个函数。
5.5.触发器函数
当函数用作触发器时，字典TD包含与触发器相关的值：
-
TD["event"]
包含事件字符串：INSERT、UPDATE、DELETE或TRUNCATE。 -
TD["when"]
包含BEFORE、AFTER或INSTEAD OF之一。 -
TD["level"]
包含ROW或STATEMENT。 -
TD["new"]
TD["old"]
对于行级触发器，这两个字段中的一个或两个包含相应的触发器行，具体取决于触发器事件。 -
TD["name"]
包含触发器名称。 -
TD["table_name"]
包含触发器发生的表的名称。 -
TD["table_schema"]
包含触发器发生的表的模式。 -
TD["relid"]
包含触发器发生的表的OID。 -
TD["args"]
如果CREATE TRIGGER命令包括参数，则可以在TD["args"][0]到TD["args"][n-1]中使用它们。
如果TD["when"]为BEFORE或INSTEAD OF，并且TD["level"]为ROW，则可以从触发器函数中返回None或"OK"。Python函数用于指示行未修改，"SKIP"用于中止事件，如果TD["event"]是INSERT或UPDATE，则可以返回"MODIFY"以指示您已修改新行。否则，返回值将被忽略。
5.6.数据库访问
PL/Python语言模块自动导入一个名为plpy的Python模块。在Python代码中，可以通过plpy.foo访问该模块中的函数和常量。
5.6.1.数据库访问函数
plpy模块提供了几个函数来执行数据库命令：
-
plpy.execute(query[,max-rows])使用查询字符串和可选的行限制参数调用
plpy.execute会运行该查询，并将结果返回到结果对象中。结果对象模拟列表或字典对象。可以通过行号和列名访问结果对象。例如：
rv = plpy.execute("SELECT * FROM my_table", 5)返回
my_table中最多5行。如果my_table有一个名为my_column的列，则可以通过以下方式访问它：foo = rv[i]["my_column"]可以使用内置的
len函数获取返回的行数。结果对象还具有以下方法：
-
nrows()返回命令处理的行数。请注意，这不一定与返回的行数相同。例如，
UPDATE命令将设置此值，但不会返回任何行（除非使用RETURNING）。 -
status()SPI_execute()的返回值。 -
colnames()coltypes()coltypmods()分别返回列名列表、列类型 OID 列表和列的类型特定类型修饰符列表。
当在没有产生结果集的命令（例如没有
RETURNING的UPDATE或DROP TABLE）的结果对象上调用这些方法时，会引发异常。但是在包含零行的结果集上使用这些方法是可以的。 -
__str__()定义了标准的
__str__方法，使得可以使用plpy.debug(rv)来调试查询执行结果。
结果对象可以被修改。
请注意，调用
plpy.execute将导致整个结果集被读入内存。只有在确定结果集相对较小的情况下才使用该函数。如果不想在获取大型结果时冒险使用过多的内存，请使用plpy.cursor而不是plpy.execute。 -
-
plpy.prepare(query[,argtypes])plpy.execute(plan[,arguments[,max-rows]])plpy.prepare准备查询的执行计划。如果查询中有参数引用，则需要提供查询字符串和参数类型列表。例如：plan = plpy.prepare("SELECT last_name FROM my_users WHERE first_name = $1", ["text"])text是将传递给$1的变量的类型。如果不想向查询传递任何参数，则第二个参数是可选的。准备语句后，可以使用
plpy.execute函数的变体来运行它：rv = plpy.execute(plan, ["name"], 5)将计划作为第一个参数（而不是查询字符串），将要替换到查询中的值的列表作为第二个参数传递。如果查询不需要任何参数，则第二个参数是可选的。第三个参数是可选的行限制，与之前一样。
或者，可以在计划对象上调用
execute方法：rv = plan.execute(["name"], 5)查询参数和结果行字段在 UXDB 和 Python 数据类型之间转换，如数据值 中所述。
使用 PL/Python 模块准备计划时，它会自动保存。请阅读 SPI 文档了解其含义。为了在函数调用之间有效地使用它，需要使用其中一种持久存储方案。字典
SD或GD（参见共享数据）可用于在函数之间共享数据。例如：CREATE FUNCTION usesavedplan() RETURNS trigger AS $$ if "plan" in SD: plan = SD["plan"] else: plan = plpy.prepare("SELECT 1") SD["plan"] = plan # rest of function $$ LANGUAGE plpython3u; -
plpy.cursor(query)
plpy.cursor(plan[,arguments])plpy.cursor函数接受与plpy.execute相同的参数（除了行限制），并返回一个游标对象，该对象允许您以较小的块处理大型结果集。与plpy.execute一样，可以使用查询字符串或计划对象以及参数列表，或者可以将cursor函数作为计划对象的方法调用。游标对象提供一个
fetch方法，该方法接受一个整数参数并返回一个结果对象。每次调用fetch，返回的对象将包含下一批行，永远不会大于参数值。一旦所有行都用完，fetch开始返回一个空结果对象。游标对象还提供了一个迭代器接口，一次产生一行，直到所有行都用完。以这种方式获取的数据不作为结果对象返回，而是作为字典返回，每个字典对应一个单独的结果行。从大型表中处理数据的两种方法的示例是：
CREATE FUNCTION count_odd_iterator() RETURNS integer AS $$ odd = 0 for row in plpy.cursor("select num from largetable"): if row['num'] % 2: odd += 1 return odd $$ LANGUAGE plpython3u; CREATE FUNCTION count_odd_fetch(batch_size integer) RETURNS integer AS $$ odd = 0 cursor = plpy.cursor("select num from largetable") while True: rows = cursor.fetch(batch_size) if not rows: break for row in rows: if row['num'] % 2: odd += 1 return odd $$ LANGUAGE plpython3u; CREATE FUNCTION count_odd_prepared() RETURNS integer AS $$ odd = 0 plan = plpy.prepare("select num from largetable where num % $1 <> 0", ["integer"]) rows = list(plpy.cursor(plan, [2])) # or: = list(plan.cursor([2])) return len(rows) $$ LANGUAGE plpython3u;游标会自动释放。但是，如果要显式释放游标持有的所有资源，请使用
close方法。一旦关闭，就无法再从游标中获取数据。提示
不要将由
plpy.cursor创建的对象与由Python数据库API规范定义的DB-API游标混淆。除了名称之外，它们没有任何共同点。
5.6.2.捕获错误
访问数据库的函数可能会遇到错误，这将导致它们中止并引发异常。 plpy.execute 和 plpy.prepare 都可以引发plpy.SPIError 子类的实例，默认情况下会终止函数。可以像处理任何其他Python异常一样，使用 try/except结构来处理此错误。例如：
CREATE FUNCTION try_adding_joe() RETURNS text AS $$
try:
plpy.execute("INSERT INTO users(username) VALUES ('joe')")
except plpy.SPIError:
return "something went wrong"
else:
return "Joe added"
$$ LANGUAGE plpython3u;
引发的异常类的实际类对应于导致错误的特定条件。模块plpy.spiexceptions为每个UXsinoDB条件定义了一个异常类，从条件名称派生它们的名称。例如，division_by_zero变成DivisionByZero，unique_violation变成UniqueViolation，fdw_error变成FdwError等等。这些异常类中的每一个都继承自SPIError。这种分离使得处理特定错误更容易，例如：
CREATE FUNCTION insert_fraction(numerator int, denominator int) RETURNS text AS $$
from plpy import spiexceptions
try:
plan = plpy.prepare("INSERT INTO fractions (frac) VALUES ($1 / $2)", ["int", "int"])
plpy.execute(plan, [numerator, denominator])
except spiexceptions.DivisionByZero:
return "denominator cannot equal zero"
except spiexceptions.UniqueViolation:
return "already have that fraction"
except plpy.SPIError as e:
return "other error, SQLSTATE %s" % e.sqlstate
else:
return "fraction inserted"
$$ LANGUAGE plpython3u;
请注意，由于plpy.spiexceptions模块中的所有异常都继承自SPIError，处理它的except子句将捕获任何数据库访问错误。
作为处理不同错误条件的替代方法，您可以捕获SPIError异常，并在except块内通过查看异常对象的sqlstate属性确定特定的错误条件。这个属性是一个包含“SQLSTATE”错误代码的字符串值。这种方法提供了大约相同的功能。
5.7.显式子事务
如捕获错误所述，由数据库访问引起的错误可能会导致一些操作在失败之前成功执行，而在从错误中恢复后，数据处于不一致状态。PL/Python提供了显式子事务的解决方案。
5.7.1.子事务上下文管理器
考虑一个实现两个账户之间转账的函数：
CREATE FUNCTION transfer_funds() RETURNS void AS $$
try:
plpy.execute("UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe'")
plpy.execute("UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary'")
except plpy.SPIError as e:
result = "error transferring funds: %s" % e.args
else:
result = "funds transferred correctly"
plan = plpy.prepare("INSERT INTO operations (result) VALUES ($1)", ["text"])
plpy.execute(plan, [result])
$$ LANGUAGE plpython3u;
如果第二个UPDATE语句导致异常被抛出，这个函数将报告错误，但第一个UPDATE的结果仍将被提交。换句话说，资金将从Joe的账户中提取，但不会转移到Mary的账户中。
为避免这种情况，您可以将plpy.execute调用包装在显式子事务中。plpy模块提供了一个帮助对象来管理显式子事务，该对象通过plpy.subtransaction()函数创建。由此函数创建的对象实现了上下文管理器接口。使用显式子事务，我们可以将函数重写为：
CREATE FUNCTION transfer_funds2() RETURNS void AS $$
try:
with plpy.subtransaction():
plpy.execute("UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe'")
plpy.execute("UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary'")
except plpy.SPIError as e:
result = "error transferring funds: %s" % e.args
else:
result = "funds transferred correctly"
plan = plpy.prepare("INSERT INTO operations (result) VALUES ($1)", ["text"])
plpy.execute(plan, [result])
$$ LANGUAGE plpython3u;
注意仍然需要使用 try/catch。否则，异常会传播到 Python 栈的顶部，并导致整个函数中止，从而导致 UXsinoDB错误，因此 operations表不会插入任何行。子事务上下文管理器不捕获错误，它只确保在其范围内执行的所有数据库操作将被原子提交或回滚。任何类型的异常退出都会回滚子事务块，而不仅仅是由数据库访问引起的错误。在显式子事务块内引发的常规Python 异常也会导致子事务被回滚。
5.8.事务管理
在从顶层调用的过程或从顶层调用的匿名代码块（DO命令）中，可以控制事务。要提交当前事务，请调用plpy.commit()。要回滚当前事务，请调用plpy.rollback()。（请注意，不可能通过plpy.execute或类似的方式运行SQL命令COMMIT或ROLLBACK。必须使用这些函数来完成。）事务结束后，会自动启动新的事务，因此没有单独的函数。
以下是一个示例：
CREATE PROCEDURE transaction_test1()
LANGUAGE plpython3u
AS $$
for i in range(0, 10):
plpy.execute("INSERT INTO test1 (a) VALUES (%d)" % i)
if i % 2 == 0:
plpy.commit()
else:
plpy.rollback()
$$;
CALL transaction_test1();
当显式子事务处于活动状态时，无法结束事务。
5.9.实用函数
plpy模块还提供了以下函数：
plpy.debug(msg, **kwargs) |
plpy.log(msg, **kwargs) |
plpy.info(msg, **kwargs) |
plpy.notice(msg, **kwargs) |
plpy.warning(msg, **kwargs) |
plpy.error(msg, **kwargs) |
plpy.fatal(msg, **kwargs) |
plpy.error和plpy.fatal实际上会引发Python异常，如果未被捕获，则会传播到调用查询，导致当前事务或子事务被中止。
raise plpy.Error(msg)和raise plpy.Fatal(msg)等效于分别调用plpy.error(msg)和plpy.fatal(msg)，但raise形式不允许传递关键字参数。其他函数仅生成不同优先级的消息。特定优先级的消息是否报告给客户端、写入服务器日志或两者都受log_min_messages和client_min_messages配置变量的控制。
msg 参数作为位置参数给出。对于向下兼容，可以给出多个位置参数。在这种情况下，位置参数元组的字符串表示形式将成为报告给客户端的消息。
接受以下关键字参数：
detailhintsqlstateschema_nametable_namecolumn_namedatatype_nameconstraint_name
传递为关键字参数的对象的字符串表示用于丰富报告给客户端的消息。例如：
CREATE FUNCTION raise_custom_exception() RETURNS void AS $$
plpy.error("custom exception message",
detail="some info about exception",
hint="hint for users")
$$ LANGUAGE plpython3u;
=# SELECT raise_custom_exception();
ERROR: plpy.Error: custom exception message
DETAIL: some info about exception
HINT: hint for users
CONTEXT: Traceback (most recent call last):
PL/Python function "raise_custom_exception", line 4, in <module>
hint="hint for users")
PL/Python function "raise_custom_exception"
另一组实用函数是plpy.quote_literal(string)，plpy.quote_nullable(string)和plpy.quote_ident(string)。在构造临时查询时它们非常有用。PL/Python中动态 SQL 的等效项可以参考示例 动态查询中的值引用：
plpy.execute("UPDATE tbl SET %s = %s WHERE key = %s" % (
plpy.quote_ident(colname),
plpy.quote_nullable(newvalue),
plpy.quote_literal(keyvalue)))
5.10.Python2 vs Python3
PL/Python仅支持Python3。过去的版本UXsinoDB支持Python2，使用plpythonu和plpython2u语言名称。
5.11.环境变量
一些被Python解释器接受的环境变量也可以用于影响PL/Python的行为。例如，在主UXsinoDB服务器进程的环境中设置它们，例如在启动脚本中。可用的环境变量取决于Python的版本；有关详细信息，请参见Python文档。在撰写本文时，以下环境变量对PL/Python有影响，假设有足够的Python版本：
PYTHONHOMEPYTHONPATHPYTHONY2KPYTHONOPTIMIZEPYTHONDEBUGPYTHONVERBOSEPYTHONCASEOKPYTHONDONTWRITEBYTECODEPYTHONIOENCODINGPYTHONUSERBASEPYTHONHASHSEED
（似乎是Python实现的细节超出了PL/Python的控制，列在python手册页上的某些环境变量仅在命令行解释器中有效，而不是嵌入式Python解释器中有效。）