Shulou(Shulou.com)05/31 Report--

This article shows you how to return multiple values in the mysql stored procedure, the content is concise and easy to understand, can definitely make your eyes bright, through the detailed introduction of this article, I hope you can get something.

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 (disputed):

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

In practice, let's look at calling a stored procedure that returns multiple values from a PHP program:

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.