mysql存储过程之返回多个值的方法示例

本文实例讲述了mysql存储过程之返回多个值的方法。分享给大家供大家参考，具体如下：

mysql存储函数只返回一个值。要开发返回多个值的存储过程，需要使用带有inout或out参数的存储过程。咱们先来看一个orders表它的结构：

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)的订单总数：

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函数的select语句根据订单状态获取相应的订单总数，并将其分配给相应的参数。按着上面的sql，我们如果要使用get_order_by_cust存储过程，可以传递客户编号和四个用户定义的变量来获取输出值。执行存储过程后，我们再使用select语句输出变量值：

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

结合实际应用，我们再来看下从php程序中调用返回多个值的存储过程：

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

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

好啦，本次分享就到这里了。