11.18.(금) OracleDB(6): 내장 함수(2)

내장 함수

MERGE

두 테이블을 병합할때, 조건에 따라서 INSERT, UPDATE가 가능하다.

먼저 병합할 테이블을 하나 새로 만들자.

CREATE TABLE emp2	-- 테이블 복사
AS SELECT * FROM emp WHERE deptno=10;

부서번호가 10인 사원 3명이 나올 것이다. KING, CLARK, MILLER

이제 emp2에 emp를 병합시킬건데, 그 중에 조건을 내새워서 성립될때와 아닐때의 경우를 각자 설정해서 병합해보자.

emp2는 emp에서 복사해온 테이블이기 때문에 병합했을때, 위 3명은 중복된 데이터일것이다. 따라서 조건을 "중복된 데이터일때"로 정하고, 중복되었다면 급여를 2000만큼 인상시키고, 중복되지 않았다면 emp2테이블에 추가할 것이다.

MERGE INTO emp2 a
USING emp b ON (a.empno=b.empno)	-- 조건에 반드시 괄호()포함
WHEN MATCHED THEN
	UPDATE SET sal = sal+2000	-- target table의 칼럼
WHEN NOT MATCHED THEN 
	INSERT VALUES (b.empno, b.ename, b.job, b.mgr, b.hiredate, b.sal, b.comm, b.deptno);

위 예시는 쉽게 말해서, 10번 부서의 사람들에게 급여를 2000만큼 인상시킨것과 같다.

CASE

DECODE 함수와 비슷하다.

Simple CASE

SELECT empno, ename, deptno,
	CASE deptno 
		WHEN 10 THEN 'PLANNING'
		WHEN 20 THEN 'HR'
		WHEN 30 THEN 'HQ'
		ELSE 'ACCOUNTING'
	END AS "DEPTNAME"
FROM emp;

deptno가 각각 10, 20, 30, 그외일때 어떤 데이터를 넣을지 설정하는 명령어이다.

CASE~END까지가 하나의 칼럼이라고 생각하면 된다. 칼럼명은 DEPTNAME이고, 조건은 deptno가 10일때 PLANNING, 20일때 HR, 30일때 HQ, 그 외엔 ACCOUNTING을 출력하라는 뜻이다.

Searched CASE

조금 더 자세하게 범위를 쪼개서 CASE를 실행하는 방법이다.

SELECT ename,
	CASE 	-- 여기에 칼럼명이 붙으면 칼럼명 LIKE 생략 가능
		WHEN ename LIKE 'AD%' THEN '10%'
		WHEN ename LIKE 'S%' THEN '20%'
		WHEN ename LIKE '%B%' THEN '30%'
		ELSE '40%'
	END AS R
FROM emp;

NULLIF

NULLIF(exp1, exp2)일때 exp1과 exp2값이 동일하면 NULL, 그렇지 않으면 exp1을 반환한다. exp1은 NULL이 올 수 없다.

SELECT NULLIF (123, 456) FROM dual;	-- 123
SELECT NULLIF (123, 123) FROM dual;	-- NULL

이것을 CASE문으로 바꿔 쓰면

SELECT
	CASE 
		WHEN 123=123 THEN NULL
		ELSE 456
	END
FROM dual;

COALESCE

COALESCE(e1, e2, e3,...)

e1이 NULL이 아니면 e1을, NULL이면 COALESCE(e2, e3,...)를 리턴한다.

SELECT comm, COALESCE(comm, 0) from emp;

comm의 데이터 중 NULL인 데이터는 0을 리턴하는 쿼리문이다.