[ORACLE]조건문을 제공하는 DECODE 함수

DECODE Function

DECODE(expression, search1, result1 [, search2, result2], ..., [,searchN, resultN] [, default]);

DECODE 함수는 조건에 따라 결과를 다르게 처리합니다.

IF-THEN-ELSE 로직을 포함합니다.

---

매개변수

expression

- 검색하고자 하는 표현식입니다.

- DECODE 함수는 값을 비교하기 전에 expression을 search1(첫 번째 검색 값)의 데이터 유형으로 자동으로 변환합니다.

 

search1, search2, ..., searchN

- expression과 비교되는 값입니다. 

 

result1, result2, ..., resultN

- expression이 search와 같은 경우 반환되는 값입니다.

 

default

- expression이 search1, search2, ..., searchN와 비교했을 때, 동일한 값이 존재하지 않을 경우 반환되는 값입니다.

- 생략 시 NULL이 반환됩니다.

---

특징

1. DECODE 함수는 첫 번째 매개변수인 expression과 비교되는 값인 (search1, search2, ..., searchN)과 비교를 합니다. 만약, expression과 search1이 같을 때 search2, ..., searchN을 비교할 필요가 없으므로 비교가 종료되며, result1의 값이 반환됩니다.
2. 매개변수는 최대 255개까지 가능합니다.

---

설명

아래 쿼리는 첫 번째 매개변수 'ORACLE'과 두 번째 매개변수 'ORACLE'을 비교해서 같으면, 세 번째 매개변수인 'TRUE'를 반환하며, 값이 다르면 default값을 설정하지 않았으므로 NULL을 반환합니다.

쿼리의 결과는 값이 동일하므로 'TRUE'를 반환합니다.

SELECT DECODE('ORACLE', 'ORACLE', 'TRUE')
FROM DUAL;

위 쿼리를 IF-THEN-ELSE로 표현하면 아래와 같습니다.

IF 'ORACLE' = 'ORACLE' THEN 
    RETURN 'TRUE'; 
END IF

아래 쿼리는 default값을 설정하였습니다.

첫 번째 매개변수 'ORACLE'과 두 번째 매개변수 'MY SQL'은 일치하지 않으므로 네 번째 매개변수(default값)인 'FALSE'를 반환합니다.

SELECT DECODE('ORACLE', 'MY SQL', 'TRUE', 'FALSE')
FROM DUAL;

위 쿼리를 IF-THEN-ELSE로 표현하면 아래와 같습니다.

IF 'ORACLE' = 'MY SQL' THEN 
    RETURN 'TRUE'; 
ELSE
    RETURN 'FALSE';
END IF;

아래 쿼리는 여러 개의 비교되는 값과 default값을 설정하였습니다. 쿼리의 결과는 'THREE'입니다.

SELECT DECODE(3,  1, 'ONE', 2, 'TWO', 3, 'THREE', 'NOT EXISTS')
FROM DUAL;

위 쿼리를 IF-THEN-ELSE로 표현하면 아래와 같습니다.

IF 3 = 1 THEN 
    RETURN 'ONE'; 
ELSIF 3 = 2 THEN 
    RETURN 'TWO';
ELSIF 3 = 3 THEN
    RETURN 'THREE';
ELSE
    RETURN 'NOT EXISTS';
END IF;

---

Example Data

EX 1) NULL과 NULL을 비교

SELECT DECODE(NULL, NULL, 'TRUE', 'FALSE') AS DECODE_COL
FROM DUAL;

---

EX 2) 날짜 비교

date1과 date2를 비교하여 date1 > date2인 경우 date2를 반환해야 하고 그렇지 않은 경우 date1을 반환해야 합니다.

DECODE((date1 - date2) - ABS(date1 - date2), 0, date2, date1)

아래 계산 결과는 date1이 date2보다 큰 경우 0이 됩니다.

(date1 - date2) - ABS(date1 - date2)

위 계산식은 아래 2가지 방법으로 대체 가능합니다.

/* 1번째 방법 */
DECODE(SIGN(date1-date2), 1, date2, date1)

/* 2번째 방법 */
LEAST(date1, date2)

date1이 date2보다 작아서 date1을 반환하는 예제입니다.

WITH DECODE_TEST AS ( 
    SELECT TO_DATE('2021-MAY-20 15:30:20 ',  'YYYY-MM-DD HH24:MI:SS' ) AS date1 
         , TO_DATE('2021-MAY-21 15:30:20 ',  'YYYY-MM-DD HH24:MI:SS' ) AS date2
      FROM DUAL 
) 
SELECT date1
     , date2
     , DECODE((date1 - date2) - ABS(date1 - date2), 0, date2, date1) as decode_result
     , DECODE(SIGN(date1-date2), 1, date2, date1) as sign_result
     , LEAST(date1, date2) as least_result
FROM DECODE_TEST;

---

EX 3) 숫자 범위에 대한 조건문

특정 숫자의 값이 범위에 만족하는 경우에 대한 조건문을 DECODE로 작성하는 예제입니다.

DECODE에서 범위에 대한 처리는 불가하며, 첫 번째 매개변수에 대해 계산된 결과에 대해서는 처리 가능합니다.

1~10 : 'ONE'

11~20: 'TWO'

21~30: 'THREE'

존재하지 않을 경우 : 'NOT EXISTS'

WITH DECODE_TEST AS ( 
    SELECT 10 AS NUM_COL FROM DUAL
    UNION ALL
    SELECT 15 AS NUM_COL FROM DUAL
    UNION ALL
    SELECT 25 AS NUM_COL FROM DUAL
    UNION ALL
    SELECT 30 AS NUM_COL FROM DUAL
    UNION ALL
    SELECT 110 AS NUM_COL FROM DUAL
) 
SELECT NUM_COL,
DECODE(TRUNC ((NUM_COL - 1) / 10), 0, 'ONE',
                                   1, 'TWO',
                                   2, 'THREE',
                                   'NOT_EXISTS') result
FROM DECODE_TEST;