标准 SQL 中的脚本语句

借助脚本，您可以在一个请求中向 BigQuery 发送多个语句。脚本可以使用变量以及控制流语句，例如 IF 和 WHILE。例如，您可以声明一个变量，为其赋值，然后在第三个语句中引用该变量。如需了解详情，请参阅脚本和存储过程。

DECLARE

DECLARE variable_name[, ...] [variable_type] [DEFAULT expression];

variable_name 必须为有效标识符，variable_type 为任何 BigQuery 类型。

说明

声明指定类型的变量。如果指定了 DEFAULT 子句，则使用表达式的值初始化变量；如果不存在 DEFAULT 子句，则使用值 NULL 初始化变量。

如果省略 [variable_type]，则必须指定 DEFAULT 子句。变量类型将根据 DEFAULT 子句中的表达式类型推断得出。

变量声明必须出现在脚本的开头且在任何其他语句之前，或者出现在使用 BEGIN 声明的块的开头。变量名称不区分大小写。

多个变量名称可以出现在一个 DECLARE 语句中，但只能出现一个 variable_type 和 expression。

声明与先前在当前块或包含块中声明的变量具有相同名称的变量是错误的。

如果存在 DEFAULT 子句，则表达式的值必须可以强制转换为指定的类型。表达式可以引用先前在同一个块或包含块中声明的其他变量。

变量的大小上限为 1 MB，脚本中使用的所有变量的大小上限为 10 MB。

示例

以下示例使用值NULL将变量x初始化为 INT64。

DECLARE x INT64;

以下示例使用当前日期的值将变量d初始化为 DATE。

DECLARE d DATE DEFAULT CURRENT_DATE();

以下示例将变量 x、y 和 z 初始化为使用值 0 的 INT64。

DECLARE x, y, z INT64 DEFAULT 0;

以下示例声明了一个名为 item 的变量，该变量与 dataset1.products 表中的任意一项相对应。item 的类型根据表架构推断得出。

DECLARE item DEFAULT (SELECT item FROM dataset1.products LIMIT 1);

SET

语法

SET name = expression;

SET (variable_name_1, variable_name_2, …, variable_name_n) =
  (expression_1, expression_2, …, expression_n);

说明

将变量设置为具有提供的表达式的值，或根据多个表达式的结果同时设置多个变量。

SET 语句可能出现在脚本正文中的任何位置。

示例

以下示例将变量 x 设置为具有值 5。

SET x = 5;

以下示例将变量 a 的值设置为 4，将 b 的值设置为“foo”，并将变量 c 的值设置为 false。

SET (a, b, c) = (1 + 3, 'foo', false);

以下示例将查询结果分配给多个变量。 首先，它声明两个变量：target_word 和 corpus_count；然后，它将 SELECT AS STRUCT 查询的结果分配给这两个变量。查询的结果是包含带两个字段的 STRUCT 的单个行；第一个元素分配给第一个变量，第二个元素分配给第二个变量。

DECLARE target_word STRING DEFAULT 'methinks';
DECLARE corpus_count, word_count INT64;

SET (corpus_count, word_count) = (
  SELECT AS STRUCT COUNT(DISTINCT corpus), SUM(word_count)
  FROM `bigquery-public-data`.samples.shakespeare
  WHERE LOWER(word) = target_word
);

SELECT
  FORMAT('Found %d occurrences of "%s" across %d Shakespeare works',
         word_count, target_word, corpus_count) AS result;

此语句列表输出以下字符串：

Found 151 occurrences of "methinks" across 38 Shakespeare works

EXECUTE IMMEDIATE

语法

EXECUTE IMMEDIATE sql_expression [ INTO variable[, ...] ] [ USING identifier[, ...] ];

sql_expression:
  { "query_statement" | expression("query_statement") }

identifier:
  { variable | value } [ AS alias ]

说明

即时执行动态 SQL 语句。

• sql_expression：表示查询语句、可对查询语句使用的表达式、一个 DDL 语句或一个 DML 语句。
• expression：可以是函数、条件表达式或表达式子查询。
• query_statement：表示要执行的有效独立 SQL 语句。如果此语句返回值，则 INTO 子句必须包含相同类型的值。您可以同时访问 USING 子句中存在的系统变量和值；所有其他局部变量和查询参数都不会提供给查询语句。
• INTO 子句：在执行 SQL 表达式后，您可以使用 INTO 子句将结果存储在一个或多个变量中。
• USING 子句：在执行 SQL 表达式之前，您可以将 USING 子句中的一个或多个标识符传入 SQL 表达式中。这些标识符的功能类似于查询参数，用于将值提供给查询语句。标识符可以是变量或值。

您可以在 USING 子句中引用的标识符的 query_statement 中添加以下占位符：

• ?：此占位符的值按索引绑定到 USING 子句中的标识符。

  -- y = 1 * (3 + 2) = 5
  EXECUTE IMMEDIATE "SELECT ? * (? + 2)" INTO y USING 1, 3;
  

• @identifier：此占位符的值按名称绑定到 USING 子句中的标识符。此语法与查询参数语法相同。

  -- y = 1 * (3 + 2) = 5
  EXECUTE IMMEDIATE "SELECT @a * (@b + 2)" INTO y USING 1 as a, 3 as b;
  

以下是关于 EXECUTE IMMEDIATE 语句行为的一些其他说明：

• EXECUTE IMMEDIATE 被限制不能作为嵌套元素动态执行。
• 如果 EXECUTE IMMEDIATE 语句返回结果，则这些结果将成为整个语句的结果，并且所有相应的系统变量会进行更新。
• 同一个变量可以同时出现在 INTO 和 USING 子句中。
• query_statement 可以包含一个已解析语句，并且该语句包含其他语句（例如 BEGIN...END）
• 如果从 query_statement（包括从零行值表）返回零行，则 INTO 子句中的所有变量都会设置为 NULL。
• 如果从 query_statement（包括从零行值表）返回一行，则系统会按位置（而非变量名称）分配值。
• 如果存在 INTO 子句，则在尝试从 query_statement 返回多行时系统会抛出错误。

示例

在此示例中，我们会创建一个图书表，并在其中填充数据。请注意，您可以通过不同的方式来引用变量、将值保存到变量以及使用表达式。

-- create some variables
DECLARE book_name STRING DEFAULT 'Ulysses';
DECLARE book_year INT64 DEFAULT 1922;
DECLARE first_date INT64;

-- Create a temporary table called Books.
EXECUTE IMMEDIATE
  "CREATE TEMP TABLE Books (title STRING, publish_date INT64)";

-- Add a row for Hamlet (less secure)
EXECUTE IMMEDIATE
  "INSERT INTO Books (title, publish_date) VALUES('Hamlet', 1599)";

-- add a row for Ulysses, using the variables declared at the top of this
-- script and the ? placeholder
EXECUTE IMMEDIATE
  "INSERT INTO Books (title, publish_date) VALUES(?, ?)"
  USING book_name, book_year;

-- add a row for Emma, using the identifier placeholder
EXECUTE IMMEDIATE
  "INSERT INTO Books (title, publish_date) VALUES(@name, @year)"
  USING 1815 as year, "Emma" as name;

-- add a row for Middlemarch, using an expression
EXECUTE IMMEDIATE
  CONCAT("INSERT INTO Books (title, publish_date)", "VALUES('Middlemarch', 1871)");

-- save the publish date of the first book, Hamlet, to a variable called first_date
EXECUTE IMMEDIATE "SELECT publish_date FROM Books LIMIT 1" INTO first_date;

+------------------+------------------+
| title            | publish_date     |
+------------------+------------------+
| Hamlet           | 1599             |
| Ulysses          | 1922             |
| Emma             | 1815             |
| Middlemarch      | 1871             |
+------------------+------------------+

BEGIN

语法

BEGIN
  sql_statement_list
END;

说明

BEGIN 启动语句块，其中，声明的变量仅在相应的 END 之前存在。sql_statement_list 列表包含以分号结尾的零个或多个 SQL 语句。

变量声明必须出现在块的开头，并且在其他类型的语句之前。在块中声明的变量只能在该块中和任何嵌套块中引用。声明与在同一个块或外部块中声明的变量具有相同名称的变量是错误的。

块和条件语句（例如 BEGIN/END、IF/ELSE/END IF 与 WHILE/END WHILE）的最大嵌套级别为 50。

BEGIN/END 被限制不能作为嵌套元素动态执行。

示例

以下示例声明一个使用默认值 10 的变量 x；然后开始一个语句块，在块中为变量 y 分配 x 的值（即为 10），并返回该值；接着以 END 语句结束该块，结束变量 y 的范围；最后，返回 x 的值。

DECLARE x INT64 DEFAULT 10;
BEGIN
  DECLARE y INT64;
  SET y = x;
  SELECT y;
END;
SELECT x;

BEGIN...EXCEPTION

语法

BEGIN
  sql_statement_list
EXCEPTION WHEN ERROR THEN
  sql_statement_list
END;

说明

BEGIN...EXCEPTION 执行语句块。如果任何语句遇到错误，脚本会跳过块的其余部分并执行 EXCEPTION 子句中的语句。

在 EXCEPTION 子句中，您可使用以下 EXCEPTION 系统变量查看错误的详细信息：

名称	类型	说明
@@error.formatted_stack_trace	STRING	@@error.stack_trace 的内容，以人类可读的字符串表示。该值仅用于显示目的，如有变更，恕不另行通知。如需以编程方式访问错误的堆栈轨迹，请改用 @@error.stack_trace。
@@error.message	STRING	指定人类可读的错误消息。
@@error.stack_trace	请参阅 1。	数组的每个元素都对应一个在出错时执行的语句或过程调用，当前正在执行的堆栈帧显示在最前面。每个字段的含义定义如下： line/column：指定堆栈帧的行号和列号，从 1 开始。如果帧出现在过程主体中，则 line 1 column 1 对应于过程主体开头的 BEGIN 关键字。 location：如果帧出现在过程主体中，请以 [project_name].[dataset_name].[procedure_name] 格式指定过程的完整名称。如果帧引用顶级脚本中的某个位置，则此字段为 NULL。 filename：预留以待将来使用。始终为 NULL。
@@error.statement_text	STRING	指定导致错误的语句文本。

¹ @@error.stack_trace 的类型为 ARRAY<STRUCT<line INT64, column INT64, filename STRING, location STRING>>。

由于 BigQuery 保留随时修订错误消息的权利，因此 @@error.message 的使用者不应指望错误消息保持不变或遵循任何特定模式。请勿通过从错误消息中提取文本来获取错误位置信息，而应改用 @@error.stack_trace 和 @@error.statement_text。

如需处理由异常处理程序本身抛出（和未处理）的异常，必须使用单独的异常处理程序将块封装在外部块中。

下面演示了如何将外部块与单独的异常处理程序结合使用：

BEGIN
  BEGIN
    ...
  EXCEPTION WHEN ERROR THEN
    SELECT 1/0;
  END;
EXCEPTION WHEN ERROR THEN
  -- The exception thrown from the inner exception handler lands here.
END;

与任何其他 BEGIN 块一样，BEGIN...EXCEPTION 块也支持 DECLARE 语句。在 BEGIN 块中声明的变量仅在 BEGIN 部分有效，不能在块的异常处理程序中使用。

示例

CREATE OR REPLACE PROCEDURE dataset1.proc1() BEGIN
  SELECT 1/0;
END;

CREATE OR REPLACE PROCEDURE dataset1.proc2() BEGIN
  CALL dataset1.proc1();
END;

BEGIN
  CALL dataset1.proc2();
EXCEPTION WHEN ERROR THEN
  SELECT
    @@error.message,
    @@error.stack_trace,
    @@error.statement_text,
    @@error.formatted_stack_trace;
END;

在此示例中，如果发生除数为 0 的错误，BigQuery 将停止 dataset1.proc1() 和 dataset1.proc2()，并在异常处理程序中执行 SELECT 语句，而不是停止整个脚本。当异常处理程序运行时，变量将具有以下值：

变量	值
@@error.message	"Query error: division by zero: 1 / 0 at <project>.dataset1.proc1:2:3]"
@@error.stack_trace	[ STRUCT(2 AS line, 3 AS column, NULL AS filename, "<project>.dataset1.proc1:2:3" AS location), STRUCT(2 AS line, 3 AS column, NULL AS filename, "<project>.dataset1.proc2:2:3" AS location), STRUCT(10 AS line, 3 AS column, NULL AS filename, NULL AS location), ]
@@error.statement_text	"SELECT 1/0"
@@error.formatted_stack_trace	"At <project>.dataset1.proc1[2:3]\nAt <project>.dataset1.proc2[2:3]\nAt [10:3]"

END

结束由 BEGIN 启动的块。BEGIN/END 被限制不能作为嵌套元素动态执行。

IF

语法

IF condition THEN [sql_statement_list]
[ELSEIF condition THEN sql_statement_list]
[ELSEIF condition THEN sql_statement_list]...
[ELSE sql_statement_list]
END IF;

说明

如果条件为 true，则执行第一个 sql_statement_list；如果没有条件匹配，则执行可选的 ELSE sql_statement_list。

块和条件语句（例如 BEGIN/END、IF/ELSE/END IF 与 WHILE/END WHILE）的最大嵌套级别为 50。

IF 被限制不能作为嵌套元素动态执行。

示例

以下示例声明一个包含默认值 103 的 INT64 变量 target_product_id；然后，它检查表 dataset.products 是否包含 product_id 列与 target_product_id 的值相匹配的行；如果包含，它将输出一个说明产品已找到的字符串以及 default_product_id 的值；如果不包含，则将输出一个说明产品未找到的字符串以及 default_product_id 的值。

DECLARE target_product_id INT64 DEFAULT 103;
IF EXISTS (SELECT 1 FROM dataset.products
           WHERE product_id = target_product_id) THEN
  SELECT CONCAT('found product ', CAST(target_product_id AS STRING));
  ELSEIF EXISTS (SELECT 1 FROM dataset.more_products
           WHERE product_id = target_product_id) THEN
  SELECT CONCAT('found product from more_products table',
  CAST(target_product_id AS STRING));
ELSE
  SELECT CONCAT('did not find product ', CAST(target_product_id AS STRING));
END IF;

循环

LOOP

语法

LOOP
  sql_statement_list
END LOOP;

说明

执行 sql_statement_list，直到 BREAK 或 LEAVE 语句退出循环。sql_statement_list 列表包含以英文分号结尾的零个或零个以上的 SQL 语句。LOOP 被限制不能作为嵌套元素动态执行。

示例

以下示例首先声明一个使用默认值 0 的变量 x；然后，它使用 LOOP 语句创建一个在变量 x 大于或等于 10 之前一直执行的循环；循环终止后，示例将输出 x 的值。

DECLARE x INT64 DEFAULT 0;
LOOP
  SET x = x + 1;
  IF x >= 10 THEN
    LEAVE;
  END IF;
END LOOP;
SELECT x;

此示例输出以下内容：

+----+
| x  |
+----+
| 10 |
+----+

WHILE

语法

WHILE boolean_expression DO
  sql_statement_list
END WHILE;

块和条件语句（例如 BEGIN/END、IF/ELSE/END IF 与 WHILE/END WHILE）的最大嵌套级别为 50。

说明

当 boolean_expression 为 true 时，执行 sql_statement_list。 系统会针对循环的每次迭代计算 boolean_expression。WHILE 被限制不能作为嵌套元素动态执行。

BREAK

说明

退出当前循环。

在循环外使用 BREAK 是错误的。

示例

以下示例首先声明 heads 和 heads_count 两个变量；接着，它开始一个向 heads 分配随机布尔值的循环；然后检查 heads 是否为 true（如果是，则输出“Heads!”并将 heads_count 递增；如果不是，则输出“Tails!”并退出循环）；最后，它输出一个字符串，说明“扔硬币”的结果为“正面”的次数。

DECLARE heads BOOL;
DECLARE heads_count INT64 DEFAULT 0;
LOOP
  SET heads = RAND() < 0.5;
  IF heads THEN
    SELECT 'Heads!';
    SET heads_count = heads_count + 1;
  ELSE
    SELECT 'Tails!';
    BREAK;
  END IF;
END LOOP;
SELECT CONCAT(CAST(heads_count AS STRING), ' heads in a row');

退出

相当于 BREAK。

CONTINUE

说明

跳过当前循环中的任何以下语句，并返回到循环的开头。

在循环外使用 CONTINUE 是错误的。

示例

以下示例声明 heads 和 heads_count 两个变量；接着，它开始一个向 heads 分配随机布尔值的循环；然后检查 heads 是否为 true（如果是，则输出“Heads!”、将 heads_count 递增，然后重启循环并跳过任何剩余语句；如果不是，则输出“Tails!”并退出循环）；最后，它输出一个字符串，说明“扔硬币”的结果为“正面”的次数。

DECLARE heads BOOL;
DECLARE heads_count INT64 DEFAULT 0;
LOOP
  SET heads = RAND() < 0.5;
  IF heads THEN
    SELECT 'Heads!';
    SET heads_count = heads_count + 1;
    CONTINUE;
  END IF;
  SELECT 'Tails!';
  BREAK;
END LOOP;
SELECT CONCAT(CAST(heads_count AS STRING), ' heads in a row');

ITERATE

相当于 CONTINUE。

RAISE

语法

RAISE [USING MESSAGE = message];

说明

在提供 USING MESSAGE = message 时，选择性地使用指定的错误消息引发错误。

未提供 USING MESSAGE 时

RAISE 语句只能在 EXCEPTION 子句中使用。RAISE 语句将重新引发被捕获的异常，并保留原始堆栈轨迹。

提供 USING MESSAGE 时

如果 RAISE 语句包含在 BEGIN...EXCEPTION 块的 BEGIN 部分中：

• 则会调用处理程序。

• @@error.message 的值将与提供的 message 字符串完全匹配（如果 message 为 NULL，则该值可能为 NULL）。

• 堆栈轨迹将设置为 RAISE 语句。

如果 RAISE 语句未包含在 BEGIN...EXCEPTION 块的 BEGIN 部分中，则 RAISE 语句将停止脚本的执行，并提供错误消息。

RETURN

在 BigQuery 脚本中，RETURN 会停止当前脚本的执行。

CALL

语法

CALL procedure_name (procedure_argument[, …])

说明

使用参数列表调用过程。 procedure_argument 可能是变量或表达式。对于 OUT 或 INOUT 参数，作为参数传递的变量必须具有正确的 BigQuery 类型。

同一个变量不能在过程的参数列表中作为 OUT 或 INOUT 参数多次出现。

过程调用的最大深度为 50 帧。

CALL 被限制不能作为嵌套元素动态执行。

示例

以下示例声明一个变量 retCode。然后，它在数据集 myDataset 中调用过程 updateSomeTables，并传递参数 'someAccountId' 和 retCode。最后，它返回 retCode 的值。

DECLARE retCode INT64;
-- Procedure signature: (IN account_id STRING, OUT retCode INT64)
CALL myDataset.UpdateSomeTables('someAccountId', retCode);
SELECT retCode;

系统变量

您可以在脚本执行期间使用系统变量检查信息。

名称	类型	说明
@@current_job_id	STRING	当前正在执行的作业的 ID。在脚本的上下文中，这将返回负责当前语句的作业，而不是整个脚本。
@@last_job_id	STRING	要在当前脚本中执行的最新作业（不包括当前作业）的 ID。如果脚本包含 CALL 语句，则此作业可能源自另一个过程。
@@project_id	STRING	用于执行当前查询的项目的 ID。在过程的上下文中，@@project_id 是指运行脚本的项目，而不是拥有该过程的项目。
@@row_count	INT64	如果在脚本中使用且前一个脚本语句为 DML，则指定由于该 DML 语句而修改、插入或删除的行数。如果上一个语句是 MERGE 语句，则 @@row_count 表示插入、移除和删除的总行数。如果不在脚本中，则此值为 NULL。
@@script.bytes_billed	INT64	到目前为止，正在执行的脚本作业中结算的总字节数。 如果不在脚本中，则此值为 NULL。
@@script.bytes_processed	INT64	到目前为止，正在执行的脚本作业中处理的总字节数。 如果不在脚本中，则此值为 NULL。
@@script.creation_time	TIMESTAMP	当前正在执行的脚本作业的创建时间。如果不在脚本中，则此值为 NULL。
@@script.job_id	STRING	当前正在执行的脚本作业的 ID。如果不在脚本中，则此值为 NULL。
@@script.num_child_jobs	INT64	当前已完成的子作业数。如果不在脚本中，则此值为 NULL。
@@script.slot_ms	INT64	到目前为止，脚本使用的槽毫秒数。如果不在脚本中，则此值为 NULL。
@@time_zone	STRING	在依赖时区的 SQL 函数中使用的默认时区（如果未将显式时区指定为参数）。与其他系统变量不同，@@time_zone 可通过对任何有效时区名称使用 SET 语句来进行修改。在每个脚本的开头，@@time_zone 会以“UTC”开头。

注意：为实现向后兼容性，OPTIONS 或 FOR SYSTEM TIME AS OF 子句中使用的表达式默认使用 America/Los_Angeles 时区，而所有其他日期/时间表达式默认使用 UTC 时区。如果之前已在脚本中设置了 @@time_zone，则所选的时区将应用于所有日期/时间表达式，包括 OPTIONS 和 FOR SYSTEM TIME AS OF 子句。

除了上面显示的系统变量之外，您还可以在脚本执行期间使用 EXCEPTION 系统变量。如果未在异常处理程序中使用 EXCEPTION 系统变量，则此类变量为 NULL。以下是 EXCEPTION 系统变量。

• @@error.formatted_stack_trace
• @@error.message
• @@error.stack_trace
• @@error.statement_text

如需详细了解 EXCEPTION 系统变量，请参阅 BEGIN...EXCEPTION。

权限

在执行语句时，BigQuery 会检查访问表、模型或其他资源的权限。如果未执行语句，或者未计算表达式，则 BigQuery 不会检查执行脚本的用户是否有权访问其引用的任何资源。

在脚本中，针对每个表达式或语句的权限会单独进行验证。例如，请考虑以下脚本：

SELECT * FROM dataset_with_access.table1;
SELECT * FROM dataset_without_access.table2;

如果执行脚本的用户有权访问 table1 但无权访问 table2，则第一个查询将成功，第二个查询将失败。脚本作业本身也将失败。

安全限制条件

虽然动态 SQL 很方便，但可能会为滥用提供新的可能性。例如，执行以下查询会构成潜在的安全威胁，因为表参数可能经过不适当的过滤，允许访问非预期表，以及对非预期表执行操作。

EXECUTE IMMEDIATE CONCAT(‘SELECT * FROM ’, @employee_table);

为避免公开或泄露表中的敏感数据，或避免运行 DROP TABLE 等命令而删除表中的数据，BigQuery 的动态 SQL 脚本支持以下多种安全措施，以减少遭到 SQL 注入攻击的风险：

• 您不能执行在传入到动态 SQL 的参数中嵌入的多个 SQL 语句。
• 以下命令被限制不能动态执行：BEGIN/END、CALL、IF、LOOP、WHILE、EXECUTE IMMEDIATE。

配置字段限制

您无法为脚本设置以下查询配置字段：

• clustering
• create_disposition
• destination_table
• destination_encryption_configuration
• range_partitioning
• schema_update_options
• time_partitioning
• user_defined_function_resources
• write_disposition