WL#3587: Allow named and defined resultsets to be returned from procedures

An enhancement to the SQL:2003 standard, that aligns with our, already existing
extension of allowing multiple resultsets to be returned from a Procedure call
is to allow clear specification of what is returned from a procedure call. This
will allow better structure in calling procedures, and will aid in adding the
ability to dynamically execute procedures inside procedures, with proper
parameter and result value handling.

The basic idea here is to:
- Allow the columns of result sets to be defined and named.
- Allow the result sets themselves to be identified / named.

The CREATE PROCEDURE syntax is extened as:
CREATE
    [DEFINER = { user | CURRENT_USER }]
    PROCEDURE sp_name
    [RETURNS ([]  [,...0]) [AS ]] [,...]

Multiple RETURNS may be specifid, they must then have different names, if named.
In the body of the procedure, the CALL and SELECT statements may optionally name
the resultsets to be returned. The resultsets will always be returned in the
order they are defined in the PROCEDURE definition, but not all resultset need
always be returned, hence the need to optionally name the result set returned,
as such:

[RESULTSET ] SELECT ...
[RESULTSET ] CALL ...

If resultsets are not named like this, and resultsets are defined in the
procedure definition, then all resultsets must be returned.

The idea of having the RESULTSET definition as a prefix is that there are more
than the CALL and SELECT statements that return a resultset.

If resulsets are defined in the procedure definition, and they are used in the
corresponding SELECT or CALL statements, then the types must either match or be
possible to be CAST to the type specified in the procedure definition.

The same RESULTSET may be returned from more than once, but the order of the
resultsets returned must still be retained as defined by the procedure
definintion, so the following is valid:

CREATE PROCREATE foo()
RETURNS (my_int1 INTEGER, my_int2 INTEGER) AS rs1,
RETURNS (my_string1 CHAR(10)) AS rs2
BEGIN
RESULTSET rs1 SELECT 1, 2;
RESULTSET rs1 SELECT 3, 4;
RESULTSET rs2 SELECT 'foobar';
END

Whereas the following is not:
CREATE PROCREATE foo()
RETURNS (my_int1 INTEGER, my_int2 INTEGER) AS rs1,
RETURNS (my_string1 CHAR(10)) AS rs2
BEGIN
RESULTSET rs1 SELECT 1, 2;
RESULTSET rs2 SELECT 'foobar';
RESULTSET rs1 SELECT 3, 4;
END

Note that in the first example, although there are three SELECT statements, only
two resultsets will be returned. The two executed SELECT statements are merged
into one resultset and this is invisible to mysql_next_result() etc. For this to
be allowed, restulsets must be named and referenced, the following is not valid:
CREATE PROCREATE foo()
RETURNS (my_int1 INTEGER, my_int2 INTEGER),
RETURNS (my_string1 CHAR(10))
BEGIN
SELECT 1, 2;
SELECT 3, 4;
SELECT 'foobar';
END

As this will return potentially return 3 resultsets, but if will fail on the
second even, as the column types don't match. The following would fail on the
third resultset:
CREATE PROCREATE foo()
RETURNS (my_int1 INTEGER, my_int2 INTEGER),
RETURNS (my_string1 CHAR(10))
BEGIN
SELECT 1, 2;
SELECT 3;
SELECT 'foobar';
END
As the second resultset column can be cast to a CHAR(10).

[ Comment by Peter Gulutzan 2006-10-22 ]

The High-level description says this is an "enhancement" of SQL:2003.
But SQL:2003 non-core feature T471, “Result sets return value” allows
CREATE PROCEDURE ... DYNAMIC RESULT SETS unsigned-integer. This
proposal is merely "non-standard".

[ Comment by Anders Karlsson 2006-10-22 ]

It is true that T471 allows a Stored Procedure definition to include an
indication of how many dynamic result sets are returned. This WL entry though is
more detailed than that (as the title says, it allows typed and column defined
resultsets, neither of which is part of T471). Our feature of allowing
multiple resultsets is a bit like T471, I agree to that, but there are
differences, which is why I didn't bring this up. In a DYNAMIC RESULTSET, there
is a CURSOR defined in the called procedure, which is what is really returned to
the caller, that is not all all what we do, which is why I didn't find T471
applicable even to how we do things right now. At least that is how I understand
DYNAMIC RESULTSETs are used according to SQL:2003. As the implementation we use
now is not like T471, and as my suggesting goes way beyond it, I didn't feel
that the reference to this was necessary. But you are right that it probably
should be.