从存储过程中返回多个结果集

Nuodb SQL支持从单个存储过程返回多个结果集。这允许能够检索相关但完全不同的数据结构,同时避免必须使多个呼叫多于一个存储过程。

We can create a stored procedure prc_player_info(), pass in a parameter, the year, and return information related to teams that won their division. At the same time, we can return the player roster for the team that won the final playoffs.

As illustrated in the following example, when creating the returned table directly with a SQL 选择 statement, which is used to create the 1st returned table, the ORDER BY clause is eliminated by the NuoDB optimizer, for performance reasons. If the result set must be returned in a specified order, use the 插 INTO…​SELECT…​ORDER BY syntax, which is used to create the 2nd returned table.
DROP PROCEDURE prc_player_info;
 DELIMITER @
CREATE PROCEDURE prc_player_info (IN i_year INTEGER)
  RETURNS PlayerTeams (TeamYear INT,TeamName STRING,TeamDiv STRING,TeamPlayoff STRING)
         , TABLE PlayersRoster (PlayerFirstName STRING, PlayerLastNAme STRING, PlayerPosition STRING )
AS
  PlayerTeams = (选择 year, Name, divisionid, playoff FROM teams
             WHERE year = i_year AND rank = 1
             ORDER BY Name);
     INTO PlayersRoster (选择 p.firstname,p.lastname, p.position
            FROM players p ,scoring s, teams t
             WHERE p.playerid = s.playerid
             AND s.year = t.year
               AND s.teamid = t.teamid
              AND t.year = i_year
              AND t.rank = 1
              AND t.playoff = 'F'
         ORDER BY p.lastname, p.firstname);
    RETURN;
END_PROCEDURE;
@
 DELIMITER ;
称呼  prc_player_info(1976);
 TEAMYEAR       TEAMNAME       TEAMDIV  TEAMPLAYOFF
 --------- ------------------- -------- ------------

   1976    Boston Bruins          AD        F
   1976    Montreal Canadiens     NO        SC
   1976    Philadelphia Flyers    PT        SF
   1976    St. Louis Blues        SM        QF
   1976    Houston Aeros          WW        AVSF
   1976    Quebec Nordiques       EW        AVC

 PLAYERFIRSTNAME  PLAYERLASTNAME  PLAYERPOSITION
 ---------------- --------------- ---------------

     Earl            Anderson            R
     John            Bucyk               L
     Wayne           Cashman             L
...

还可以通过嵌套存储过程填充多个结果集。在下面的示例中,通过调用前一个示例中的存储过程可以填充由存储过程返回的结果集。

DROP PROCEDURE IF EXISTS prc_call_player_info ;
 DELIMITER @
CREATE PROCEDURE  prc_call_player_info (IN i_year INTEGER)
    RETURNS TABLE PlayerTeams (TeamYear INT,TeamName STRING,TeamDiv STRING,TeamPlayoff STRING)
         , TABLE PlayersRoster (PlayerFirstName STRING, PlayerLastNAme STRING, PlayerPosition STRING )
AS
    PlayerTeams,PlayersRoster = 称呼 prc_player_info(i_year);
    RETURN;
END_PROCEDURE;
@
 DELIMITER ;
称呼 prc_call_player_info(1976);


TEAMYEAR       TEAMNAME       TEAMDIV  TEAMPLAYOFF
 --------- ------------------- -------- ------------

   1976    Boston Bruins          AD        F
   1976    Houston Aeros          WW        AVSF
   1976    Montreal Canadiens     NO        SC
   1976    Philadelphia Flyers    PT        SF
   1976    Quebec Nordiques       EW        AVC
   1976    St. Louis Blues        SM        QF

 PLAYERFIRSTNAME  PLAYERLASTNAME  PLAYERPOSITION
 ---------------- --------------- ---------------

     Earl            Anderson            R
     John            Bucyk               L
     Wayne           Cashman             L
...