Oracle SQLPLUS에서 CASE문 사용하기

CASE 문은 PL/SQL 없이 SQL문 안에서 if-then-else logic을 구현하는 데 사용합니다.

CASE 문은 "Simple case expressions" 와 "Searched case expressions"로 나눌 수 있는데, "Simple case expressions"은 expression을, "Searched case expressions"은 condition을 사용해 return 값이 결정된다.

다음은 simple CASE 구문의 사용방법이다.

CASE search_expression
  WHEN expression1 THEN result1
  WHEN expression2 THEN result2
  ...
  WHEN expressionN THEN resultN
  ELSE default_result
END

위의 예제는 search_expression이 expression1,2,3.. 일 경우 result1,2,3.. 이 출력된다.

SELECT product_id, product_type_id,
  CASE product_type_id
  WHEN 1 THEN 'Book'
  WHEN 2 THEN 'Video'
  WHEN 3 THEN 'DVD'
  WHEN 4 THEN 'CD'
  ELSE 'Magazine'
  END
FROM products;

PRODUCT_ID PRODUCT_TYPE_ID CASEPROD
---------- --------------- --------
         1               1 Book
         2               1 Book
         3               2 Video
         4               2 Video
         5               2 Video
         6               2 Video
         7               3 DVD
         8               3 DVD
         9               4 CD
        10               4 CD
        11               4 CD
        12                 Magazine

다음은 searched CASE 구문의 사용방법이다.

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ...
  WHEN conditionN THEN resultN
  ELSE default_result
END

위의 예는 condition1,2 가 true 일 경우 result1,2,.. 출력한다.

SELECT product_id, product_type_id,
  CASE
  WHEN product_type_id = 1 THEN 'Book'
  WHEN product_type_id = 2 THEN 'Video'
  WHEN product_type_id = 3 THEN 'DVD'
  WHEN product_type_id = 4 THEN 'CD'
  ELSE 'Magazine'
  END
FROM products;

PRODUCT_ID PRODUCT_TYPE_ID CASEPROD
---------- --------------- --------
         1               1 Book
         2               1 Book
         3               2 Video
         4               2 Video
         5               2 Video
         6               2 Video
         7               3 DVD
         8               3 DVD
         9               4 CD
        10               4 CD
        11               4 CD
        12                 Magazine

SELECT product_id, price,
  CASE
  WHEN price > 15 THEN 'Expensive'
  ELSE 'Cheap'
  END
FROM products;

PRODUCT_ID      PRICE CASEWHENP
---------- ---------- ---------
         1      19.95 Expensive
         2         30 Expensive
         3      25.99 Expensive
         4      13.95 Cheap
         5      49.99 Expensive
         6      14.95 Cheap
         7      13.49 Cheap
         8      12.99 Cheap
         9      10.99 Cheap
        10      15.99 Expensive
        11      14.99 Cheap
        12      13.49 Cheap