)
--..一个序列被2个表/2字段共用...可以用如下方式进行
UNION
SELECT 'ETL_CS_CUST_INFO_MID' ,
'BATCH_NO', --若数据为VARCHAR类型需要TO_NUMBER转换来取MAX(字段)
'SEQ_ETL_CS_CUST_INFO_MID'
FROM DUAL) C,
DBA_TAB_COLUMNS C1
WHERE C1.OWNER = UPPER (USERNAME)
AND C1.COLUMN_NAME = C.COLUMN_NAME
AND C1.TABLE_NAME = C.TABLE_NAME)
/**
---提供表长度大于26 的表名字/序列 ..再关联DBA_CONS_COLUMNS找到对应的主键字段..和表长度小于26部分的查询进行UNION ALL
CS_BEAR_ALLOWANCE_AND_INJ_DET ---> SEQ_CS_BEAR_ALLOWANCE_INJ_DET
CS_BEAR_ALLOWANCE_AND_INJ_DETS ---> SEQ_CS_BEAR_ALLOWANCE_INJ_DETS...等
*/
UNION ALL (SELECT M1.TABLE_NAME, COLUMN_NAME, M2.SEQUENCE_NAME
FROM (SELECT LENGTH(C.TABLE_NAME) AA,
C.TABLE_NAME,
C.COLUMN_NAME
FROM DBA_CONS_COLUMNS C
WHERE C.OWNER = UPPER (USERNAME)
AND (C.CONSTRAINT_NAME, C.TABLE_NAME) IN
( SELECT S.CONSTRAINT_NAME, S.TABLE_NAME
FROM DBA_CONSTRAINTS S
WHERE S.OWNER = UPPER (USERNAME)
AND S.CONSTRAINT_TYPE = 'P' )) M1 --如果不限制主键 可能找到NOT NULL的列
JOIN (SELECT TABLE_NAME, SEQUENCE_NAME
FROM (SELECT 'CS_BEAR_ALLOWANCE_AND_INJ_DET' TABLE_NAME,
'SEQ_CS_BEAR_ALLOWANCE_INJ_DET' SEQUENCE_NAME
FROM DUAL
UNION ALL
SELECT 'CS_BEAR_ALLOWANCE_AND_INJ_DETS' ,
'SEQ_CS_BEAR_ALLOWANCE_INJ_DETS'
FROM DUAL)) M2
ON M1.TABLE_NAME = M2.TABLE_NAME
WHERE AA > 26 )) T1,
DBA_SEQUENCES SQ, --(列出的序列是否在库中存在)
DBA_TABLES T --(列出的表是否在库中存在)..由于环境不同用到的序列可能也是不同的.若不加可能会报错
WHERE SQ.SEQUENCE_NAME = T1.SEQUENCE_NAME1
AND T.TABLE_NAME = T1.TABLE_NAME
AND SQ.SEQUENCE_OWNER = UPPER (USERNAME)
AND T.OWNER = UPPER (USERNAME);
----------------------以上查询表/对应序列/主键字段 -------------
----------------------以下开始判断序列是否需要更新 -------------
BEGIN
----------------------SEQUENCE判断更新语句 -----------------------------
--~~注释：DBMS_OUTPUT.PUT_LINE(XX)是将这个结果或者查询显示出来
--EXECUTE IMMEDIATE XX; --执行XX的查询
--开始 SEQUENCE.nextval和主键最大值 做比较..
FOR P_C_CONS IN C_CONS LOOP
--利用C_CONS游标对应列值
P_TABLE_NAME := P_C_CONS.TABLE_NAME;
P_COLUMN := P_C_CONS.COLUMN_NAME;
P_SEQUENCE := P_C_CONS.SEQUENCE_NAME;
---每次循环都赋值0 ..
MAX_ID := 0;
--查询表主键中最大值
P_SQL := 'SELECT MAX(' || P_COLUMN || ') FROM ' || P_TABLE_NAME;
--USING MAX_ID
EXECUTE IMMEDIATE P_SQL
INTO MAX_ID;
-- 查询序列.nextval值
P_SEQ_SQL := 'SELECT ' || P_SEQUENCE || '.NEXTVAL FROM DUAL' ;
--USING P_SEQ_SQL
EXECUTE IMMEDIATE P_SEQ_SQL
INTO P_SEQ_NUM;
---SEQUENCE.nextval和主键最大值 做比较..（如果SEQUENCE.nextval<主键最大值，更新序列'drop-create'）
IF P_SEQ_NUM < MAX_ID THEN
/*DBMS_OUTPUT.PUT_LINE( 'DROP SEQUENCE ' || P_SEQUENCE);*/
--删除原来不正确的SEQUENCE
EXECUTE IMMEDIATE 'DROP SEQUENCE ' || P_SEQUENCE;
P_NEW_COUNT := 0;
P_NEW_COUNT := MAX_ID + 1; -->当前主键最大值+1 才是SEQUENCE要更新值，才保证主键值再加入的时候不冲突；
P_SQL_SEQ := 'CREATE SEQUENCE ' || P_SEQUENCE ||
' MINVALUE 1 NOMAXVALUE START WITH ' || P_NEW_COUNT ||
' INCREMENT BY 1 CACHE 20'; --创建正确的SEQUENCE语句
/*打印序列创建语句*/