[MySQL] Stored Procedure의 조건문 IF ELSEIF / CASE

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

---

IF

MySQL의 IF문은 식의 특정한 조건이나 값에 맞는 SQL문을 실행한다.

표현식은 세개의 값(true, false, null) 중 하나를 반환한다.

 

1) IF문

IF expression(조건식) THEN
	statements(실행문);
END IF;

 

2) IF ELSE문

IF expression(조건식) THEN
	statements(실행문);
ELSE
	else-statements;
END IF;

 

3) IF ELSEIF ELSE문

IF expression(조건식) THEN
	statements(실행문);
ELSEIF elseif-expression THEN
	elseif-statements;
...
ELSE
	else-statements;
END IF;

 

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

DELIMITER $$
USE `classicmodels`$$
CREATE PROCEDURE `GetCustomerLevel`(
	IN p_customerNumber int(11),
    OUT p_customerLevel varchar(10)
)
BEGIN
	DECLARE creditlim double;
    
    SELECT creditlimit INTO creditlim
    FROM customers
    WHERE customerNumber = p_customerNumber;
    
    IF creditlim > 50000 THEN
		SET p_customerLevel = 'PLATINUM';
	ELSEIF (creditlim <= 50000 AND creditlim >= 10000) THEN
		SET p_customerLevel = 'GOLD';
	ELSEIF creditlim < 100000 THEN
		SET p_customerLevel = 'SILVER';
	END IF;
END$$

DELIMITER ;

CALL GetCustomerLevel(114,@customerLevel);
CALL GetCustomerLevel(103,@customerLevel2);
SELECT @customerLevel,@customerLevel2;

---

CASE

 

1) 간단한 CASE문

CASE case_expression
    WHEN when_expression1 THEN commands
    WHEN when_expression2 THEN commands
    ...
    ELSE commands
END CASE;

 

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

DELIMITER $$
USE `classicmodels`$$
CREATE PROCEDURE `GetCustomerShipping` (
	IN p_customerNumber int(11),
    OUT p_shiping varchar(50)
)
BEGIN
	DECLARE customerCountry varchar(50);
    
    SELECT country INTO customerCountry
    FROM customers
    WHERE customerNumber = p_customerNumber;
    
    CASE customerCountry
		WHEN 'USA' THEN
			SET p_shiping = '2-day';
		WHEN 'Canada' THEN
			SET p_shiping = '3-day';
		ELSE
			SET p_shiping = '5-day';
	END CASE;
END$$

DELIMITER ;

CALL GetCustomerSHipping(114,@shipping1);
CALL GetCustomerSHipping(103,@shipping2);
SELECT	@shipping1, @shipping2;

 

2) 검색된 CASE문

CASE
    WHEN condition_1 THEN commands
    WHEN condition_2 THEN commands
    ...
    ELSE commands
END CASE;

만약 모든 조건이 true가 아닐 경우 ELSE절에 있는 commands를 실행하게 되는데, 

ELSE절을 작성하지않고 일치하는 조건이 없을 경우에는 MySQL에서 에러 메세지를 출력한다.

THEN절, ELSE절에 빈 명령줄을 허용하지않는데, ELSE절에 있는 로직을 처리하고 싶지않다면 BEGIN END를 작성한다.

 

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

DELIMITER $$
USE `classicmodels`$$
CREATE PROCEDURE `CustomerLevelGet`(
	IN p_customerNumber int(11),
    OUT p_customerLevel varchar(10)
)
BEGIN
	DECLARE creditlim double;
    
    SELECT creditlimit INTO creditlim
    FROM customers
    WHERE customerNumber = p_customerNumber;
    
    CASE
		WHEN creditlim > 50000 THEN
			SET p_customerLevel = 'PLATINUM';
		WHEN (creditlim <= 50000 AND creditlim >= 10000) THEN
			SET p_customerLevel = 'GOLD';
		WHEN creditlim < 10000 THEN
			SET p_customerLevel = 'SILVER';
	END CASE;
END$$

DELIMITER ;

CALL CustomerLevelGet(177,@cusLevel1);
CALL CustomerLevelGet(299,@cusLevel2);
SELECT @cusLevel1, @cusLevel2;

---

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