从存储过程中返回结果集

You can use the RETURNS option to return a result set, in the form of a temporary table, from a stored procedure. This temporary table can only be inserted into. It cannot be referenced, from inside the stored procedure, using a SQL 选择, 更新 or 删除 statement.

例子

Example 1: Using the RETURNS option to return a result set of data from a database table

The statement must be a valid insert statement as supported by NuoDB. The values provided can be either a select statement, a values list, or a combination of both (see 有效语法的示例)。

/* Using select statement as values for INSERT statement */
DROP PROCEDURE IF EXISTS prc_player_position;
 DELIMITER @
CREATE PROCEDURE prc_player_position (IN in_pos STRING)
      RETURNS tmp_tab ( number INTEGER,
                        name   STRING,
                        team   STRING)
AS
       INTO tmp_tab 选择 number, name, team
                           FROM hockey.hockey
                           WHERE position = in_pos
                            ORDER BY number;
END_PROCEDURE
@
 DELIMITER ;
执行 prc_player_position('Defense');
 NUMBER        NAME         TEAM
 ------- ----------------- ------
   21    ANDREW FERENCE    Bruins
   27    DOUGIE HAMILTON   Bruins
   33    ZDENO CHARA       Bruins
   44    DENNIS SEIDENBERG Bruins
   45    AARON JOHNSON     Bruins
   54    ADAM MCQUAID      Bruins
   55    JOHNNY BOYCHUK    Bruins

/* Using a combination of select statement and a values column list to provide
/* values for INSERT.
/* In this case, using a table alias in the select statement is required */

DROP PROCEDURE IF EXISTS prc_player_position;
 DELIMITER @
CREATE PROCEDURE prc_player_position (IN in_pos STRING)
      RETURNS tmp_tab ( id       INTEGER,
                        number   INTEGER,
                        name     STRING,
                        position STRING,
                        team     STRING,
                        date     DATE,
                        active   BOOLEAN)
AS
       INTO tmp_tab 选择 h.*, current_date, 'TRUE'
                           FROM hockey.hockey h
                           WHERE h.position = in_pos
                           ORDER BY h.id;
END_PROCEDURE
@
 DELIMITER ;
执行 prc_player_position('Defense');
 ID  NUMBER        NAME        POSITION   TEAM     DATE    ACTIVE
 --- ------- ----------------- --------- ------ ---------- -------
 15    55    JOHNNY BOYCHUK     Defense  Bruins 2015-04-23  TRUE
 16    33    ZDENO CHARA        Defense  Bruins 2015-04-23  TRUE
 17    21    ANDREW FERENCE     Defense  Bruins 2015-04-23  TRUE
 18    27    DOUGIE HAMILTON    Defense  Bruins 2015-04-23  TRUE
 19    45    AARON JOHNSON      Defense  Bruins 2015-04-23  TRUE
 20    54    ADAM MCQUAID       Defense  Bruins 2015-04-23  TRUE
 21    44    DENNIS SEIDENBERG  Defense  Bruins 2015-04-23  TRUE
示例2:访问由返回选项定义的结果集

The result set defined by the RETURNS option is an in-memory temporary table that does not support 选择, 更新 or 删除 SQL statements. Attempting to access it with any SQL statement, other than , will throw an exception. The following examples show attempts to select from and update the result set and the exception that is returned. The last case shows multiple attempts to insert into the table, which is valid.

用 test;
 DELIMITER @
    Delimiter is now [@]

CREATE PROCEDURE prc_test_rs
   RETURNS temp_table(col1 int, col2 string, col3 string)
AS
     VAR l_cnt INT = 1;
     VAR l_name STRING;
      INTO temp_table (col2)
       选择 name FROM hockey.hockey;
     FOR 选择 col2 from temp_table FOR UPDATE;
         l_name=col2;
         UPDATE temp_table  col1 = l_cnt+1;
         l_cnt=l_cnt+1;
     END_FOR;
END_PROCEDURE;
@
Table TEST.TEMP_TABLE does not support select | update | delete access

CREATE PROCEDURE prc_test_rs
   RETURNS temp_table(col1 int, col2 string, col3 string)
AS
      INTO temp_table (col1,col2)
        选择 number,name FROM hockey.hockey;
     UPDATE temp_table  col3 = 'source: prc_test_rs';
END_PROCEDURE;
@
Table TEST.TEMP_TABLE does not support update access

CREATE PROCEDURE prc_test_rs
   RETURNS temp_table(col1 int, col2 string, col3 string)
AS
      INTO temp_table (col1,col2,col3)
        选择 number,name,'first insert' FROM hockey.hockey WHERE number < 20;
      INTO temp_table (col1,col2,col3)
        选择 number,name,'second insert' FROM hockey.hockey WHERE number > 60;
END_PROCEDURE;
@
 DELIMITER ;
    Delimiter is now [;]

称呼 prc_test_rs;

 COL1        COL2           COL3
 ----- ---------------- -------------
  11   GREGORY CAMPBELL first insert
  18   NATHAN HORTON    first insert
  17   MILAN LUCIC      first insert
  19   TYLER SEGUIN     first insert
   1   MAX SUMMIT       first insert
  64   LANE MACDERMID   second insert
  63   BRAD MARCHAND    second insert
  91   MARC SAVARD      second insert