문법
DECODE (expr ,search_and_result_list [,default])
return [제3 인수 (search_and_result_list 의 첫번째 result 식)의 데이터 형식
파라미터
expr NULL로 설정 가능한 표현식 (숫자, 문자열, 날짜, etc)
search_and_result_list 검색 및 결과 목록 [search , result]
default 검색에 일치하지 않는 경우의 결과 default NULL
리턴
수식 expr 과 일치하는 검색 식 search 를 가진 result 를 돌린다.
수식 expr 검색 및 결과 목록에 포함되지 않은 경우에는 default_expr 을 돌린다.
조건
SQL 임베디드 SQL에서 사용 가능한 PL / SQL에 포함되어 있지 않다. (Oracle 10g R2 시점)
내용
검색 식 및 결과 식 목록
검색 식 및 결과 식의 목록은 [ search , result ]의 형식으로 1 세트 이상하고
126 또는 127 세트 이하의 범위에서 검색과 그 결과를 지정한다.
결과는 모든 목록에서 등호만. 비교 연산자는 선택할 수 없다.
수식 expr 이 search_and_result_list 의 제1 번째의 요소 search1 과 등가라면 result1 을 돌린다.
search2 와 등가라면 result2 를 돌린다.
이것을 검색 및 결과 목록 [ search , result ]에 있는 만큼 반복한다.
끝까지 일치하는 요소가 없는 경우에는 옵션 지정 식 default 값을 되돌린다.
주의
DECODE 인수는 최대 255 개까지된다.
만약 기본값을 사용하는 경우에는 expr 과 default 요소를 255에서 뺀 253 개가 검색 및 결과 표현식 목록의 최대이다.
결과 253/2 ⇒ 126 개까지 검색과 비교하여 대체할 수 있다.
기본값을 지정하지 않으면 127 목록을 지정할 수 있다.
그 때의 default 는 NULL이 된다.
select dump (DECODE (2, 1, 'ONE', 2, 2)) "STRING" from dual;
⇒ 2가 아니라 '2'를 리턴
STRING
------------------------------
Typ = 1 Len = 1 : 50
select DECODE (num, 1, SYSDATE 2 'TWO', 3, NULL 'UNKNOWN') from xxx;
num = 1 일 때 ⇒ SYSDATE (DATE 형태)
num = 2 일 때 ⇒ 'TWO'(DATE 형태 ... 변환 불가능 런타임 오류)
num = 3 일 때 ⇒ NULL (DATE 형태 : OK)
기본 ⇒ 'UNKNOWN'(DATE 형태 ... 변환 불가능 런타임 오류)
DECODE의 사용 예
문자의 코드 포인트 순서와 사전 순이 아니라 모든 목록 순서로 정렬한다.
SQL> select size_name from size_sample
order by DECODE (size_name, 'XS' , 1, 'S' , 2, 'M' , 3, 'L' , 4 'XL' , 5);
SIZE_NAME
----------
XS
S
M
L
XL숫자의 일부를 DECODE 함수로 표현하고 체크한다.
DECODE 함수는 NULL을 검색 search 로 사용할 수 있는 점이 독특한 점이다.
(TO_CHAR의 로마 숫자 변환은 자리 갖춘 처리가 되어 있다)
SQL> select num, to_char (num, 'RN' )
2 DECODE (num, 1, 'I' , 10, 'X' , 20, 'XX' 30, 'XXX' , 40, 'XL' ,
3 NULL, '****' ,
4 'UNDEFINED' ) Roman_Num
5 from decode_sample1;
NUM TO_CHAR (NUM, 'RN' ) ROMAN_NUM
---------- ------------------ ------------ ------
1 I I
10 X X
20 XX XX
21 XXI UNDEFINED
30 XXX XXX
40 XL XL
<NULL> <NULL> ****
DECODE 수치의 등가, 대소 관계를 평가하기 위해 SIGN 함수를 사용한다.
SQL> select num, DECODE ( sign (num-10), -1, '10 미만 ' +1, '10 이상' , 0, '10' ) X,
2 DECODE ( sign (num-20), -1, '20 미만 ' +1, '20 이상' , 0, '20' ) XX,
3 DECODE ( sign (num-30), -1, '30 미만 ' +1, '30 이상' , 0, '30' ) XXX,
4 DECODE ( sign (num-40), -1, '40 미만 ' +1, '40 이상' , 0, '40' ) XL
5 from decode_sample1;
NUM X XX XXX XL
---------- -------- -------- -------- --------
1 10 미만 20 미만 30 미만 40 미만
10 10 20 미만 30 미만 40 미만
20 10 이상 20 30 미만 40 미만
21 10 이상 20 이상 30 미만 40 미만
30 10 이상 20 이상 30 미만 40
40 10 이상 20 이상 30 이상 40
<NULL> <NULL> <NULL> <NULL> <NULL>
DECODE 문자열의 대소 관계의 한쪽을 평가하려면 GREATEST, LEAST를 사용한다. DECODE를 중첩하면 더 알아낼 수 있습니다.
SQL> select str, DECODE ( greatest ( 'AAA' , str) 'AAA' , 'AAA 이하' ) AAA 등급
2 DECODE ( greatest ( 'BBB' , str) 'BBB' , 'BBB 이하' ) BBB,
3 DECODE ( least ( 'CCC' , str), 'CCC' , 'CCC 이상' ) CCC,
4 DECODE ( least ( 'DDD' , str) 'DDD' , 'DDD 이상' ) DDD
5 from decode_sample2;
STR AAA BBB CCC DDD
-------- -------- -------- -------- --------
AAA AAA 다음 BBB 이하 <NULL> <NULL>
AAB <NULL> BBB 이하 <NULL> <NULL>
BBB <NULL> BBB 이하 <NULL> <NULL>
CCC <NULL> <NULL> CCC 이상 <NULL>
<NULL> <NULL> <NULL> <NULL> <NULL>
DDD <NULL> <NULL> CCC 이상 DDD 이상
EEE <NULL> <NULL> CCC 이상 DDD 이상
결론
DECODE 함수를 이용하는건 조건 제어 할 CASE 표현식의 존재하지 않는 Oracle8 시대까지의 기술이므로
단순 CASE 표현식, 검색 CASE 표현식이 구비되어있는 Oracle 9i 이상이면 추천 할 수 없는 방법이다.
보기에도 좋은 간단한 CASE 표현식과 검색 CASE 표현식 을 사용하는 것이 바람직하다.