oracle存储过程动态更新序列

当系统正在工作时导数据，导出的数据可能会有主键冲突，因为导出数据时数据库的序列跟表不同步，所以一般需要更新下序列的当前值，但表多难免就麻烦。这里我用存储过程写了个动态更新序列的功能。存储过程会读一个表，此表指定了序列和对应的表和主键，根据表里的值去发起更新，如果需要更新，会更新成当前最大id + 100的值。大家看下就知道什么情况了。

DROP TABLE "T_TABLE_SEQ_MAPPING";
CREATE TABLE "T_TABLE_SEQ_MAPPING" (
"TABLE_NAME" VARCHAR2(200 BYTE) NULL ,
"SEQ_NAME" VARCHAR2(200 BYTE) NULL ,
"TABLE_ID_NAME" VARCHAR2(200 BYTE) NULL 
)
LOGGING
NOCOMPRESS
NOCACHE

;

-- ----------------------------
-- Records of T_TABLE_SEQ_MAPPING
-- ----------------------------
INSERT INTO "T_TABLE_SEQ_MAPPING" VALUES ('T_TICKET_TASK', 'S_TICKET_TASK_ID', 'TASK_ID');
INSERT INTO "T_TABLE_SEQ_MAPPING" VALUES ('T_TICKET', 'S_TICKET_ID', 'TICKET_ID');


CREATE OR REPLACE 
PROCEDURE "UPDATE_SEQ" AS
BEGIN
DECLARE
v_current_id NUMBER;
v_next_seq NUMBER;
v_current_user VARCHAR2(200);
v_table_name VARCHAR2(200);
v_seq_name VARCHAR2(200);
v_table_id_name VARCHAR2(200);
CURSOR map IS SELECT TABLE_NAME,SEQ_NAME,TABLE_ID_NAME FROM T_TABLE_SEQ_MAPPING;
	BEGIN
		OPEN map;
		LOOP
		FETCH map INTO v_table_name,v_seq_name,v_table_id_name;
			EXIT WHEN map%NOTFOUND;
			select user INTO v_current_user from dual;
			execute immediate 'grant create  sequence to ' || v_current_user;
			execute immediate 'grant drop any  sequence to ' || v_current_user;
			execute immediate 'SELECT MAX(' || v_table_id_name || ') FROM '|| v_table_name into v_current_id;
			execute immediate 'SELECT ' || v_seq_name || '.nextval FROM DUAL' into v_next_seq; 
			IF v_next_seq < v_current_id THEN
				execute immediate 'DROP sequence ' || v_seq_name;
				execute immediate 
				'create sequence '|| v_seq_name || chr(10) ||
				'minvalue 1' || chr(10) ||
				'maxvalue 999999999999999999999999999' || chr(10) ||
				'start with ' ||(v_current_id+100) || chr(10) ||
				'increment by 1' || chr(10) ||
				'cache 20';
			END IF;
		END LOOP;
		CLOSE map;
		commit;
	END;
END;


call UPDATE_SEQ();