MERGE 문을 사용하면 테이블에 신규 행을 삽입하거나, 테이블의 기존 행을 갱신, 삭제할 수 있다.

 

[기본 문법]

MERGE 문의 구문.

USING 절에 지정한 소스 테이블을 INTO 절에 지정한 타깃 테이블과 ON 절의 조건으로 조인한 후, 조인이 성공하면 MERGE UPDATE 절, 조인이 실패하면 MERGE INSERT 절을 수행한다.

 

MERGE

INTO {table | view | (subquery)} [t_alias]

USING {table | view | (subquery)} [t_alias]

ON (condition)

WHEN MATCHED THEN -- MERGE UPDATE 절

UPDATE

SET column = {expr | DEFAULT} [, column = {expr | DEFAULT}] ...

[WHERE condition]

[DELETE

WHERE condition]

WHEN NOT MATCHED THEN -- MERGE INSERT 절

INSERT [(column [, column] ...)]

VALUES ({expr | DEFAULT} [, {expr | DEFAULT}]...)

[WHERE condition];

 

항목 - 설명

INTO 절 - 갱신 또는 삽입할 타깃 테이블

USING 절 - 갱신 또는 삽입에 사용할 소스 테이블

ON 절 - 갱신 또는 삽입을 결정하는 조건

MERGE UPDATE 절 - ON 절의 조건이 만족하는 경우 수행될 구문

MERGE INSERT 절 - ON 절의 조건이 만족하지 않는 경우 수행될 구문

 

-- 예제 테이블 생성

DROP TABLE T1 PURGE;

DROP TABLE T2 PURGE;

DROP TABLE T3 PURGE;

DROP TABLE T4 PURGE;

 

CREATE TABLE T2 AS SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE DEPTNO = 20;

CREATE TABLE T1 AS SELECT * FROM T2 WHERE EMPNO IN (7369, 7566);

CREATE TABLE T3 AS SELECT * FROM T2 WHERE EMPNO = 7369;

CREATE TABLE T4 AS SELECT '2050' AS YYYY, A.* FROM T2 A UNION ALL

SELECT '2051' AS YYYY, A.* FROM T2 A;

 

-- 아래는 T1, T2 테이블을 조회한 결과.

-- SMITH와 JONES는 두 테이블에 모두 존재한다.

 

SELECT * FROM T1;

SELECT * FROM T2;

 

-- 아래의 MERGE 문을 수행하면 조인에 성공한 행은 갱신되고, 실패한 행은 삽입된다.

 

MERGE

INTO T1 T

USING T2 S

ON (T.EMPNO = S.EMPNO)

WHEN MATCHED THEN

UPDATE

SET T.SAL = S.SAL - 500

WHEN NOT MATCHED THEN

INSERT (T.EMPNO, T.ENAME, T.JOB)

VALUES (S.EMPNO, S.ENAME, S.JOB);

 

-- 아래는 T1 테이블을 조회한 결과다.

-- SMITH와 JONES의 SAL이 갱신되었고, T1 테이블이 존재하지 않았던 SCOTT, ADAMS, FORD가 신규 행으로 삽입되었다.

 

SELECT * FROM T1;

 

-- 이후 예제부터 MERGE 문을 수행하고 결과를 확인한 후 롤백을 수행하도록 하자.

ROLLBACK;

 

[선택 작업]

10.1 버전부터 MERGE UPDATE 절과 MERGE INSERT 절을 선택적으로 사용할 수 있다.

 

-- 아래는 MERGE UPDATE 절만 사용한 쿼리

MERGE

INTO T1 T

USING T2 S

ON (T.EMPNO = S.EMPNO)

WHEN MATCHED THEN

UPDATE

SET T.SAL = S.SAL - 500;

 

-- 아래는 T1 테이블을 조회한 결과.

-- 기존 행만 갱신된 것을 확인할 수 있다.

 

SELECT * FROM T1;

 

-- 위 MERGE 문은 아래 UPDATE 문과 결과가 동일하다.

UPDATE (SELECT A.SAL, B.SAL AS SAL_N FROM T1 A, T2 B WHERE B.EMPNO = A.EMPNO)

SET SAL = SAL_N - 500;

 

-- 아래는 MERGE INSERT 절만 사용한 쿼리

MERGE

INTO T1 T

USING T2 S

ON (T.EMPNO = S.EMPNO)

WHEN NOT MATCHED THEN

INSERT (T.EMPNO, T.ENAME, T.JOB)

VALUES (S.EMPNO, S.ENAME, S.JOB);

 

-- 아래는 T1 테이블을 조회한 결과다.

-- 기존 행은 갱신되지 않고 신규 행만 삽입된 것을 확인할 수 있다.

 

SELECT * FROM T1;

 

-- 위 MERGE 문은 아래 INSERT 문과 결과가 동일하다.

INSERT INTO T1 (EMPNO, ENAME, JOB, SAL)

SELECT EMPNO, ENAME, JOB, SAL

FROM T2 A

WHERE NOT EXISTS (SELECT 1 FROM T1 X WHERE X.EMPNO = A.EMPNO);

 

[WHERE 절]

10.1 버전부터 MERGE UPDATE 절과 MERGE INSERT 절에 WHERE 절을 사용할 수 있다.

 

아래는 MERGE UPDATE 절에 WHERE 절을 사용한 쿼리다.

WHERE 절에 타깃 테이블(T1)의 일반 조건을 기술했다.

 

MERGE

INTO T1 T

USING T2 S

ON (T.EMPNO = S.EMPNO)

WHEN MATCHED THEN

UPDATE

SET T.SAL = S.SAL - 500

WHERE T.JOB = 'CLERK';

 

아래는 T1 테이블을 조회한 결과다.

JOB이 CLERK인 SMITH의 SAL만 갱신된 것을 확인할 수 있다.

 

SELECT * FROM T1;

 

아래와 같이 인라인 뷰를 사용해도 동일한 결과를 얻을 수 있다.

 

MERGE

INTO (SELECT * FROM T1 WHERE JOB = 'CLERK') T

USING T2 S

ON (T.EMPNO = S.EMPNO)

WHEN MATCHED THEN

UPDATE

SET T.SAL = S.SAL - 500;

 

-- 아래 쿼리는 MERGE UPDATE 절의 WHERE 절에 소스 테이블(T2)의 일반 조건을 기술했다.

 

MERGE

INTO T1 T

USING T2 S

ON (T.EMPNO = S.EMPNO)

WHEN MATCHED THEN

UPDATE

SET T.SAL = S.SAL - 500

WHERE S.JOB = 'CLERK';

 

-- 아래는 T1 테이블을 조회한 결과다.

-- JOB이 CLERK인 SMITH의 SAL만 갱신된 것을 확인할 수 있다.

 

SELECT * FROM T1;

 

-- 아래와 같이 인라인 뷰를 사용해도 동일한 결과를 얻을 수 있다.

 

MERGE

INTO T1 T

USING (SELECT * FROM T2 WHERE JOB = 'CLERK') S

ON (T.EMPNO = S.EMPNO)

WHEN MATCHED THEN

UPDATE

SET T.SAL = S.SAL - 500;

 

-- 아래 쿼리와 같이 조건에 소스 테이블과 타깃 테이블의 열을 함께 사용할 경우에는 MERGE UPDATE 절의 WHERE 절에 조건을 기술해야 한다.

 

MERGE

INTO T1 T

USING T2 S

ON (T.EMPNO = S.EMPNO)

WHEN MATCHED THEN

UPDATE

SET T.SAL = S.SAL - 500

WHERE ( (T.JOB = 'CLERK' AND S.SAL >= 1000)

OR (T.JOB <> 'CLERK'));

 

-- 아래 쿼리는 에러가 발생한다.

-- MERGE INSERT 절의 WHERE 절에는 소스 테이블(T2)의 일반 조건만 기술할 수 있다.

 

MERGE

INTO T1 T

USING T2 S

ON (T.EMPNO = S.EMPNO)

WHEN NOT MATCHED THEN

INSERT (T.EMPNO, T.ENAME, T.JOB)

VALUES (S.EMPNO, S.ENAME, S.JOB)

WHERE T.JOB = 'CLERK';

 

ORA-38102 : INSERT WHERE 절에 부적합한 열이 있음 : "T"."JOB"

 

-- 아래 쿼리는 수행 시점에 에러가 발생할 수 있다.

-- MERGE INSERT 절을 사용할 경우 타깃 테이블 (T1)의 인라인 뷰에 일반 조건을 기술하지 않아야 한다.

(MERGE UPDATE 절과 MERGE INSERT 절을 함께 사용할 경우에도 타깃 테이블의 일반 조건을 기술하면 에러가 발생할 수 있다.)

 

MERGE

INTO (SELECT * FROM T1 WHERE JOB = 'CLERK') T

USING T2 S

ON (T.EMPNO = S.EMPNO)

WHEN NOT MATCHED THEN

INSERT (T.EMPNO, T.ENAME, T.JOB)

VALUES (S.EMPNO, S.ENAME, S.JOB);

 

ORA-00001: 무결성 제약 조건 (SCOTT.T1_PK)에 위배됩니다

 

-- 아래 쿼리는 MERGE INSERT 절의 WHERE 절에 소스 테이블 (T2)의 일반 조건을 기술했다.

 

MERGE

INTO T1 T

USING T2 S

ON (T.EMPNO = S.EMPNO)

WHEN NOT MATCHED THEN

INSERT (T.EMPNO, T.ENAME, T.JOB)

VALUES (S.EMPNO, S.ENAME, S.JOB)

WHERE S.JOB = 'CLERK';

 

-- 아래는 T1 테이블을 조회한 결과다.

-- ADAMS가 신규 행으로 삽입된 것을 확인할 수 있다.

 

SELECT * FROM T1;

 

-- 아래와 같이 인라인 뷰를 사용해도 동일한 결과를 얻을 수 있다.

-- MERGE UPDATE 절, MERGE INSERT 절의 WHERE 절보다 인라인 뷰에 일반 조건을 기술하는 편이 성능 측면에서 유리할 수 있다.

(옵티마이저 동작과 관련된 내용이다. 실행 계획 수립 시 일반 조건을 사용할 수 있는 방식에 차이가 있다는 정도로 이해하자.)

 

MERGE

INTO T1 T

USING (SELECT * FROM T2 WHERE JOB = 'CLERK') S

ON (T.EMPNO = S.EMPNO)

WHEN NOT MATCHED THEN

INSERT (T.EMPNO, T.ENAME, T.JOB)

VALUES (S.EMPNO, S.ENAME, S.JOB);

 

[DELETE 절]

10.1 버전부터 MERGE UPDATE 절에 DELETE 절을 기술할 수 있다.

DELETE 절은 MERGE UPDATE 절로 갱신된 행을 대상으로 수행되며, 갱신된 값을 기준으로 행을 삭제한다.

 

-- 아래는 MERGE UPDATE 절에 DELETE 절을 사용한 쿼리다.

-- 갱신된 SAL이 2000보다 작은 행을 삭제한다.

 

MERGE

INTO T1 T

USING T2 S

ON (T.EMPNO = S.EMPNO)

WHEN MATCHED THEN

UPDATE

SET T.SAL = S.SAL - 500

WHERE T.JOB = 'CLERK'

DELETE

WHERE T.SAL < 2000;

 

-- 아래는 T1 테이블을 조회한 결과다.

갱신된 SAL이 300인 SMITH가 삭제된 것을 확인할 수 있다.

 

SELECT * FROM T1;

 

-- 아래는 MERGE 문의 모든 절을 사용한 쿼리다.

-- MERGE UPDATE 절에 의해 T1, T2 테이블에 모두 존재하는 행 중 JOB이 CLERK인 행의 SAL이 갱신되고, 갱신된 SAL이 2000보다 작은 행이 삭제된다. MERGE INSERT 절에 의해 T2 테이블에만 존재하는 행 중 JOB이 CLERK인 행이 삽입된다.

 

MERGE

INTO T1 T

USING T2 S

ON (T.EMPNO = S.EMPNO)

WHEN MATCHED THEN

UPDATE

SET T.SAL = S.SAL - 500

WHERE T.JOB = 'CLERK'

DELETE

WHERE T.SAL < 2000

WHEN NOT MATCHED THEN

INSERT (T.EMPNO, T.ENAME, T.JOB)

VALUES (S.EMPNO, S.ENAME, S.JOB)

WHERE S.JOB = 'CLERK';

 

-- 아래는 T1 테이블을 조회한 결과다.

 

SELECT * FROM T1;

 

[고급 주제]

MERGE 문에 대한 고급 주제를 살펴보자. 모두 ON 절과 관련된 내용이다.

 

[조인 차수]

MERGE 문도 UPDATE 문처럼 조인 차수에 따라 에러가 발생할 수 있다.

 

-- 아래 쿼리는 에러가 발생한다.

-- T1, T4 테이블의 조인 차수가 1:M이므로 T1 테이블의 값이 T4 테이블의 값으로 여러 번 갱신될 수 있기 때문이다.

 

MERGE

INTO T1 T

USING T4 S

ON (T.EMPNO = S.EMPNO)

WHEN MATCHED THEN

UPDATE

SET T.SAL = S.SAL - 500;

 

ORA-30926: 원본 테이블의 고정 행 집합을 가져올 수 없습니다.

 

-- 아래 쿼리는 에러가 발생하지 않는다.

-- ROW_NUMBER 함수를 사용하여 조인 차수를 1:1로 변경했다.

 

MERGE

INTO T1 T

USING (SELECT *

FROM (SELECT A.*

, ROW_NUMBER () OVER (PARTITION BY A.EMPNO

ORDER BY A.YYYY DESC) AS RN

FROM T4 A)

WHERE RN = 1) S

ON (T.EMPNO = S.EMPNO)

WHEN MATCHED THEN

UPDATE

SET T.SAL = S.SAL - 500;

 

[조인 조건]

ON 절에 기술된 열은 갱신할 수 없다.

무한 루프가 발생할 수 있기 때문이다.

 

-- 아래 쿼리는 에러가 발생한다.

MERGE

INTO T1 T

USING (SELECT EMPNO, EMPNO + ROW_NUMBER () OVER (ORDER BY EMPNO) AS EMPNO_N

FROM T1) S

ON (T.EMPNO = S.EMPNO)

WHEN MATCHED THEN

UPDATE

SET T.EMPNO = S.EMPNO_N;

 

ORA-38104: ON 절에서 참조되는 열은 업데이트할 수 없음: "T"."EMPNO"

 

-- 갱신할 테이블이 동일 테이블이면 ROWID 슈도 칼럼을 사용할 수 있다.

MERGE

INTO T1 T

USING (SELECT EMPNO + ROW_NUMBER () OVER (ORDER BY EMPNO) AS EMPNO_N FROM T1) S

ON (T.ROWID = S.ROWID)

WHEN MATCHED THEN

UPDATE

SET T.EMPNO = S.EMPNO_N;

 

-- 다른 테이블을 기준으로 ON 절에 기술된 열을 갱신하려면 USING 절에서 갱신할 테이블을 조인해야 한다.

-- 아래 쿼리는 인라인 뷰(T3)와 T1 테이블을 조인했다.

 

MERGE

INTO T1 T

USING (SELECT S.EMPNO_N, B ROWID AS RID

FROM (SELECT EMPNO, EMPNO + ROW_NUMBER () OVER (ORDER BY EMPNO) AS EMPNO_N

FROM T3) A

, T1 B

WHERE B.EMPNO = A.EMPNO) S

ON (T.ROWID = S.RID)

WHEN MATCHED THEN

UPDATE

SET T.EMPNO = S.EMPNO_N;

 

[일반 조건]

MERGE 문의 ON 절은 SELECT 문의 WHERE 절과 유사하게 동작한다.

조인 조건과 일반 조건을 기술할 수 있지만 조인 조건만 사용하는 편이 성능 측면에서 효율적이다.

(옵티마이저 동작과 관련된 내용이다. 실행 계획 수립 시 일반 조건을 사용할 수 있는 방식에 차이가 있다는 정도로 이해하자.)

 

아래 쿼리는 ON 절에 타깃 테이블(T1)의 일반 조건(T.JOB = 'CLERK')을 기술했다.

MERGE

INTO T1 T

USING T2 S

ON (T.JOB = 'CLERK' AND T.EMPNO = S.EMPNO)

WHEN MATCHED THEN

UPDATE

SET T.SAL = S.SAL - 500;

 

-- 아래는 T1 테이블을 조회한 결과다.

-- ON 조건이 TRUE인 SMITH만 갱신된 것을 확인할 수 있다.

 

SELECT * FROM T1;

 

-- 아래 쿼리는 위 쿼리와 동일하게 동작한다. 아래 쿼리가 가독성 측면에서 바람직하다.

MERGE

INTO (SELECT * FROM T1 WHERE JOB = 'CLERK') T

USING T2 S

ON (T.EMPNO = S.EMPNO)

WHEN MATCHED THEN

UPDATE

SET T.SAL = S.SAL - 500;

 

-- 아래 쿼리는 ON 절에 소스 테이블(T2)의 일반 조건(S.JOB = 'CLERK')을 기술했다.

MERGE

INTO T1 T

USING T2 S

ON (T.EMPNO = S.EMPNO AND S.JOB = 'CLERK')

WHEN MATCHED THEN

UPDATE

SET T.SAL = S.SAL - 500;

 

-- 아래와 같이 인라인 뷰를 사용하는 편이 바람직하다.

MERGE

INTO T1 T

USING (SELECT * FROM T2 WHERE JOB = 'CLERK') S

ON (T.EMPNO = S.EMPNO)

WHEN MATCHED THEN

UPDATE

SET T.SAL = S.SAL - 500;

 

MERGE INSERT 문을 사용할 경우 ON 절에 타깃 테이블 (T1)의 일반 조건을 기술하면 에러가 발생할 수 있다.

아래 쿼리는 에러가 발생한다. ON 절이 FALSE인 행이 삽입되므로 T.JOB <> 'CLERK' OR <> T.EMPNO = S.EMPNO 조건이 TRUE인 행이 삽입될 수 있다.

JOB이 CLERK이 아니고 EMPNO가 동일한 행이 TRUE로 평가되므로 무결성 제약 조건 에러가 발생한 것이다.

 

MERGE

INTO T1 T

USING T2 S

ON (T.JOB = 'CLERK' AND T.EMPNO = S.EMPNO)

WHEN NOT MATCHED THEN

INSERT (T.EMPNO, T.ENAME, T.SAL)

VALUES (S.EMPNO, S.ENAME, S.SAL);

 

ORA-00001: 무결성 제약 조건(SCOTT.T1_PK)에 위배됩니다.

 

-- ON 절에 소스 테이블 (T2)의 일반 조건을 기술해도 에러가 발생할 수 있다. 원이느 위 쿼리와 동일하다.

MERGE

INTO T1 T

USING T2 S

ON (T.EMPNO = S.EMPNO AND S.JOB = 'CLERK')

WHEN NOT MATCHED THEN

INSERT (T.EMPNO, T.ENAME, T.SAL)

VALUES (S.EMPNO, S.ENAME, S.SAL);

 

ORA-00001: 무결성 제약 조건(SCOTT.T1_PK)에 위배됩니다.

 

[아우터 조인]

ON 절의 조인 조건에 (+) 기호를 기술하면 소스 테이블과 타깃 테이블이 아우터 조인으로 조인된다.

타깃 테이블의 전체 행을 갱신할 때 사용할 수 있다.

 

아래 쿼리는 ON 절의 조인 조건에 (+) 기호를 기술했다. 타깃 테이블 (T1)을 아우터 기준으로 소스 테이블 (T3)을 아우터 조인한다.

 

MERGE

INTO T1 T

USING T3 S

ON (T.3MPNO = S.EMPNO (+))

WHEN MATCHED THEN

UPDATE

SET T.SAL = NVL (S.SAL - 500, 0);

 

-- 아래는 T1 테이블을 조회한 결과다.

-- JONES는 T3 테이블에 존재하지 않지만 아우터 조인으로 인해 SAL이 0으로 갱신되었다.

 

SELECT * FROM T1;

 

-- 아래 쿼리는 위 쿼리와 결과가 동일하다.

-- 상관 서브 쿼리를 사용했기 때문에 쿼리 성능이 저하될 수 있다.

 

UPDATE T1 A

SET SAL = NVL ((SELECT X.SAL FROM T3 X WHERE X.EMPNO = A.EMPNO) - 500, 0);

 

-- 아래와 같이 인라인 뷰를 사용하면 MERGE 문과 동일한 성능을 보장할 수 있다.

UPDATE (SELECT A.SAL, B.SAL AS SAL_N FROM T1 A, T3 B WHERE B.EMPNO (+) = A.EMPNO)

SET SAL = NVL (SAL_N - 500, 0);

 

불친절한 SQL 프로그래밍저자정희락출판디비안(DBian)발매2018.09.10.

 

 

 

DELETE 문을 사용하면 테이블의 기존 행을 삭제할 수 있다.

 

DELETE

FROM {table | view | subquery} [t_alias]

WHERE condition;

 

아래 쿼리는 t1 테이블에서 10번 부서를 삭제한다.

DELETE FROM T1 WHERE DEPTNO = 10;

 

아래는 T1 테이블을 조회한 결과다.

10번 부서가 삭제된 것을 확인할 수 있다.

 

SELECT * FROM T1;

 

아래 쿼리는 사원이 존재하지 않는 부서를 삭제한다.

 

DELETE

FROM T1 A

WHERE NOT EXISTS (SELECT 1 FROM EMP X WHERE X.DEPTNO = A.DEPTNO);

 

WHERE 절을 기술하지 않으면 테이블의 모든 행이 삭제된다.

 

DELETE FROM T1;

 

 

[실수 방지]

UPDATE 문과 DELETE 문은 수행하기 전에 갱신 또는 삭제할 행을 반드시 확인해야 한다.

먼저 SELECT 문을 작성하고, SELECT 문을 UPDATE 문이나 DELETE 문으로 변경하는 방식을 사용해야 실수를 방지할 수 있다.

 

아래 쿼리는 SELECT 문으로 갱신할 행과 값을 확인한 후, UPDATE 문으로 변경하는 과정을 보여준다.

강조한 부분을 변경하면 된다.

 

SELECT A.*

, (SELECT SUM (X.COMM)

FROM EMP X

WHERE X.DEPTNO = A.DEPTNO)

FROM T1 A

WHERE EXISTS (SELECT 1

FROM EMP X

WHERE X.DEPTNO = A.DEPTNO);

 

UPDATE T1 A

SET A.SAL = (SELECT SUM (X.SAL)

FROM EMP X

WHERE X.DEPTNO = A.DEPTNO)

WHERE EXISTS (SELECT 1

FROM EMP X

WHERE X.DEPTNO = A.DEPTNO);

 

DELETE 문도 SELECT 문으로 삭제할 행을 확인한 후 DELETE 문으로 변경하는 편이 바람직하다.

 

SELECT A.*

FROM T1 A

WHERE NOT EXISTS (SELECT 1

FROM EMP X

WHERE X.DEPTNO = A.DEPTNO);

 

DELETE

FROM T1 A

WHERE NOT EXISTS (SELECT 1

FROM EMP X

WHERE X.DEPTNO = A.DEPTNO);

 

 

 

불친절한 SQL 프로그래밍저자정희락출판디비안(DBian)발매2018.09.10.

 

 

 

UPDATE 문을 사용하면 테이블의 기존 행을 갱신할 수 있다.

 

UPDATE {table | view | subquery} [t_alias]

SET {column = {expr | (subquery) | DEFAULT} | (column [, column]...) = (subquery)}

[, {column = {expr | (subquery) | DEFAULT} | (column [, column]...) = (subquery)}] ...

WHERE condition;

 

예제를 위해 아래와 같이 테이블을 생성하자.

T1, T2 테이블은 1:M 비식별 관계를 가진다.

 

DROP TABLE T1 PURGE;

DROP TABLE T2 PURGE;

 

CREATE TABLE T1 AS SELECT DEPTNO, DNAME, 0 AS SAL, 0 AS COMM FROM DEPT;

CREATE TABLE T2 AS SELECT EMPNO, ENAME, SAL, COMM, DEPTNO FROM EMP;

 

ALTER TABLE T1 ADD CONSTRAINT T1_PK PRIMARY KEY (deptno);

ALTER TABLE T2 ADD CONSTRAINT T2_PK PRIMARY KEY (empno);

ALTER TABLE T2 ADD CONSTRAINT T2_F1 FOREIGN KEY (deptno) REFERENCES T1 (deptno);

 

아래는 T1 테이블을 조회한 결과다.

SAL과 COMM이 모두 0이다.

 

SELECT * FROM T1;

 

아래 쿼리는 40번 부서의 SAL을 10000, COMM을 1000으로 갱신한다.

 

UPDATE T1 SET SAL = 10000, COMM = 1000 WHERE DEPTNO = 40;

 

아래는 T1 테이블을 조회한 결과다.

40번 부서의 SAL과 COMM이 각각 10000, 1000으로 갱신된 것을 확인할 수 있다.

 

SELECT * FROM T1;

 

아래 쿼리는 SET 절에 다중 열 서브 쿼리를 사용했다.

서브 쿼리의 결과로 값이 갱신된다.

 

UPDATE T1 A

SET (A.SAL, A.COMM) =

(SELECT SUM (X.SAL), SUM (X.COMM) FROM T2 X WHERE X.DEPTNO = A.DEPTNO);

 

아래는 T1 테이블을 조회한 결과다.

40번 부서의 SAL과 COMM이 널로 갱신된 것을 확인할 수 있다.

T2 테이블에 DEPTNO가 40인 행이 존재하지 않기 때문에 널로 갱신된 것이다.

 

SELECT * FROM T1;

 

아래와 같이 WHERE 절에 상관 서브 쿼리를 사용하면 불필요한 갱신을 방지할 수 있다.

 

UPDATE T1 A

SET (A.SAL, A.COMM) =

(SELECT SUM (X.SAL), SUM (X.COMM) FROM T2 X WHERE X.DEPTNO = A.DEPTNO)

WHERE EXISTS (SELECT 1 FROM T2 X WHERE X.DEPTNO = A.DEPTNO);

 

UPDATE 문에도 인라인 뷰를 사용할 수 있다.

아래 쿼리는 위 쿼리와 결과가 동일하다.

인라인 뷰를 통해 T2 테이블을 1번만 읽고 갱신을 수행했다.

위 쿼리에 비해 성능 측면에서 효율적이다.

 

UPDATE (SELECT A.SAL, A.COMM, B.SAL AS SAL_N, B.COMM AS COMM_N

FROM T1 A

, (SELECT DEPTNO, SUM (SAL) AS SAL, SUM (COMM) AS COMM

FROM T2

GROUP BY DEPTNO) B

WHERE B.DEPTNO = A.DEPTNO)

SET SAL = SAL_N, COMM = COMM_N;

 

위 쿼리는 11.2 이하 버전에서 "ORA-01779 : 키-보존된 것이 아닌 테이블로 대응한 열을 갱신할 수 없습니다" 에러가 발생한다.

(T2 테이블을 조인 조건인 DEPTNO 열로 그룹핑했기 때문에 조인 차수가 1:1이다. T1 테이블의 단일 행이 단일 값으로 갱신되는 것이 보장되므로 불필요한 에러를 발생시킨 것이다. 12.1 버전부터는 에러가 발생하지 않는다.)

11.2 이하 버전에서는 아래와 같이 MERGE 문을 사용해야 한다.

(10.2 이하 버전에서 BYPASS_UJVC 힌트를 사용하면 에러가 발생하지 않는다. 해당 힌트는 문서화되지 않은 힌트다.

MERGE 문을 사용하는 편이 바람직하다.)

 

MERGE

INTO T1 T

USING (SELECT DEPTNO, SUM (SAL) AS SAL, SUM (COMM) AS COMM

FROM T2

GROUP BY DEPTNO) S

ON (T.DEPTNO = S.DEPTNO)

WHEN MATCHED THEN

UPDATE

SET T.SAL = S.SAL, T.COMM = S.COMM;

 

아래 쿼리는 버전과 관계없이 에러가 발생한다.

갱신할 테이블(T1)과 나머지 테이블(T2)의 조인 차수가 1:M이면, 1쪽 테이블(T1)의 값이 M쪽 테이블(T2)의 값으로 여러 번 갱신될 수 있기 때문이다.

 

UPDATE (SELECT A.SAL, A.COMM, B.SAL AS SAL_N, B.COMM AS COMM_N

FROM T1 A, T2 B

WHERE B.DEPTNO = A.DEPTNO)

SET SAL = SAL_N, COMM = COMM_N;

 

ORA-01779 : 키-보존된 것이 아닌 테이블로 대응한 열을 수정할 수 없습니다.

 

M쪽 테이블 (T2)의 값을 갱신하면 에러가 발생하지 않는다.

M쪽 테이블(T2)의 값이 1쪽 테이블(T1)의 값을 1번만 갱신되는 것이 보장되기 때문이다.

1:M 조인 차수에서 M쪽 테이블의 행이 늘어나지 않는 것과 동일한 원리다.

 

UPDATE (SELECT A.SAL, A.COMM, B.SAL AS SAL_N, B.COMM AS COMM_N

FROM T2 A, T1 B

WHERE B.DEPTNO = A.DEPTNO)

SET SAL = SAL_N, COMM = COMM_N;

 

아래와 같이 T1 테이블의 PK 제약 조건을 삭제해보자.

 

ALTER TABLE T1 DROP CONSTRAINT T1_PK CASCADE;

 

동일한 쿼리를 수행하면 에러가 발생한다.

T1 테이블에 PK 제약 조건이 없기 때문에 T2 테이블의 값이 1번만 갱신되는 것을 보장할 수 없기 때문이다.

 

UPDATE (SELECT A.SAL, A.COMM, B.SAL AS SAL_N, B.COMM AS COMM_N

FROM T2 A, T1 B

WHERE B.DEPTNO = A.DEPTNO)

SET SAL = SAL_N, COMM = COMM_N;

 

ORA-01779 : 키-보존된 것이 아닌 테이블로 대응한 열을 수정할 수 없습니다.

 

아래 쿼리는 버전과 관계없이 정상적으로 동작한다.

T1 테이블과 DEPT 테이블이 DEPTNO 열로 등가 조인되기 때문에 조인 차수가 1:1이다.

T1 테이블의 단일 행이 단일 값으로 갱신되는 것이 보장된다.

 

UPDATE (SELECT A.*, B.DNAME AS DNAME_N

FROM T1 A, DEPT B

WHERE B.DEPTNO = A.DEPTNO)

SET DNAME = DNAME_N;

 

불친절한 SQL 프로그래밍저자정희락출판디비안(DBian)발매2018.09.10.

 

 

 

DML은 Data Manipulation Language의 약자다.

데이터 조작어로 해석할 수 있다.

DML 문을 사용하면 테이블에 신규 행을 삽입하거나, 테이블의 기존 행을 갱신, 삭제할 수 있다.

 

[INSERT 문]

INSERT 문을 사용하면 테이블에 신규 행을 삽입할 수 있다.

INSERT 문은 삽입할 테이블의 개수에 따라 단일 테이블 INSERT 문과 다중 테이블 INSERT 문으로 구분할 수 있다.

 

[단일 테이블 INSERT 문]

단일 테이블 INSERT 문은 하나의 테이블에 행을 삽입한다.

VALUES 절과 서브 쿼리 방식을 사용할 수 있다.

 

[VALUES 절]

INSERT 문에 VALUES 절을 사용하면 단일 행을 삽입할 수 있다.

 

INSERT

INTO {table | view | subquery} [t_alias] [(column [, column] ...)]

VALUES ({expr | DEFAULT} [, {expr | DEFAULT}] ...)

 

-- 예제를 위해 아래와 같이 테이블을 생성.

deptno 열의 기본값을 20으로 지정했다.

 

DROP TABLE T1 PURGE;

CREATE TABLE T1 (EMPNO NUMBER(4), ENAME VARCHAR2(10), DEPTNO NUMBER(2) DEFAULT 20);

 

아래 쿼리는 T1 테이블에 단일 행을 삽입한다.

INTO 절에 삽입할 열을 지정했고, VALUES 절에 삽입할 값을 기술했다.

 

INSERT INTO T1 (EMPNO, ENAME, DEPTNO) VALUES (7369, 'SMITH', 20);

 

아래는 T1 테이블을 조회한 결과다.

행이 삽입된 것을 확인할 수 있다.

 

SELECT * FROM T1;

 

아래 쿼리는 INTO 절에 EMPNO 열만 지정했다.

INTO 절에 지정되지 않은 열은 기본값이 있으면 기본값, 기본값이 없으면 널이 삽입된다.

 

INSERT INTO T1 (EMPNO) VALUES (7566);

 

아래 쿼리에서 결과를 확인할 수 있다.

ENAME 열은 널, DEPTNO 열은 기본값인 20이 삽입되었다.

 

SELECT * FROM T1;

 

아래 쿼리는 INTO 절에 열을 지정하지 않았다.

INTO 절에 열을 지정하지 않으면 VALUES 절에 전체 열의 값을 기술해야 한다.

 

INSERT INTO T1 VALUES (7788, 'SCOTT', 20);

 

기본값이 지정된 열은 VALUES 절에 DEFAULT 키워드를 기술할 수 있다.

아래 쿼리는 기본값이 지정된 DEPTNO 열에 DEFAULT 키워드를 기술했다.

 

INSERT INTO T1 VALUES (7876, 'ADAMS', DEFAULT);

 

아래는 T1 테이블을 조회한 결과다.

ADAMS의 DEPTNO 열에 기본값인 20이 삽입된 것을 확인할 수 있다.

 

SELECT * FROM T1;

 

열 개수와 값 개수가 일치하지 않으면 아래와 같이 에러가 발생한다.

 

INSERT INTO T1

VALUES (7902, 'FORD');

 

ORA-00947 : 값의 수가 충분하지 않습니다.

 

INSERT

INTO T1

VALUES (7902, 'FORD', 20, ANALYST);

 

ORA-00913 : 값의 수가 너무 많습니다.

 

예제를 위해 T1 테이블에 JOB 열을 추가하자.

 

ALTER TABLE T1 ADD JOB VARCHAR2(9);

 

아래 쿼리는 에러가 발생한다.

JOB 열이 추가되어 열 개수와 값 개수가 달라졌기 때문이다.

 

INSERT INTO T1 VALUES (7902, 'FORD', 20);

 

ORA-00947 : 값의 수가 충분하지 않습니다.

 

아래 쿼리는 INTO 절에 열을 지정했기 때문에 에러가 발생하지 않는다.

INTO 절에 열을 지정하는 편이 쿼리의 안정성 측면에서 바람직하다.

 

INSERT INTO T1 (EMPNO, ENAME, DEPTNO) VALUES (7902, 'FORD', 20);

 

[서브 쿼리]

INSERT 문에 서브 쿼리를 사용하면 서브 쿼리의 결과를 테이블에 삽입할 수 있다.

서브 쿼리의 결과가 다중 행이면 다중 행이 삽입된다.

 

INSERT

INTO {table | view | subquery} [t_alias] [(column [, column]...)]

subquery

 

아래는 INSERT 문에 서브 쿼리를 사용한 쿼리다.

job이 ANLAYST인 행을 T1 테이블에 삽입했다.

 

INSERT INTO T1 (EMPNO, ENAME)

SELECT EMPNO, ENAME FROM EMP WHERE JOB = 'ANALYST';

 

서브 쿼리에 UNION ALL 연산자를 사용하면 여러 테이블의 행을 삽입할 수도 있다.

 

INSERT INTO T1 (EMPNO, ENAME)

SELECT EMPNO, ENAME FROM EMP WHERE JOB = 'PRESIDENT'

UNION ALL

SEECT DEPTNO, DNAME FROM DEPT WHERE DEPTNO = 10;

 

[다중 테이블 INSERT 문]

다중 테이블 INSERT 문은 다수의 테이블에 행을 삽입한다.

조건의 유무에 따라 무조건 INSERT 문과 조건부 INSERT 문으로 구분할 수 있다.

 

[무조건 INSERT 문]

무조건 (unconditional) INSERT 문은 INTO 절에 지정한 모든 테이블에 서브 쿼리의 결과를 삽입한다.

 

INSERT ALL

{INTO table [(column [, column]...)] [VALUES ({expr | DEFAULT} [, {expr | DEFAULT}]...)}...

subquery

 

예제를 위해 아래와 같이 테이블을 생성하자.

DROP TABLE T1 PURGE;

DROP TABLE T2 PURGE;

 

CREATE TABLE T1 (EMPNO NUMBER(4), JOB VARCHAR2(9));

CREATE TABLE T2 (EMPNO NUMBER(4), MGR NUMBER(4));

 

아래 쿼리는 서브 쿼리의 결과를 T1, T2 테이블에 삽입한다.

 

INSERT ALL

INTO T1 (EMPNO, JOB) VALUES (EMPNO, JOB)

INTO T2 (EMPNO, MGR) VALUES (EMPNO, MGR)

SELECT * FROM EMP WHERE DEPTNO = 10;

 

아래는 T1, T2 테이블을 조회한 결과다.

T1, T2 테이블에 각각 3행씩 삽입된 것을 확인할 수 있다.

 

SELECT * FROM T1;

SELECT * FROM T2;

 

무조건 INSERT 문을 사용하면 PIVOT한 데이터를 삽입할 수 있다.

 

DROP TABLE T1 PURGE;

CREATE TABLE T1 (DEPTNO NUMBER(2), TP VARCHAR2(3), SAL NUMBER(7, 2));

 

아래 쿼리는 INTO 절에 T1 테이블을 4번 기술했다.

서브 쿼리의 1행이 T1 테이블에 4번 삽입된다.

아래와 같은 INSERT 문을 PIVOT INSERT 문으로 부르기도 한다.

 

INSERT ALL

INTO T1 VALUES (DEPTNO, 'MIN', SAL_MIN)

INTO T1 VALUES (DEPTNO, 'MAX', SAL_MAX)

INTO T1 VALUES (DEPTNO, 'SUM', SAL_SUM)

INTO T1 VALUES (DEPTNO, 'AVG', SAL_AVG)

SELECT DEPTNO

, MIN (SAL) AS SAL_MIN, MAX (SAL) AS SAL_MAX

, SUM (SAL) AS SAL_SUM, AVG (SAL) AS SAL_AVG

FROM EMP

GROUP BY DEPTNO;

 

아래는 T1 테이블을 조회한 결과다.

위 쿼리의 SAL_MIN, SAL_MAX, SAL_SUM, SAL_AVG 열이 행으로 PIVOT되어 삽입된 것을 확인할 수 있다.

 

SELECT * FROM T1 ORDER BY 1, 2;

 

아래와 같이 UNPIVOT 절을 사용해도 동일한 결과를 얻을 수 있지만, PIVOT INSERT 문을 사용하는 편이 성능 측면에서 효율적이다.

 

INSERT

INTO T1 (DEPTNO, TP, SAL)

SELECT *

FROM (SELECT DEPTNO

, MIN (SAL) AS SAL_MIN, MAX (SAL) AS SAL_MAX

, SUM (SAL) AS SAL_SUM, AVG (SAL) AS SAL_AVG

FROM EMP

GROUP BY DEPTNO)

UNPIVOT (SAL FOR TP IN (SAL_MIN AS 'MIN', SAL_MAX AS 'MAX'

, SAL_SUM AS 'SUM', SAL_AVG AS 'AVG'));

 

[조건부 INSERT 문]

조건부 (condition) INSERT 문은 서브 쿼리의 결과에서 condition을 만족하는 행을 INTO 절에 지정한 테이블에 삽입한다.

모든 condition을 만족하지 않은 행은 ELSE 절에 지정한 테이블에 삽입되고, ELSE 절이 기술되지 않았다면 무시된다.

 

INSERT [ALL | FIRST]

WHEN condition THEN

INTO table [(column [, column]...)] [VALUES ({expr | DEFAULT} [, {expr | DEFAULT}]...)]

[ WHEN condition THEN

INTO table [(column [, column]...)] [VALUES ({expr | DEFAULT} [, {expr | DEFAULT}]...)]]

[ELSE

INTO table [(column [, column]...)] [VALUES ({expr | DEFAULT} [, {expr | DEFAULT}]...)]]

subquery

 

조건부 INSERT 문은 ALL 방식과 FIRST 방식을 사용할 수 있다.

 

항목 - 설명

ALL - 조건을 만족하는 모든 테이블에 삽입 (기본값)

FIRST - 조건을 만족하는 첫 번째 테이블에 삽입

 

예제를 위해 아래와 같이 테이블을 생성

DROP TABLE T1 PURGE;

DROP TABLE T2 PURGE;

DROP TABLE T3 PURGE;

 

CREATE TABLE T1 AS SELECT EMPNO, ENAME, SAL FROM EMP WHERE 0 = 1;

CREATE TABLE T2 AS SELECT * FROM T1;

CREATE TABLE T3 AS SELECT * FROM T1;

 

아래는 ALL 방식을 사용한 쿼리다.

조건을 만족하는 모든 테이블에 서브 쿼리의 결과가 삽입된다.

 

INSERT ALL

WHEN SAL >= 2000 THEN INTO T1

WHEN SAL >= 3000 THEN INTO T2

ELSE INTO T3

SELECT EMPNO, ENAME, SAL FROM WHERE DEPTNO = 10;

 

아래는 T1, T2, T3 테이블을 조회한 결과다.

조건에 따라 테이블에 행이 삽입된 것을 확인할 수 있다.

KING은 T1, T2 테이블 모두에 삽입되었다.

 

SELECT * FROM T1;

SELECT * FROM T2;

SELECT * FROM T3;

 

다음 예제를 위해 롤백을 수행하자.

롤백은 DML 문의 변경 내용을 취소한다.

 

ROLLBACK;

 

아래는 FIRST 방식을 사용한 쿼리다.

FIRST 방식은 WHEN 절의 기술 순서에 주의해야 한다.

 

INSERT FIRST

WHEN SAL >= 2000 THEN INTO T1

WHEN SAL >= 3000 THEN INTO T2

ELSE INTO T3

SELECT EMPNO, ENAME, SAL FROM EMP WHERE DEPTNO = 10;

 

아래는 T1, T2, T3 테이블을 조회한 결과다.

T2 테이블에 행이 삽입되지 않은 것을 확인할 수 있다.

SAL >= 2000 조건을 만족하는 모든 행이 SAL >= 3000 조건을 만족하기 때문이다.

 

SELECT * FROM T1;

SLEECT * FROM T2;

SELECT * FROM T3;

 

불친절한 SQL 프로그래밍저자정희락출판디비안(DBian)발매2018.09.10.

 

 

 

[동적 PIVOT]

동적 PIVOT은 쿼리 수행 시점에 동적으로 열이 변경되는 PIVOT을 말한다.

쿼리는 수행 전에 열의 개수가 결정되어야 하기 때문에 정적(static) 쿼리로는 동적 PIVOT이 불가능하다.

XML 키워드를 사용하면 반환되는 열이 1개로 고정되므로 동적 PIVOT이 가능하지만 활용도는 높지 않다.

결국 동적(Dynamic) 쿼리를 사용해야 동적 PIVOT을 수행할 수 있다.

 

아래 쿼리는 동적 PIVOT을 수행한다.

SQL*Plus는 대체 변수로 동적 쿼리를 생성할 수 있다.

DEF[INE] 명령어로 대체 변수를 선언하고, & 변수 형식으로 대체 변수를 참조했다.

 

DEF V_AF = 'SUM (SAL) AS SAL';

DEF V_IL = 'DEPTNO IN (10 AS D10, 20 AS D20)';

 

SELECT *

FROM (SELECT JOB, DEPTNO, SAL FROM EMP)

PIVOT (&V_AF FOR &V_IL)

ORDER BY 1;

 

아래 쿼리는 대체 변수를 변경하여 동적 PIVOT을 수행한다.

 

DEFINE V_AF = 'SUM (SAL) AS SAL, COUNT (*) AS CNT';

DEFINE V_IL = 'DEPTNO IN (10 AS D10)';

 

SELECT *

FROM (SELECT JOB, DEPTNO, SAL FROM EMP)

PIVOT (&V_AF FOR &V_IL)

ORDER BY 1;

 

[전체 열 UNPIVOT]

테이블의 전체 열을 UNPIVOT하여 값을 조회하거나 집계할 수 있다.

 

아래 쿼리는 테이블의 전체 열을 UNPIVOT한다.

열이 많은 행을 조회할 때 유용하다.

IN 절에 입력되는 값은 데이터 타입이 모두 동일해야 한다.

 

WITH W1 AS (

SELECT TO_CHAR (EMPNO) AS EMPNO, ENAME, JOB, TO_CHAR (MGR) AS MGR

, TO_CHAR (HIREDATE, 'YYYY-MM-DD') AS HIREDATE, TO_CHAR (SAL) AS SAL

, TO_CHAR (COMM) AS COMM, TO_CHAR (DEPTNO) AS DEPTNO

FROM EMP

WHERE EMPNO = 7788)

SELECT *

FROM W1

UNPIVOT (VALUE FOR COLUMN_NAME IN (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO));

 

UNPIVOT 절을 사용하면 열 값의 분포를 계산할 수 있다.

아래 쿼리는 JOB 열과 DEPTNO 열 값의 분포를 계산한다.

 

SELECT COLUMN_NAME, VALUE, COUNT (*) AS CNT

FROM (SELECT JOB, TO_CHAR (DEPTNO) AS DEPTNO FROM EMP)

UNPIVOT (VALUE FOR COLUMN_NAME IN (JOB, DEPTNO))

GROUP BY COLUMN_NAME, VALUE

ORDER BY 1, 2;

 

[행령 전환 집계]

DW 시스템은 행을 열로 전환한 후 수식을 통해 값을 계산하고, 계산된 값을 다시 행으로 전환하는 집계 방식을 사용한다.

 

아래 쿼리는 DW 시스템에서 자주 사용하는 집계 쿼리 방식이다.

W1에서 부서별 SAL을 열로 PIVOT하고, W2에서 표현식으로 열을 계산한 후, 메인 쿼리에서 계산한 열을 행으로 UNPIVOT 한다.

20번 부서의 CLERK은 타 부서 SAL의 평균 대비 169% 수준의 SAL을 받는 것으로 계산된다.

 

WITH W1 AS (

SELECT JOB

, NVL (D10_SAL, 0) AS D10_SAL

, NVL (D20_SAL, 0) AS D20_SAL

, NVL (D30_SAL, 0) AS D30_SAL

FROM (SELECT JOB, DEPTNO, SAL FROM EMP)

PIVOT (SUM (SAL) AS SAL

FOR DEPTNO IN (10 AS D10, 20 AS D20, 30 AS D30)))

, W2 AS (

SELECT A.*

, D10_SAL / NVL (NULLIF (D20_SAL + D30_SAL, 0) / 2, D10_SAL) AS D10_RATIO

, D20_SAL / NVL (NULLIF (D10_SAL + D30_SAL, 0) / 2, D20_SAL) AS D20_RATIO

, D30_SAL / NVL (NULLIF (D10_SAL + D20_SAL, 0) / 2, D30_SAL) AS D30_RATIO

FROM W1 A)

SELECT JOB, DEPTNO, SAL, ROUND (RATIO, 2) * 100 AS RATIO

FROM W2

UNPIVOT ((SAL, RATIO) FOR DEPTNO IN ((D10_SA, D10_RATIO) AS 10

, (D20_SAL, D20_RATIO) AS 20

, (D30_SAL, D30_RATIO) AS 30))

ORDER BY 1, 2;

 

불친절한 SQL 프로그래밍저자정희락출판디비안(DBian)발매2018.09.10.

 

 

 

PIVOT은 회전시킨다는 의미를 가지고 있다.

PIVOT 절은 행을 열로 회전시키고, UNPIVOT 절은 열을 행으로 회전시킨다.

PIVOT 절과 UNPIVOT 절은 11.1 버전부터 사용할 수 있다.

 

[PIVOT 절]

PIVOT 절은 행을 열로 전환한다.

 

[기본 문법]

PIVOT 절의 구문은 아래와 같다.

 

PIVOT [XML]

(aggregate_function (expr) [[AS] alias]

[, aggregate_function (expr) [[AS] alias]]...

FOR {column | (column [, column]...)}

IN ({{{expr | (expr [, expr]...)} [[AS] alias]}...

| subquery

| ANY [, ANY] ...

})

)

 

항목 - 설명

aggregate_function - 집계할 열을 지정

FOR 절 - PIVOT할 열을 지정

IN 절 - PIVOT할 열 값을 지정

 

PIVOT 절을 사용한 쿼리.

PIVOT 절은 집계 함수와 FOR 절에 지정되지 않은 열을 기준으로 집계되기 때문에 인라인 뷰를 통해 사용할 열을 지정해야 한다.

 

SELECT *

FROM (SELECT JOB, DEPTNO, SAL FROM EMP)

PIVOT (SUM (SAL) FOR DEPTNO IN (10, 20, 30))

ORDER BY 1;

 

아래 쿼리는 인라인 뷰에 YYYY 표현식을 추가했다.

행 그룹에 YYYY 표현식이 추가된 것을 확인할 수 있다.

 

SELECT *

FROM (SELECT TO_CHAR (HIREDATE, 'YYYY') AS YYYY, JOB, DEPTNO, SAL FROM EMP)

PIVOT (SUM (SAL) FOR DEPTNO IN (10, 20, 30))

ORDER BY 1, 2;

 

아래 쿼리는 집계 함수와 IN 절에 별칭을 지정했다.

별칭을 지정하면 결과 집합의 열명이 변경된다.

 

SELECT *

FROM (SELECT JOB, DEPTNO, SAL FROM EMP)

PIVOT (SUM (SAL) AS SAL FOR DEPTNO IN (10 AS D10, 20 AS D20, 30 AS D30))

ORDER BY 1;

 

집계 함수와 IN 절에 지정한 별칭에 따라 아래와 같은 규칙으로 열명이 부여된다.

집계 함수와 IN 절 모두 별칭을 지정하는 편이 바람직하다.

 

10 - 10 AS D10

SUM (SAL) 10 - D10

SUM (SAL) AS SAL 10_SAL - D10_SAL

 

SELECT 절에 부여된 열명을 지정하면 필요한 열만 조회할 수 있다.

 

SELECT JOB, D20_SAL

FROM (SELECT JOB, DEPTNO, SAL FROM EMP)

PIVOT (SUM (SAL) AS SAL FOR DEPTNO IN (10 AS D10, 20 AS D20, 30 AS D30))

WHERE D20_SAL > 2500

ORDER BY 1;

 

PIVOT 절은 다수의 집계 함수를 지원한다.

아래 쿼리는 SUM 함수와 COUNT 함수를 함께 사용했다.

 

SELECT *

FROM (SELECT JOB, DEPTNO, SAL FROM EMP)

PIVOT (SUM (SAL) AS SAL, COUNT (*) AS CNT FOR DEPTNO IN (10 AS D10, 20 AS D20))

ORDER BY 1;

 

FOR 절에도 다수의 열을 기술할 수 있다.

아래와 같이 IN 절에 다중 열을 사용해야 한다.

 

SELECT *

FROM (SELECT TO_CHAR (HIREDATE, 'YYYY') AS YYYY, JOB, DEPTNO, SAL FROM EMP)

PIVOT (SUM (SAL) AS SAL, COUNT (*) AS CNT

FOR (DEPTNO, JOB) IN ((10, 'ANALYST') AS D10A, (10, 'CLERK') AS D10C

, (20, 'ANALYST') AS D20A, (20, 'CLERK') AS D20C))

ORDER BY 1;

 

PIVOT 절에 XML 키워드를 기술하면 XML 포맷으로 결과가 반환된다.

XML 키워드를 사용하면 IN 절에 서브 쿼리와 ANY 키워드를 사용할 수 있다.

아래는 서브 쿼리를 사용한 쿼리다.

 

SELECT *

FROM (SELECT JOB, DEPTNO, SAL FROM EMP)

PIVOT XML (SUM (SAL) AS SAL FOR DEPTNO IN (SELECT DEPTNO FROM DEPT))

ORDER BY 1;

 

ANY 키워드는 존재하는 값과 일치하는 와일드 카드로 동작한다.

아래 쿼리에서 ANY 키워드는 10, 20, 30과 일치한다.

 

SELECT *

FROM (SELECT JOB, DEPTNO, SAL FROM EMP)

PIVOT XML (SUM (SAL) AS SAL FOR DEPTNO IN (ANY))

ORDER BY 1;

 

XML 키워드를 기술해도 다수의 집계 함수와 다수의 열을 사용할 수 있다.

 

SELECT *

FROM (SELECT JOB, DEPTNO, SAL FROM EMP)

PIVOT XML (SUM (SAL) AS SAL, COUNT (*) AS CNT FOR (DEPTNO, JOB) IN (ANY, ANY));

 

[기존 방식]

11.1 이전 버전은 집계 함수와 DECODE 함수로 PIVOT을 수행할 수 있다.

 

SELECT JOB

, SUM (DECODE (DEPTNO, 10, SAL) AS D10_SAL

, SUM (DECODE (DEPTNO, 20, SAL) AS D20_SAL

, SUM (DECODE (DEPTNO, 30, SAL) AS D30_SAL

FROM EMP

GROUP BY JOB

ORDER BY JOB;

 

[UNPIVOT 절]

UNPIVOT 절은 PIVOT 절과 반대로 동작한다.

열을 행으로 전환한다.

 

[기본 문법]

UNPIVOT 절의 구문은 아래와 같다.

 

UNPIVOT [{INCODE | EXCLUDE} NULLS]

( {column | (column [, col]...)}

FOR {column | (column [, col]...)}

IN {column | (column [, col]...)} [AS {literal | (literal [, literal]...)}]

[, {column | (column [, col]...)} [AS {literal | (literal [, literal]...)}]...

)

)

 

항목 - 설명

UNPIVOT column - UNPIVOT된 값이 들어갈 열을 지정

FOR 절 - UNPIVOT된 값을 설명할 값이 들어갈 열을 지정

IN 절 - UNPIVOT할 열과 설명할 값의 리터럴 값을 지정

 

예제를 위해 아래와 같이 테이블 생성

DROP TABLE T1 PURGE;

 

CREATE TABLE T1 AS

SELECT JOB, D10_SAL, D20_SAL, D10_CNT, D20_CNT

FROM (SELECT JOB, DEPTNO, SAL FROM EMP WHERE JOB IN ('ANALYST', 'CLERK'))

PIVOT (SUM (SAL) AS SAL, COUNT (*) AS CNT FOR DEPTNO IN (10 AS D10, 20 AS D20));

 

아래는 T1 테이블을 조회한 결과다.

SELECT * FROM T1 ORDER BY JOB;

 

아래는 UNPIVOT 절을 사용한 쿼리다.

D10_SAL, D20_SAL 열이 행으로 전환된다.

 

SELECT JOB, DEPTNO, SAL

FROM T1

UNPIVOT (SAL FOR DEPTNO IN (D10_SAL, D20_SAL))

ORDER BY 1, 2;

 

IN 절에 별칭을 지정하면 FOR 절에 지정한 열의 값을 변경할 수 있다.

아래 쿼리는 10, 20으로 값을 변경했다.

 

SELECT JOB, DEPTNO, SAL

FROM T1

UNPIVOT (SAL FOR DEPTNO IN (D10_SAL AS 10, D20_SAL AS 20))

ORDER BY 1, 2;

 

아래와 같이 INCLUDE NULLS 키워드를 기술하면 UNPIOT된 열의 값이 널인 행도 결과에 포함된다.

 

SELECT JOB, DEPTNO, SAL

FROM T1

UNPIVOT INCLUDE NULLS (SAL FOR DEPTNO IN (D10_SAL AS 10, D20_SAL AS 20))

ORDER BY 1, 2;

 

UNPIVOT 절에 다수의 열을 지정할 수 있다.

아래 쿼리는 SAL 열과 CNT 열을 UNPIOT 한다.

 

SELECT *

FROM T1

UNPIVOT ((SAL, CNT)

FOR DEPTNO IN ((D10_SAL, D10_CNT) AS 10, (D20_SAL, D20_CNT) AS 20))

ORDER BY 1, 2;

 

FOR 절에도 다수의 열을 지정할 수 있다.

IN 절도 별칭을 함께 지정할 수 있다.

 

SELECT *

FROM T1

UNPIVOT ((SAL, CNT)

FOR (DEPTNO, DNAME) IN ((D10_SAL, D10_CNT) AS (10, 'ACCOUNTING')

, (D20_SAL, D20_CNT) AS (20, 'RESEARCH')))

ORDER BY 1, 2;

 

아래와 같이 PIVOT 절과 UNPIVOT 절을 함께 사용할 수도 있다.

PIVOT 절의 결과가 UNPIVOT 절에 인라인 뷰로 공급되는 방식이다.

INCLUDE NULLS 키워드로 파티션 아우터 조인의 동작을 모방할 수 있다.

 

SELECT *

FROM (SELECT JOB, DEPTNO, SAL, COMM FROM EMP)

PIVOT (SUM (SAL) AS SAL, SUM (COMM) AS COMM

FOR DEPTNO IN (10 AS D10, 20 AS D20, 30 AS D30))

UNPIVOT INCLUDE NULLS

((SAL, COMM) FOR DEPTNO IN ((D10_SAL, D10_COMM) AS 10

, (D20_SAL, D20_COMM) AS 20

, (D30_SAL, D30_COMM) AS 30))

ORDER BY 1, 2;

 

-- 예제를 위해 아래와 같이 테이블을 생성

DROP TABLE T1 PURGE;

 

CREATE TABLE T1 AS

SELECT TO_CHAR (ADD_MONTHS (DATE '2050-01-01', ROWNUM - 1), 'YYYYMM') AS YM

, MOD (ROWNUM, 12) AS V1, MODE (ROWNUM, 12) + 1 AS V2

FROM XMLTABLE ('1 TO 24');

 

-- 아래는 T1 테이블을 조회한 결과다.

SELECT * FROM T1;

 

아래 쿼리는 V1, V2 열을 UNPIVOT 한 후 월별로 V1 열을 PIVOT 한다.

 

SELECT *

FROM (SELECT SUBSTR (YM, 1, 4) AS YY, SUBSTR (YM, 5, 2) AS MM, V1, V2 FROM T1)

UNPIVOT (V1 FOR TP IN (V1, V2))

PIVOT (MAX (VL) AS VL

FOR MM IN ('01' AS M01, '02' AS M02, '03' AS M03

, '04' AS M04, '05' AS M05, '06' AS M06))

ORDER BY 1, 2;

 

[기존 방식]

11.1 이전 버전에서는 카티션 곱을 사용하여 UNPIVOT을 수행할 수 있다.

UNPIVOT할 열의 개수만큼 행을 복제하고 DECODE 함수로 UNPIVOT할 열을 선택하는 방식이다.

 

SELECT A.JOB

, DECODE (B.LV, 1, 10, 2, 20) AS DEPTNO

, DECODE (B.LV, 1, A.D10_SAL, 2, A.D20_SAL) AS SAL

, DECODE (B.LV, 1, A.D10_CNT, 2, A.D20_CNT) AS CNT

FROM T1 A

, (SELECT LEVEL AS LV FROM DUAL CONNECT BY LEVEL <= 2) B

ORDER BY 1, 2;

 

아래 쿼리에서 강조된 부분이 DECODE 함수로 선택한 값이다.

SELECT A.JOB, B.LV, A.D10_SAL, A.D20_SAL, A.D10_CNT, A.D20_CNT

FROM T1 A

, (SELECT LEVEL AS LV FROM DUAL CONNECT BY LEVEL <= 2) B

ORDER BY A.JOB, B.LV;

 

불친절한 SQL 프로그래밍저자정희락출판디비안(DBian)발매2018.09.10.

 

 

[순번 생성]

계층 쿼리를 사용하여 순번을 가진 테이블을 생성할 수 있다.

행 복제 시 해당 기법을 활용할 수 있다.

아래 쿼리는 100까지의 순번을 반환한다.

START WITH 절을 생략했기 때문에 DUAL 테이블의 전체 행이 루트 노드로 생성된다.

DUAL 테이블이 1행이므로 루트 노드가 1행으로 생성되고,

1행과 1행을 카티션 곱합 결과는 1행이므로 LEVEL <= 100 조건을 만족할 때까지 1행씩 레벨이 증가하는 것이다.

SELECT LEVEL AS LV

FROM DUAL

CONNECT BY LEVEL <= 100;

아래와 같이 XMLTABLE 함수를 사용해도 동일한 결과를 얻을 수 있다.

SELECT ROWNUM AS RN

FROM XMLTABLE ('1 TO 100');

[변경이력]

계층 쿼리를 응용하면 값의 변경 이력을 확인할 수 있따.

예제를 위해 아래와 같이 테이블을 생성하자

DROP TABLE T1 PURGE;

CREATE TABLE T1 (

YM VARCHAR2(6) -- 연월

, BF VARCHAT2(4) -- 변경 전 코드

, AF VARCHAR2(4) -- 변경 후 코드

);

INSERT INTO T1 VALUES ('205001', 'A', 'B');

INSERT INTO T1 VALUES ('205001', 'I', 'J');

INSERT INTO T1 VALUES ('205001', 'X', 'Y');

INSERT INTO T1 VALUES ('205004', 'B', 'C');

INSERT INTO T1 VALUES ('205004', 'J', 'K');

INSERT INTO T1 VALUES ('205007', 'C', 'D');

COMMIT;

아래 표는 코드 변경 이력을 표현하고 있다

A 코드는 2050년 1월에 B 코드로, B 코드는 2050년 4월에 C 코드로, C 코드는 2050년 7월에 D 코드로 변경되었다.

최초 - 205001 - 205004 - 205007

A - B - C - D

I - J - K

X - Y

아래 쿼리는 코드의 최종 변경 코드를 조회한다.

START WITH 절이 생략되어 전체 행이 루트 노드로 생성된다.

CONNECT_BY_ROOT 연산자로 루트 노드를 조회하고, CONNECT_BY_ISLEAF 슈도 칼럼으로 리프 노드만 조회했다.

SELECT BF, AF, YM

FROM (SELECT YM, CONNECT_BY_ROOT BF AS BF, AF, CONNECT_BY_ISLEAF AS LF

FROM T1

CONNECT BY BF = PRIOR AF)

WHERE LF = 1

ORDER BY 1;

아래 쿼리는 최초 코드의 변경 정보를 조회한다.

최초 코드로 루트 노드를 생성하기 위해

START WITH 절에서 변경 전 코드가 존재하지 않는 행을 조회한 후 순방향으로 계층을 전개했다.

SELECT BF, CD, YM, CD

FROM (SELECT CONNECT_BY_ROOT BF AS BF

, SUBSTR (SYS_CONNECT_BY_PATH (AF, ','), 2) AS CD

, SUBSTR (SYS_CONNECT_BY_PATH (YM, ','), 2) AS YM

, LEVEL AS CN

, CONNECT_BY_ISLEAF AS LF

FROM T1 A

START WITH NOT EXISTS (SELECT 1 FROM T1 X WHERE X.AF = A.BF)

CONNECT BY BF = PRIOR AF)

WHERE LF = 1

ORDER BY 1;

아래 쿼리는 최종 코드의 변경 정보를 조회한다.

최종 코드로 루트 노드를 생성하기 위해 START WITH 절에서 변경 후 코드가 존재하지 않는 행을 조회한 후 역방향으로 계층을 전개했다.

SELECT AF, CD, YM, CN

FROM (SELECT CONNECT_BY_ROOT AF AS AF

, SUBSTR (SYS_CONNECT_BY_PATH (BF, ','), 2) AS CD

, SUBSTR (SYS_CONNECT_BY_PATH (YM, ','), 2) AS YM

, LEVEL AS CN

, CONNECT_BY_ISLEAF AS LF

FROM T1 A

START WITH NOT EXISTS (SELECT 1 FROM T1 X WHERE X.BF = A.AF)

CONNECT BY AF = PRIOR BF)

WHERE LF = 1

ORDER BY 1;

[생성순서]

계층 쿼리를 사용하면 순차적으로 계산되는 계정의 생성 순서를 결정할 수 있다.

예제를 위해 아래와 같이 테이블을 생성

DROP TABLE T1 PURGE;

CREATE TABLE T1 (

CD VARCHAR2(1) -- 계정

, C1 VARCHAR2(1) -- 계산계정1

, C2 VARCHAR2(1) -- 계산계정2

, C3 VARCHAR2(1) -- 계산계정3

, C4 VARCHAR2(1) -- 계산계정4

);

INSERT INTO T1 VALUES ('A', 'B', 'C', 'D', 'E');

INSERT INTO T1 VALUES ('B', 'F', 'G', 'H', NULL);

INSERT INTO T1 VALUES ('C', 'I', 'J', NULL, NULL);

INSERT INTO T1 VALUES ('D', 'K', NULL, NULL, NULL);

INSERT INTO T1 VALUES ('E', 'B', 'C', NULL, NULL);

INSERT INTO T1 VALUES ('F', 'C', 'D', NULL, NULL);

COMMIT;

T1 테이블의 데이터를 아래와 같이 표현할 수 있다.

계정(CD)은 계산계정(C1, C2, C3, C4)으로 계산된다.

계정 A는 계정 B, C, D, E로 계산되고, 계정 B는 계정 F, G, H로 계산된다.

계정 A를 계산하기 위해서는 계정 B를 먼저 생성해야 한다.

CD - C1 - C2 - C3 - C4

A - B - C - D - E

B - F - G - H

C - I - J

D - K

E - B - C

F - C - D

아래 쿼리로 계정의 생성 순서를 결정할 수 있다.

먼저 생성할 필요가 없는 계산계정으로 생성되는 계정을 루트 노드로 생성한 후 역방향으로 계층을 전개했다.

계정 A는 다섯 번째로 생성해야 한다.

SELECT CD, MAX (LEVEL) AS LV

FROM T1 A

START WITH NOT EXISTS (SELECT 1 FROM T1 X WHERE X.CD IN (A.C1, A.C2, A.C3, A.C4))

CONNECT BY PRIOR CD IN (C1, C2, C3, C4)

GROUP BY CD

ORDER BY 2, 1;

위 쿼리의 CONNECT BY 절은 아래와 같이 해석된다.

CONNECT BY (C1 = PRIOR CD OR C2 = PRIOR CD OR C3 = PRIOR CD OR C4 = PRIRO CD)

[누적 연산]

재귀 서브 쿼리 팩토링을 사용하면 상위 노드의 값을 누적 연산할 수 있다.

아래 쿼리는 상위 노드의 SAL을 하위 노드로 누적 집계한다.

WITH W1 (EMPNO, ENAME, MGR, SAL, LV, C1) AS (

SELECT EMPNO, ENAME, MGR, SAL, 1 AS LV, SAL AS C1

FROM EMP

WHERE MGR IS NULL

UNION ALL

SELECT C.EMPNO, C.ENAME, C.MGR, C.SAL, P.LV + 1 AS LV, P.C1 + C.SAL AS C1

FROM W1 P, EMP C

WHERE C.MGR = P.EMPNO)

SEARCH DEPTH FIRST BY EMPNO SET SO

SELECT LV, EMPNO, LPAD (' ', LV - 1, ' ') || ENAME AS ENAME, MGR, SAL, C1

FROM W1

ORDER BY SO;

계층 쿼리 절은 부모 노드의 값만 참조할 수 있기 때문에 누적 연산이 불가능하다.

SELECT LEVEL AS LV, EMPNO, LPAD (' ', LEVEL - 1, ' ') || ENAME AS ENAME, MGR

, SAL, SAL + PRIOR SAL AS C1

FROM EMP

START WITH MGR IS NULL

CONNECT BY MGR = PRIOR EMPNO;

 

 

 

[노드 제거]

CONNECT BY 절이나 WHERE 절에 조건을 기술하면 조건을 만족하지 않는 노드를 제거할 수 있다.

 

아래 쿼리는 CONNECT BY 절에 EMPNO <> 7698 조건을 기술했다.

CONNECT BY 절에 조건을 기술하면 조건을 만족하지 않는 노드와 해당 노드의 모든 하위 노드가 제거된다.

계층 전개 시점에 노드가 제거되기 때문에 하위 노드까지 제거되는 것이다.

 

SELECT LEVEL AS LV, EMPNO, LPAD (' ', LEVEL - 1, ' ') || ENAME AS ENAME, MGR

FROM EMP

START WITH MGR IS NULL

CONNECT BY MGR = PRIOR EMPNO

AND EMPNO <> 7698;

 

아래 쿼리는 WHERE 절에 EMPNO <> 7698 조건을 기술했다.

WHERE 절에 조건을 기술하면 조건을 만족하지 않는 노드만 제거된다.

CONNECT BY 절이 수행된 후 WHERE 절이 수행되기 때문이다.

 

SELECT LEVE AS LV, EMPNO, LPAD (' ', LEVEL - 1, ' ') || ENAME AS ENAME, MGR

FROM EMP

WHERE EMPNO <> 7698

START WITH MGR IS NULL

CONNECT BY MGR = PRIOR EMPNO;

 

[다중 루트 노드]

계층 쿼리 절은 1개 이상의 루트 노드를 가질 수 있다.

 

아래 쿼리는 JOB이 MANAGER인 행으로 루트 노드를 생성하고, 순방향으로 계층을 전개한다.

JONES, BLAKE, CLARK이 루트 노드로 생성된다.

 

SELECT LEVEL AS LV, EMPNO, LPAD (' ', LEVEL - 1, ' ') || ENAME AS ENAME, MGR

FROM EMP

START WITH JOB = 'MANAGER'

CONNECT BY MGR = PRIOR EMPNO;

 

아래 쿼리는 20번 부서에서 부하가 없는 사원으로 루트 노드를 생성하고, 역방향으로 계층을 전개한다.

SMITH와 ADAMS가 루트 노드로 생성된다.

 

SELECT LEVEL AS LV, EMPNO, LPAD (' ', LEVEL - 1, ' ') || ENAME AS ENAME, MGR

FROM EMP A

START WITH DEPTNO = 20

AND NOT EXISTS (SELECT 1 FROM EMP X WHERE X.MGR = A.EMPNO)

CONNECT BY EMPNO = PRIOR MGR;

 

[다중 속성 순환 관계]

순환 관계는 1개 이상의 속성을 관계 속성으로 가질 수 있다.

 

예제를 위해 아래와 같이 테이블을 생성

DROP TABLE EMP_C PURGE;

 

CREATE TABLE EMP_C AS

SELECT 1 AS COMPNO, A.*, 1 AS PCOMPNO FROM EMP A UNION ALL

SELECT 2 AS COMPNO, A.*, 2 AS PCOMPNO FROM EMP A;

 

아래는 EMP_C 테이블을 조회한 결과다. EMP_C 테이블은 여러 회사의 사원 정보를 통합 관리하는 테이블이다.

고유 식별자가 COMPNO(회사번호) + EMPNO이므로 순환 관계를 상속받는 외래 식별자는 PCOMPNO (부모회사번호) + MGR다.

 

SELECT COMPNO, EMPNO, ENAME, MGR, PCOMPNO FROM EMP_C;

 

아래 쿼리는 COMPNO가 1인 회사의 사원 정보를 조회한다.

CONNECT Y 절에 순환 관계 속성을 모두 기술해야 의도한 결과를 얻을 수 있다.

 

SELECT LEVEL AS LV, COMPNO, EMPNO, LPAD (' ', LEVEL - 1, ' ') || ENAME AS ENAME

, MGR, PCOMPNO

FROM EMP_C

START WITH COMPNO = 1

AND MGR IS NULL

CONNECT BY PCOMPNO = PRIOR COMPNO

AND MGR = PRIOR EMPNO;

 

아래 쿼리는 의도하지 않은 결과가 반환된다.

CONNECT BY 절에 PCOMPNO = PRIOR COMPNO 조건을 기술하지 않으면 COMPNO가 1이 아닌 회사의 사원으로 계층이 전개된다.

 

SELECT LEVEL AS LV, COMPNO, EMPNO, LPAD (' ', LEVEL - 1, ' ') || ENAME AS ENAME

, MGR, PCOMPNO

FROM EMP_C

START WITH COMPNO = 1

AND MGR IS NULL

CONNECT BY MGR = PRIOR EMPNO;

 

예제를 위해 아래와 같이 테이블을 생성하자.

EMP_H 테이블은 순환 관계를 가지는 EMP 테이블의 월별 이력 테이블이다.

 

DROP TABLE EMP_H PURGE;

 

CRAETE TABLE EMP_H AS

SELECT '205001' AS YM, A.* FROM EMP A UNION ALL

SELECT '205002' AS YM, A.* FROM EMP A;

 

아래는 EMP_H 테이블을 조회한 결과다.

EMP_H 테이블은 다중 속성 순환 관계를 가지지는 않는다.

(순환 관계를 가지는 테이블의 월별 이력 테이블에 대한 관계는 모호한 면이 있다. 굳이 따지면 식별자를 부분 상속했다고 볼 수 있다. EMP_H 엔티티의 경우 EMPNO 속성만 MGR 속성으로 상속된 것이다.)

 

SELECT YM, EMPNO, ENAME, MGR FROM EMP_H;

 

아래 쿼리는 2050년 1월의 사원 이력을 조회한다.

동일한 이력 시점의 계층을 전개하기 위해서는 CONNECT BY 절에 YM = '205001' 조건을 기술해야 한다.

 

SELECT LEVEL AS LV, YM, EMPNO, LPAD (' ', LEVEL - 1, ' ') || ENAME AS ENAME, MGR

FROM EMP_H

START WITH YM = '205001'

AND MGR IS NULL

CONNECT BY YM = '205001'

AND MGR = PRIOR EMPNO;

 

월별 이력에 대한 계층 쿼리는 아래와 같이 인라인 뷰를 사용하는 편이 명시적이다.

 

SELECT LEVEL AS LV, YM, EMPNO, LPAD (' ', LEVEL - 1, ' ') || ENAME AS ENAME, MGR

FROM (SELECT * FROM EMP_H WHERE YM = '205001')

START WITH MGR IS NULL

CONNECT BY MGR = PRIOR EMPNO;

 

[계층 쿼리와 조인]

계층 쿼리는 세 가지 방식으로 조인을 수행할 수 있다.

 

아래 쿼리는 계층을 전개한 후 조인을 수행한다. 인라인 뷰를 사용한 조인과 동일하다.

SELECT A.LV, A.EMPNO, A.ENAME, B.DNAME

FROM (SELECT LEVEL AS LV, EMPNO, LPAD (' ', LEVEL - 1, ' ') || ENAME AS ENAME

, DEPTNO, ROWNUM AS RN

FROM EMP

START WITH MGR IS NULL

CONNECT BY MGR = PRIOR EMPNO) A

, DEPT B

WHERE B.DEPTNO = A.DEPTNO

ORDER BY A.RN;

 

아래 쿼리는 조인을 수행한 후 계층을 전개한다.

B.LOC = 'NEW YORK' 조건처럼 계층을 전개할 대상을 먼저 정의해야 할 때 사용할 수 있다.

 

SELECT LEVEL AS LV, EMPNO, LPAD (' ', LEVEL - 1, ' ') || ENAME AS ENAME

, DEPTNO, DNAME

FROM (SELECT A.*, B.DNAME

FROM EMP A, DEPT B

WHERE B.DEPTNO = A.DEPTNO

AND B.LOC = 'NEW YORK')

START WITH MGR IS NULL

CONNECT BY MGR = PRIOR EMPNO;

 

WHERE 절에 조인 조건을 기술하면 조인을 수행한 후 계층을 전개한다.

위 쿼리처럼 인라인 뷰를 사용하는 편이 명시적이다.

 

SELECT LEVEL AS LV, A.EMPNO, LPAD (' ', LEVEL - 1, ' ') || A.ENAME AS ENAME

, A.DEPTNO, B.DNAME

FROM EMP A, DEPT B

WHERE B.DEPTNO = A.DEPTNO

START WITH A.MGR IS NULL

CONNECT BY A.MGR = PRIOR A.DEPTNO;

 

ANSI 조인 문법도 조인을 수행한 후 계층을 전개한다.

 

SELECT LEVEL AS LV, A.EMPNO, LPAD (' ', LEVEL - 1, ' ') || A.ENAME AS ENAME

, A.DEPTNO, B.DNAME

FROM EMP A

JOIN DEPT B

ON B.DEPTNO = A.DEPTNO

START WITH A.MGR IS NULL

CONNECT BY A.MGR = PRIOR A.EMPNO;

 

아래 쿼리는 계층 전개 시점에 조인을 수행한다.

START WITH 절과 CONNECT BY 절에 조인 조건을 기술해야 한다.

B.LOC = 'DALLAS' 조건처럼 계층 전개 중 노드를 제한해야 할 때 사용할 수 있다.

 

SELECT A.*, B.DNAME

FROM EMP A, DEPT B

START WITH A.MGR IS NULL

AND B.DEPTNO = A.DEPTNO

CONNECT BY A.MGR = PRIOR A.EMPNO

AND B.DEPTNO = A.DEPTNO

AND B.LOC = 'DALLAS';

 

불친절한 SQL 프로그래밍저자정희락출판디비안(DBian)발매2018.09.10.

 

 

+ Recent posts