[MySQL] 저장프로시저 다중 값 반환 예제

prinha.tistory.com/entry/MySQL-%EC%A0%80%EC%9E%A5-%ED%94%84%EB%A1%9C%EC%8B%9C%EC%A0%80%EC%9D%98-%EB%A7%A4%EA%B0%9C%EB%B3%80%EC%88%98-IN-OUT-INOUT

[MySQL] 저장 프로시저의 매개변수 - IN, OUT, INOUT

---

USE `classicmodels`;
DROP procedure IF EXISTS `get_order_by_cust`;

DELIMITER $$
USE `classicmodels`$$
CREATE PROCEDURE `get_order_by_cust` (
		IN cust_no INT, # CustomerNumber를 IN으로 받고 OUT매개변수 각각 출력
        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(486,@shipped,@canceled,@resolved,@disputed);
SELECT @shipped,@canceled,@resolved,@disputed;

 

---

참고 및 출처 : blog.duveen.me/15?category=657591