Decode 함수와 형 변환

Oracle의 내장함수이며, 값을 비교해서 원하는 값으로 치환하게 해주는 함수입니다.

Decode함수를 사용하다보니 형변환이 어떻게 일어나는지 궁금해졌고, 형변환에 대해서 잘 몰라서 문제가 되었던 적도 있엇습니다.

decode 함수 형식

아래의  Oracle 문서에 보면 잘 나와 있습니다.

우선적으로  DECODE의 형식은 DECODE(expr,search,result,default) 형식이고, expr을 search와 비교해서 맞으면 result를 반환하고 아니면 default를 반환하는 함수입니다.

 

오라클은 첫번째 search값에 따라서 자동적으로 변환한다고 되어 있습니다. 즉 result값에 따라 같은 데이터타입으로 값이 반환되는 것입니다. first result값이 char나 null이면 자동적으로 결과값은 VARCHAR2로 리턴합니다.

 

Oracle automatically converts expr and each search value to the datatype of the first search value before comparing. Oracle automatically converts the return value to the same datatype as the first result. If the first result has the datatype CHAR or if the first result is null, then Oracle converts the return value to the datatype VARCHAR2.

 

형변환 이해하는 문제

해당 함수를 사용한 간단한 쿼리를 살펴보겠습니다.

 

TBL_NULL 테이블이 있습니다. 세개의 컬럼이 있고, NUM_COL1은 NUMBER값,CHAR_COL1,CHAR_COL2는 CHAR값으로 이루어 져있습니다.

 

NUM_COL1    CHAR_COL1     CHAR_COL2  

----------- ------------- ---------  

          1 1             X          

          2 2             X          

          3 3             Y          

          4 4             Y          

          5 5                 --> NULL

          6 6             X          

          7 7             X          

          8 8             Y          

          9 9             Y          

         10 10                --> NULL

 

아래의 DECODE 함수의 SELECT 결과값을 적어보세요.

SELECT MAX(DECODE(NUM_COL1,  9, NULL, NUM_COL1))                        col1 ,
       MAX(DECODE(NUM_COL1,  9, TO_NUMBER(NULL), NUM_COL1))             col2 , 
       MAX(DECODE(NUM_COL1,  9, NULL, TO_NUMBER(NUM_COL1)))             col3 ,
       MAX(DECODE(NUM_COL1,  9, TO_NUMBER(NULL), TO_NUMBER(NUM_COL1)))  col4 ,
       MAX(DECODE(CHAR_COL1, 9, NULL, CHAR_COL1))                       col5 ,
       MAX(DECODE(CHAR_COL1, 9, TO_NUMBER(NULL), CHAR_COL1))            col6 ,
       MAX(DECODE(CHAR_COL1, 9, NULL, TO_NUMBER(CHAR_COL1)))            col7 ,
       MAX(DECODE(CHAR_COL1, 9, TO_NUMBER(NULL), TO_NUMBER(CHAR_COL1))) col8
FROM TBL_NULL;

답:

SELECT MAX(DECODE(NUM_COL1,  9, NULL, NUM_COL1))                        col1 , -- '8'
       MAX(DECODE(NUM_COL1,  9, TO_NUMBER(NULL), NUM_COL1))             col2 , --10
       MAX(DECODE(NUM_COL1,  9, NULL, TO_NUMBER(NUM_COL1)))             col3 , -- '8'
       MAX(DECODE(NUM_COL1,  9, TO_NUMBER(NULL), TO_NUMBER(NUM_COL1)))  col4 ,  --10
       MAX(DECODE(CHAR_COL1, 9, NULL, CHAR_COL1))                       col5 , --'8'
       MAX(DECODE(CHAR_COL1, 9, TO_NUMBER(NULL), CHAR_COL1))            col6 , --10
       MAX(DECODE(CHAR_COL1, 9, NULL, TO_NUMBER(CHAR_COL1)))            col7 , --'8'
       MAX(DECODE(CHAR_COL1, 9, TO_NUMBER(NULL), TO_NUMBER(CHAR_COL1))) col8 --10
FROM TBL_NULL;

 

result에 NULL만 있으면 결과값은 VARCHAR2로 반환되어 VARCHAR2의 MAX값을 반환합니다.
result에 to_number(NULL)이 있으면 결과값은 NUMBER로 반환되어 NUMBER의 MAX값을 반환합니다.

 

 

출처 : https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions040.htm

DECODE