Oracle -- 批量更新sequence的儲存

前言：

ORACLE的序列（SEQUENCE）A SCHEMA OBJECT THAT GENERATES A SERIAL LIST OF UNIQUE NUMBERS FOR TABLE COLUMNS.TO USE A SEQUENCE ,YOUR SCHEMA MUST CONTAIN THE SEQUENCE OR YOU MUST HAVE BEEN GRANTED THE SELECT OBJECT PRIVILEGE FOR ANOTHER USER IS SEQUENCE.ONCE A SEQUENCE IS DEFINED,IT CAN BE ACCESSED AND INCREMENTED BY MULTIPLE USERS(WHO HAVE SELECT OBJECT PRIVILEGE FOR THE SEQUENCE CONTAINING THE SEQUENCE)WITH NO WAITING.THE DATABASE DOES NOT WAIT FOR A TRANSACTION THAT HAS INCREMENTED A SEQUENCE TO COMPLETE BEFORE THAT SEQUENCE CAN BE INCREMENTED AGAIN.

簡單說：序列一般用於自動遞增生成主鍵值 ..

但是否有一些情況會導致呼叫SEQ_....NEXTVAL時大於主鍵最大值呢？

場景：

主鍵表 -> T表 '100W'資料同步到T1表（同步：TRUNCATE/DELETE或IMPDP...等方式[手動改資料]）

例如： T表對應SEQ_T.NEXTVAL= 100W；

T1表對應SEQ_T.NEXTVAL= 10W；

TRUNCATE TABLE T1;

INSERT TABLE T1 SELECT * FROM T;

資料同步但T1表對應SEQ_T.NEXTVAL= 10W序列不變；

此時T1呼叫序列INSERT到T1表時就會報錯 ( ORA-00001 : 違反唯一約束條件 (LOTTERY.PK_T1))

(若批量同步很多表就可能會引起很多表序列需要更新成主鍵最大值+ 1才能保證不報錯

(可以用把源庫的SEQUENCE同步過來①或者如下儲存解決② ))

①PLSQL 工具的COMPARE USER OBJECTS可以解決SEQUENCE序列物件同步到其他環境...在此就不細說了

②我們環境序列一般都是由SEQ_表名字組成.所以寫批量更新儲存的時候比較方便~

如下儲存針對常用於以上場景的環境,,生產環境不批量導新資料/同步使用者資料/表資料就很少用到...也可只提供參考...

--批量更新序列儲存--
CREATE OR REPLACE PROCEDURE P_SYNCSEQ(USERNAME VARCHAR2 /*傳入要檢查/更新序列的使用者*/ ) IS

/*

**@AUTHOR 毛海晴

ORACLE 批量更新SEQUENCE

註釋：

批量更新SEQUENCE,

更新序列下一個值 = 主鍵最大值+1

---序列建立時，屬性NOMAXVALUE=最大值是10的28次方

思路：

1、找到每個表主鍵列且在該表主鍵最大值是什麼？

2、找到表對應SEQUENCE值與表主鍵最大值去對比。

如果SEQUENCE 下一個值大於表主鍵最大值就不做更新；

否則需要進行更新(2中更新方式)

1）刪除SEQUENCE ，建立新序列開始值為表主鍵最大值+1； --本文選擇此方案...嘿嘿~

(壞處：趕好在DROP SEQUENCE..而程式也恰巧呼叫依賴它的函式和儲存過程將失效

但後續CREATE SEQUENCE了，再呼叫了會重新編譯呼叫..不會報錯....有實驗過哦~)

2）ALTER SEQUENCE .. INCREMENT BY .. NOCACHE;

SELECT ...NEXTVAL FROM DUAL;

ALTER SEQUENCE .. INCREMENT BY 1 CACHE 20;

.... sequence.nextval其實也可以用user_sequences.last_number欄位實現..由於最早儲存就這樣的寫就沒改~...諒解~

SEQUENCE和表名長度最大限制是30

SEQUENCE規範的名字SEQ_+表名字 -- 此處規範只是管理維護方便而已並不是非要這樣要求

如果表名長度大小大於26 加上"SEQ_"就大於了SEQUENCE長度限制的30

若表名長度大於26，那對應序列肯定不是規範命名(SEQ_表名字),再由於這樣的序列並不多,所以將這些一一處理

在更新前可先註釋掉EXECUTE IMMEDIATE，先作下測試看下效果，免得EXECUTE IMMEDIATE DROP .後建立報錯，導致在呼叫序列不會建立,也校驗不到序列

所需許可權：
-- 建立序列許可權 --

    -- Grant/Revoke system privileges
  grant create sequence to LOTTERY;
  --查詢許可權--
  -- Grant/Revoke object privileges
  grant select on DBA_CONSTRAINTS to LOTTERY;
  grant select on DBA_CONS_COLUMNS to LOTTERY;
  grant select on DBA_SEQUENCES to LOTTERY;
  grant select on DBA_TABLES to LOTTERY;
  grant select on DBA_TAB_COLUMNS to LOTTERY;
  --或者--
  -- Grant/Revoke system privileges
  grant select any dictionary to LOTTERY;

*/

--變數

MAX_ID NUMBER(12 );

P_SEQ_NUM NUMBER(12 );

P_TABLE_NAME VARCHAR2(50 );

P_COLUMN VARCHAR2(50 );

P_SEQUENCE VARCHAR2(50 );

P_SQL VARCHAR2(500 );

P_SEQ_SQL VARCHAR2(5000 );

P_SQL_SEQ VARCHAR2(30000 );

P_NEW_COUNT NUMBER(12 );

--查詢表長度小於26 的表/序列

--遊標

CURSOR C_CONS IS -- 查詢表長度小於26 的表/序列

SELECT T1.TABLE_NAME TABLE_NAME,

T1.COLUMN_NAME COLUMN_NAME,

T1.SEQUENCE_NAME1 SEQUENCE_NAME

FROM ((SELECT C.TABLE_NAME,

CASE

WHEN C1.DATA_TYPE = 'NUMBER' THEN

C.COLUMN_NAME

ELSE

'TO_NUMBER(' || C.COLUMN_NAME || ')'

END COLUMN_NAME,

C.SEQUENCE_NAME1

FROM (SELECT C.TABLE_NAME,

C.COLUMN_NAME,

'SEQ_' || C.TABLE_NAME SEQUENCE_NAME1

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' /*CONSTRAINT_TYPE: P：主鍵,R：外來鍵,C：非空約束/CHECK;*/

---若主鍵是由多欄位'ID1,ID2',該查詢會顯示成2行分別為(T.ID1 SEQ_T和T.ID2 SEQ_T)

)

--..一個序列被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 ---&gt SEQ_CS_BEAR_ALLOWANCE_INJ_DET

CS_BEAR_ALLOWANCE_AND_INJ_DETS ---&gt 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

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; --&gt當前主鍵最大值+1 才是SEQUENCE要更新值，才保證主鍵值再加入的時候不衝突；

P_SQL_SEQ := 'CREATE SEQUENCE ' || P_SEQUENCE ||

' MINVALUE 1 NOMAXVALUE START WITH ' || P_NEW_COUNT ||

' INCREMENT BY 1 CACHE 20'; --建立正確的SEQUENCE語句

/*列印序列建立語句*/

/*DBMS_OUTPUT.PUT_LINE('CREATE SEQUENCE ' || P_SEQUENCE ||

' MINVALUE 1 NOMAXVALUE START WITH ' ||

P_NEW_COUNT || ' INCREMENT BY 1 CACHE 20');*/

--執行建立序列語句

EXECUTE IMMEDIATE P_SQL_SEQ;

--列印錯錯誤序列對應的表、序列由之前值更新到現在的值；

DBMS_OUTPUT.PUT_LINE( '錯誤序列對應的表：' || P_TABLE_NAME || '

' || P_SEQUENCE || ' 由' ||

P_SEQ_NUM || '更新到' || P_NEW_COUNT || '；' );

END IF ;

END LOOP;

END P_SYNCSEQ;

--使用步驟：

-- 編輯儲存..--&gt呼叫儲存（Call change_varchar2(username => 'u1' );或者begin..傳值.等）

--輸出結果：

--DROP SEQUENCE SEQ_T1 --本文中儲存列印部分註釋掉了.若想看其效果將註釋/**/開啟.

--CREATE SEQUENCE SEQ_T1 MINVALUE 1 NOMAXVALUE START WITH 1004 INCREMENT BY 1 CACHE 20 --本文中儲存列印部分註釋掉了.若想看其效果將註釋/**/開啟.

--錯誤序列對應的表：T1

SEQ_T1 由1000更新到1004；

祝好~

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28602568/viewspace-1266101/，如需轉載，請註明出處，否則將追究法律責任。

Oracle -- 批量更新sequence的儲存

相關文章