Deila með

BEGIN END compound statement

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 16.3 and above

Implements a SQL Script block that can contain a sequence of SQL statements, control-of-flow statements, local variable declarations, and exception handlers.

Note

When invoking a compound statement from a notebook it must be the only statement in the cell.

Syntax

[ label : ]
      BEGIN
      [ { declare_variable | declare_condition } ; [...] ]
      [ declare_cursor ; [...] ]
      [ declare_handler ; [...] ]
      [ SQL_statement ; [...] ]
      END [ label ]

declare_variable
  DECLARE variable_name [, ...] datatype [ DEFAULT default_expr ]

declare_condition
  DECLARE condition_name CONDITION [ FOR SQLSTATE [ VALUE ] sqlstate ]

declare_cursor
  DECLARE cursor_name [ ASENSITIVE | INSENSITIVE ] CURSOR FOR query [ FOR READ ONLY ]

declare_handler
  DECLARE handler_type HANDLER FOR condition_values handler_action

handler_type
  EXIT | CONTINUE

condition_values
 { { SQLSTATE [ VALUE ] sqlstate | condition_name } [, ...] |
   { SQLEXCEPTION | NOT FOUND } [, ...] }

Prior to Databricks Runtime 17.2 you can only declare one variable at a time.

Parameters

  • label

    An optional identifier is used to qualify variables defined within the compound and to leave the compound. Both label occurrences must match, and the END label can only be specified if label: is specified.

    label must not be specified for a top level compound statement.

  • NOT ATOMIC

    Specifies that, if an SQL statement within the compound fails, previous SQL statements will not be rolled back. This is the default and only behavior.

  • declare_variable

    A local variable declaration for one or more variables

    • variable_name

      A name for the variable. The name must not be qualified, and be unique within the compound statement.

    • data_type

      Any supported data type. If data_type is omitted, you must specify DEFAULT, and the type is derived from the default_expression.

    • { DEFAULT | = } default_expression

      Defines the variable's initial value after declaration. default_expression must be castable to data_type. If no default is specified, the variable is initialized with NULL.

  • Declare_condition

    A local condition declaration

    • condition_name

      The unqualified name of the condition is scoped to the compound statement.

    • sqlstate

      A STRING literal of 5 alphanumeric characters (case insensitive) consisting of A-Z and 0..9. The SQLSTATE must not start with '00', '01', or 'XX'. Any SQLSTATE starting with '02' will be caught by the predefined NOT FOUND exception as well. If not specified, the SQLSTATE is '45000'.

  • declare_cursor

    Applies to: check marked yes Databricks Runtime 18.1 and above

    A local cursor declaration for iterating through query results.

    Note

    For DECLARE CURSOR, only syntax errors are detected and raised. The query is not executed until the cursor is opened with OPEN.

    • cursor_name: An unqualified name for the cursor, unique among cursors in this compound statement. When referencing the cursor in OPEN, FETCH, or CLOSE, you can qualify the cursor name with the compound statement label (e.g., label.my_cursor) to disambiguate in nested scopes.
    • ASENSITIVE | INSENSITIVE: Optional. Once the cursor is opened, the result set is not affected by DML changes. This is the default and only supported behavior.
    • query: The query that defines the cursor; it is executed when the cursor is opened with OPEN.

  • declare_handler

    A declaration for an error handler.

    • handler_type

      • EXIT

        Classifies the handler to exit the compound statement after the condition is handled. All cursors opened within the compound statement and nested compound statements are implicitly closed.

      • CONTINUE

        Applies to: check marked yes Databricks Runtime 18.1 and above

        Classifies the handler to continue execution after the handler completes. Execution resumes with the statement following the one that raised the condition.

    • condition_values

      Specifies to which sqlstates or conditions the handler applies. Condition values must be unique within all handlers within the compound statement. Specific condition values take precedence over SQLEXCEPTION.

    • sqlstate

      A STRING literal of 5 characters 'A'-'Z' and '0'-'9' (case insensitive).

    • condition_name

      A condition defined within this compound, an outer compound statement, or a system-defined error class.

    • SQLEXCEPTION

      Applies to any user-facing error condition.

    • NOT FOUND

      Applies to any error condition with a SQLSTATE '02' class, including the CURSOR_NO_MORE_ROWS condition (SQLSTATE '02000') raised when fetching beyond the end of a cursor result set.

    • handler_action

      A SQL statement to execute when any of the condition values occur. To add multiple statements, use a nested compound statement.

  • SQL_statement

    A SQL statement such as a DDL, DML, control statement, or compound statement. Any SELECT or VALUES statement produces a result set that the invoker can consume.

Examples

-- A compound statement with local variables, and exit hanlder and a nested compound.
> BEGIN
    DECLARE a INT DEFAULT 1;
    DECLARE b INT DEFAULT 5;
    DECLARE EXIT HANDLER FOR DIVIDE_BY_ZERO
      div0: BEGIN
        VALUES (15);
      END div0;
    SET a = 10;
    SET a = b / 0;
    VALUES (a);
END;
15
  • Last updated on