본문 바로가기
프로그래밍/MySQL

[Stored Procedure] 7. MySQL CASE 문법

by 듀빈 2016. 7. 29.
반응형

요약 : 이번 강좌에서는, 저장된 프로그램 내부의 복잡한 조건문을 구성하기 위해 MySQL CASE문의 사용 방법을 배울 것이다.

IF문 이외에, MySQL은 CASE라고 불리우는 대안 조건문을 제공한다. MySQL CASE 문은 코드를 더 가독성 있고 효율성 있게 만들어 준다.

CASE 문법에 관한 두개의 형태가 있다: 간단한 CASE 문, 검색된(Searched) CASE 문


간단한 CASE 문

간단한 CASE문의 구조에 대해서 살펴보자:

1
2
3
4
5
6
CASE  case_expression
   WHEN when_expression_1 THEN commands
   WHEN when_expression_2 THEN commands
   ...
   ELSE commands
END CASE;
cs

고유값의 집합에 대해 표현식의 값을 체크하는 간단한 CASE문을 사용할 수 있다.

case_expression은 올바른 표현식이 될 수 있다. 우리는 각 WHEN절(when_expression_1, when_expression_2, etc)의 값과 case_expression의 값을 비교한다. 만약 case_expression 의 값과 when_expression_n 의 값이 동일 할 때, 해당하는 WHEN의 commands를 실행한다.

WHEN의 when_expression 과 case_expression의 값이 일치하는 조건이 없을 경우에는, ELSE 절에 있는 commands를 실행하게 된다. ELSE 절은 옵션으로 제공한다. 만약 ELSE 절을 작성하지 않고 조건에 맞는 값을 찾지 못할 경우에, MySQL은 에러를 출력한다.

아래 예시는 간단한 CASE 문의 사용방법을 보여주고 있다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
DELIMITER $$
 
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 Shipping';
 WHEN 'Canada' THEN
    SET p_shiping = '3-day Shipping';
 ELSE
    SET p_shiping = '5-day Shipping';
 END CASE;

END$$
 
DELIMITER ;
cs

저장 프로시저의 동작 방법을 알아보자.

GetCustomerShipping 저장 프로시저는 IN 매개변수로 customer number를 받아서, customer의 나라에 맞는 배송기간을 반환한다.

저장 프로시저 내부에서, 첫번째로 입력받은 customer number에 맞는 customer의 country를 얻는다. 그 후, 간단한 CASE문을 사용하여 customer 의 country에 맞는 배송 기간을 결정한다. 만약 USA에 살고 있는 customer 이라면 배송 기간은 2일이 걸린다. Canada에 customer가 있다면 배송 기간은 3일이 걸린다. 이외의 countries에 있는 customers 은 5일의 기간이 소요된다.

아래의 순서도는 배송기간을 결정하는 로직을 설명한다.

아래의 코드는 위의 저장 프로시저의 테스트 스크립트 이다.

1
2
3
4
5
6
7
8
9
10
11
SET @customerNo = 112;
 
SELECT country INTO @country
FROM customers
WHERE customernumber = @customerNo;
 
CALL GetCustomerShipping(@customerNo,@shipping);
 
SELECT @customerNo AS Customer,
       @country    AS Country,
       @shipping   AS Shipping;
cs


검색된(Searched) CASE 문

간단한 CASE 문은 명확한 값을 가지는 집합에 대한 표현식의 값을 매칭하는것만 허용한다. 범위 같은 더 복잡한 매칭을 수행하려면, 검색된(Searched) CASE 문을 사용해야 한다. Searched CASE 문은 IF문과 비슷하다. 하지만 IF문에 비해 더 가독성이 좋다.

아래는 Searched CASE 문의 구조를 설명하고 있다.

1
2
3
4
5
6
CASE
    WHEN condition_1 THEN commands
    WHEN condition_2 THEN commands
    ...
    ELSE commands
END CASE;
cs

MySQL은 WHEN 절에 있는 조건의 값이 TRUE인 것을 찾아 해당하는 절의 commands 를 실행합니다.

만약 모든 조건이 TRUE가 아닐 경우 ELSE절에 있는 commands 를 실행하게 된다. 만약 특별히 ELSE 절을 작성하지 않고, 일치하는 조건이 없을 경우에는, MySQL은 에러 메세지를 출력하게 된다.

MySQL은 THEN절이나 ELSE절에 빈 명령줄을 가지는 것을 허용하지 않는다. 만약 MySQL이 에러를 출력하지 않게 하고, ELSE 절에 있는 로직을 처리하고 싶지 않는 것을 원한다면, ELSE 절에 빈 BEGIN END 문맥을 작성하면 된다.

아래 예제 소비자들의 신용한도에 맞는 소비자 레벨 (SILVER, GOLD, PLATINUM)을 찾을 때 Searched CASE 문을 이용하는것을 설명하고 있다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
DELIMITER $$
 
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;
 
    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 ;
cs

신용한도가 50000 이상이면 소비자는 PLATINUM 회원이다.

10000 이상 50000 미만의 소비자는 GOLD 회원이다.

10000 미만 소비자는 SILVER 회원이다.

아래의 테스트 스크립트를 실행해서 저장 프로시저를 테스트 할 수 있다.

1
2
CALL GetCustomerLevel(112, @level);
SELECT @level AS 'Customer Level';
cs


이번 강좌에서는 간단한 CASE 문과 검색된(Searched) CASE문을 포함하는 MySQL 2개의 CASE 문을 사용하는 방법을 보여주었다.


출처 : http://www.mysqltutorial.org/mysql-case-statement/

이전 글 : [Stored Procedure] 6. MySQL IF 문법

다음 글 : [Stored Procedure] 8. IF 문과 CASE문 중 선택하는 팁

반응형