안녕하세요.
임시저장 테이블 관련해서 문의 드립니다.
A에 있는 테이블을 가져와서 임시테이블에 저장을 하고 특정데이터 업데이트 후 B 테이블에 저장을 하려고합니다.
CREATE OR REPLACE PROCEDURE TEST
... 생략
AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
SELECT COUNT(TABLE_NAME)
INTO V_COUNT
FROM USER_TABLES
WHERE TABLE_NAME = 'TBL_ECRP_TMEP_TABLE';
IF V_COUNT = 1 THEN
V_D_TMP := 'DROP TABLE TBL_ECRP_TMEP_TABLE';
EXECUTE IMMEDIATE V_D_TMP;
COMMIT;
END IF;
V_C_TMP := 'CREATE GLOBAL TEMPORARY TABLE TBL_ECRP_TMEP_TABLE ON COMMIT DELETE ROWS AS
SELECT * FROM TBL_ECRP_' || V_P_PROCESS_CODE_ECRP || '_DETAIL WHERE FACTORY_CODE_ECRP = ''' || V_P_FACTORY_CODE_ECRP || '''';
EXECUTE IMMEDIATE V_C_TMP;
COMMIT;
V_U_TMP := 'UPDATE TBL_ECRP_TMEP_TABLE SET FACTORY_CODE_ECRP = ''' || V_P_FACTORY_CODE_CHECK_ECRP || '''';
EXECUTE IMMEDIATE V_U_TMP;
COMMIT;
V_I_TAB := 'INSERT INTO TBL_ECRP_CMP_DETAIL_TEST SELECT * FROM TBL_ECRP_TMEP_TABLE';
EXECUTE IMMEDIATE V_I_TAB;
COMMIT;
END;
아래 프로시저를 이용하여
저장을 했을시에
TBL_ECRP_TMEP_TABLE
테이블에 저장이 되지않습니다. (아마도 COMMIT 할때 데이터가 삭제가 되는거 같습니다.)
그래서
임시테이블 옵션을 ON COMMIT PRESERVE ROWS 줘서 했을 시에
저장이 가능했으나 다시 실행시 현재 세션을 물고 있어서 DROP 이 되지 않습니다.
ON COMMIT PRESERVE ROWS 사용시
처음에 임시테이블을 삭제하고 사용하고 싶은데 세션을 죽이는 방법 말고
다른방법이 있는지 궁금합니다.
동적으로 생성되어서 EXECUTE IMMEDIATE 을 사용하여 ON COMMIT DELETE ROWS 을 사용하는 방법이 없는지 궁금합니다.
선배님들의 조언 부탁드립니다.
감사합니다.
임시 테이블을 잘못 사용하고 계신 듯 합니다.
임시 테이블을 매번 드롭하고 다시 생성하는 것이 아닙니다.
임시 테이블을 미리 만들어 두고 지속적으로 재사용 하는 것입니다.
1. DROP 은 필요 없고
2. Create 문은 Insert 문으로 대체하고
3. Insert, Update 문은 Execute Immediate 없이 바로 실행하고
4. Commit 은 마지막에 한번만.
이런 방식으로 작성하면 될 듯 하긴 한데.
이런 방식 자체도 비효율적으로 보입니다.
굳이 임시 테이블이 필요 없어 보이는 간단한 로직이네요.
대상 테이블에 직접 작업해도 될 듯.
안녕하세요~ 답변 감사드립니다.
임시 테이블을 매번 드랍하는 이유는..
TBL_ECRP_' || V_P_PROCESS_CODE_ECRP || '_DETAIL 에서 테이블이름을 변수를 받아서
그 테이블에 맞는 컬럼 구조로 생성을 해주기 위합니다.. ㅠㅠ 각 테이블 마다 컬럼 내용이 달라서
하나를 생성해주면 다른 테이블 저장시 구조가 달라 사용을 할 수 없어서
동적으로 테이블명을 받아 그 구조에 맞게 생성을 해서 쓰고 드랍을 하려는 구조입니다.
만약 다른 여러 테이블도 임시테이블 구조가 같다면 당연히 DROP을 하지 않고 INSERT만 가능하지만
INSERT INTO TBL_ECRP_CMP_DETAIL_TEST 구문도
INSERT INTO TBL_ECRP_' || V_P_PROCESS_CODE_ECRP || '_DETAIL 동적으로 저장 하기 위해서
EXECUTE IMMEDIATE을 사용하였습니다.
마농님께서 알려주신대로 INSERT를 추가하니 잘되었습니다.
감사합니다.
아 공정별 테이블이 30개정도 됩니다.
맨끝에 _TEST 에 저장한 이유는 한공정을 상대로 데이터가 정확하게
들어 오는지 확인을 하기 위해 임시로 저장하려고 만든 테이블입니다.
(V_I_TAB := 'INSERT INTO TBL_ECRP_' || V_P_PROCESS_CODE_ECRP || '_DETAIL SELECT * FROM TBL_ECRP_TMEP_TABLE';) << 이 로직 사용전에 테스트 용도로 테이블을 지정했습니다.
CREATE GLOBAL TEMPORARY TABLE TBL_ECRP_TMEP_TABLE ON COMMIT PRESERVE ROWS AS
SELECT * FROM TBL_ECRP_' || V_P_PROCESS_CODE_ECRP || '_DETAIL WHERE FACTORY_CODE_ECRP = ''' || V_P_FACTORY_CODE_ECRP || '''';
시에는 임시 테이블 만들고 바로 저장이 되어서 INSERT를 안태워도 되는줄 알았습니다 ㅠㅠ
ON COMMIT DELETE ROWS 를 사용하고 CREATE 이후 INSERT 해주니 정상적으로 저장이 가능했습니다.
다시한번 감사드립니다 마농님 ㅠㅠ
1. DDL 은 자동 커밋 되기 때문에 임시 테이블에 자료가 남지 않는 것입니다.
- Create 할 때 어차피 데이터가 남지 않으므로
- Create 문을 데이터 없이 구조만 생성하도록 간결하게 바꿀 필요가 있겠네요.
- 'Create ... AS Select * FROM ... WHERE 1=2'
- Create 후에 데이터는 Insert 문으로 추가.
2. 테이블이 30개로 고정되어 있다면?
- 임시테이블을 프로그램 내에서 삭제 및 생성 하는 것 보다는
- 30개를 미리 만들어 두고 사용하는게 나을 듯 하네요.
3. 30개 임시 테이블 만들어 두는게 부담된다면?
- user_tab_columns 를 이용해 컬럼명을 알 수 있으므로
- user_tab_columns 를 이용한 동적 쿼리 가능하리라고 생각됩니다.