SQL Scripting

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, cursors, 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.
• DML statements INSERT.
• 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.
• Cursor processing with OPEN, FETCH, and CLOSE statements.
• Nested compound statements, which provide nested scopes for variables, cursors, 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 and cursor scoping

Variables declared within a compound statement can be referenced in any expression within a compound statement. Spark 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.

Cursors declared within a compound statement can be referenced in OPEN, FETCH, and CLOSE statements within that compound statement. Like variables, you can use optional compound statement labels to disambiguate duplicate cursor names in nested scopes.

Condition handling

SQL Scripting supports condition handlers, which are used to intercept and process exceptions to either EXIT the compound statement or CONTINUE execution.

Condition handlers can be defined to handle three distinct classes of conditions:

• One or more named conditions that can be a specific Spark-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 Spark. 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***).

• A NOT FOUND handler catches all conditions with SQLSTATE class '02xxx', which includes the CURSOR_NO_MORE_ROWS condition raised when fetching beyond the end of a cursor’s result set.

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.

The outcome of a condition handler is as follows:

• An EXIT handler executes its statement, then exits the compound statement that declared the handler. All cursors opened within that compound statement and nested compound statements are implicitly closed.

• A CONTINUE handler executes its statement, then continues execution with the statement following the one that raised the condition.

The following is a list of supported control flow statements:

• CASE
• compound statement
• FOR
• IF
• ITERATE
• LEAVE
• LOOP
• REPEAT
• WHILE

The following is a list of cursor statements:

• OPEN
• FETCH
• CLOSE