MySQL stored procedures can use SELECT statements in their body, returning a data set to the caller. The
manual also explains that multiple data sets can be returned from such a stored procedure.
For example, assume you have this stored procedure:
CREATE PROCEDURE p1(x INT, y INT)
DETERMINISTIC
BEGIN
SELECT
x ;
SELECT
x AS first_param,
y AS second_param;
SELECT
x,
y,
x + y AS sum_xy,
x * y AS prod_xy;
SELECT * FROM t1;
END
There are four SELECT statements, with different headers, and a variable number of rows returned.
Early versions of
DBD::mysql could not handle multiple data sets. Actually, prior to version 3.0004 they could not even handle one dataset from a stored procedure. Starting with version 4.001, single and multiple datasets from SPs are handled gracefully.
The trick is to call
$sth->more_results
after retrieving each dataset.
See the complete example at
Multiple data sets in MySQL stored proceduresFor those interested, the
MySQL Users Conference and Expo includes a
BoF about DBD::mysql on April 24
th at 7:30pm.