Decode
2022, Aug 10
0810 SQL/PLSQL
Decode
Decode?
Decode는 프로그래밍의 if, else와 비슷한 기능을 수행한다.
decode의 함수예시
select empno, sal, deptno
from
emp;
-
결과
SQL 문장에 if 기능 넣기 : decode 함수, simple case 표현식, searched case 표현식
select /* decode 함수 */
empno, sal, decode(deptno, 10, sal*1.1, 20, sal*1.2, sal) whatif, deptno
from emp;
-
결과
** decode 함수 대신 case 표현 식을 사용해도 좋습니다!
select /* simple case 표현식 */
empno, sal, case deptno when 10 then sal*1.1
when 20 then sal*1.2
else sal
end whatif,
deptno
from emp;
-
결과
select /* searched case 표현식 */
empno, sal, case when deptno = 10 then sal*1.1
when deptno = 20 then sal*1.2
else sal
end whatif,
deptno
from emp;
-
결과
select empno, sal, decode(deptno, 10, sal*1.1, 20, sal*1.2) whatif, deptno
from emp;
-
결과
DECODE + SUM 패턴 예시 1 : 부서별 업무별 급여합
집계
select sal, sal, sal, sal, sal, deptno from emp;
-
결과
select sal, decode(deptno, 10, sal) d10, sal, sal, sal, sal, deptno from emp;
-
결과
select sal,
decode(deptno, 10, sal) d10,
decode(deptno, 20, sal) d20,
decode(deptno, 30, sal) d30,
deptno
from emp;
-
결과
select sum(sal) total,
sum(decode(deptno, 10, sal)) d10,
sum(decode(deptno, 20, sal)) d20,
sum(decode(deptno, 30, sal)) d30
from emp;
-
결과
select job,
sum(sal) total,
sum(decode(deptno, 10, sal)) d10,
sum(decode(deptno, 20, sal)) d20,
sum(decode(deptno, 30, sal)) d30
from emp
group by job
order by job;
-
결과
select job,
nvl(sum(sal), 0) total,
nvl(sum(decode(deptno, 10, sal)), 0) d10,
nvl(sum(decode(deptno, 20, sal)), 0) d20,
nvl(sum(decode(deptno, 30, sal)), 0) d30
from emp
group by job
order by job;
-
결과
DECODE + SUM 패턴 예시 2 : 부서별 분기별 입사자수, 업무별 분기별 입사자수, …
select to_char(hiredate, 'Q') quarter, deptno
from emp;
-
결과
select quarter, quarter, quarter, quarter, quarter
from (select deptno, to_char(hiredate, 'Q') quarter
from emp);
-
결과
select quarter, decode(quarter, 1, '*') as q1, quarter, quarter, quarter
from (select deptno, to_char(hiredate, 'Q') quarter
from emp);
-
결과
서브 쿼리
select '*' as total,
decode(quarter, 1, '*') as q1,
decode(quarter, 2, '*') as q2,
decode(quarter, 3, '*') as q3,
decode(quarter, 4, '*') as q4
from (select deptno, to_char(hiredate, 'Q') quarter
from emp);
-
결과
select quarter, quarter, quarter, quarter, quarter, deptno
from (select deptno, to_char(hiredate, 'Q') quarter from emp);
-
결과
select count('*') as total,
count(decode(quarter, 1, '*')) as q1,
count(decode(quarter, 2, '*')) as q2,
count(decode(quarter, 3, '*')) as q3,
count(decode(quarter, 4, '*')) as q4
from (select deptno, to_char(hiredate, 'Q') quarter
from emp);
-
결과
select deptno,
count('*') as total,
count(decode(quarter, 1, '*')) as q1,
count(decode(quarter, 2, '*')) as q2,
count(decode(quarter, 3, '*')) as q3,
count(decode(quarter, 4, '*')) as q4
from (select deptno, to_char(hiredate, 'Q') quarter
from emp)
group by deptno
order by deptno;
-
결과
부서별 업무별 입사자 수
select job,
count('*') as total,
count(decode(quarter, 1, '*')) as q1,
count(decode(quarter, 2, '*')) as q2,
count(decode(quarter, 3, '*')) as q3,
count(decode(quarter, 4, '*')) as q4
from (select job, to_char(hiredate, 'Q') quarter
from emp)
group by job
order by job;
-
결과
중첩 decode 사용을 최대한 피하자!
drop table t1 purge;
create table t1 as select empno as cust_no, ename as cust_name,
-- db에서 랜덤값 집어넣기 (0~1 사이)
to_char(round(dbms_random.value)) as option1,
to_char(round(dbms_random.value)) as option2 from emp;
-
결과
select * from t1;
col option1 format a2
col option2 format a2
select cust_no, cust_name, option1, option2,
decode(option1, '0',
decode(option2, '0', 'A', '1','B'), '1',
decode(option2, '0', 'C', '1', 'D')) as cust_level from t1;
-
결과
select cust_no, cust_name, option1, option2,
decode(option1||option2,
'00', 'A',
'01', 'B',
'10', 'C',
'11', 'D') as cust_level from t1;
-
결과
SQL에서 주석과 프롬프트 창에 출력하는 방법
rem 'abc'
-- 주석
prompt 'abc'
-- 창에 출력
-
결과
asterisk(’*’)
select count(empno), count('*') from emp;
-- '*' 해당컬럼의 갯수로 대체해서 갯수를 센다.
-
결과
remark 디코드 함수에서 else 부분 생략해서 null 이 리턴되는 예제
select empno, sal, decode(deptno, 10, sal*1.1, 20, sal*1.2) whatif, deptno
from emp;
-
결과
Where
select empno, ename, sal, sal*1.2 whatif
from emp
where deptno = 10;
select empno, ename, sal, sal*1.2 whatif
from emp
where deptno = 30 and sal >= 2000;
-
결과
부정문
select empno, ename, sal, sal*1.2 whatif
from emp
where not(deptno = 30 and sal >= 2000);
-- 조건에 해당하는 사람 빼고 모두 다
-
결과
create table emp3
as select /*CTAS 작업할 때 항상 false 조건을 사용해서 구조만 복사한 테이블 생성함*/
from emp
where 1 = 2;
연산자
- 일반 비교연산자 : =, >, <, >=, <=, <>, ^=, !=
- SQL 비교연산자 : between, in, like, is null
- 논리연산자 : and, or, not
- Set 연산자 : union all, union, intersect, minus
→ between 연산자
-- QT에서 이 식으로 바꿔서 실행
select * from emp where empno >= 7000 and empno <= 7500;
-- 아래 식을 실행하면
select * from emp where empno between 7000 and 7500;
select * from emp where empno not between 7000 and 7500;
** Query Transformation **
predicate pushing
view merging : 쿼리 1이 쿼리2로 변경된 것
Subquery unnesting
select * from (
select * from emp where deptno = 10
union all
select * from emp
where deptno = 30)
where sal >= 3000;
⇒ 변경 코드
select * from (select * from emp
where deptno = 10
and sal >= 3000
union all
select * from emp
and where deptno = 30)
and sal >= 3000;