[SQL 요약 정리] 1-14. ADVANCED SQL

14. ADVANCED SQL

1) NVL FUNCTION

- NULL값을 포함하는 컬럼을 지정값으로 변경

- 모든 데이터타입에 적용 가능, 변경되는 값과 타입을 일치시켜야 함

- 문형

NVL (형식1, 형식2)

형식1: NULL값을 포함하고있는 컬럼이나 식

형식2: 변경하려는 값

- NVL함수의 형식

NUMBER: NVL(commission_pct,0)

DATE: NVL(start_date, '17/01/01')

CHAR OR VARCHAR2: NVL(mailid, 'ID없음')

2) DCODE FUNCTION

- DECODE: 값을 비교해서 해당하는 값을 돌려주는 함수

- 문형

DECODE (형식, 비교값1, 결과치1, 비교값2, 결과치, ...기본치)

형식: 컬럼이나 값

비교값1: 형식이 비교값1에 맞는지 비교

결과값1: 형식이 비교값1에 맞을 때의 결과값

기본치: 형식이 비교값에 맞지 않을 때의 결과값(생략가능, 생략시 NULL)

- 예시

select name, salary, DECODE(trunc(salary/1000), 0, 'E', 1, 'D', 2, 'C', 3, 'B', 4, 'A')

from s_emp

ORDER BY 3

;

3) CASE문

- 문형

CASE 함수 WHEN 함수연산값 THEN 결과값 ELSE 나머지결과값 END

CASE WHEN 조건식 THEN 결과값 ELSE 나머지결과값 END

- 예시

select name, salary,

case

when salary < 1000 then 'E'

when salary < 2000 then 'D'

when salary < 3000 then 'C'

when salary < 4000 then 'B'

else 'A'

end

from s_emp

order by 3

;

///OR///

select name, salary,

case trunc(salary/1000)

when 0 then 'E'

when 1 then 'D'

when 2 then 'C'

when 3 then 'B'

else 'A'

end

from s_emp

order by 3

;

4) PIVOT함수

- 데이터를 PIVOT테이블의 형태로 출력(ORCLE 11G부터)

- 예시

SELECT * FROM (SELECT dept_id, title FROM s_emp)

PIVOT ( COUNT(*) FOR title IN ('사원','과장','부장','이사','사장'))

ORDER BY dept_id;

- *PIVOT 함수 미지원시 DECODE 함수로 처리

SELECT dept_id,

count(decode(title, '사원',0)) "사원",

count(decode(title, '과장',0)) "과장",

count(decode(title, '부장',0)) "부장",

count(decode(title, '이사',0)) "이사",

count(decode(title, '사장',0)) "사장"

FROM s_emp

GROUP BY dept_id

ORDER BY dept_id;

5) ROLLUP함수 (그룹별 합계)

- 그룹별 합계를 보여주는 함수

- 예시

SELECT dept_id, title, count(*) FROM s_emp

WHERE dept_id IN(106, 112, 113)

GROUP BY ROLLUP(dept_id, title)

ORDER BY dept_id;

6) CUBE함수(그룹별 합계&소계)

- 그룹별 합계를 보여주는 함수

SELECT dept_id, title, count(*) FROM s_emp

WHERE dept_id IN(106, 112, 113)

GROUP BY CUBE(dept_id, title)

ORDER BY dept_id;

7) RANK함수

- 행별 순위를 계산해서 보여주는 함수

- 문형

행별 순위_ RANK() OVER (ORDER BY 컬럼명 [ASC | DESC])

그룹별 순위_ RANK() OVER (PARTITION BY 컬럼명1 ORDER BY 컬럼명2 [ASC | DESC])

- 예시

행별

SELECT id, name, salary, RANK() OVER (ORDER BY salary DESC) as rank

FROM S_EMP;

그룹별

SELECT dept_id, name, salary,

RANK() OVER(PARTITION BY dept_id ORDER BY salary DESC) as rank)

FROM s_emp;

8) CORRELATED SubQUERY

-

- 예시

SELECT name, salary, dept_id

FROM s_emp outer

WHERE salary < (SELECT AVG(salary) FROM s_emp WHERE dept_id = outer.dept_id);

9) Multi Row Comparision Operator

- (NOT) IN: LIST에 있는 값과 같은지 비교

- ANY: SUBQUERY에 의해 RETURN되는 값 중 어느 하나와 비교

- ALL: SUBQUERY에 의해 RETURN되는 모든 값 비교

- (NOT) EXISTS: SUBQUERY에서 만족되는 값이 발견되면 TRUE 후 찾기 종료

*NOT EXISTS: NOT IN 연산자와 유사하지만 NOT IN연산자는  NULL값 리턴시 FALSE반환

- 예시(ANY)

SELECT name, salary, dept_id FROM s_emp

WHERE salary < ANY (SELECT AVG(salary) FROM s_emp GROUP BY dept_id);

- 예시(EXISTS)

SELECT id, name, title, dept_id FROM s_emp e

WHERE EXISTS (SELECT id FROM s_emp WHERE manager_id = e.id);

10) PSEUDO COLUMN(rowid, rownum)

- ROWID(80bit 18): 각 행의 실제 저장 위치(데이터 한 건 액세스 가능)

오브젝트 번호(32bit 6): 해당 데이터가 속하는 오브젝트 번호

상대 파일 번호(10bit 3): 데이터가 저장된 DATAFILE의 번호

블록 번호(22bit 6): DATAFILE 안의 어느 블록인지를 의미

데이터번호(16bit 3): 블록에서 데이터의 위치

*ROWID 확인 방법: SELECT ROWID FROM 테이블

- ROWNUM: QUERY의 결과로 반환되는 각 행들에 대한 순서(행 개수 제한시 사용)

11) TRIGGER(자동 실행 프로그램)

- 임의의 테이블에서 특정 DML문 수행 시 DB에서 자동 동작하는 프로그램

- 분류

TIMING: BEFORE TRIGGER / AFTER TRIGGER -> DML 수행 전과 후

수행 차수: STATEMENT TRIGGER -> 한번만 수행 / ROW TRIGGER -> 행 마다 수행

- 문형

CREATE [OR REPLACE] TRIGGER trigger명

timing event1 [OR event2...] OF 컬럼명 ON 테이블명

[FOR EACH ROW]

pl/sql_block;

12) 기타

- ORACLE HINT 예문(정렬)

select /*+INDEX_ASC (S_EMP S_EMP_NAME_IDX) */

* FROM S_EMP;