ON COMMIT DELETE ROWS
: COMMIT 시에 row를 삭제하라는 의미CREATE GLOBAL TEMPORARY TABLE 테이블명 (
컬럼1 데이터타입, ...
)
[ON COMMIT DELETE ROWS];
CREATE GLOBAL TEMPORARY TABLE TX_GTT (
NO NUMBER,
NAME VARCHAR(20)
)
ON COMMIT DELETE ROWS;
DECLARE
txGttCnt NUMBER;
BEGIN
INSERT INTO TX_GTT (NO, NAME)
SELECT 1, 'Kate' FROM DUAL
UNION ALL
SELECT 2, 'Bob' FROM DUAL
UNION ALL
SELECT 3, 'Alex' FROM DUAL
;
SELECT COUNT(1) INTO txGttCnt FROM TX_GTT;
dbms_output.put_line('txGttCnt : ' || txGttCnt);
COMMIT;
SELECT COUNT(1) INTO txGttCnt FROM TX_GTT;
dbms_output.put_line('txGttCnt : ' || txGttCnt);
END
;
/* 결과
txGttCnt : 6
txGttCnt : 0
*/
ON COMMIT PRESERVE ROWS
는 COMMIT을 실행한 후에도 데이터(로우)를 보존하라는 뜻CREATE GLOBAL TEMPORARY TABLE 테이블명 (
컬럼1 데이터타입, ...
)
ON COMMIT PRESERVE ROWS;
CREATE GLOBAL TEMPORARY TABLE SS_GTT (
NO NUMBER,
NAME VARCHAR(20)
)
ON COMMIT PRESERVE ROWS;
DECLARE
ssGttCnt NUMBER;
BEGIN
INSERT INTO SS_GTT
SELECT 1, 'Alex' FROM DUAL
UNION ALL
SELECT 2, 'Rose' FROM DUAL
UNION ALL
SELECT 3, 'Peter' FROM DUAL;
SELECT COUNT(1) INTO ssGttCnt FROM SS_GTT;
dbms_output.put_line('ssGttCnt : ' || ssGttCnt);
COMMIT;
SELECT COUNT(1) INTO ssGttCnt FROM SS_GTT;
dbms_output.put_line('ssGttCnt : ' || ssGttCnt);
END;
/* 결과
ssGttCnt : 6
ssGttCnt : 6
*/
여러 개의 테이블을 조인해서 복잡한 연산을 수행한 결과를 보여주는 리포트를 만들어야 하는데 해당 결과를 산출하기에는 단일 SELECT문으로 구현하기가 어렵다고 하자. 이럴 때, 과거에는 최종 리포트 구조에 맞게 테이블을 만들고 프로시저 안에서 여러 단계에 걸쳐 데이터를 입력하고 조작해 원하는 결과를 만들었다. 이렇게 하면 리포트 화면에서는 WHERE 조건도 필요 없는 단순 SELECT문만 실행하면 됐다.
하지만 이 방법은, 다른 세션의 사용자가 거의 동시에 같은 프로시저를 실행하고 결과를 조회하면 데이터가 중복되거나 누락되는 등의 문제가 발생할 소지가 있다. 하지만 GTT를 사용하면 데이터가 세션별로 관리되므로 데이터가 꼬이는 현상이 발생할 가능성은 거의 없을 뿐만 아니라 원하던 기능도 구현할 수 있다.
WITH [별명1] [(컬럼명1 [,컬럼명2])] AS (
SUB QUERY
) [,별명2 AS ...]
MAIN QUERY
WITH TEMP_USER_INFO_SCORE AS
(
SELECT I.USER_NUM, I.USER_NM, I.USER_BIRTH, S.USER_GRADE
FROM USER_INFO I
LEFT JOIN USER_SCORE S
ON I.USER_NUM = S.USER_NUM
)
SELECT
TEMP.USER_NUM
, TEMP.USER_NM
, TEMP.USER_BIRTH
, TEMP.USER_GRADE
FROM TEMP_USER_INFO_SCORE TEMP
WHERE USER_GRADE <> 'A'
;