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" );
