脚本语句

借助脚本,您可以在一个请求中向 BigQuery 发送多个语句。脚本可以使用变量以及控制流语句,例如 IFWHILE。例如,您可以声明一个变量,为其赋值,然后在第三个语句中引用该变量。

如需详细了解变量和临时表规则、调试和编写脚本的过程,请参阅脚本和存储过程

DECLARE

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

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

说明

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

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

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

多个变量名称可以出现在一个 DECLARE 语句中,但只能出现一个 variable_typeexpression

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

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

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

示例

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

DECLARE x INT64;

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

DECLARE d DATE DEFAULT CURRENT_DATE();

以下示例将变量 xyz 初始化为使用值 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_wordcorpus_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 语句。不能是 IF 之类的控制语句。
  • 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 不能嵌套在其他 EXECUTE IMMEDIATE 语句中。
  • 如果 EXECUTE IMMEDIATE 语句返回结果,则这些结果将成为整个语句的结果,并且所有相应的系统变量会进行更新。
  • 同一个变量可以同时出现在 INTOUSING 子句中。
  • 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...END

语法

BEGIN
  sql_statement_list
END;

说明

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

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

块和条件语句(例如 BEGIN/ENDIF/ELSE/END IFWHILE/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...END

语法

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 指定导致错误的语句文本。

1 @@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]"

CASE

语法

CASE
  WHEN boolean_expression THEN sql_statement_list
  [...]
  [ELSE sql_statement_list]
END CASE;

说明

如果布尔表达式为 true,则执行第一个 sql_statement_list;如果没有条件匹配,则执行可选的 ELSE sql_statement_list

CASE 最多可以有 50 个嵌套级别。

CASE 被限制不能作为嵌套元素动态执行。这意味着 CASE 不能嵌套在 EXECUTE IMMEDIATE 语句中。

示例

以下脚本会在 products_a 表中搜索 target_product_ID。如果未找到 ID,脚本会在 products_b 表中搜索该 ID。如果未找到 ID,则执行 ELSE 块中的语句。

DECLARE target_product_id INT64 DEFAULT 103;
CASE
  WHEN
    EXISTS(SELECT 1 FROM dataset.products_a WHERE product_id = target_product_id)
    THEN SELECT 'found product in products_a table';
  WHEN
    EXISTS(SELECT 1 FROM dataset.products_b WHERE product_id = target_product_id)
    THEN SELECT 'found product in products_b table';
  ELSE
    SELECT 'did not find product';
END CASE;

CASE search_expression

语法

CASE search_expression
  WHEN expression THEN sql_statement_list
  [...]
  [ELSE sql_statement_list]
END CASE;

说明

执行搜索表达式与 WHEN 表达式匹配的第一个 sql_statement_listsearch_expression 会计算一次,然后针对每个 WHEN 表达式测试其是否相等,直到找到匹配项。如果未找到匹配项,则执行可选的 ELSE sql_statement_list

CASE 最多可以有 50 个嵌套级别。

CASE 被限制不能作为嵌套元素动态执行。这意味着 CASE 不能嵌套在 EXECUTE IMMEDIATE 语句中。

示例

以下示例使用产品 ID 作为搜索表达式。如果 ID 为 1,则返回 'Product one'。如果 ID 为 2,则返回 'Product two'。如果 ID 为任何其他值,则返回 Invalid product

DECLARE product_id INT64 DEFAULT 1;
CASE product_id
  WHEN 1 THEN
    SELECT CONCAT('Product one');
  WHEN 2 THEN
    SELECT CONCAT('Product two');
  ELSE
    SELECT CONCAT('Invalid product');
END CASE;

IF

语法

IF 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/ENDIF/ELSE/END IFWHILE/END WHILE)的最大嵌套级别为 50。

IF 被限制不能作为嵌套元素动态执行。这意味着 IF 不能嵌套在 EXECUTE IMMEDIATE 语句中。

示例

以下示例声明一个包含默认值 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;

标签

语法

label_name: BEGIN
  block_statement_list
END [label_name];
label_name: LOOP
  loop_statement_list
END LOOP [label_name];
label_name: WHILE condition DO
  loop_statement_list
END WHILE [label_name];
label_name: FOR variable IN query DO
  loop_statement_list
END FOR [label_name];
label_name: REPEAT
  loop_statement_list
  UNTIL boolean_condition
END REPEAT [label_name];
block_statement_list:
  { statement | break_statement_with_label }[, ...]

loop_statement_list:
  { statement | break_continue_statement_with_label }[, ...]

break_statement_with_label:
  { BREAK | LEAVE } label_name;

break_continue_statement_with_label:
  { BREAK | LEAVE | CONTINUE | ITERATE } label_name;

说明

带有标签的 BREAK 或 CONTINUE 语句可无条件跳转到与该标签关联的块或循环的末尾。如需将标签用于块或循环,该标签必须出现在块或循环的开头,可根据需要出现在末尾。

  • 标签名称可以包含任何 BigQuery 标识符,包括使用反引号来包含保留字符或关键字。
  • 多部分路径名称可用作且仅可用作带英文引号的标识符。

    `foo.bar`: BEGIN ... END -- Works
    foo.bar: BEGIN ... END -- Does not work
    
  • 标签名称不区分大小写。

  • 每个存储过程都有一个独立的标签名称存储区。例如,一个过程可能会重新定义已在调用过程中使用的标签。

  • 循环或块不得重复封装循环或块中使用的标签名称。

  • 允许在脚本的非重叠部分使用重复的标签名称。

  • 允许使用同名的标签和变量。

  • BREAKLEAVECONTINUEITERATE 语句指定标签时,它会退出或继续与标签名称匹配的循环,而不是始终选择最内层循环。

示例

只有在块或循环内部时才能引用该块或该循环。

label_1: BEGIN
  SELECT 1;
  BREAK label_1;
  SELECT 2; -- Unreached
END;
label_1: LOOP
  BREAK label_1;
END LOOP label_1;

WHILE x < 1 DO
  CONTINUE label_1; -- Error
END WHILE;

允许在脚本的非重叠部分使用重复的标签名称。以下内容有效:

label_1: BEGIN
  BREAK label_1;
END;

label_2: BEGIN
  BREAK label_2;
END;

label_1: BEGIN
  BREAK label_1;
END;

循环或块不得重复封装循环或块中使用的标签名称。这会引发错误:

label_1: BEGIN
   label_1: BEGIN -- Error
     BREAK label_1;
   END;
END;

标签和变量可以具有相同的名称。以下内容有效:

label_1: BEGIN
   DECLARE label_1 INT64;
   BREAK label_1;
END;

终止块或循环的 END 关键字可以指定标签名称,但此操作是可选的。两者都有效:

label_1: BEGIN
  BREAK label_1;
END label_1;
label_1: BEGIN
  BREAK label_1;
END;

如果块或循环的开头没有标签,则块或循环末尾不能有标签。这会引发错误:

BEGIN
  BREAK label_1;
END label_1;

在此示例中,BREAKCONTINUE 语句针对外部 label_1: LOOP,不针对内部 WHILE x < 1 DO 循环:

label_1: LOOP
  WHILE x < 1 DO
    IF y < 1 THEN
      CONTINUE label_1;
    ELSE
      BREAK label_1;
  END WHILE;
END LOOP label_1

如果 BREAKLEAVECONTINUEITERATE 语句指定了不存在的标签,则会引发错误:

WHILE x < 1 DO
  BREAK label_1; -- Error
END WHILE;

允许从异常处理程序部分内部退出某个块:

label_1: BEGIN
  SELECT 1;
  EXCEPTION WHEN ERROR THEN
    BREAK label_1;
    SELECT 2; -- Unreached
END;

CONTINUE 不能与块标签一起使用。这会引发错误:

label_1: BEGIN
  SELECT 1;
  CONTINUE label_1; -- Error
  SELECT 2;
END;

循环

LOOP

语法

LOOP
  sql_statement_list
END LOOP;

说明

执行 sql_statement_list,直到 BREAKLEAVE 语句退出循环。sql_statement_list 列表包含以分号结尾的零个或多个 SQL 语句。

LOOP 被限制不能作为嵌套元素动态执行。 这意味着 LOOP 不能嵌套在 EXECUTE IMMEDIATE 语句中。

您可在此语句中使用标签。如需了解详情,请参阅标签

示例

以下示例首先声明一个使用默认值 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 |
+----+

REPEAT

语法

REPEAT
  sql_statement_list
  UNTIL boolean_condition
END REPEAT;

说明

重复执行零个或零个以上 SQL 语句的列表,直到列表末尾的布尔型条件为 TRUE 为止。布尔型条件必须是表达式。您可以使用 BREAKLEAVE 语句尽早退出此循环。

REPEAT 被限制不能作为嵌套元素动态执行。这意味着 REPEAT 不能嵌套在 EXECUTE IMMEDIATE 语句中。

您可在此语句中使用标签。如需了解详情,请参阅标签

示例

以下示例声明一个包含默认值 0 的变量 x;然后,它使用 REPEAT 语句创建一个循环,该变量在变量 x 大于或等于 3 之前一直执行。

DECLARE x INT64 DEFAULT 0;

REPEAT
  SET x = x + 1;
  SELECT x;
  UNTIL x >= 3
END REPEAT;

此示例输出以下内容:

+---+
| x |
+---+
| 1 |
+---+

+---+
| x |
+---+
| 2 |
+---+

+---+
| x |
+---+
| 3 |
+---+

WHILE

语法

WHILE boolean_expression DO
  sql_statement_list
END WHILE;

块和条件语句(例如 BEGIN/ENDIF/ELSE/END IFWHILE/END WHILE)的最大嵌套级别为 50。

说明

boolean_expression 为 true 时,执行 sql_statement_list。 为循环的每次迭代计算 boolean_expression

WHILE 被限制不能作为嵌套元素动态执行。 这意味着 WHILE 不能嵌套在 EXECUTE IMMEDIATE 语句中。

您可在此语句中使用标签。如需了解详情,请参阅标签

BREAK

语法

BREAK;

说明

退出当前循环。

不能在循环外使用 BREAK

您可在此语句中使用标签。如需了解详情,请参阅标签

示例

以下示例首先声明 headsheads_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

您可在此语句中使用标签。如需了解详情,请参阅标签

示例

以下示例声明 headsheads_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

FOR...IN

语法

FOR loop_variable_name IN (table_expression)
DO
  sql_expression_list
END FOR;

说明

循环遍历 table_expression 中的每一行,并将行分配给 loop_variable_name。在每个循环内,sql_expression_list 中的 SQL 语句使用 loop_variable_name 的当前值执行。

table_expression 的值在循环开始时计算一次。在每次迭代中,loop_variable_name 的值为 STRUCT,其中包含表表达式的顶级列作为字段。除非表表达式具有顶级 ORDER BY 子句或 UNNEST 数组运算符,否则分配给 loop_variable_name 的值的顺序未定义。

loop_variable_name 的范围是循环的正文。loop_variable_name 的名称不能与同一范围内的其他变量冲突。

您可在此语句中使用标签。如需了解详情,请参阅标签

示例

FOR record IN
  (SELECT word, word_count
   FROM bigquery-public-data.samples.shakespeare
   LIMIT 5)
DO
  SELECT record.word, record.word_count;
END FOR;

事务

BEGIN TRANSACTION

语法

BEGIN [TRANSACTION];

说明

开始一个事务。

当达到 COMMIT TRANSACTIONROLLBACK TRANSACTION 语句时,事务结束。如果脚本在达到其中任何一个语句之前结束,则会自动回滚。

如需详细了解 BigQuery 中的事务,请参阅多语句事务

示例

以下示例执行了一个事务,该事务从现有表中选择行并复制到临时表中,从原始表中删除这些行,并将临时表合并到另一个表中。

BEGIN TRANSACTION;

-- Create a temporary table of new arrivals from warehouse #1
CREATE TEMP TABLE tmp AS
SELECT * FROM mydataset.NewArrivals WHERE warehouse = 'warehouse #1';

-- Delete the matching records from the original table.
DELETE mydataset.NewArrivals WHERE warehouse = 'warehouse #1';

-- Merge the matching records into the Inventory table.
MERGE mydataset.Inventory AS I
USING tmp AS T
ON I.product = T.product
WHEN NOT MATCHED THEN
 INSERT(product, quantity, supply_constrained)
 VALUES(product, quantity, false)
WHEN MATCHED THEN
 UPDATE SET quantity = I.quantity + T.quantity;

DROP TABLE tmp;

COMMIT TRANSACTION;

COMMIT TRANSACTION

语法

COMMIT [TRANSACTION];

说明

提交未完成的事务。如果没有正在进行的未完成事务,则语句将失败。

如需详细了解 BigQuery 中的事务,请参阅多语句事务

示例

BEGIN TRANSACTION;

-- SQL statements for the transaction go here.

COMMIT TRANSACTION;

ROLLBACK TRANSACTION

语法

ROLLBACK [TRANSACTION];

说明

回滚未完成的事务。如果没有正在进行的未完成事务,则语句将失败。

如需详细了解 BigQuery 中的事务,请参阅多语句事务

示例

以下示例会在事务过程中发生错误时回滚事务。为说明逻辑,该示例在将表中插入行后触发了除数为零错误。这些语句运行后,表不受影响。

BEGIN

  BEGIN TRANSACTION;
  INSERT INTO mydataset.NewArrivals
    VALUES ('top load washer', 100, 'warehouse #1');
  -- Trigger an error.
  SELECT 1/0;
  COMMIT TRANSACTION;

EXCEPTION WHEN ERROR THEN
  -- Roll back the transaction inside the exception handler.
  SELECT @@error.message;
  ROLLBACK TRANSACTION;
END;

RAISE

语法

RAISE [USING MESSAGE = message];

说明

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

未提供 USING MESSAGE

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

提供 USING MESSAGE

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

  • 则会调用处理程序。
  • @@error.message 的值将与提供的 message 字符串完全匹配(如果 messageNULL,则该值可能为 NULL)。

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

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

RETURN

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

CALL

语法

CALL procedure_name (procedure_argument[, …])

说明

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

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

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

CALL 被限制不能作为嵌套元素动态执行。 这意味着 CALL 不能嵌套在 EXECUTE IMMEDIATE 语句中。

示例

以下示例声明一个变量 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。在脚本的上下文中,这将返回负责当前语句的作业,而不是整个脚本。
@@dataset_id STRING 当前项目中默认数据集的 ID。 如果未在查询中为项目指定数据集,则会使用此项。 您可以使用 SET 语句将 @@dataset_id 分配给当前项目中的其他数据集 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 函数中使用的默认时区(如果未将时区指定为参数)。 可通过对任何有效时区名称使用 SET 语句来修改 @@time_zone。 在每个脚本的开头,@@time_zone 会以“UTC”开头。

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

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

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

系统过程

BigQuery 支持以下系统过程。

BQ.JOBS.CANCEL

语法

CALL BQ.JOBS.CANCEL(job);

说明

取消正在运行的作业。

将作业指定为 '[project_id.]job_id' 格式的字符串。如果您从作业之外的其他项目运行此系统过程,则还必须包含项目 ID。您必须在作业所在的区域中运行该过程。

如需了解详情,请参阅取消作业

BQ.REFRESH_MATERIALIZED_VIEW

语法

CALL BQ.REFRESH_MATERIALIZED_VIEW(view_name);

说明

刷新具体化视图。

将具体化视图的名称指定为 '[project_id.]dataset.table' 格式的字符串。如果您从具体化视图之外的其他项目运行此系统过程,则还必须包含项目 ID。

如需了解详情,请参阅手动刷新

权限

在执行语句时,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/ENDCALLCASEIFLOOPWHILEEXECUTE IMMEDIATE

配置字段限制

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

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