[ORACLE] PL/SQL Procedure 또는 DECLARE에서 DDL 사용하기.

1. 사용 배경

 1) 외주 작업 중 데이터 테이블을 백업하고 갱신해야할일이 생겼다.

 2) 오라클을 사용하다보니 이전 시퀀스가 문제가 발생하여, 일일히 시퀀스 현재 값을 유지하면서 옮겨야한다.

 

2. 작업 

 1) 오라클 응용 쿼리

-- create immediate 문
begin
  execute immediate 'create table xx(x date)';
end;

-- PL/SQL procedure successfully completed.
 
-- 테이블 100개 생성
  begin
        for i in 1..100 loop
            execute immediate 'create table tx'||i||'(id number(10),data varchar2(20))';
       end loop;
  end;

   ※ 출처 : http://drakneen.blogspot.com/2009/08/ddl%EC%9D%B4-%EB%AC%B8%EC%9D%84-plsql%EC%97%90%EC%84%9C-%EC%82%AC%EC%9A%A9%ED%95%98%EB%8A%94-%EB%B2%95.html

DDL이 문을 PL/SQL에서 사용하는 법

 2) 업무 적용

CREATE OR REPLACE PROCEDURE SEQ_COPY(
    SEQ_NAME IN VARCHAR2,
    CHANGE_NAME_SEQ_NAME IN VARCHAR2
)
    IS
    SEQ_NUM NUMBER;
BEGIN
    -- 현재 SEQUECE를 가져온다.
    execute immediate 'SELECT ' || SEQ_NAME || '.NEXTVAL -1 FROM DUAL' INTO SEQ_NUM;

    -- NEXT VAL 했으니 이전으로 되돌린다.
    execute immediate 'ALTER SEQUENCE ' || SEQ_NAME || ' MINVALUE ' || SEQ_NUM;
	-- 현재 밸류 재 조회
    execute immediate 'SELECT ' || SEQ_NAME || '.CURRVAL FROM DUAL' INTO SEQ_NUM;

    -- 기존 시퀀스 제거.
    execute immediate 'RENAME ' || SEQ_NAME || ' to ' || CHANGE_NAME_SEQ_NAME;

    execute immediate 'CREATE SEQUENCE ' || SEQ_NAME || ' MINVALUE ' || SEQ_NUM;

    -- SEQUECE 생성 이후 SEQ 번호를 NEXTVAL 필수
    execute immediate 'SELECT ' || SEQ_NAME || '.NEXTVAL FROM DUAL' INTO SEQ_NUM;

    execute immediate 'ALTER SEQUENCE ' || SEQ_NAME || ' MINVALUE ' || (SEQ_NUM - 1);

    DBMS_OUTPUT.PUT_LINE('현재 시퀀스 ' || SEQ_NUM);
EXCEPTION
    WHEN NO_DATA_FOUND THEN SEQ_NUM := NULL;
END;