PL/SQL 動態建表：EXECUTE IMMEDIATE 正確拼接表名與變數

錯誤代碼分析

-- 錯誤：表名變數 tpg_gzfspbnum 直接寫在字串常數中，不會被解析
execute immediate ' create table tpg_gzfspbnum as select ... where ywslid = L_YWSLID;';

字串常數內的識別符不會自動替換為變數值，需用拼接運算子 `||`。

正確寫法

CREATE OR REPLACE PROCEDURE prc_gzf_bz_ywslid (p_ywslid IN VARCHAR2) IS
    v_table_name VARCHAR2(30) := 'tpg_gzfspb' || p_ywslid;
    v_sql        VARCHAR2(4000);
BEGIN
    -- 1. 動態建表 (CTAS)
    v_sql := 'CREATE TABLE ' || v_table_name || 
             ' AS SELECT * FROM gzf_zfbz.tpg_gzfspb@gzf_zfbz WHERE ywslid = :b1';
    EXECUTE IMMEDIATE v_sql USING p_ywslid; -- 使用繫結變數防注入

    -- 2. 若需建立索引/約束，同理拼接
    EXECUTE IMMEDIATE 'CREATE INDEX idx_' || v_table_name || '_ywslid ON ' || v_table_name || '(ywslid)';
EXCEPTION
    WHEN OTHERS THEN
        -- 記錄錯誤日誌
        RAISE;
END;
/

關鍵最佳實踐

1. 繫結變數 (`USING`)：WHERE 條件值務必用繫結變數，避免 SQL 注入與硬解析。
2. 物件命名規範：表名長度 ≤ 30 字元 (Oracle 12.2 前)，避免保留字，建議加前綴 `TMP_` 或 `T_`。
3. 權限：執行建表的使用者需有 `CREATE TABLE` 系統權限及目標表空間配額。
4. DDL 隱含提交：`EXECUTE IMMEDIATE` 執行 DDL 會隱含提交當前事務，謹慎在事務中穿插。

進階：檢查表是否存在再建立

DECLARE
    v_cnt INTEGER;
BEGIN
    SELECT COUNT(*) INTO v_cnt FROM user_tables WHERE table_name = UPPER(v_table_name);
    IF v_cnt = 0 THEN
        EXECUTE IMMEDIATE v_sql USING p_ywslid;
    END IF;
END;

常見錯誤對照