Oracle 使用游標為所有使用者資料表批次新增主鍵

Oracle 使用游標為所有使用者資料表批次新增主鍵

在資料遷移或系統初始化階段，常需為缺乏主鍵的資料表統一新增自增主鍵以提升存取效能。本文提供一支完整的 PL/SQL 程式碼，利用游標遍歷指定表空間下的所有資料表，自動判斷欄位狀態並建立主鍵、序列與觸發器。

適用情境與邏輯說明

• 適用場合： 匯入 Oracle 資料庫後，針對無主鍵的資料表批次處理。整數型主鍵索引效能最佳。
• 處理邏輯：
  1. 主鍵欄位統一命名為 ID。
  2. 若表中已有 ID 欄位且為 NUMBER 型別，直接沿用並建立主鍵約束。
  3. 若表中有 ID 但非 NUMBER 型別，將舊欄位重命名為 表名_ID，再新增 ID。
  4. 若無 ID 欄位，直接新增 ID NUMBER。
  5. 為每張表建立對應序列 (SEQ_表名) 與觸發器 (TRG_表名) 實現自動遞增。
  6. 建立主鍵約束 (PK_表名) 並設定 NOT NULL。
• 風險提示： 腳本會刪除原有索引與主鍵約束，嚴禁直接在正式營運資料庫執行，請先於測試環境驗證。

核心程式碼解析

DECLARE
  -- 變數宣告：動態 SQL 字串、主鍵/序列/觸發器命名、計數器等
  addstring      NVARCHAR2(2000);
  renamestring   NVARCHAR2(2000);
  tablestring    NVARCHAR2(2000);
  keyidname      NVARCHAR2(255) := 'ID';
  tableidname    NVARCHAR2(255);
  trigerstring   NVARCHAR2(2000);
  trgname        NVARCHAR2(255);
  seqstring      NVARCHAR2(2000);
  seqname        NVARCHAR2(255);
  pkname         NVARCHAR2(255);
  constring      NVARCHAR2(2000);
  notnullstring  NVARCHAR2(2000);
  
  -- 游標：查詢指定表空間 'SZGABL' 下的所有資料表
  CURSOR mycursor IS 
    SELECT * FROM user_tables WHERE tablespace_name='SZGABL' ORDER BY table_name;
  myrecord       mycursor%ROWTYPE;
  CounterName    INT := 0; -- 是否存在 ID 欄位
  CounterData    INT := 0; -- ID 是否為 NUMBER
BEGIN
  DBMS_OUTPUT.PUT_LINE('DECLARE counter INT :=0; BEGIN ');
  OPEN mycursor;
  IF mycursor%ISOPEN THEN
    LOOP
      FETCH mycursor INTO myrecord;
      EXIT WHEN mycursor%NOTFOUND;
      
      -- 產生標準化命名 (移除 TB_ 前綴)
      SELECT REPLACE(myrecord.table_name, 'TB_', '') INTO tablestring FROM dual;
      SELECT 'SEQ_' || tablestring INTO seqname FROM dual;
      SELECT 'TRG_' || tablestring INTO trgname FROM dual;
      SELECT 'PK_' || tablestring INTO pkname FROM dual;
      SELECT tablestring || UPPER(keyidname) INTO tableidname FROM dual;
      
      -- 檢查是否存在 ID 欄位
      SELECT COUNT(*) INTO CounterName FROM dual 
      WHERE EXISTS (SELECT * FROM user_tab_cols 
                    WHERE LOWER(column_name)=LOWER(keyidname) 
                    AND table_name=myrecord.table_name);
      
      IF CounterName = 0 THEN
        -- 情況 1：完全無 ID 欄位 -> 新增 ID、建立序列、觸發器、PK、NOT NULL
        DBMS_OUTPUT.PUT_LINE('-- 表 ' || myrecord.table_name || ' 無 ID 欄位');
        -- 動態 SQL 組裝並透過 DBMS_OUTPUT 輸出 (實際執行需移除註解的 execute immediate)
        ...
      ELSE
        -- 檢查 ID 是否為 NUMBER
        SELECT COUNT(*) INTO CounterData FROM dual 
        WHERE EXISTS (SELECT * FROM user_tab_cols 
                      WHERE LOWER(column_name)=LOWER(keyidname) 
                      AND data_type='NUMBER' 
                      AND table_name=myrecord.table_name);
        IF CounterData = 0 THEN
          -- 情況 2：有 ID 但非 NUMBER -> 重命名舊欄，新增 ID，後續同情況 1
          ...
        ELSE
          -- 情況 3：已有 NUMBER 型 ID -> 僅建立序列、觸發器、PK、NOT NULL
          ...
        END IF;
      END IF;
      DBMS_OUTPUT.PUT_LINE('');
    END LOOP;
  ELSE
    DBMS_OUTPUT.PUT_LINE('-- 游標開啟失敗');
  END IF;
  CLOSE mycursor;
  DBMS_OUTPUT.PUT_LINE('END;');
END;

執行步驟與驗證

1. 使用 PL/SQL Developer、SQL*Plus 或 SQLcl 連線目標 Schema。
2. 確認游標查詢條件 (`TABLESPACE_NAME='SZGABL'`) 符合目標環境。
3. 執行區塊，觀察 DBMS_OUTPUT 視窗輸出的動態 SQL 語句。
4. 驗證無誤後，將輸出的 SQL 語句複製執行，或直接取消註解 EXECUTE IMMEDIATE 重新執行區塊 (謹慎操作)。
5. 執行後查詢 USER_CONSTRAINTS、USER_SEQUENCES、USER_TRIGGERS 確認物件建立狀態。

參考查詢語句

-- 查詢所有主鍵約束
SELECT * FROM user_constraints WHERE constraint_type='P';
-- 查詢所有序列
SELECT * FROM user_sequences;
-- 查詢所有觸發器
SELECT * FROM user_triggers;

此腳本展示了動態 SQL、游標處理、條件判斷與資料字典查詢的綜合應用，可作為自動化 Schema 維護的參考範本。