"오라클 시퀀스 관리"의 두 판 사이의 차이
DB CAFE
(→사용법) |
|||
133번째 줄: | 133번째 줄: | ||
SELECT * FROM V_SEQ_MAKE_QRY; | SELECT * FROM V_SEQ_MAKE_QRY; | ||
</source> | </source> | ||
− | # 시퀀스 변경 프로시져 실행 예시 | + | ## 시퀀스 변경 프로시져 실행 예시 |
<source lang=sql> | <source lang=sql> | ||
EXEC SP_DBA_RESET_SEQ(P_OWNER => '오너',P_SEQ_NAME => '시퀀스명',P_NEW_NUM =>최대값 ,P_EXEC => 1); | EXEC SP_DBA_RESET_SEQ(P_OWNER => '오너',P_SEQ_NAME => '시퀀스명',P_NEW_NUM =>최대값 ,P_EXEC => 1); | ||
</source> | </source> |
2019년 10월 16일 (수) 11:43 판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1 오라클 시퀀스 변경 관리[편집]
1.1 설치[편집]
1.1.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.1.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),
SEQ_MAX_NUM VARCHAR2(30 BYTE),
CREATED DATE DEFAULT SYSDATE NOT NULL,
CACHE VARCHAR2(10 BYTE),
COL_TYPE VARCHAR2(30 BYTE),
SEQ_COMMENT VARCHAR2(200 BYTE)
)
;
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.1.3 프로시져 자동 수행 뷰[편집]
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));
1.2 사용법[편집]
- 시퀀스생성 목록 조회
SELECT * FROM V_SEQ_MAKE_QRY;
- 시퀀스 변경 프로시져 실행 예시
EXEC SP_DBA_RESET_SEQ(P_OWNER => '오너',P_SEQ_NAME => '시퀀스명',P_NEW_NUM =>최대값 ,P_EXEC => 1);