扩展运算符¶

扩展运算符将表示列表的查询表达式扩展为列表中的单个值。目前，扩展运算符（**）是 Snowflake 支持的唯一扩展运算符。

扩展¶

扩展运算符将数组扩展为单个值的列表。此运算符适用于以下用例：

包含 IN 子句的查询。
对系统定义的函数的调用，这些函数将值列表作为实参，例如 COALESCE、GREATEST 和 LEAST。
SQL 用户定义的函数，它们使用实参来提供值数组。
Snowflake Scripting 存储过程，它们使用 BIND 变量来提供值数组。有关在 Snowflake Scripting 中使用绑定变量的更多信息，请参阅在 SQL 语句中使用变量（绑定）和在 SQL 语句中使用实参（绑定）。

有关这些用例的更多信息，请参阅 Snowflake 推出 SQL 扩展运算符 (**) 博客文章。

语法¶

** <array>

限制¶

输入必须是常量值的数组，可以是字面量值的数组，也可以是表示字面量值数组的绑定变量。
半结构化数组中的每个值都是 VARIANT 类型。VARIANT 值可以包含任何其他数据类型的值。对于存储在 VARIANT 值中的值，扩展运算符支持以下数据类型：
- :doc:`数值 </sql-reference/data-types-numeric>`（例如，INTEGER 和 NUMERIC）
- :doc:`字符串和二进制 </sql-reference/data-types-text>`（例如，VARCHAR 和 BINARY）
- :doc:`逻辑 </sql-reference/data-types-logical>`（例如，BOOLEAN）
- :doc:`日期和时间 </sql-reference/data-types-datetime>`（例如，DATE、TIME 和 TIMESTAMP）
用 SQL 以外的语言编写的用户定义函数和存储过程不能使用扩展运算符。
使用扩展运算符扩展非常大的数组可能会降低性能。

示例¶

一些示例使用下表中的数据：

创建表并插入数据：

CREATE OR REPLACE TABLE spread_demo (col1 INT, col2 VARCHAR);

INSERT INTO spread_demo VALUES
  (1, 'a'),
  (2, 'b'),
  (3, 'c'),
  (4, 'd'),
  (5, 'e');

SELECT * FROM spread_demo;

+------+------+
| COL1 | COL2 |
|------+------|
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
+------+------+

以下示例使用扩展运算符。

在 IN 子句中扩展字面量值的数组
在系统定义的函数调用中扩展字面量值的数组
在 SQL 用户定义函数中使用带有绑定变量的扩展运算符
在 Snowflake Scripting 存储过程中将扩展运算符与绑定变量一起使用

在 IN 子句中扩展字面量值的数组¶

在之前创建的 spread_demo 表上的查询中使用扩展运算符扩展数字的数组：

SELECT * FROM spread_demo
  WHERE col1 IN (** [3, 4])
  ORDER BY col1;

+------+------+
| COL1 | COL2 |
|------+------|
|    3 | c    |
|    4 | d    |
+------+------+

使用扩展运算符扩展字符串的数组：

SELECT * FROM spread_demo
  WHERE col2 IN (** ['b', 'd'])
  ORDER BY col1;

+------+------+
| COL1 | COL2 |
|------+------|
|    2 | b    |
|    4 | d    |
+------+------+

在混合使用 INTEGER 值和扩展数组值的查询中使用 IN 子句：

SELECT * FROM spread_demo
  WHERE col1 IN (** [1, 2], 4, 5)
  ORDER BY col1;

+------+------+
| COL1 | COL2 |
|------+------|
|    1 | a    |
|    2 | b    |
|    4 | d    |
|    5 | e    |
+------+------+

在系统定义的函数调用中扩展字面量值的数组¶

在对 COALESCE 函数的调用中扩展字符串的数组：

SELECT COALESCE(** [NULL, NULL, 'my_string_1', 'my_string_2']) AS first_non_null;

+----------------+
| FIRST_NON_NULL |
|----------------|
| my_string_1    |
+----------------+

在对 GREATEST 函数的调用中扩展数字的数组：

SELECT GREATEST(** [1, 2, 5, 4, 5]) AS greatest_value;

+----------------+
| GREATEST_VALUE |
|----------------|
|              5 |
+----------------+

在 SQL 用户定义函数中使用带有绑定变量的扩展运算符¶

创建使用扩展运算符的 SQL 用户定义函数。该函数将数组作为实参，然后扩展数组值以查询之前创建的 spread_demo 表：

CREATE OR REPLACE FUNCTION spread_function_demo(col_1_values ARRAY)
  RETURNS TABLE(
    col1 INT,
    col2 VARCHAR)
AS
$$
   SELECT * FROM spread_demo
     WHERE col1 IN (** col_1_values)
     ORDER BY col1
$$;

使用函数查询表：

SELECT * FROM TABLE(spread_function_demo([1, 3, 5]));

+------+------+
| COL1 | COL2 |
|------+------|
|    1 | a    |
|    3 | c    |
|    5 | e    |
+------+------+

在 Snowflake Scripting 存储过程中将扩展运算符与绑定变量一起使用¶

创建使用扩展运算符的 Snowflake Scripting 存储过程。存储过程将数组作为实参，然后扩展绑定变量中的数组值以查询之前创建的 spread_demo 表：

CREATE OR REPLACE PROCEDURE spread_sp_demo(col_1_values ARRAY)
  RETURNS TABLE(
    col1 INT,
    col2 VARCHAR)
  LANGUAGE SQL
AS
$$
DECLARE
  res RESULTSET;
BEGIN
  res := (SELECT * FROM spread_demo
     WHERE col1 IN (** :col_1_values)
     ORDER BY col1);
  RETURN TABLE(res);
END;
$$;

调用存储过程：

CALL spread_sp_demo([2, 4]);

+------+------+
| COL1 | COL2 |
|------+------|
|    2 | b    |
|    4 | d    |
+------+------+