1.2.1 시퀀스 변경 프로시져[편집]

CREATE OR REPLACE PROCEDURE SP_DBA_RESET_SEQ(P_OWNER VARCHAR2, P_SEQ_NAME VARCHAR2, P_NEW_NUM NUMBER, P_EXEC NUMBER DEFAULT 0)
IS
    V_NEXTVAL NUMBER;
    V_SQL_RESET     VARCHAR2(4000);
    V_SQL_INIT    VARCHAR2(4000);
    V_SQL_NEXTVAL  VARCHAR2(4000);
BEGIN
    V_SQL_INIT := 'ALTER SEQUENCE '||P_OWNER||'.'||P_SEQ_NAME||' INCREMENT BY 1';
    EXECUTE IMMEDIATE V_SQL_INIT;  -- 증가값 초기화
    
    V_SQL_NEXTVAL := 'SELECT '||P_OWNER||'.'||P_SEQ_NAME||'.NEXTVAL FROM DUAL';
    EXECUTE IMMEDIATE V_SQL_NEXTVAL INTO V_NEXTVAL;
    
    V_SQL_RESET  := 'ALTER SEQUENCE '||P_OWNER||'.'||P_SEQ_NAME||' INCREMENT BY '||TO_CHAR(P_NEW_NUM - V_NEXTVAL);
        DBMS_OUTPUT.ENABLE(1000);
        DBMS_OUTPUT.PUT_LINE('V_NEXTVAL = '||V_NEXTVAL);
    
    IF(P_EXEC = 1) THEN
        EXECUTE IMMEDIATE V_SQL_RESET;  -- GAP만큼 조정
        EXECUTE IMMEDIATE V_SQL_NEXTVAL INTO V_NEXTVAL; -- 채번 1회 실행
        EXECUTE IMMEDIATE V_SQL_INIT;   -- 증가값 초기화
    ELSE 
        DBMS_OUTPUT.ENABLE(1000);
        DBMS_OUTPUT.PUT_LINE(V_SQL_RESET||';');
        DBMS_OUTPUT.PUT_LINE(V_SQL_NEXTVAL||';');
        DBMS_OUTPUT.PUT_LINE(V_SQL_INIT||';');
    END IF;   
END;

1.2.2 시퀀스 관리 테이블[편집]

CREATE TABLE TB_MIG_SEQ_MGR
(
  OWNER           VARCHAR2(30 BYTE),
  TABLE_NAME      VARCHAR2(50 BYTE),
  TABLE_HAN_NAME  VARCHAR2(50 BYTE),
  USE_YN          VARCHAR2(2 BYTE),
  SEQ_NAME        VARCHAR2(30 BYTE),
  SEQ_COL         VARCHAR2(30 BYTE),
  SEQ_RULE        VARCHAR2(50 BYTE),   -- 시퀀스 적용 룰 => MAX(컬럼)+1 형태로 저장 
--   CACHE           VARCHAR2(10 BYTE), 미사용 
  COL_TYPE        VARCHAR2(30 BYTE),
  SEQ_COMMENT     VARCHAR2(200 BYTE),
  CREATED         DATE                          DEFAULT SYSDATE               NOT NULL
)
;

COMMENT ON TABLE TB_MIG_SEQ_MGR IS '시퀀스 관리 테이블';
COMMENT ON COLUMN TB_MIG_SEQ_MGR.OWNER IS 'OWNER';
COMMENT ON COLUMN TB_MIG_SEQ_MGR.TABLE_NAME IS '테이블명';
COMMENT ON COLUMN TB_MIG_SEQ_MGR.TABLE_HAN_NAME IS '테이블한글명';
COMMENT ON COLUMN TB_MIG_SEQ_MGR.USE_YN IS '사용여부';
COMMENT ON COLUMN TB_MIG_SEQ_MGR.SEQ_NAME IS '시퀀스명';
COMMENT ON COLUMN TB_MIG_SEQ_MGR.SEQ_COL IS '시퀀스컬럼';
COMMENT ON COLUMN TB_MIG_SEQ_MGR.SEQ_RULE IS '시퀀스규칙';
COMMENT ON COLUMN TB_MIG_SEQ_MGR.CREATED IS '생성일시';
COMMENT ON COLUMN TB_MIG_SEQ_MGR.SEQ_COMMENT IS '시퀀스_코멘트';


CREATE UNIQUE INDEX PK_TB_MIG_SEQ_MGR ON TB_MIG_SEQ_MGR
(OWNER, TABLE_NAME, SEQ_NAME)
;
ALTER TABLE TB_MIG_SEQ_MGR ADD (
  CONSTRAINT PK_TB_MIG_SEQ_MGR
  PRIMARY KEY
  (OWNER, TABLE_NAME, SEQ_NAME)
  USING INDEX PK_TB_MIG_SEQ_MGR
  ENABLE VALIDATE);

1.2.3 프로시져 자동 수행 뷰[편집]

• 시퀀스 관리테이블(TB_MIG_SEQ_MGR)의 테이블별 시퀀스사용컬럼(SEQ_RULE) 의 MAX(컬럼) 값 조회(XML쿼리 이용)
• (예시) SEQ_RULE : MAX(컬럼)+1 으로 사용하면 입력된 데이터 다음값으로 시퀀스 적용됨.

CREATE OR REPLACE FORCE VIEW V_SEQ_MAKE_QRY
AS
    SELECT OWNER
         , TABLE_NAME
         , SEQ_NAME
         , COL_TYPE
         ,    'EXEC SP_DBA_RESET_SEQ(P_OWNER => '''
           || OWNER
           || ''',P_SEQ_NAME => '''
           || SEQ_NAME
           || ''',P_NEW_NUM =>'
           || C1
           || ',P_EXEC => 1);'    SP
      FROM (SELECT A.OWNER
                 , A.TABLE_NAME
                 , SEQ_NAME
                 , COL_TYPE
                 , REGEXP_REPLACE (
                       EXTRACT (
                           DBMS_XMLGEN.getXmlType (
                                  'SELECT TO_CHAR('
                               || SEQ_RULE
                               || ')||'' ''  C FROM '
                               || A.OWNER
                               || '.'
                               || A.TABLE_NAME)
                         , '/ROWSET/ROW/C').getStringVal ()
                     , '<C>|</C><C>|</C>'
                     , ''                                             -- 태그 제거
                         )    C1
              FROM TB_MIG_SEQ_MGR  A
                   LEFT JOIN DBA_TAB_COLUMNS B
                       ON     A.OWNER = B.OWNER
                          AND A.TABLE_NAME = B.TABLE_NAME
                          AND A.SEQ_COL = B.COLUMN_NAME
             WHERE     A.OWNER = 'SCOOT'
               AND A.COL_TYPE IN ('NUMBER', 'VARCHAR2')
               AND A.USE_YN = 'Y'
            UNION ALL
            -- MAX값이 아닌 초기값으로 지정할 경우 
            SELECT A.OWNER
                 , A.TABLE_NAME
                 , SEQ_NAME
                 , COL_TYPE
                 , A.SEQ_RULE     C1
              FROM TB_MIG_SEQ_MGR A
             WHERE A.OWNER = 'SCOTT' 
               AND (A.COL_TYPE IS NULL));