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;