Shulou(Shulou.com)06/01 Report--

This article shares with you an example of how mysql stored procedures return multiple result sets. The editor thought it was very practical, so I shared it with you as a reference. Let's follow the editor and have a look.

The mysql storage function returns only one 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 the customer number and returns the total number of orders shipped (shipped), cancelled (canceled), resolved (resolved), and disputed (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 parameter, the stored procedure requires four additional OUT parameters: shipped, canceled, resolved and disputed. In the stored procedure, the select statement with the count function is used to get the corresponding total number of orders based on the order status and assign them to the corresponding parameters. According to the sql above, if we want to use the get_order_by_cust stored procedure, we can pass the customer number and four user-defined variables to get 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 Thank you for reading! So much for the example of mysql stored procedure returning multiple result sets. I hope the above can be helpful to you so that you can learn more. If you think the article is good, you can share it and let more people see it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.