Oracle - decode()함수 정리

• 디코드함수 표현식

DECODE함수는 expr과 search가 일치하면 result, 모두 일치하지 않으면 default를 반환, default를 지정하지 않으면 널을 반환 한다.

 

DECODE( expr, search , result [, search, result]... [, default] )

 

• 자동형변환 주의

1) EMP테이블 데이터

-- emp테이블
SELECT * FROM EMP;

EMPNO|ENAME    |JOB      |MGR |HIREDATE               |SAL |COMM|DEPTNO|
-----+---------+---------+----+-----------------------+----+----+------+
 7369|SMITH    |CLERK    |7902|1980-12-17 00:00:00.000| 800|    |    20|
 7499|ALLEN    |SALESMAN |7698|1981-02-20 00:00:00.000|1600| 300|    30|
 7521|WARD     |SALESMAN |7698|1981-02-22 00:00:00.000|1250| 500|    30|
 7566|JONES    |MANAGER  |7839|1981-04-02 00:00:00.000|2975|    |    20|
 7654|MARTIN   |SALESMAN |7698|1981-09-28 00:00:00.000|1250|1400|    30|
 7698|BLAKE    |MANAGER  |7839|1981-05-01 00:00:00.000|2850|    |    30|
 7782|CLARK    |MANAGER  |7839|1981-06-09 00:00:00.000|2450|    |    10|
 7839|KING     |PRESIDENT|    |1981-11-17 00:00:00.000|5000|    |    10|
 7844|TURNER   |SALESMAN |7698|1981-09-08 00:00:00.000|1500|   0|    30|
 7900|JAMES    |CLERK    |7698|1981-12-03 00:00:00.000| 950|    |    30|
 7902|FORD     |ANALYST  |7566|1981-12-03 00:00:00.000|3000|    |    20|
 7934|MILLER   |CLERK    |7782|1982-01-23 00:00:00.000|1300|    |    10|
 8000|JEONG SEO|MANAGER  |7839|1993-08-27 00:00:00.000|3500|    |    50|
 8001|CHANG SUP|DEVELOPER|8000|1993-08-28 00:00:00.000|3500|    |    50|
 8002|JE UK SEO|DEVELOPER|8000|1993-08-29 00:00:00.000|2900|    |    50|
 8003|GU RI    |DEVELOPER|8000|1993-08-30 00:00:00.000|2800|    |    50|
 8004|GO TAE   |DEVELOPER|8000|1993-09-01 00:00:00.000|2500|    |    50|
 8005|YOUNG JAE|DEVELOPER|8000|1994-02-07 00:00:00.000|2100|    |    50|
 8006|JU PARK  |DEVELOPER|8000|1994-02-07 00:00:00.000|2100|    |    50|

오라클 scott스키마의 EMP테이블 테이블이다. 몇개는 내가 추가한 데이터!

 

2) 형변환 예제

SELECT MIN(SAL)
     , MAX(SAL)
     , MAX(DECODE(JOB,'DEVELOPER',NULL,SAL)) MAX_SAL
  FROM EMP;

MIN(SAL)|MAX(SAL)|MAX_SAL|
--------+--------+-------+
     800|    5000|950    |

2) 를 보면 ALIAS MAX_SAL컬럼 값이 950으로 잘못된 값이 나왔다. PRESIDENT값이 5000으로 가장 높은 SAL을 가진다그러므로 해석해보면 DEVELOPER가 아닌 데이터에서 가장 높은 값 MAX(SAL)인 5000이 나와야하지만 950으로 잘못된 값을 리턴받았다. 자동형변환 규칙 때문이다.

DECODE(a, b, c, d)를 처리할 때 a = b를 처리할 때 a = b이면 c를 반환하고, 아니면 d를 반환한다.

 

이 때, 데이터 타입은 세 번째 인자 c에 의해 결정된다.

c가 문자형이고 d가 숫자형이면, d는 문자형으로 변환. DECODE함수가 가진 또 다른 규칙은 세 번째 인자(c)가 null 값이면 varchar2로 취급한다.

 

3) TO_NUMBER로 강제 형변환

SELECT MIN(SAL)
     , MAX(SAL)
     , MAX(DECODE(JOB,'DEVELOPER',TO_NUMBER(NULL),SAL)) MAX_SAL
  FROM EMP;


MIN(SAL)|MAX(SAL)|MAX_SAL|
--------+--------+-------+
     800|    5000|   5000|

위 SQL NULL에 TO_NUMBER() 강제 형변환을 해주면, 그제서야 제대로된 값이 출력된다.

 

 

 

친절한SQL튜닝책으로 정리한 내용!