您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 教學

Oracle 循環建表與批量建立存儲過程：動態 SQL 實戰指南

編輯：Oracle 教學

Oracle 循環建表與批量建立存儲過程：動態 SQL 實戰指南

實務常需依據業務鍵（如身份證尾號、區域代碼）建立數十至數百張同結構分表，並配套專屬存儲過程處理資料路由。手工重複編寫不僅低效、易錯，維護更是惡夢。本文對比「手工重複」與「動態 SQL 迴圈」兩種模式，展示如何用 EXECUTE IMMEDIATE 實現參數化批量建構。

場景設定

建立 10 張員工資訊表 tb_employeeinfo0 ~ 9，欄位：employeeno VARCHAR2(10)、employeeage INT。
依工號最後一位分表（0~9）。
配套存儲過程 pr_insertdata0 ~ 9，實現「不存在則插入」邏輯。

模式一：手工重複（不推薦）

逐個撰寫 CREATE TABLE、CREATE INDEX、CREATE PROCEDURE，程式碼量隨表數線性膨脹，修改結構時需同步修改所有腳本。原文列出完整 10 組重複代碼，此處略。

模式二：動態 SQL 迴圈（推薦）

1. 批量建表與索引

DECLARE
  i        INT := 0;
  tbname   VARCHAR2(50);
  tmpcount INT;
  strsql   VARCHAR2(1000);
BEGIN
  WHILE i < 10 LOOP
    tbname := 'tb_employeeinfo' || TO_CHAR(i);
    i := i + 1;

    -- 若存在則刪除
    SELECT COUNT(1) INTO tmpcount FROM user_tables WHERE table_name = UPPER(tbname);
    IF tmpcount > 0 THEN
      EXECUTE IMMEDIATE 'DROP TABLE ' || tbname;
      COMMIT;
    END IF;

    -- 建表
    strsql := 'CREATE TABLE ' || tbname || '(
                 employeeno VARCHAR2(10) NOT NULL,
                 employeeage INT NOT NULL
               )';
    EXECUTE IMMEDIATE strsql;

    -- 重建唯一索引（先嘗試刪除舊索引避免報錯）
    EXECUTE IMMEDIATE q'[BEGIN EXECUTE IMMEDIATE 'DROP INDEX idx1_' || :1 || ''; EXCEPTION WHEN OTHERS THEN NULL; END;]' USING tbname;
    EXECUTE IMMEDIATE 'CREATE UNIQUE INDEX idx1_' || tbname || ' ON ' || tbname || '(employeeno)';
  END LOOP;
END;
/

2. 批量建立存儲過程

DECLARE
  v_i               INT := 0;
  v_procname        VARCHAR2(50);
  v_employeeinfotbl VARCHAR2(50);
  strsql            VARCHAR2(4000);
BEGIN
  WHILE v_i < 10 LOOP
    v_procname        := 'pr_insertdata' || SUBSTR(TO_CHAR(v_i),1,1);
    v_employeeinfotbl := 'tb_employeeinfo' || SUBSTR(TO_CHAR(v_i),1,1);
    v_i := v_i + 1;

    strsql := 'CREATE OR REPLACE PROCEDURE ' || v_procname || '(
                 v_employeeno IN VARCHAR2,
                 v_employeeage IN INT
               ) AS
                 v_employeecnt INT;
               BEGIN
                 SELECT COUNT(*) INTO v_employeecnt
                 FROM ' || v_employeeinfotbl || '
                 WHERE employeeno = v_employeeno;

                 IF v_employeecnt > 0 THEN
                   RETURN;
                 ELSE
                   INSERT INTO ' || v_employeeinfotbl || '(employeeno, employeeage)
                   VALUES (v_employeeno, v_employeeage);
                 END IF;
                 COMMIT;
               EXCEPTION
                 WHEN OTHERS THEN ROLLBACK; RETURN;
               END;';

    EXECUTE IMMEDIATE strsql;
  END LOOP;
END;
/

關鍵技術點解析

技術點	說明
`EXECUTE IMMEDIATE`	執行動態構建的 DDL/DML，支援綁定變數（`USING` 子句）防注入。
引號處理	動態 SQL 字串中單引號需雙寫（`''`）或使用 `q'[...]'` 替代引號機制。
異常處理	建立索引前先 `DROP INDEX ... EXCEPTION WHEN OTHERS THEN NULL` 避免不存在報錯中斷。
事務控制	存儲過程內顯式 `COMMIT/ROLLBACK`，確保單筆業務原子性。
效能考量	大量 DDL 建議分批提交、避開高峰期；索引建立可並行（`PARALLEL`）。

進階擴充建議

參數化表數量：將 10 改為變數或從配置表讀取。
統一路由包：改為單一 Package，內含 INSERT_DATA(p_empno, p_age) 內部路由，減少物件數量，便於管理權限與版本。
分區表替代：Oracle 11g+ 支援 REFERENCE PARTITIONING、INTERVAL PARTITIONING，若業務允許，單表分區優於手工分表。
元數據驅動：建立控制表記錄表結構、索引、程式碼模板，通用產生器自動化全流程。