# 返回多个值

在本教程中，您将学习如何编写/开发返回多个值的存储过程。

[MySQL存储函数](http://www.yiibai.com/mysql/stored-function.html)只返回一个值。要开发返回多个值的[存储过程](http://www.yiibai.com/mysql/stored-procedure.html)，需要使用带有`INOUT`或`OUT`参数的存储过程。

如果您不熟悉`INPUT`或`OUT`参数的用法，请查看[存储过程参数教程](http://www.yiibai.com/mysql/stored-procedures-parameters.html)的详细信息。

## 返回多个值的存储过程示例

我们来看看[示例数据库(yiibaidb)](http://www.yiibai.com/mysql/sample-database.html)中的`orders`表。

```sql
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
```

以下存储过程接受客户编号，并返回发货(*shipped*)，取消(*canceled*)，解决(*resolved*)和争议(*disputed*)的订单总数。

```sql
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`参数之外，存储过程还需要`4`个额外的`OUT`参数：`shipped`, `canceled`, `resolved` 和 `disputed`。 在存储过程中，使用带有[COUNT函数](http://www.yiibai.com/mysql/count.html)的[SELECT语句](http://www.yiibai.com/mysql/select-statement-query-data.html)根据订单状态获取相应的订单总数，并将其分配给相应的参数。

要使用`get_order_by_cust`存储过程，可以传递客户编号和四个用户定义的变量来获取输出值。

执行存储过程后，使用`SELECT`语句输出变量值。

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

## 从PHP调用返回多个值的存储过程

以下代码片段显示如何从[PHP](http://www.yiibai.com/php/)程序中调用返回多个值的存储过程。

```sql
<?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);
```

在`@`符号之前的用户定义的变量与数据库连接相关联，因此它们可用于在调用之间进行访问。

在本教程中，我们向您展示了如何编写/开发返回多个值的存储过程以及如何从`PHP`调用它。


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://hezhiqiang-book.gitbook.io/mysql/di-san-zhang/fan-hui-duo-ge-zhi.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.