SQL scripting
Applies to: Databricks Runtime 16.3 and later
This feature is in Public Preview.
You can employ powerful procedural logic using SQL/PSM standard-based scripting syntax.
Any SQL script consists of and starts with a compound statement block (BEGIN ... END
).
A compound statement starts with a section to declare local variables, user-defined conditions, and condition handlers, which are used to catch exceptions.
This is followed by the compound statement body, which consists of:
- Flow control statements include loops over predicate expressions, FOR loops over query results, conditional logic such as IF and CASE, and means to break out loops such as LEAVE and ITERATE.
- DDL statements such as
ALTER
,CREATE
,DROP
. - DCL (Data Control) statements such as GRANT and REVOKE.
- DML statements INSERT, UPDATE, DELETE, and MERGE.
- Queries that return result sets to the invoker of the script.
- SET statements to set local variables as well as session variables.
- The EXECUTE IMMEDIATE statement.
- Nested compound statements, which provide nested scopes for variables, conditions, and condition handlers.
Passing data between the invoker and the compound statement
There are two ways to pass data to and from a SQL script:
- Use session variables to pass scalar values or small sets of arrays or maps from one SQL script to another.
- Use parameter markers to pass scalar values or small sets of arrays or map data from a notebook widget, Python, or another language to the SQL Script.
Variable scoping
Variables declared within a compound statement can be referenced in any expression within a compound statement. Databricks resolves identifiers from the innermost scope outward, following the rules described in Name Resolution. You can use the optional compound statement labels to disambiguate duplicate variable names.
Condition handling
SQL Scripting supports condition handlers, which are used to intercept and process exceptions to EXIT
processing of the SQL script.
Within the condition handler, you can RESIGNAL the original exception, SIGNAL a new exception, or exit the compound statement without an exception.
Condition handlers can be defined to handle three distinct classes of conditions:
-
One or more named conditions that can be a specific Databricks-defined error class such as
DIVIDE_BY_ZERO
or a user-declared condition. These handlers handle these specific conditions. -
One or more
SQLSTATE
s, that can be raised by Databricks or a userSIGNAL
statement. These handlers can handle any condition associated with thatSQLSTATE
. -
A generic
SQLEXCEPTION
handler can catch all conditions falling into theSQLEXCEPTION
(anySQLSTATE
which is notXX***
and not02***
).
The following are used to decide which condition handler applies to an exception. This condition handler is called the most appropriate handler:
-
A condition handler cannot apply to any statement defined in its own body or the body of any condition handler declared in the same compound statement.
-
The applicable condition handlers defined in the innermost compound statement within which the exception was raised are appropriate.
-
If more than one appropriate handler is available, the most specific handler is the most appropriate. For example, a handler on a named condition is more specific than one on a named
SQLSTATE
. A genericEXCEPTION
handler is the least specific.
Unless a handler SIGNAL
s or RESIGNAL
s a condition of its own, the outcome of a condition handler is to execute the statement following the compound statement that declared the handler to execute next.
The following is a list of supported control flow statement: