MySQL stored procedure method example of returning multiple values

This article uses an example to describe how to return multiple values in a MySQL stored procedure. Share with you for your reference, the details are as follows:

MySQL stored functions only return 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

Next, let's look at a stored procedure that accepts a customer number and returns the total number of orders that were shipped, canceled, resolved, and 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 parameters, the stored procedure also requires four additional OUT parameters: shipped, canceled, resolved and disputed. In the stored procedure, use a select statement with the count function to get the corresponding total number of orders 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 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 combination with practical applications, let's take a look at calling a stored procedure that returns multiple values from a PHP program:

<?php
/**
 * Call stored procedure that return multiple values
 * @param $customerNumber
 */
function call_sp($customerNumber)
{
  try {
    $pdo = new PDO("mysql:host=localhost;dbname=yiibaidb", 'root', '123456');
    // execute the stored procedure
    $sql = 'CALL get_order_by_cust(:no,@shipped,@canceled,@resolved,@disputed)';
    $stmt = $pdo->prepare($sql);
    $stmt->bindParam(':no', $customerNumber, PDO::PARAM_INT);
    $stmt->execute();
    $stmt->closeCursor();
    // execute the second query to get values from OUT parameter
    $r = $pdo->query("SELECT @shipped,@canceled,@resolved,@disputed")
         ->fetch(PDO::FETCH_ASSOC);
    if ($r) {
      printf('Shipped: %d, Canceled: %d, Resolved: %d, Disputed: %d',
        $r['@shipped'],
        $r['@canceled'],
        $r['@resolved'],
        $r['@disputed']);
    }
  } catch (PDOException $pe) {
    die("Error occurred:" . $pe->getMessage());
  }
}
call_sp(141);

In the above code, the user-defined variables before the @ symbol are associated with the database connection, so they are available for access between calls.

Okay, that’s all for this sharing.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL stored procedure skills", "MySQL common function summary", "MySQL log operation skills", "MySQL transaction operation skills summary" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.

You may also be interested in:

Instructions for using the MySQL CASE WHEN statement
Summary of if and case statements in MySQL
Several examples of using MySQL's CASE WHEN statement
How to use case when statement in MySQL to implement multi-condition query
Detailed example of using if statement in mysql stored procedure
Detailed explanation of the principle and usage of cursor (DECLARE) in MySQL stored procedure
Definition and assignment of variables in mysql stored procedures
Introduction to the use of MySQL stored procedure cursor loop
Detailed explanation of MySql stored procedures and functions
Detailed example of using case statement in MySQL stored procedure

<<: js implements form validation function

>>: Solution to Nginx session loss problem