控制流

Nuodb SQL支持以下执行控制流语句,用于SQL存储过程,用户定义的函数(UDF)或触发器。

执行 ...;

称呼 ...;

FOR 选择 ...; sql_code_block END_FOR;

FOR  INTO ...; sql_code_block END_FOR;

FOR 执行 ...; 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 );

描述

执行 and 称呼 statements will invoke a stored procedure. See 执行, 称呼 想要查询更多的信息。

The FOR SELECT statement loop executes the 选择 statement (see 选择) and then invokes the inner sql_code_block once per each returned row. In the 选择 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 statement (see ) 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 选择 statement that returns a single row. The selected result can be from one column or a concatenation of multiple columns.

参数

sql_code_block

诸如在存储过程中,用户定义的函数或触发器中执行的处理控制语句,SQL命令等的语句的分组。

variable_expression

UDF或由存储过程,UDF或触发抛出的有效表达式。

sql_query_statement

表示要分配给SQL过程变量的有效SQL Query语句的字符串。看 选择 for syntax of a valid 选择 statement.
The 选择 statement select list will be assigned to each var_name in the order listed in the 选择 list. The number of var_name s to the left of the equation must match the number of items in the 选择 list.

dynamic_sql_statement

An 执行 IMMEDIATE statement. See SQL程序动态SQL. If the 执行 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 选择 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 选择 list. If 执行 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 例8.)。

例子

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));
 DELIMITER @
CREATE PROCEDURE prc_copy_year(IN copy_year INTEGER)
AS
    VAR new_year INTEGER;
    FOR 选择 * FROM hockey.teams WHERE year=copy_year;
        new_year=year+1;
         INTO teams_update VALUES (new_year, teamid, name, conferenceid, divisionid);
    END_FOR;
END_PROCEDURE
@
 DELIMITER ;

称呼 prc_copy_year(2011);
选择 * 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

此示例使用该表,在示例1中创建的teams_update。

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 statement with values that are provided by a 选择 statement from the players_update table. This 选择 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 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));
 DELIMITER @
CREATE PROCEDURE prc_copy_players(IN i_prior_year INTEGER, IN i_current_year INTEGER)
AS
    FOR 选择 ht.year,ht.teamid AS HT_TEAM_ID FROM hockey.teams_update ht WHERE ht.year = i_current_year;
         INTO players_update
             选择 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
@
 DELIMITER ;

称呼 prc_copy_players(2011,2012);
选择 * 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);
 DELIMITER @
CREATE PROCEDURE test(_country STRING, _city STRING)
AS
    FOR  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 */
         INTO city_table (country_id, value) VALUES (id, _city);
    END_FOR;
END_PROCEDURE
@
 DELIMITER ;

执行 test('Ireland', 'Dublin');

选择 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);

 DELIMITER @
CREATE PROCEDURE test(_country STRING, _city STRING, _postcode STRING)
AS
    FOR  INTO country_table(value) VALUES (_country);
        /* note: all references to field "id" within this FOR loop refers to */
        /* the country_table.id */
        FOR  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 */
           INTO postcode_table (country_id, value) VALUES (id, _postcode);
        END_FOR;
    END_FOR;
END_PROCEDURE
@
 DELIMITER ;

执行 test('Ireland', 'Dublin', 'D02 XH98');
选择 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);
 DELIMITER @
CREATE PROCEDURE get_winning_teams
    RETURNS winning_teams (year INTEGER,
                           name STRING)
AS
     INTO winning_teams 选择 year, name
                                FROM hockey.teams
                               WHERE playoff = 'F';
END_PROCEDURE
@
CREATE PROCEDURE display_winners
AS
    VAR total INTEGER = 0;
    FOR 执行 get_winning_teams;
         INTO team_wins VALUES (NAME, YEAR);
    END_FOR;
END_PROCEDURE
@
 DELIMITER ;
称呼 display_winners;
选择 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.
创建程序 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 执行 p2(1)
    IF (CURRENT_RESULT = 1)
        INTO total VALUES (field);
    END_IF;
    IF (CURRENT_RESULT = 2)
        INTO total VALUES (field);
    END_IF;
    IF (CURRENT_RESULT = 3)
     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;
 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 )
         INTO temptab
         选择 birthyear,count(*) FROM players
           WHERE birthyear=begyr GROUP BY birthyear;
        begyr=begyr+1;
    END_WHILE;
END_PROCEDURE
@
 DELIMITER ;
称呼 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);
 DELIMITER @
CREATE PROCEDURE forins1() AS
    FOR  INTO player VALUES
        (DEFAULT, 'Messi'),
        (DEFAULT, 'Ronaldo'),
        (DEFAULT, 'Bale'),
        (DEFAULT, 'Neymar'),
        (DEFAULT, 'Aguero'),
        (DEFAULT, 'Saurez');
        IF (pid % 2 = 0) CONTINUE; END_IF;
         INTO id VALUES ( pid );
    END_FOR ;
END_PROCEDURE;
@
 DELIMITER ;

称呼 forins1();
选择 pid, pname FROM player;

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

选择 pid FROM id;

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

此示例通过通过指定的次数连接字符来创建字符串,但避免超过目标字符串的容量。

DROP PROCEDURE IF EXISTS test2;
DROP PROCEDURE IF EXISTS test;
 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) = '';
    称呼 test(length, ch, str);
    THROW str;
END_PROCEDURE
@
 DELIMITER ;
称呼 test2(3, 'x');
Procedure HOCKEY.TEST2, xxx
称呼 test2(16, 'x');
Procedure HOCKEY.TEST2, xxxxxxxxxxxxxxxx
称呼 test2(17, 'x');
Procedure HOCKEY.TEST2, xxxxxxxxxxxxxxxx
称呼 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 */
 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;
     INTO tmp_hockey (number,name,position,team)
        VALUES (num, nam, pos, team);
END_PROCEDURE
@
 DELIMITER ;
称呼 prc_hockey_maint(77,'TOM JONES','Backup','Bruins');
Procedure HOCKEY.PRC_HOCKEY_MAINT, Error 101: Invalid Position

称呼 prc_hockey_maint(77,'TOM JONES','Goalie','Bruins');
选择 * 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;
 DELIMITER @
CREATE PROCEDURE prc_throw_query (IN inumber INTEGER)
AS
    THROW (
        选择 NAME||' plays '||position||' for '||TEAM FROM hockey
            WHERE number = inumber );
END_PROCEDURE;
@
 DELIMITER ;
执行 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;
 DELIMITER @
CREATE PROCEDURE prc_throw_msg (IN i_error BOOLEAN)
AS
    IF (i_error)
        THROW 5001;
    END_IF;
END_PROCEDURE;
@
 DELIMITER ;
执行 prc_throw_msg(0);
执行 prc_throw_msg(1);
Procedure HOCKEY.PRC_THROW_MSG, 5001

/* THROW text message with single quote */
DROP PROCEDURE IF EXISTS prc_throw_msg;
 DELIMITER @
CREATE PROCEDURE prc_throw_msg (IN inumber INTEGER)
AS
    THROW (
        选择 number||' was '||name||'''s NUMBER WHEN PLAYING FOR '||team FROM hockey
            WHERE number = inumber );
END_PROCEDURE;
@
 DELIMITER ;
执行 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 称呼 or 执行 statement, from within a stored procedure. Also, an example of a CASE statement within a stored procedure.

 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 (选择 conferenceid, divisionid, rank
                                                   FROM hockey.teams
                                                  WHERE year = in_year
                                                    AND teamid = in_teamid)
                                        WHEN 'Record'
                                           THEN (选择 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;
    执行 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
@
 DELIMITER ;

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