Oracle 批次更新sequence的儲存

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 ---> 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語句

   

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

      /*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;
 

--使用步驟：

--  編輯儲存..-->調用儲存（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；

祝好~