建站网址不安全,wordpress百度百科,企业网站建设发展历程,广州网站建设公司奇亿网站建设本文实例讲述了mysql存储过程之返回多个值的方法。分享给大家供大家参考#xff0c;具体如下#xff1a;mysql存储函数只返回一个值。要开发返回多个值的存储过程#xff0c;需要使用带有INOUT或OUT参数的存储过程。咱们先来看一个orders表它的结构#xff1a;mysql de…本文实例讲述了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-- shippedSELECTcount(*) INTO shippedFROMordersWHEREcustomerNumber cust_noAND status Shipped;-- canceledSELECTcount(*) INTO canceledFROMordersWHEREcustomerNumber cust_noAND status Canceled;-- resolvedSELECTcount(*) INTO resolvedFROMordersWHEREcustomerNumber cust_noAND status Resolved;-- disputedSELECTcount(*) INTO disputedFROMordersWHEREcustomerNumber cust_noAND 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程序中调用返回多个值的存储过程/*** Call stored procedure that return multiple values* param $customerNumber*/function call_sp($customerNumber){try {$pdo new PDO(mysql:hostlocalhost;dbnameyiibaidb, 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);上述代码中在符号之前的用户定义的变量与数据库连接相关联因此它们可用于在调用之间进行访问。好啦本次分享就到这里了。更多关于MySQL相关内容感兴趣的读者可查看本站专题《MySQL存储过程技巧大全》、《MySQL常用函数大汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》及《MySQL数据库锁相关技巧汇总》希望本文所述对大家MySQL数据库计有所帮助。
