UNNEST 函数

UNNEST 函数组合一个或多个数组和/或表变量。结果表包括指定数组的每个元素的一行。UNNEST 函数的结果需要分配给表变量。语法为：

 = UNNEST( [ {, } ...] )[WITH
ORDINALITY] [AS ( [ {, }... ]) ]
 
 ::= :table_variable
                 | :array_variable
                 | :array_function
 
 ::= '*'
                     | '('  ')'
                     | 
 
 ::=  [AS ] [, ]

例如，以下语句将类型为 INTEGER 的数组 arr_id 和类型为 VARCHAR(10) 的数组 arr_name 转换为表，并将其指派给表格输出参数 rst：

CREATE PROCEDURE ARRAY_UNNEST_SIMPLE(OUT rst TABLE(":ARR_ID" INT, ":ARR_NAME" NVARCHAR(10)))
READS SQL DATA
AS BEGIN
  DECLARE arr_id INTEGER ARRAY = ARRAY(1, 2);
  DECLARE arr_name NVARCHAR(10) ARRAY = ARRAY('name1', 'name2', 'name3');
  rst = UNNEST(:arr_id, :arr_name);
END;

对于多个数组，行数将等于数组的基数中的最大基数。在返回的表中，与数组的任何元素不对应的单元格将填充 NULL 值。以上示例将导致 rst 的下列表格输出：

:ARR_ID :ARR_NAME 1 name1 2 name2

? name3

也可以使用 AS 子句显式命名表的返回列。在以下示例中，的列名称：ARR_ID 和：ARR_NAME 更改为 ID 和 NAME 。

rst = UNNEST(:arr_id, :arr_name) AS (ID, NAME);

结果是：

ID NAME 1 name1 2 name2

? name3

作为附加选项，可以使用 WITH ORDINALITY 子句指定 ordinal 列。

然后，顺序列将附加到返回的表中。顺序列的别名需要显式指定。下一个示例说明了用法。SEQ 用作序数列的别名：

CREATE PROCEDURE ARRAY_UNNEST(OUT rst TABLE(AMOUNT INTEGER, SEQ INTEGER))
LANGUAGE SQLSCRIPT READS SQL DATA AS
BEGIN
	DECLARE amount	INTEGER	ARRAY = ARRAY(10, 20);

	rst = UNNEST(:amount) WITH ORDINALITY AS ( "AMOUNT", "SEQ");
END;

调用此过程的结果如下：

AMOUNT SEQ 10 1

20 2

也可以在 UNNEST 函数中使用表变量。虽然对于数组，关联的列说明符列表条目需要包含单个列名称，但表变量的关联条目必须是 '*' 或投影别名列表。'*' 表示输入表的所有列都应包含在结果中。使用投影别名列表，可以指定输入表的列的子集并将其重命名，以避免名称冲突（结果不得包含名称相同的多个列）。

示例代码create column table tab0(a int);insert into tab0 values(1);insert into tab0 values(2);insert into tab0 values(3); do begin t0 = select * from tab0 order by a asc; t1 = select * from tab0 order by a desc; lt = unnest(:t0, :t1) as (*, (a as b)); select * from :lt;end; -- expected result {1, 3}, {2, 2}, {3, 1} do begin t0 = select * from tab0 order by a asc; t1 = select * from tab0 order by a desc; lt = unnest(:t0, :t1) as (*, (a as b, a as c)); select * from :lt;end; -- expected result {1, 3, 3}, {2, 2, 2}, {3, 1, 1}

如果显式声明结果表变量，则它可能包含类型为 NOT NULL 的列。由于列已调整到最长的列，因此此方案可能会导致运行时错误。下表显示 NOT NULL 行为：

结果LHS 类型RHS 类型潜在运行时错误非空非空编译时间错误非空可为空没有错误可为空非空没有错误可为空可为空  备注数组类型始终可为空。备注默认列名称如果没有列说明符列表，将生成数组的列名称和结果表中的 ordinality 列。生成的名称始终以 "COL" 开头，后跟数字，表示结果表中的列索引。例如，如果结果表中的第三列具有生成的名称，则为“COL3”。但是，如果此名称已被占用，因为输入表变量包含具有此名称的列，索引编号将增加以生成空闲的列名称（如果使用 "COL3"，"COL4" 是下一个候选项）。此行为与 ordinality 列类似。如果此名称可用，则将此列命名为 "ORDINALITY"（无索引）；如果已占用 "ORDINALITY"，则为 "ORDINALITY" + INDEX（从 1 开始）。