oralce动态维护数据库的序列

现在项目里是每一张表对应一个序列，并且有三个库，总是出现序列最大值比数据表主键最大值小，报违反唯一性约束问题，解决的方法是：循环比较每一个表的序列，如果序列值不够大，就调整到表主键最大值加1，适应情况：每一个表对应一个序列而且序列名按规则生成并且表有主键，存储过程如下[@more@]

create or replace procedure SYNCSEQ is
MAX_ID NUMBER(12);
P_SEQ_NUM NUMBER(12);
P_TABLE_NAME VARCHAR2(50);
P_COLUMN VARCHAR2(50);
P_SQL VARCHAR2(500);
P_SEQ_SQL VARCHAR2(5000);
P_SYN_SQL VARCHAR2(5000);
P_COUNT NUMBER(2);
--同步seq
CURSOR C_CONS IS SELECT c.table_name,c.column_name FROM user_cons_columns c WHERE (c.constraint_name,c.table_name) IN (
SELECT S.constraint_name,s.table_name FROM user_constraints s WHERE
(s.table_name LIKE 'BASE%' OR s.table_name LIKE 'BD%'
OR s.table_name LIKE 'CS%' OR s.table_name LIKE 'FM%' OR s.table_name LIKE 'ES%')
AND s.constraint_type='P');

begin

FOR P_C_CONS IN C_CONS LOOP
P_TABLE_NAME:=P_C_CONS.TABLE_NAME;
P_COLUMN:=P_C_CONS.COLUMN_NAME;
P_SQL:='SELECT MAX('||P_COLUMN||') FROM '||P_TABLE_NAME;
dbms_output.put_line(P_SQL);
execute immediate p_sql INTO MAX_ID;--USING MAX_ID;
--判断序列是否存在
SELECT COUNT(1) INTO P_COUNT FROM user_objects WHERE object_name = 'SEQ_'||P_TABLE_NAME;
IF P_COUNT>0 THEN
P_SEQ_SQL:='select seq_'||P_TABLE_NAME||'.nextval from dual';
dbms_output.put_line(P_SEQ_SQL);
execute immediate P_SEQ_SQL INTO P_SEQ_NUM;
dbms_output.put_line('P_SEQ_NUM:'||P_SEQ_NUM||'+++MAX_ID:'||MAX_ID);
IF P_SEQ_NUM MAX_ID:=MAX_ID+1;
dbms_output.put_line('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
P_SYN_SQL:='alter sequence SEQ_'||P_TABLE_NAME||' increment by '||MAX_ID||' nocache';
dbms_output.put_line(P_SYN_SQL);
execute immediate P_SYN_SQL;
--P_SYN_SQL:='select SEQ_'||P_TABLE_NAME||'.nextval from dual';
--dbms_output.put_line(P_SYN_SQL);
--execute immediate P_SYN_SQL;
--P_SYN_SQL:='alter sequence SEQ_'||P_TABLE_NAME||' increment by 1 cache 20';
--dbms_output.put_line(P_SYN_SQL);
--execute immediate P_SYN_SQL;
dbms_output.put_line('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
END IF;
END IF;
END LOOP;

end SYNCSEQ;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25261409/viewspace-1058983/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25261409/viewspace-1058983/