INOUT
或OUT
参数的存储过程。
返回多个值的存储过程示例:
SELECT * FROM orders;
原表的数据如上面所示。返回多个值,其实就是多设置几个inout/out的参数。
SQL 如下:
USE yiibaidb; DROP PROCEDURE IF EXISTS get_order_by_cust; -- 以下存储过程接受客户编号,并返回发货(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 SELECT COUNT(*) INTO shipped FROM orders WHERE customerNumber=cust_no AND STATUS=shipped; SELECT COUNT(*) INTO canceled FROM orders WHERE customerNumber=cust_no AND STATUS=canceled; SELECT COUNT(*) INTO resolved FROM orders WHERE customerNumber=cust_no AND STATUS=resolved; SELECT COUNT(*) INTO disputed FROM orders WHERE customerNumber=cust_no AND STATUS=disputed; END $$ DELIMITER; CALL get_order_by_cust(141,@shipped,@canceled,@resolved,@disputed); SELECT @shipped,@canceled,@resolved,@disputed;
除IN
参数之外,存储过程还需要4
个额外的OUT
参数:shipped
, canceled
, resolved
和 disputed
。 在存储过程中,使用带有COUNT函数的SELECT语句根据订单状态获取相应的订单总数,并将其分配给相应的参数。
要使用get_order_by_cust
存储过程,可以传递客户编号和四个用户定义的变量来获取输出值。
执行存储过程后,使用SELECT
语句输出变量值。结果如下: