Pular para o conteúdo principal

SQL scripting

Applies to: check marked yes Databricks Runtime 16.3 and later

Preview

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 SQLSTATEs, that can be raised by Databricks or a user SIGNAL statement. These handlers can handle any condition associated with that SQLSTATE.

  • A generic SQLEXCEPTION handler can catch all conditions falling into the SQLEXCEPTION (any SQLSTATE which is not XX*** and not 02***).

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 generic EXCEPTION handler is the least specific.

Unless a handler SIGNALs or RESIGNALs 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:

Última atualização em