ICT블로그

※ DECODE 함수의 특징

- 프로그래밍 언어의 IF문을 SQL, PL/SQL 내에서 구현하기 위해 만들어진 오라클 함수.

- FROM 절을 제외한 나머지 절 전체(SELECT, GROUP BY, WHERE 등등)에 사용가능. --> 동적으로 FROM절의 TABLE을 선택할 수는 없다는 의미.

- 중복된 DECODE사용이 가능

- 단독으로 쓰이기보단 SIGN, LEAST, GREATEST 등의 함수, SUM, MAX, MIN 등의 GROUP 함수와 많이 쓰인다.

- DECODE는 최소 3개의 인수만 받을 수 있으며, 이 경우 내부적으로 NULL을 출력하는 ELSE로 처리한다.

따라서 아래 i)번과 ii)번은 같다.

 예) 

i) IF A = B THEN

   RETURN 'T';

END IF;

==> DECODE(A,B,'T')

ii) IF A = B THEN

    RETURN 'T';

ELSE

    NULL

END IF;

==> DECODE(A,B,'T',NULL)

- 단순 상수뿐만 아니라 컬럼 간의 비교에도 유용하게 쓰일 수 있다.

예제) decode를 이용한 암호 생성

select to_char(sysdate,'DD')||decode(to_char(sysdate,'DAY'),'월요일','01','화요일','11','수요일','21','목요일','31','금요일','41','토요일','51','일요일','61') SEC_KEY  from dual;

예제) decode가 sign과 함께 쓰이는 경우

   ==> decode는 부등호를 이용한 대소비교를 할 수 없기 때문에, 필요한 경우 sign, least 등의 함수로 대소비교 처리가 가능하게끔 해준다.

 select lec_id,lec_time,lec_point,

        decode(sign(lec_time-lec_point),0,'일반과목',1,'실험과목',-1,'기타과목') as ty from lecture;

select lec_id, lec_time, lec_point, decode(sign(lec_time-lec_point),1,'실험과목','일반과목') from lecture;

select lec_id,lec_time,lec_point, decode(least(lec_time,lec_point),lec_time,'일반과목','실험과목') from lecture;

-- 중첩된 DECODE의 사용 (중첩된 IF)

-- 예제: DECODE(A,B,DECODE(A,C,'T','F'),'F')

desc lecture;

select lec_id, lec_time, lec_point from lecture;

select lec_id, lec_time, lec_point, decode(lec_time,lec_point,decode(lec_time,3,'중요과목')) from lecture;

-- 연습문제 Q/A : 04-2

질문: TEMP의 행을 3개씩 묶어 하나의 번호를 부여하고, 해당 ROWNUM을 3으로 나눈 나머지가 1, 2, 0 인 경우로 나누어 컬럼의 위치를 정해주는 SQL을 만들어라.

select rownum, ceil(rownum/3) "no+", decode(mod(rownum,3),1,'A',2,'B',0,'C') as G, emp_id, emp_name from temp;

select ceil(rownum/3) COL1, decode(mod(rownum,3),1,emp_id) COL2, decode(mod(rownum,3),1,emp_name) COL3, decode(mod(rownum,3),2,emp_id) COL4

, decode(mod(rownum,3),2,emp_name) COL5, decode(mod(rownum,3),0,emp_id) COL7, decode(mod(rownum,3),0,emp_name) COL8

from temp;

-- 연습문제 Q/A : 04-3

(발전형) TEMP의 자료를 이용하여 한 행에 사번, 성명을 3명씩 (총 6개 컬럼) 보여주는 SQL을 작성하라.

select emp_id, emp_name from temp;

select ceil(rownum/3) COL1,

        decode(mod(rownum,3),1,emp_id) COL2,

        decode(mod(rownum,3),1,emp_name) COL3, 

        decode(mod(rownum,3),2,emp_id) COL4, 

        decode(mod(rownum,3),2,emp_name) COL5, 

        decode(mod(rownum,3),0,emp_id) COL7, 

        decode(mod(rownum,3),0,emp_name) COL8

from temp;

==>

select ceil(rownum/3) COL1,

        max(decode(mod(rownum,3),1,emp_id)) COL2,

        max(decode(mod(rownum,3),1,emp_name)) COL3, 

        max(decode(mod(rownum,3),2,emp_id)) COL4, 

        max(decode(mod(rownum,3),2,emp_name)) COL5, 

        max(decode(mod(rownum,3),0,emp_id)) COL7, 

        max(decode(mod(rownum,3),0,emp_name)) COL8

from temp

where emp_id>0

group by ceil(rownum/3);

- group by 절을 이용해 3명씩 그룹을 지어준다.

1-1 ROWNUM

※ rownum의 속성에 대하여

-- rownum에 대해 알아 두어야 할 사실

첫째. 정의: rownum은 where절 (where절 없으면 from절)에 의해 추출된 DATA SET에 row 단위로 붙는 순번이다.

둘째. where절에 rownum을 조건으로 줄 때에는 항상 다른 조건을 모두 만족시킨 결과에 대해 조건이 반영된다.

셋째, order by 절을 사용할 때에는 우선 rownum이 부여되고 난 후 해당 결과 집합에 대해 sort를 하게 된다.

   (순서) rownum --> order by

① 단순 조회 시

 select rownum, emp_id, emp_name

  from temp

 where lev = '수습';

1 20000101 이태백

2 20000102 김설악

3 20000203 최오대

4 20000334 박지리

5 20000305 정북악

6 20006106 유도봉

7 20000407 윤주왕

8 20000308 강월악

9 20000119 장금강

10 20000210 나한라

--> 별도 정렬 없이 temp 자료 중에서 "lev=수습"인 값을 보여줌

② 단순 조회 + 조건 추가 (where emp_id >0) 

 select rownum, emp_id, emp_name

  from temp

 where emp_id >0

 and lev = '수습';

1 20000101 이태백

2 20000102 김설악

3 20000119 장금강

4 20000203 최오대

5 20000210 나한라

6 20000305 정북악

7 20000308 강월악

8 20000334 박지리

9 20000407 윤주왕

10 20006106 유도봉

where emp_id >0 조건이 추가되면서, 별도의 order by 절 없이도 emp_id 순으로 ascending 하여 자료가 출력됨.

③ rownum을 이용한 필터링 (and rownum < 5)

 select rownum, emp_id, emp_name

  from temp

 where emp_id >0

 and lev = '수습'

 and rownum < 5;

1 20000101 이태백

2 20000102 김설악

3 20000119 장금강

4 20000203 최오대

②번에서 출력된 자료에 and rownum < 5 라는 조건을 통해 emp_id 순으로 상위 4개 값만 출력(필터링)된 것을 알 수 있다.

!!주의!! rownum 조건을 이용한 필터링 시에는 <, <= 두 개 연산자 외에는 결과 값이 출력되지 않는다

*예외적으로 1과 비교할 때는 =(같다)로 비교가능하다.

④ rownum을 이용한 필터링 + order by 구문 추가

 select rownum, emp_id, emp_name

  from temp

 where emp_id >0

 and lev = '수습'

 and rownum < 5

 order by emp_name;

2 20000102 김설악

1 20000101 이태백

3 20000119 장금강

4 20000203 최오대

③번에 order by emp_name 절을 추가하였다. 이때 첫번째, ③번의 결과집합은 그대로 가져감. 둘째, ③번에서 확인했던 rownum은 바뀌지 않음을 알 수 있다.

즉, 'and rownum < 5' 구문으로 필터링 시 각 레코드에 대해 rownum 번호가 부여되며, 이후 order by를 이용한 sort가 이뤄졌단 사실을 알 수 있다.

연습문제 Q/A : 04-1 ROWNUM의 이용

 --> TEMP 테이블의 자료를 이용하여 SELECT 결과를 3개행씩 묶어 하나의 번호를 부여하는 SQL을 만들어보자. ROWNUM, 부여된번호, EMP_ID, EMP_NAME을 보여주면 된다.

나의답변)

 select rownum, trunc((rownum-1)/3)+1 as "부여된번호", emp_id, emp_name

 from temp;

모범답안)

 select rownum, ceil(rownum/3) as "부여된번호", emp_id, emp_name from temp;

* ceil : 지정된 값 이상의 가장 작은 정수를 리턴하는 함수

1-2 ROWID

※ ANY와 ALL

ANY와 ALL은 ①서브쿼리와 함께 쓰이거나 ②값들의 집합과 함께 쓰인다. + 비교 연산자와 함께

②값들의 집합

select * from temp

where salary > all (10000000,20000000,30000000,40000000);

그러나 대게는 ①서브쿼리를 사용할 때 쓰이는데, 만약 질의에 필요할 결과값 집합을 미리 알고 있다면, 굳이 ANY나 ALL연산자를 사용해야 할 이유가 없기 때문이다.

예제

<1-1 ANY>

select emp_id, emp_name, salary

from temp

where salary > any (select salary

                      from temp

                     where lev = '과장');

<1-2 MIN을 사용할 경우 -- 연관성 없는 서브쿼리>    

select emp_id, emp_name, salary

from temp

where salary > (select min(salary)

                      from temp

                     where lev = '과장');

<2-1 ALL>

select emp_id, emp_name, salary

from temp

where salary > all (select salary

                      from temp

                     where lev = '과장');

<2-2 ALL -- 연관성 없는 서브쿼리>                     

select emp_id, emp_name, salary

from temp

where salary > (select max(salary)

                      from temp

                     where lev = '과장');

-- Inner Query에서 Outer Query의 어떤 컬럼 값을 사용하는 경우를 일컬음

-- Inner Query = Sub Query, Outer Query = Main Query

-- 대게의 경우 Main Query가 Sub Query의 결과를 이용하기만 하는데, CORERELATED 서브쿼리의 경우 Sub Query도 Main Query의 값을 이용하게 됨

-- 주의: Correlated Sub query는 Main Query에서 하나의 레코드가 조건 절에서 비교되기 위해서 Inner Query 가 꼭 한번씩 수행되어야 하므로, Sub Query의 response time이 늦고 Main Query에서 처리될 record 수가 많은 경우 속도가 저하 될 수 있음 --> InLine View 등 다른 방법을 강구해야 함.

[따라하기]

-- 틀린 답 (조인 사용)

select a.emp_id, a.emp_name from

   temp a, (select lev, avg(salary) as a_sal

            from temp

            group by lev) b

            where a.lev = b.lev

              and a.salary > b.a_sal;

-- 옳은 답 (연관성 있는 서브 쿼리 사용)

select a.emp_id, a.emp_name

  from temp a

  where a.salary > (select avg(salary)

                      from temp b

                      where b.lev = a.lev);

[예제2] UPDATE 문에서의 활용

update tdept a

      set a.boss_id = (select min(b.emp_id)

                      from temp b

                      where b.dept_code =a.dept_code);

[Q/A : 03-13] INSERT문의 이용

-- 실행 안되는 문장

insert into tcom values (select '2002',emp_id, 0.1, 0.1 * salary from temp);

-- 실행 되는 문장

insert into tcom (work_year, emp_id, bonus_rate, comm)

           select '2002',emp_id, 0.1, 0.1 * salary from temp;