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.
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.
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.
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.
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;
CALL
Syntax
CALL procedure_name (procedure_argument[, …])
Description
Calls a procedure with an argument list. procedure_argument may be a variable or an expression. Spanner doesn't support stored procedures or server-side scripts. Use CALL with only the example procedures listed on this page.
The maximum depth of procedure calls is 50 frames.
Examples
The following example cancels a query with the query ID 12345.
CALL cancel_query("12345");