Does mysql stored procedure return multiple result sets?

This article will introduce the results when the stored procedure returns multiple result sets. I hope it can be a reference for everyone.

#mysql stored function only returns a value. To develop a stored procedure that returns multiple values, you need to use a stored procedure with INOUT or OUT parameters.

Let’s first look at the structure of an orders table:

mysql> desc orders;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| orderNumber    | int(11)     | NO   | PRI | NULL    |       |
| orderDate      | date        | NO   |     | NULL    |       |
| requiredDate   | date        | NO   |     | NULL    |       |
| shippedDate    | date        | YES  |     | NULL    |       |
| status         | varchar(15) | NO   |     | NULL    |       |
| comments       | text        | YES  |     | NULL    |       |
| customerNumber | int(11)     | NO   | MUL | NULL    |       |
+----------------+-------------+------+-----+---------+-------+
7 rows in set

Then, let’s look at a stored procedure that accepts a customer number and returns shipped (shipped) and canceled (canceled) ), the total number of resolved (resolved) and disputed (disputed) orders (multiple result sets):

DELIMITER $$
 
CREATE PROCEDURE get_order_by_cust(
 IN cust_no INT,
 OUT shipped INT,
 OUT canceled INT,
 OUT resolved INT,
 OUT disputed INT)
BEGIN
 -- shipped
 SELECT
            count(*) INTO shipped
        FROM
            orders
        WHERE
            customerNumber = cust_no
                AND status = 'Shipped';
 
 -- canceled
 SELECT
            count(*) INTO canceled
        FROM
            orders
        WHERE
            customerNumber = cust_no
                AND status = 'Canceled';
 
 -- resolved
 SELECT
            count(*) INTO resolved
        FROM
            orders
        WHERE
            customerNumber = cust_no
                AND status = 'Resolved';
 
 -- disputed
 SELECT
            count(*) INTO disputed
        FROM
            orders
        WHERE
            customerNumber = cust_no
                AND status = 'Disputed';
 
END

In fact, in addition to the IN parameters, the stored procedure also requires 4 additional OUT parameters: shipped, canceled , resolved and disputed. In the stored procedure, use the select statement with the count function to obtain the corresponding order total based on the order status and assign it to the corresponding parameter. According to the above SQL, if we want to use the get_order_by_cust stored procedure, we can pass the customer number and four user-defined variables to obtain the output value. After executing the stored procedure, we use the SELECT statement to output the variable value:

+----------+-----------+-----------+-----------+
| @shipped | @canceled | @resolved | @disputed |
+----------+-----------+-----------+-----------+
|       22 |         0 |         1 |         1 |
+----------+-----------+-----------+-----------+
1 row in set

The above is the detailed content of Does mysql stored procedure return multiple result sets?. For more information, please follow other related articles on the PHP Chinese website!