CHAPER 10 ORACLE TEMPORARY TABLE

/* CHAPER 10 ORACLE TEMPORARY TABLE */

 

복잡한 식을 간단하게 보기 위해 view와 temporary table을 사용한다.

 

이 둘의 공통점은

 

->복잡한 SQL문을 간단하게 만들어서 조회할 수 있다.

 

 

CREATE OR REPLACE VIEW V_EMP

AS

SELECT EMPNO,ENAME, SAL*12 + NVL(COMM,0)"YEARPAY",

RANK()OVER(ORDER BY SAL*12 + NVL(COMM,0)DESC)"RANK"

FROM SCOTT.EMP;

 

SELECT *

FROM V_EMP;

 

 

SELECT *

FROM V_EMP

WHERE RANK <=5;

 

 

--------------------------------------------------------------------------------

 

CREATE GLOBAL TEMPORARY TABLE TEMPEMP

ON COMMIT PRESERVE ROWS

AS

SELECT EMPNO,ENAME, SAL*12 + NVL(COMM,0)"YEARPAY",

RANK()OVER(ORDER BY SAL*12 + NVL(COMM,0)DESC)"RANK"

FROM SCOTT.EMP;

 

SELECT *

FROM TEMPEMP;

 

SELECT *

FROM TEMPEMP

WHERE rank <=5;

 

 

• view와 temporary table의 차이점은 dml을 통한 데이터 추출시 원본 테이블에 변경유무가 다르다.

 

--------------------------------------------------------------------------------

 

1. temporary table의 종류

1. commit을 하면 temporary table에 존재했던 모든 행들이 자동적으로 delete 되어지는 temporary table

        -> Transaction-Level Temporary

2. commit을 하더라도 temporary table에 존재했던 모든 행들이 그대로 존재하는 temporary table

        -> Session-Level Temporary

      

    

Ex

--TRANSACTION LEVEL TEMPORARY TABLE CREATE GLOBAL TEMPORARY TABLE TEMPEMP1 ON COMMIT DELETE ROWS AS SELECT EMPNO,ENAME, SAL*12 + NVL(COMM,0)"YEARPAY", RANK()OVER(ORDER BY SAL*12 + NVL(COMM,0)DESC)"RANK" FROM SCOTT.EMP;   --SESSION LEVEL TEMPORARY TABLE CREATE GLOBAL TEMPORARY TABLE TEMPEMP1 ON COMMIT PRESERVE ROWS AS SELECT EMPNO,ENAME, SAL*12 + NVL(COMM,0)"YEARPAY", RANK()OVER(ORDER BY SAL*12 + NVL(COMM,0)DESC)"RANK" FROM SCOTT.EMP;

 

 

그리고 위의 1번과 2번 temporary table이 drop 되어지는 경우는

 

drop table temporary 테이블명;

 

을 하던지 해당 세션이 끊어지면 자동적으로 DELETE 되어 진다.

 

--------------------------------------------------------------------------------

 

CREATE GLOBAL TEMPORARY TABLE TBLTEMPMEMBER1

(ID NUMBER

,NAME VARCHAR2(10)

)ON COMMIT DELETE ROWS;

--COMMIT을 하면 자동적으로 DELETE

 

CREATE GLOBAL TEMPORARY TABLE TBLTEMPMEMBER2

(ID NUMBER

,NAME VARCHAR2(10)

)ON COMMIT PRESERVE ROWS;

--COMMIT을 하더라도 계속 보존(PRESERVE)된다.

 

INSERT INTO TBLTEMPMEMBER1

VALUES(1,'일지매');

 

 

INSERT INTO TBLTEMPMEMBER2

VALUES(1,'한석규');

 

 

select *

from TBLTEMPMEMBER1;

 

select *

from TBLTEMPMEMBER2;

 

commit;

 

위와 같이 commit을 한 후, TBLTEMPMEMBER1 과 2를 확인해보면 1은 data가 날라가고, 2는 남아 있음을 확인할 수 있다.

또한 세션을 끊게 되면, TBLTEMPMEMBER2의 행도 제거가 되어짐을 확인할 수 있다.

--------------------------------------------------------------------------------

 

SELECT *

FROM USER_TABLES

WHERE TABLE_NAME IN ('TBLA','TBLTEMPMEMBER1','TBLTEMPMEMBER2');

 

위의 세 테이블을 비교해보면 그림과 같이 속성값에서 일반 테이블과 temporary table의 값에 차이가 나는 것을 확인할 수 있다.

 

DESC TBLTEMPMEMBER1;

 

SELECT *

FROM USER_TABLES

WHERE TEMPORARY ='Y';

 

SELECT TABLE_NAME,DURATION

FROM USER_TABLES

WHERE TEMPORARY ='Y' AND TABLE_NAME IN ('TBLTEMPMEMBER1','TBLTEMPMEMBER2');

$Transaction은 commit이 이루어지면 지속(DURATION)이 종료되는 것이고,

$Session은 Session이 끊어지게 되면 지속(DURATION)이 종료되어지는 것을 의미한다.