오라클 시퀀스 ↔ 테이블 시퀀스 맞추기 (동기화하기)

 

간혹 데이터를 이관하다보면,

해당 테이블의 pk 로 사용하는 시퀀스와, 거기에 사용되는 시퀀스의 value가 맞지 않을 때가 있다.

 

나는 무결성 제약위반 오류가 뜨면서 데이터가 수정처리 되지 않았기 때문에, 시퀀스를 맞춰주어야 하는 상황이었다

또 이런 상황이 생길 수도 있으니 간단한 프로시저로 만들어보았다

 

 

< 프로시저 내용 >

create or replace PROCEDURE "SYNC_SEQ" 
(
	P_SQ_NM in VARCHAR2	-- 시퀀스명
	, P_TB_SEQ_NM in VARCHAR2 	-- 테이블 내 시퀀스 컬럼명
	, P_TB_NM in VARCHAR2 	-- 테이블명
)

IS

V_NEXT_VALUE NUMBER;
V_MAX_VALUE NUMBER;
V_CAL_VALUE NUMBER;
T_NUM NUMBER;

-------------------------------------------------------------------

BEGIN

	DBMS_OUTPUT.DISABLE;
	DBMS_OUTPUT.ENABLE;

	EXECUTE IMMEDIATE 'SELECT MAX(' || P_TB_SEQ_NM || ') FROM ' || P_TB_NM INTO V_MAX_VALUE;
	EXECUTE IMMEDIATE 'SELECT ' || P_SQ_NM || '.NEXTVAL FROM DUAL' INTO V_NEXT_VALUE;

	SELECT V_MAX_VALUE-V_NEXT_VALUE INTO V_CAL_VALUE FROM DUAL;

	IF V_MAX_VALUE > V_NEXT_VALUE THEN
    
		FOR I IN 1..V_CAL_VALUE
		LOOP
			EXECUTE IMMEDIATE 'SELECT ' || P_SQ_NM || '.NEXTVAL FROM DUAL' INTO T_NUM;
		END LOOP;

	ELSE 

		DBMS_OUTPUT.PUT_LINE('시퀀스 싱크 맞추지 않아도 되는 경우');

	END IF;

END;

 

 

< 로직 >

1. 로직 흐름

시퀀스명, 테이블내 시퀀스 컬럼명, 테이블명을 넣어서 

테이블내 시퀀스값이 시퀀스의 next value보다 크다면 그만큼 반복문으로 nextval() 함수를 호출해주도록 했당

 

2 EXECUTE IMMEDIATE 명령어

- 테이블명, 시퀀스명을 텍스트로 넣었기 때문에, 텍스트를 조합해서 명령어처럼 실행하고 싶었다. 그럴 때 EXECUTE IMMEDIATE 명령어를 통해 사용할 수 있다.

- 프로시저 내에서 쓸 수 있다.

- 다만, 나는 호출만 필요할 뿐 이 명령어를 통해 어딘가에 저장하고 싶었던 것은 아니기에 
EXECUTE IMMEDIATE 명령어 뒤에 INTO 변수 부분을 넣어주지 않았는데, INTO 변수 부분을 넣지 않았을 때 프로시저는 정상적으로 완료되었다고 떴지만 실제로 호출이 안되더라.
그래서 변수를 저장하고 사용할 목적이 아니더라도, 이 명령어 뒤에 INTO 를 붙여줘야 한다는 것을 알게되었다.