The GoogleSQL procedural language lets you execute multiple statements in one query as a multi-statement query. You can use a multi-statement query to:
- Run multiple statements in a sequence, with shared state.
- Automate management tasks such as creating or dropping tables.
- Implement complex logic using programming constructs such as
IF
andWHILE
.
This reference contains the statements that are part of the GoogleSQL procedural language. To learn more about how you can use this procedural language to write multi-statement queries, see Work with multi-statement queries. To learn how you can convert multi-statement queries into stored procedures, see Work with stored procedures.
DECLARE
DECLARE variable_name[, ...] [variable_type] [DEFAULT expression];
variable_name
must be a valid identifier, and variable_type
is any
GoogleSQL type.
Description
Declares a variable of the specified type. If the DEFAULT
clause is specified,
the variable is initialized with the value of the expression; if no
DEFAULT
clause is present, the variable is initialized with the value
NULL
.
If [variable_type]
is omitted then a DEFAULT
clause must be specified. The
variable’s type will be inferred by the type of the expression in the DEFAULT
clause.
Variable declarations must appear before other procedural statements, or at the
start of a BEGIN
block. Variable names are case-insensitive.
Multiple variable names can appear in a single DECLARE
statement, but only
one variable_type
and expression
.
It is an error to declare a variable with the same name as a variable declared earlier in the current block or in a containing block.
If the DEFAULT
clause is present, the value of the expression must be
coercible to the specified type. The expression may reference other variables
declared previously within the same block or a containing block.
GoogleSQL also supports system variables. You do not need to declare system variables, but you can set any of them that are not marked read-only. You can reference system variables in queries.
Examples
The following example initializes the variable x
as an
INT64
with the value NULL
.
DECLARE x INT64;
The following example initializes the variable d
as a
DATE
object with the value of the current date.
DECLARE d DATE DEFAULT CURRENT_DATE();
The following example initializes the variables x
, y
, and z
as
INT64
with the value 0.
DECLARE x, y, z INT64 DEFAULT 0;
The following example declares a variable named item
corresponding to an
arbitrary item in the schema1.products
table. The type of item
is inferred
from the table schema.
DECLARE item DEFAULT (SELECT item FROM schema1.products LIMIT 1);
SET
Syntax
SET variable_name = expression;
SET (variable_name[, ...]) = (expression[, ...]);
Description
Sets a variable to have the value of the provided expression, or sets multiple variables at the same time based on the result of multiple expressions.
The SET
statement may appear anywhere within a multi-statement query.
Examples
The following example sets the variable x
to have the value 5.
SET x = 5;
The following example sets the variable a
to have the value 4, b
to have the
value 'foo', and the variable c
to have the value false
.
SET (a, b, c) = (1 + 3, 'foo', false);
The following example assigns the result of a query to multiple variables.
First, it declares two variables, target_word
and corpus_count
; next, it
assigns the results of a
SELECT AS STRUCT
query
to the two variables. The result of the query is a single row containing a
STRUCT
with two fields; the first element is
assigned to the first variable, and the second element is assigned to the second
variable.
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;
This statement list outputs the following string:
Found 151 occurrences of "methinks" across 38 Shakespeare works
EXECUTE IMMEDIATE
Syntax
EXECUTE IMMEDIATE sql_expression [ INTO variable[, ...] ] [ USING identifier[, ...] ];
sql_expression:
{ "query_statement" | expression("query_statement") }
identifier:
{ variable | value } [ AS alias ]
Description
Executes a dynamic SQL statement on the fly.
sql_expression
: An expression that can represent one of the following:- A query statement
- An expression that you can use on a query statement
- A single DDL statement
A single DML statement
A single DCL statement
This expression cannot be a control statement like
IF
.expression
: Can be a function, conditional expression, or expression subquery.query_statement
: Represents a valid standalone SQL statement to execute. If this returns a value, theINTO
clause must contain values of the same type. You may access both system variables and values present in theUSING
clause; all other local variables and query parameters are not exposed to the query statement.INTO
clause: After the SQL expression is executed, you can store the results in one or more variables, using theINTO
clause.USING
clause: Before you execute your SQL expression, you can pass in one or more identifiers from theUSING
clause into the SQL expression. These identifiers function similarly to query parameters, exposing values to the query statement. An identifier can be a variable or a value.
You can include these placeholders in the query_statement
for identifiers
referenced in the USING
clause:
?
: The value for this placeholder is bound to an identifier in theUSING
clause by index.-- y = 1 * (3 + 2) = 5 EXECUTE IMMEDIATE "SELECT ? * (? + 2)" INTO y USING 1, 3;
@identifier
: The value for this placeholder is bound to an identifier in theUSING
clause by name. This syntax is identical to the query parameter syntax.-- y = 1 * (3 + 2) = 5 EXECUTE IMMEDIATE "SELECT @a * (@b + 2)" INTO y USING 1 as a, 3 as b;
Here are some additional notes about the behavior of the EXECUTE IMMEDIATE
statement:
EXECUTE IMMEDIATE
is restricted from being executed dynamically as a nested element. This meansEXECUTE IMMEDIATE
cannot be nested in anotherEXECUTE IMMEDIATE
statement.- If an
EXECUTE IMMEDIATE
statement returns results, then those results become the result of the entire statement and any appropriate system variables are updated. - The same variable can appear in both the
INTO
andUSING
clauses. query_statement
can contain a single parsed statement that contains other statements (for example, BEGIN...END)- If zero rows are returned from
query_statement
, including from zero-row value tables, all variables in theINTO
clause are set to NULL. - If one row is returned from
query_statement
, including from zero-row value tables, values are assigned by position, not variable name. - If an
INTO
clause is present, an error is thrown if you attempt to return more than one row fromquery_statement
.
Examples
In this example, we create a table of books and populate it with data. Note the different ways that you can reference variables, save values to variables, and use expressions.
-- 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 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
Syntax
BEGIN sql_statement_list END;
Description
BEGIN
initiates a block of statements where declared variables exist only
until the corresponding END
. sql_statement_list
is a list of zero or more
SQL statements ending with semicolons.
Variable declarations must appear at the start of the block, prior to other types of statements. Variables declared inside a block may only be referenced within that block and in any nested blocks. It is an error to declare a variable with the same name as a variable declared in the same block or an outer block.
There is a maximum nesting level of 50 for blocks and conditional statements
such as BEGIN
/END
, IF
/ELSE
/END IF
, and WHILE
/END WHILE
.
BEGIN
/END
is restricted from being executed dynamically as a nested element.
You can use a label with this statement. To learn more, see Labels.
Examples
The following example declares a variable x
with the default value 10; then,
it initiates a block, in which a variable y
is assigned the value of x
,
which is 10, and returns this value; next, the END
statement ends the
block, ending the scope of variable y
; finally, it returns the value of x
.
DECLARE x INT64 DEFAULT 10; BEGIN DECLARE y INT64; SET y = x; SELECT y; END; SELECT x;
BEGIN...EXCEPTION...END
Syntax
BEGIN sql_statement_list EXCEPTION WHEN ERROR THEN sql_statement_list END;
Description
BEGIN...EXCEPTION
executes a block of statements. If any of the statements
encounter an error, the remainder of the block is skipped and the statements in
the EXCEPTION
clause are executed.
Within the EXCEPTION
clause, you can access details about the error using the
following EXCEPTION
system variables:
Name | Type | Description |
---|---|---|
@@error.formatted_stack_trace |
STRING |
The content of @@error.stack_trace expressed as a human readable string. This value is intended for display purposes, and is subject to change without notice. Programmatic access to an error's stack trace should use @@error.stack_trace instead. |
@@error.message |
STRING |
Specifies a human-readable error message. |
@@error.stack_trace |
See 1. | Each element of the array corresponds to a statement or procedure call executing at the time of the error, with the currently executing stack frame appearing first. The meaning of each field is defined as follows:
|
@@error.statement_text |
STRING |
Specifies the text of the statement which caused the error. |
1 The type for @@error.stack_trace
is
ARRAY<STRUCT<line INT64, column INT64, filename STRING, location STRING>>
.
As BigQuery reserves the right to revise error messages at any time,
consumers of @@error.message
should not rely on error messages remaining the
same or following any particular pattern. Do not obtain error location
information by extracting text out of the error message — use
@@error.stack_trace
and @@error.statement_text
instead.
To handle exceptions that are thrown (and not handled) by an exception handler itself, you must wrap the block in an outer block with a separate exception handler.
The following shows how to use an outer block with a separate exception handler:
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...EXCEPTION
blocks also support DECLARE
statements, just like any
other BEGIN
block. Variables declared in a BEGIN
block are valid only in
the BEGIN
section, and may not be used in the block’s exception handler.
You can use a label with this statement. To learn more, see Labels.
Examples
In this example, when the division by zero error occurs, instead of
stopping the entire multi-statement query, GoogleSQL will stop
schema1.proc1()
and schema1.proc2()
and execute the SELECT
statement in
the exception handler.
CREATE OR REPLACE PROCEDURE schema1.proc1() BEGIN SELECT 1/0; END; CREATE OR REPLACE PROCEDURE schema1.proc2() BEGIN CALL schema1.proc1(); END; BEGIN CALL schema1.proc2(); EXCEPTION WHEN ERROR THEN SELECT @@error.message, @@error.stack_trace, @@error.statement_text, @@error.formatted_stack_trace; END;
When the exception handler runs, the variables will have the following values:
Variable | Value |
---|---|
@@error.message |
"Query error: division by zero: 1 / 0 at <project>.schema1.proc1:2:3]" |
@@error.stack_trace |
[ STRUCT(2 AS line, 3 AS column, NULL AS filename, "<project>.schema1.proc1:2:3" AS location), STRUCT(2 AS line, 3 AS column, NULL AS filename, "<project>.schema1.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>.schema1.proc1[2:3]\nAt <project>.schema1.proc2[2:3]\nAt [10:3]" |
CASE
Syntax
CASE WHEN boolean_expression THEN sql_statement_list [...] [ELSE sql_statement_list] END CASE;
Description
Executes the THEN sql_statement_list
where the boolean expression is true,
or the optional ELSE sql_statement_list
if no conditions match.
CASE
can have a maximum of 50 nesting levels.
CASE
is restricted from being executed dynamically as a nested element. This
means CASE
cannot be nested in an EXECUTE IMMEDIATE
statement.
Examples
In this example, a search if conducted for the target_product_ID
in the
products_a
table. If the ID is not found there, a search is conducted for
the ID in the products_b
table. If the ID is not found there, the statement in
the ELSE
block is executed.
DECLARE target_product_id INT64 DEFAULT 103; CASE WHEN EXISTS(SELECT 1 FROM schema.products_a WHERE product_id = target_product_id) THEN SELECT 'found product in products_a table'; WHEN EXISTS(SELECT 1 FROM schema.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
Syntax
CASE search_expression WHEN expression THEN sql_statement_list [...] [ELSE sql_statement_list] END CASE;
Description
Executes the first sql_statement_list
where the search expression is matches
a WHEN
expression. The search_expression
is evaluated once and then
tested against each WHEN
expression for equality until a match is found.
If no match is found, then the optional ELSE
sql_statement_list
is executed.
CASE
can have a maximum of 50 nesting levels.
CASE
is restricted from being executed dynamically as a nested element. This
means CASE
cannot be nested in an EXECUTE IMMEDIATE
statement.
Examples
The following example uses the product ID as the search expression. If the
ID is 1
, 'Product one'
is returned. If the ID is 2
, 'Product two'
is returned. If the ID is anything else, Invalid product
is returned.
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
Syntax
IF condition THEN [sql_statement_list]
[ELSEIF condition THEN sql_statement_list]
[...]
[ELSE sql_statement_list]
END IF;
Description
Executes the first sql_statement_list
where the condition is true, or the
optional ELSE
sql_statement_list
if no conditions match.
There is a maximum nesting level of 50 for blocks and conditional statements
such as BEGIN
/END
, IF
/ELSE
/END IF
, and WHILE
/END WHILE
.
IF
is restricted from being executed dynamically as a nested element. This
means IF
cannot be nested in an EXECUTE IMMEDIATE
statement.
Examples
The following example declares a INT64 variable
target_product_id
with a default value of 103; then, it checks whether the
table schema.products
contains a row with the product_id
column matches
the value of target_product_id
; if so, it outputs a string stating that the
product has been found, along with the value of default_product_id
; if not,
it outputs a string stating that the product has not been found, also with the
value of default_product_id
.
DECLARE target_product_id INT64 DEFAULT 103;
IF EXISTS(SELECT 1 FROM schema.products
WHERE product_id = target_product_id) THEN
SELECT CONCAT('found product ', CAST(target_product_id AS STRING));
ELSEIF EXISTS(SELECT 1 FROM schema.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;
Labels
Syntax
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;
Description
A BREAK or CONTINUE statement with a label provides an unconditional jump to the end of the block or loop associated with that label. To use a label with a block or loop, the label must appear at the beginning of the block or loop, and optionally at the end.
- A label name may consist of any GoogleSQL identifier, including the use of backticks to include reserved characters or keywords.
Multipart path names can be used, but only as quoted identifiers.
`foo.bar`: BEGIN ... END -- Works foo.bar: BEGIN ... END -- Does not work
Label names are case-insensitive.
Each stored procedure has an independent store of label names. For example, a procedure may redefine a label already used in a calling procedure.
A loop or block may not repeat a label name used in an enclosing loop or block.
Repeated label names are allowed in non-overlapping parts in procedural statements.
A label and variable with the same name is allowed.
When the
BREAK
,LEAVE
,CONTINUE
, orITERATE
statement specifies a label, it exits or continues the loop matching the label name, rather than always picking the innermost loop.
Examples
You can only reference a block or loop while inside of it.
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;
Repeated label names are allowed in non-overlapping parts of the multi-statement query. This works:
label_1: BEGIN BREAK label_1; END; label_2: BEGIN BREAK label_2; END; label_1: BEGIN BREAK label_1; END;
A loop or block may not repeat a label name used in an enclosing loop or block. This throws an error:
label_1: BEGIN label_1: BEGIN -- Error BREAK label_1; END; END;
A label and variable can have same name. This works:
label_1: BEGIN DECLARE label_1 INT64; BREAK label_1; END;
The END
keyword terminating a block or loop may specify a label name, but
this is optional. These both work:
label_1: BEGIN BREAK label_1; END label_1;
label_1: BEGIN BREAK label_1; END;
You can't have a label at the end of a block or loop if there isn't a label at the beginning of the block or loop. This throws an error:
BEGIN BREAK label_1; END label_1;
In this example, the BREAK
and CONTINUE
statements target the outer
label_1: LOOP
, rather than the inner WHILE x < 1 DO
loop:
label_1: LOOP WHILE x < 1 DO IF y < 1 THEN CONTINUE label_1; ELSE BREAK label_1; END WHILE; END LOOP label_1
A BREAK
, LEAVE
, or CONTINUE
, or ITERATE
statement that specifies a label
that does not exist throws an error:
WHILE x < 1 DO
BREAK label_1; -- Error
END WHILE;
Exiting a block from within the exception handler section is allowed:
label_1: BEGIN
SELECT 1;
EXCEPTION WHEN ERROR THEN
BREAK label_1;
SELECT 2; -- Unreached
END;
CONTINUE
cannot be used with a block label. This throws an error:
label_1: BEGIN SELECT 1; CONTINUE label_1; -- Error SELECT 2; END;
Loops
LOOP
Syntax
LOOP sql_statement_list END LOOP;
Description
Executes sql_statement_list
until a BREAK
or LEAVE
statement exits the
loop. sql_statement_list
is a list of zero or more SQL statements ending with
semicolons.
LOOP
is restricted from being executed dynamically as a nested element. This
means LOOP
cannot be nested in an EXECUTE IMMEDIATE
statement.
You can use a label with this statement. To learn more, see Labels.
Examples
The following example declares a variable x
with the default value 0; then,
it uses the LOOP
statement to create a loop that executes until the variable
x
is greater than or equal to 10; after the loop exits, the example
outputs the value of x
.
DECLARE x INT64 DEFAULT 0; LOOP SET x = x + 1; IF x >= 10 THEN LEAVE; END IF; END LOOP; SELECT x;
This example outputs the following:
/*----*
| x |
+----+
| 10 |
*----*/
REPEAT
Syntax
REPEAT sql_statement_list UNTIL boolean_condition END REPEAT;
Description
Repeatedly executes a list of zero or more SQL statements until the
boolean condition at the end of the list is TRUE
. The boolean condition
must be an expression. You can exit this loop early with the BREAK
or LEAVE
statement.
REPEAT
is restricted from being executed dynamically as a nested element. This
means REPEAT
cannot be nested in an EXECUTE IMMEDIATE
statement.
You can use a label with this statement. To learn more, see Labels.
Examples
The following example declares a variable x
with the default value 0
; then,
it uses the REPEAT
statement to create a loop that executes until the variable
x
is greater than or equal to 3
.
DECLARE x INT64 DEFAULT 0; REPEAT SET x = x + 1; SELECT x; UNTIL x >= 3 END REPEAT;
This example outputs the following:
/*---*
| x |
+---+
| 1 |
*---*/
/*---*
| x |
+---+
| 2 |
*---*/
/*---*
| x |
+---+
| 3 |
*---*/
WHILE
Syntax
WHILE boolean_expression DO
sql_statement_list
END WHILE;
There is a maximum nesting level of 50 for blocks and conditional statements
such as BEGIN
/END
, IF
/ELSE
/END IF
, and WHILE
/END WHILE
.
Description
While boolean_expression
is true, executes sql_statement_list
.
boolean_expression
is evaluated for each iteration of the loop.
WHILE
is restricted from being executed dynamically as a nested element. This
means WHILE
cannot be nested in an EXECUTE IMMEDIATE
statement.
You can use a label with this statement. To learn more, see Labels.
BREAK
Syntax
BREAK;
Description
Exit the current loop.
It is an error to use BREAK
outside of a loop.
You can use a label with this statement. To learn more, see Labels.
Examples
The following example declares two variables, heads
and heads_count
; next,
it initiates a loop, which assigns a random boolean value to heads
, then
checks to see whether heads
is true; if so, it outputs "Heads!" and increments
heads_count
; if not, it outputs "Tails!" and exits the loop; finally, it
outputs a string stating how many times the "coin flip" resulted in "heads."
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');
LEAVE
Synonym for BREAK
.
CONTINUE
Syntax
CONTINUE;
Description
Skip any following statements in the current loop and return to the start of the loop.
It is an error to use CONTINUE
outside of a loop.
You can use a label with this statement. To learn more, see Labels.
Examples
The following example declares two variables, heads
and heads_count
; next,
it initiates a loop, which assigns a random boolean value to heads
, then
checks to see whether heads
is true; if so, it outputs "Heads!", increments
heads_count
, and restarts the loop, skipping any remaining statements; if not,
it outputs "Tails!" and exits the loop; finally, it outputs a string stating how
many times the "coin flip" resulted in "heads."
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
Synonym for CONTINUE
.
FOR...IN
Syntax
FOR loop_variable_name IN (table_expression)
DO
sql_expression_list
END FOR;
Description
Loops over every row in table_expression
and assigns the row to
loop_variable_name
. Inside each loop, the SQL statements in
sql_expression_list
are executed using the current value of
loop_variable_name
.
The value of table_expression
is evaluated once at the start of the loop. On
each iteration, the value of loop_variable_name
is a STRUCT
that contains
the top-level columns of the table expression as fields. The order in which
values are assigned to loop_variable_name
is not defined, unless the table
expression has a top-level ORDER BY
clause or UNNEST
array operator.
The scope of loop_variable_name
is the body of the loop. The name of
loop_variable_name
cannot conflict with other variables within the same
scope.
You can use a label with this statement. To learn more, see Labels.
Example
FOR record IN (SELECT word, word_count FROM bigquery-public-data.samples.shakespeare LIMIT 5) DO SELECT record.word, record.word_count; END FOR;
Transactions
BEGIN TRANSACTION
Syntax
BEGIN [TRANSACTION];
Description
Begins a transaction.
The transaction ends when a COMMIT TRANSACTION
or
ROLLBACK TRANSACTION
statement is reached. If
execution ends before reaching either of these statements,
an automatic rollback occurs.
For more information about transactions in BigQuery, see Multi-statement transactions.
Example
The following example performs a transaction that selects rows from an existing table into a temporary table, deletes those rows from the original table, and merges the temporary table into another table.
BEGIN TRANSACTION; -- Create a temporary table of new arrivals from warehouse #1 CREATE TEMP TABLE tmp AS SELECT * FROM myschema.NewArrivals WHERE warehouse = 'warehouse #1'; -- Delete the matching records from the original table. DELETE myschema.NewArrivals WHERE warehouse = 'warehouse #1'; -- Merge the matching records into the Inventory table. MERGE myschema.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
Syntax
COMMIT [TRANSACTION];
Description
Commits an open transaction. If no open transaction is in progress, then the statement fails.
For more information about transactions in BigQuery, see Multi-statement transactions.
Example
BEGIN TRANSACTION; -- SQL statements for the transaction go here. COMMIT TRANSACTION;
ROLLBACK TRANSACTION
Syntax
ROLLBACK [TRANSACTION];
Description
Rolls back an open transaction. If there is no open transaction in progress, then the statement fails.
For more information about transactions in BigQuery, see Multi-statement transactions.
Example
The following example rolls back a transaction if an error occurs during the transaction. To illustrate the logic, the example triggers a divide-by-zero error after inserting a row into a table. After these statements run, the table is unaffected.
BEGIN BEGIN TRANSACTION; INSERT INTO myschema.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
Syntax
RAISE [USING MESSAGE = message];
Description
Raises an error, optionally using the specified error message when USING
MESSAGE = message
is supplied.
When USING MESSAGE
is not supplied
The RAISE
statement must only be used within an EXCEPTION
clause. The
RAISE
statement will re-raise the exception that was caught, and preserve the
original stack trace.
When USING MESSAGE
is supplied
If the RAISE
statement is contained within the BEGIN
section of a
BEGIN...EXCEPTION
block:
- The handler will be invoked.
The value of
@@error.message
will exactly match themessage
string supplied (which may beNULL
ifmessage
isNULL
).The stack trace will be set to the
RAISE
statement.
If the RAISE
statement is not contained within the BEGIN
section of a
BEGIN...EXCEPTION
block, the RAISE
statement will stop execution of the
multi-statement query with the error message supplied.
RETURN
RETURN
stops execution of the multi-statements query.
CALL
Syntax
CALL procedure_name (procedure_argument[, …])
Description
Calls a procedure with an argument list.
procedure_argument
may be a variable or an expression.
For OUT
or INOUT
arguments, a variable passed as an argument must have the
proper GoogleSQL type. The same variable may not appear
multiple times as an OUT
or INOUT
argument in the procedure's argument list.
The maximum depth of procedure calls is 50 frames.
CALL
is restricted from being executed dynamically as a nested element. This
means CALL
cannot be nested in an EXECUTE IMMEDIATE
statement.
Examples
The following example declares a variable retCode
. Then, it calls the
procedure updateSomeTables
in the schema mySchema
, passing the arguments
'someAccountId'
and retCode
. Finally, it returns the value of retCode
.
DECLARE retCode INT64;
-- Procedure signature: (IN account_id STRING, OUT retCode INT64)
CALL mySchema.UpdateSomeTables('someAccountId', retCode);
SELECT retCode;