Oracle 表存在則刪除不存在則建立：PL/SQL 動態 SQL 實現 IF EXISTS 邏輯

問題背景

Oracle 無 SQL Server 的 IF EXISTS (...) DROP TABLE ... 簡潔語法。批次建立臨時表 (如 8 個 DB Link 並行採集) 時，手動確認極耗時。需自動化：存在即 Drop，不存在直接 Create，一次執行到位。

解決方案：匿名 PL/SQL 區塊 + 動態 SQL

DECLARE
  V_CNT INTEGER;
BEGIN
  -- 1. 查詢 USER_TABLES 判斷是否存在
  SELECT COUNT(*) INTO V_CNT
  FROM USER_TABLES
  WHERE TABLE_NAME = 'XB_TEST001';  -- 注意：大寫

  -- 2. 存在則輸出訊息並 DROP
  IF V_CNT > 0 THEN
    DBMS_OUTPUT.PUT_LINE('表已存在，執行 DROP...');
    EXECUTE IMMEDIATE 'DROP TABLE XB_TEST001';
  ELSE
    DBMS_OUTPUT.PUT_LINE('表不存在，直接建立');
  END IF;

  -- 3. 重新建立表
  EXECUTE IMMEDIATE 'CREATE TABLE XB_TEST001 (
    ID INT PRIMARY KEY,
    NAME VARCHAR2(50)
  )';
  DBMS_OUTPUT.PUT_LINE('建立完成');
END;
/

關鍵技術點解析

進階：封裝為可重用 Procedure

CREATE OR REPLACE PROCEDURE RECREATE_TABLE (
  P_TABLE_NAME IN VARCHAR2,
  P_DDL        IN CLOB
) AS
  V_CNT INTEGER;
BEGIN
  SELECT COUNT(*) INTO V_CNT FROM USER_TABLES WHERE TABLE_NAME = UPPER(P_TABLE_NAME);
  IF V_CNT > 0 THEN
    EXECUTE IMMEDIATE 'DROP TABLE ' || P_TABLE_NAME;
  END IF;
  EXECUTE IMMEDIATE P_DDL;
END;
/

-- 呼叫範例
BEGIN
  RECREATE_TABLE('XB_TEST001', 'CREATE TABLE XB_TEST001 (ID INT PRIMARY KEY, NAME VARCHAR2(50))');
END;
/

總結

透過 USER_TABLES + EXECUTE IMMEDIATE 實現 Oracle 版「IF EXISTS DROP THEN CREATE」，解決批次部署臨時表的互動確認痛點。封裝為 Procedure 後可進一步參數化 DDL，提升腳本復用性與維運效率。