Returning Multiple Result Sets from a Stored Procedure
NuoDB SQL supports returning multiple result sets from a single stored procedure. This allows for the ability to retrieve related but completely different data structures while avoiding having to make multiple calls to more than one stored procedure.
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 SELECT 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 INSERT INTO…SELECT…ORDER BY syntax, which is used to create the 2nd returned table.
|
DROP PROCEDURE prc_player_info;
SET 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 = (SELECT year, Name, divisionid, playoff FROM teams
WHERE year = i_year AND rank = 1
ORDER BY Name);
INSERT INTO PlayersRoster (SELECT 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;
@
SET DELIMITER ;
CALL 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
...
Multiple result sets can also be populated by nesting stored procedures. In the example below, the result sets returned by the stored procedure can be populated by calling the stored procedure in the previous example.
DROP PROCEDURE IF EXISTS prc_call_player_info ;
SET 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 = CALL prc_player_info(i_year);
RETURN;
END_PROCEDURE;
@
SET DELIMITER ;
CALL 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
...