SQL Procedural Control Flow

NuoDB SQL supports the following execution control flow statements for use in SQL stored procedures, user defined functions (UDFs), or triggers.

EXECUTE ...;    

CALL ...;

FOR SELECT ...; sql_code_block END_FOR;
FOR INSERT INTO ...; sql_code_block END_FOR;  
FOR EXECUTE ...; sql_code_block END_FOR;


RETURN [ variable_expression | ( sql_query_statementdynamic_sql_statement ) ];

THROW messagevariable_expression | 
      ( sql_query_statement | dynamic_sql_statement );


EXECUTE and CALL statements will invoke a stored procedure. See EXECUTE, CALL for more information.

The FOR SELECT statement loop executes the SELECT statement (see SELECT) and then invokes the inner sql_code_block once per each returned row. In the SELECT column_list, each column name must be unique in the scope of the sql_code_block of the FOR SELECT loop. This ensures a set of unique variable names. If a column name is not unique in this scope, then you must assign a unique alias to it by using AS. The variable name for this column will be its assigned alias. See Example 2.

The FOR INSERT statement executes the INSERT statement (see INSERT) and retrieves the list of the IDs the database automatically generated for the inserted rows. Then, it invokes the inner statements once per each ID. Use of the FOR INSERT statement is only appropriate with tables that have a column defined as GENERATED ALWAYS AS IDENTITY. Statements within the FOR INSERT statement loop execute only upon the return of the system generated sequence number.

The FOR EXECUTE statement invokes the specified stored procedure, and then invokes the inner sql_code_block once for each row in the returned table.

The BREAK statement can be used to terminate a loop early, and continues the execution from the statement following the closest END_IF, END_FOR, or END_WHILE.

The CONTINUE statement can be used within a WHILE, FOR SELECT, FOR INSERT, or FOR EXECUTE statement. It will continue with the next iteration through the WHILE or FOR loop, that is, it will transfer program control to the next iteration of the loop without executing any statements after the CONTINUE statement.

The RETURN statement can be used to terminate a stored procedure, UDF, or trigger early, with a success exit code. Statements after the RETURN statement do not execute. For UDFs only, the RETURN statement can optionally return the function RETURNS value as defined by the function definition. The UDF RETURN statement returns a variable, a scalar query, or a query result set, depending on the UDF RETURNS type definition.

The THROW statement can be used to terminate the stored procedure, UDF, or trigger early with a failure exit code. Statements after the THROW statement do not execute. The THROW statement can return a value or expression of either string or numeric data type. The THROW statement can also return the results of a scalar query, which is a SQL SELECT statement that returns a single row. The selected result can be from one column or a concatenation of multiple columns.