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;

BREAK;

CONTINUE;

RETURN [ variable_expression | ( sql_query_statement | dynamic_sql_statement ) ];

THROW message |
      variable_expression |
      ( sql_query_statement | dynamic_sql_statement );

Description

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.

Parameters

sql_code_block

A grouping of statements, such as processing control statements, SQL commands, etc., that execute inside a stored procedure, user-defined function, or trigger.

variable_expression

A valid expression to be returned by a UDF or thrown by a stored procedure, UDF or trigger.

sql_query_statement

A string representing a valid SQL query statement to be assigned to a SQL procedure variable. See SELECT for syntax of a valid SELECT statement.
The SELECT statement select list will be assigned to each var_name in the order listed in the SELECT list. The number of var_name s to the left of the equation must match the number of items in the SELECT list.

dynamic_sql_statement

An EXECUTE IMMEDIATE statement. See SQL Procedural Dynamic SQL. If the EXECUTE IMMEDIATE statement is a query returning a result set, then the result set will be assigned to each var_name in the order listed in the SELECT list of the query. The number of var_name s to the left of the equation must match the number of items in the query SELECT list. If EXECUTE IMMEDIATE is used in variable assignment, an error will incur if it also specifies INTO target_var.

message

A string literal or numeric value that will be displayed when invoked with the THROW statement. A string literal is required to be enclosed in single quotes. If the message itself contains a single quote character then the single quote character needs to be escaped by using another single quote character (see Example 8).

Examples

Example 1: Using FOR SELECT within a stored procedure
CREATE TABLE teams_update (year         INTEGER,
                          teamid        VARCHAR(3),
                          name          VARCHAR(48),
                          conferenceid  VARCHAR(2),
                          divisionid    VARCHAR(2));
SET DELIMITER @
CREATE PROCEDURE prc_copy_year(IN copy_year INTEGER)
AS
    VAR new_year INTEGER;
    FOR SELECT * FROM hockey.teams WHERE year=copy_year;
        new_year=year+1;
        INSERT INTO teams_update VALUES (new_year, teamid, name, conferenceid, divisionid);
    END_FOR;
END_PROCEDURE
@
SET DELIMITER ;

CALL prc_copy_year(2011);
SELECT * FROM teams_update;
 YEAR  TEAMID          NAME          CONFERENCEID  DIVISIONID
 ----- ------- --------------------- ------------- -----------
 2012    AND   Anaheim Ducks              WC           PC
 2012    BOS   Boston Bruins              EC           NE
 2012    BUF   Buffalo Sabres             EC           NE
 2012    CAL   Calgary Flames             WC           NW
 2012    CAR   Carolina Hurricanes        EC           SE
....
Example 2: Using FOR SELECT within a stored procedure with an ambiguous column name

This example uses the table, teams_update, created in Example 1.

The teams_update table includes a column named teamid. This column is included in the column_list of the FOR SELECT statement. By default, this selected column value is assigned a variable name, teamid, which is the same name as the table column name. This variable is passed to the inner sql_code_block and can be referred to by this name.

The inner sql_code_block contains an INSERT statement with values that are provided by a SELECT statement from the players_update table. This SELECT statement is only selecting rows where the players_update.teamid is equal to this variable named teamid that has been passed to the inner sql_code_block by the FOR SELECT statement. For the INSERT statement to correctly distinguish this variable teamid from the column named teamid in the players_update table, you must assign the teamid an alias name in the column_list of the FOR SELECT statement. This makes the variable name unique that represents the teams_update.teamid column.

CREATE TABLE players_update (playerid  VARCHAR(10),
                              year      INTEGER,
                              teamid    VARCHAR(3),
                              position  VARCHAR(4));
SET DELIMITER @
CREATE PROCEDURE prc_copy_players(IN i_prior_year INTEGER, IN i_current_year INTEGER)
AS
    FOR SELECT ht.year,ht.teamid AS HT_TEAM_ID FROM hockey.teams_update ht WHERE ht.year = i_current_year;
        INSERT INTO players_update
             SELECT distinct hs.playerid, i_current_year, hs.teamid, hs.position
               FROM hockey.scoring hs
              WHERE hs.teamid = HT_TEAM_ID
                AND hs.year = i_prior_year;
    END_FOR;
END_PROCEDURE
@
SET DELIMITER ;

CALL prc_copy_players(2011,2012);
SELECT * FROM players_update;
 PLAYERID  YEAR  TEAMID  POSITION
 --------- ----- ------- ---------
 beaucfr01 2012    AND       D
 belesma01 2012    AND       L
 bellma01  2012    AND       L
 blakeja01 2012    AND       C
 boninni01 2012    AND       C
 brooksh01 2012    AND       D
....
Example 3: Using FOR INSERT INTO within a stored procedure
DROP PROCEDURE IF EXISTS test;
DROP TABLE IF EXISTS city_table;
DROP TABLE IF EXISTS country_table;
CREATE TABLE country_table (
    id INTEGER GENERATED ALWAYS AS IDENTITY,  /* MUST be auto-generated */
    value STRING);
CREATE TABLE city_table (
    id INTEGER GENERATED ALWAYS AS IDENTITY,  /* Auto-generated is optional */
    country_id INTEGER,
    value     STRING);
SET DELIMITER @
CREATE PROCEDURE test(_country STRING, _city STRING)
AS
    FOR INSERT INTO country_table (value) VALUES (_country);
        /* note: all references to field "id" within this FOR INSERT loop refer */
        /* to the country_table.id. This INSERT statement below will not execute */
        /* if COUNTRY_TABLE.ID is not defined as system generated */
        INSERT INTO city_table (country_id, value) VALUES (id, _city);
    END_FOR;
END_PROCEDURE
@
SET DELIMITER ;

EXECUTE test('Ireland', 'Dublin');

SELECT country.value AS country, city.value AS city
  FROM country_table AS country, city_table AS city
 WHERE country.id = city.country_id;
 COUNTRY   CITY
 -------- ------
 Ireland  Dublin

DROP PROCEDURE IF EXISTS test;
DROP TABLE IF EXISTS city_table;
DROP TABLE IF EXISTS country_table;
DROP TABLE IF EXISTS postcode_table;
CREATE TABLE country_table (id INTEGER GENERATED ALWAYS AS IDENTITY,
                            value STRING);
/* create sub-table of country_table named city_table */
CREATE TABLE city_table (id INTEGER GENERATED ALWAYS AS IDENTITY,
                         country_id INTEGER,
                         value STRING);
/* create sub-table of city_table named postcode_table */
CREATE TABLE postcode_table (id INTEGER GENERATED ALWAYS AS IDENTITY,
                             country_id INTEGER,
                             value STRING);

SET DELIMITER @
CREATE PROCEDURE test(_country STRING, _city STRING, _postcode STRING)
AS
    FOR INSERT INTO country_table(value) VALUES (_country);
        /* note: all references to field "id" within this FOR loop refers to */
        /* the country_table.id */
        FOR INSERT INTO city_table (country_id, value) VALUES (id, _city);
           /* note: all references to field "id" within this FOR loop refers */
           /* to the city_table.id */
          INSERT INTO postcode_table (country_id, value) VALUES (id, _postcode);
        END_FOR;
    END_FOR;
END_PROCEDURE
@
SET DELIMITER ;

EXECUTE test('Ireland', 'Dublin', 'D02 XH98');
SELECT country.value AS country, city.value AS city, postcode.value as postcode
  FROM country_table AS country, city_table AS city, postcode_table as postcode
 WHERE country.id = city.country_id
   AND city.id = postcode.country_id;
 COUNTRY   CITY  POSTCODE
 -------- ------ ---------
 Ireland  Dublin D02 XH98
Example 4: Using FOR EXECUTE within a stored procedure
DROP PROCEDURE IF EXISTS display_winners;
DROP PROCEDURE IF EXISTS get_winning_teams;
DROP TABLE IF EXISTS team_wins;
CREATE TABLE team_wins (team_name STRING,
                        win_year  NUMBER);
SET DELIMITER @
CREATE PROCEDURE get_winning_teams
    RETURNS winning_teams (year INTEGER,
                           name STRING)
AS
    INSERT INTO winning_teams SELECT year, name
                                FROM hockey.teams
                               WHERE playoff = 'F';
END_PROCEDURE
@
CREATE PROCEDURE display_winners
AS
    VAR total INTEGER = 0;
    FOR EXECUTE get_winning_teams;
        INSERT INTO team_wins VALUES (NAME, YEAR);
    END_FOR;
END_PROCEDURE
@
SET DELIMITER ;
CALL display_winners;
SELECT team_name, count(win_year) as years_won
  FROM team_wins
  GROUP BY team_name ORDER BY team_name ;

        TEAM_NAME        YEARS_WON
 ----------------------- ----------

 Boston Bruins               12
 Buffalo Sabres               2
 Calgary Flames               2
 Calgary Tigers               1
 Carolina Hurricanes          1
 Chicago Black Hawks          6
 Chicago Blackhawks           1
...
Example 5: Using FOR EXECUTE to process multiple result sets from a procedure.
CREATE PROCEDURE p2(first_value int)
RETURNS TABLE one(field string),
     TABLE two(field string),
        TABLE three (field string, num decimal(5,4))
AS
 RETURN (SELECT value FROM storage WHERE ID = first_value),
     (SELECT value FROM storage WHERE ID = first_value+1),
    (SELECT value, id FROM storage WHERE ID = first_value+2);
END_PROCEDURE;

…​

CREATE PROCEDURE p4
RETURNS TABLE total(field string)
AS
  FOR EXECUTE p2(1)
    IF (CURRENT_RESULT = 1)
       INSERT INTO total VALUES (field);
    END_IF;
    IF (CURRENT_RESULT = 2)
       INSERT INTO total VALUES (field);
    END_IF;
    IF (CURRENT_RESULT = 3)
    INSERT INTO total VALUES (num);
    END_IF;
  END_FOR;
END_PROCEDURE;
Example 6: Using BREAK to exit loop processing within a stored procedure
DROP PROCEDURE IF EXISTS prc_players_by_year;
SET DELIMITER @
CREATE PROCEDURE prc_players_by_year(begyr INTEGER, endyr INTEGER)
    RETURNS temptab (year INTEGER, player_ct INTEGER)
AS
    IF ( (begyr < 1) OR (endyr > YEAR(current_date)) )
        THROW 'Error 10: Invalid Year';
    END_IF;
    WHILE ( begyr <= endyr )
        INSERT INTO temptab
         SELECT birthyear,count(*) FROM players
           WHERE birthyear=begyr GROUP BY birthyear;
        begyr=begyr+1;
    END_WHILE;
END_PROCEDURE
@
SET DELIMITER ;
CALL prc_players_by_year(1970,1973);
 YEAR  PLAYER_CT
 ----- ----------
 1970     127
 1971     125
 1972     126
 1973     119
Example 7: Using CONTINUE to pass control flow to the next iteration of the loop.
CREATE TABLE player (pid BIGINT GENERATED ALWAYS AS IDENTITY, pname STRING);
CREATE TABLE id (pid BIGINT);
SET DELIMITER @
CREATE PROCEDURE forins1() AS
    FOR INSERT INTO player VALUES
        (DEFAULT, 'Messi'),
        (DEFAULT, 'Ronaldo'),
        (DEFAULT, 'Bale'),
        (DEFAULT, 'Neymar'),
        (DEFAULT, 'Aguero'),
        (DEFAULT, 'Saurez');
        IF (pid % 2 = 0) CONTINUE; END_IF;
        INSERT INTO id VALUES ( pid );
    END_FOR ;
END_PROCEDURE;
@
SET DELIMITER ;

CALL forins1();
SELECT pid, pname FROM player;

 PID   PNAME
---- -------
  1   Messi
  2   Ronaldo
  3   Bale
  4   Neymar
  5   Aguero
  6   Saurez

SELECT pid FROM id;

 PID
----
  1
  3
  5
Example 8: Using RETURN to return from a stored procedure

This example creates a string by concatenating a character by a specified number of times, but avoids exceeding the capacity of the target string.

DROP PROCEDURE IF EXISTS test2;
DROP PROCEDURE IF EXISTS test;
SET DELIMITER @
CREATE PROCEDURE test(length INTEGER, ch CHAR(1), OUT str CHAR(16))
AS
    VAR idx INTEGER = 0;
    str = '';
    WHILE (idx < length)
        str = str || ch;
        IF (CHAR_LENGTH(str) = 16)
            RETURN;
        END_IF;
        idx = idx + 1;
    END_WHILE;
END_PROCEDURE
@
CREATE PROCEDURE test2(length INTEGER, ch CHAR(1))
AS
    VAR str CHAR(16) = '';
    CALL test(length, ch, str);
    THROW str;
END_PROCEDURE
@
SET DELIMITER ;
CALL test2(3, 'x');
Procedure HOCKEY.TEST2, xxx
CALL test2(16, 'x');
Procedure HOCKEY.TEST2, xxxxxxxxxxxxxxxx
CALL test2(17, 'x');
Procedure HOCKEY.TEST2, xxxxxxxxxxxxxxxx
CALL test2(100, 'x');
Procedure HOCKEY.TEST2, xxxxxxxxxxxxxxxx
Example 9: Using THROW to display a message and exit a stored procedure
DROP PROCEDURE IF EXISTS prc_hockey_maint;
DROP TABLE IF EXISTS tmp_hockey;
CREATE TABLE tmp_hockey
  (number INTEGER, name STRING, position STRING, team STRING);

/* THROW a message */
SET DELIMITER @
CREATE PROCEDURE prc_hockey_maint (num INTEGER, nam STRING, pos STRING, team STRING)
AS
    IF ( pos NOT IN ('Forward','Defense','Goalie','Fan') )
        THROW 'Error 101: Invalid Position';
    END_IF;
    INSERT INTO tmp_hockey (number,name,position,team)
        VALUES (num, nam, pos, team);
END_PROCEDURE
@
SET DELIMITER ;
CALL prc_hockey_maint(77,'TOM JONES','Backup','Bruins');
Procedure HOCKEY.PRC_HOCKEY_MAINT, Error 101: Invalid Position

CALL prc_hockey_maint(77,'TOM JONES','Goalie','Bruins');
SELECT * FROM tmp_hockey WHERE number = 77;
 NUMBER    NAME    POSITION   TEAM
 ------- --------- --------- ------
   77    TOM JONES  Goalie   Bruins

/* THROW query result set - one column, one row */
DROP PROCEDURE IF EXISTS prc_throw_query;
SET DELIMITER @
CREATE PROCEDURE prc_throw_query (IN inumber INTEGER)
AS
    THROW (
        SELECT NAME||' plays '||position||' for '||TEAM FROM hockey
            WHERE number = inumber );
END_PROCEDURE;
@
SET DELIMITER ;
EXECUTE prc_throw_query(40);
Procedure HOCKEY.PRC_THROW_QUERY, TUUKKA RASK plays Goalie for Bruins

/* THROW numeric value */
DROP PROCEDURE IF EXISTS prc_throw_msg;
SET DELIMITER @
CREATE PROCEDURE prc_throw_msg (IN i_error BOOLEAN)
AS
    IF (i_error)
        THROW 5001;
    END_IF;
END_PROCEDURE;
@
SET DELIMITER ;
EXECUTE prc_throw_msg(0);
EXECUTE prc_throw_msg(1);
Procedure HOCKEY.PRC_THROW_MSG, 5001

/* THROW text message with single quote */
DROP PROCEDURE IF EXISTS prc_throw_msg;
SET DELIMITER @
CREATE PROCEDURE prc_throw_msg (IN inumber INTEGER)
AS
    THROW (
        SELECT number||' was '||name||'''s NUMBER WHEN PLAYING FOR '||team FROM hockey
            WHERE number = inumber );
END_PROCEDURE;
@
SET DELIMITER ;
EXECUTE prc_throw_msg(40);
Procedure HOCKEY.PRC_THROW_MSG, 40 was TUUKKA RASK's NUMBER WHEN PLAYING FOR Bruins
Example 10: Invoking another stored procedure. Also using CASE statement within a stored procedure

It is possible to invoke another procedure, using either the CALL or EXECUTE statement, from within a stored procedure. Also, an example of a CASE statement within a stored procedure.

SET DELIMITER @
CREATE PROCEDURE get_stats(IN  in_stat   STRING,
                           IN  in_year   INTEGER,
                           IN  in_teamid STRING,
                           OUT out_stat1 STRING,
                           OUT out_stat2 STRING,
                           OUT out_stat3 STRING)
AS
    out_stat1,out_stat2,out_stat3 = CASE in_stat
                                        WHEN 'Conference'
                                           THEN (SELECT conferenceid, divisionid, rank
                                                   FROM hockey.teams
                                                  WHERE year = in_year
                                                    AND teamid = in_teamid)
                                        WHEN 'Record'
                                           THEN (SELECT wins, losses, ties
                                                   FROM hockey.teams
                                                  WHERE year = in_year
                                                    AND teamid = in_teamid)
                                    END;
END_PROCEDURE
@

CREATE PROCEDURE get_team_stats(IN in_stat   STRING,
                                IN in_year   INTEGER,
                                IN in_teamid STRING)
AS
    VAR out1 STRING, out2 STRING, out3 STRING;
    EXECUTE get_stats(in_stat,in_year,in_teamid,out1,out2,out3);
    VAR display_out STRING;
    display_out=CASE in_stat
                     WHEN 'Conference'
                         THEN 'Conference='||out1||' Division='||out2||' Rank='||out3
                     WHEN 'Record'
                         THEN 'Wins='||out1||' Loses='||out2||' Ties='||out3
                 END;
    THROW display_out;
END_PROCEDURE
@
SET DELIMITER ;

EXECUTE get_team_stats('Conference',2011,'BOS');
Procedure HOCKEY.GET_TEAM_STATS, Conference=EC Division=NE Rank=1
EXECUTE get_team_stats('Record',2011,'BOS');
Procedure HOCKEY.GET_TEAM_STATS, Wins=49 Loses=29 Ties=0