Oracle 使用游標動態生成欄位清單與 SELECT 語句

應用場景

需動態取得指定資料表所有欄位名稱，拼成逗號分隔字串，進而組裝 SELECT col1, col2... FROM table，避免硬編碼欄位。

實作 PL/SQL 區塊

DECLARE
    mytablename  NVARCHAR2(200) := 'TB_UD_USER';  -- 目標表名
    mystring     NVARCHAR2(4000) := '';            -- 欄位清單字串
    selstring    VARCHAR2(4000)  := '';            -- 最終 SELECT 語句

    CURSOR mycursor IS
        SELECT DISTINCT ucc.column_name
        FROM user_tab_comments utc
        JOIN user_col_comments ucc ON utc.table_name = ucc.table_name
        WHERE utc.table_name = mytablename
        ORDER BY ucc.column_name;

    myrecord  mycursor%ROWTYPE;
BEGIN
    OPEN mycursor;
    IF mycursor%ISOPEN THEN
        LOOP
            FETCH mycursor INTO myrecord;
            EXIT WHEN mycursor%NOTFOUND;

            IF mystring IS NULL THEN
                mystring := myrecord.column_name;
            ELSE
                mystring := mystring || ',' || myrecord.column_name;
            END IF;
        END LOOP;

        DBMS_OUTPUT.PUT_LINE('當前資料表 ' || mytablename || ' 的欄位清單：');
        DBMS_OUTPUT.PUT_LINE(mystring);

        selstring := 'SELECT ' || mystring || ' FROM ' || mytablename;
        DBMS_OUTPUT.PUT_LINE('查詢所有記錄語句：');
        DBMS_OUTPUT.PUT_LINE(selstring);
    ELSE
        DBMS_OUTPUT.PUT_LINE('游標開啟失敗');
    END IF;
    CLOSE mycursor;
END;
/

輸出範例

當前資料表 TB_UD_USER 的欄位清單：
AH,BIRTHPLACE,BM,CELLPHONE,CJGZRQ,DEPARTMENT2,DJJID,GZCX,GZKH,GZSFZH,HJDZ,HYZK,ID,JHRQ,JTZZ,LAFX_LD,LJDZ,LLY,LXDH,NAME,NXDH,POLICENUMBER,RESERVE1,RESERVE10,RESERVE9,SCCP,SEX,SFJH,SFQBY,SFZ,SPJB,YL_22,ZJ,ZW,ZZMM
查詢所有記錄語句：
SELECT AH,BIRTHPLACE,BM,CELLPHONE,CJGZRQ,DEPARTMENT2,DJJID,GZCX,GZKH,GZSFZH,HJDZ,HYZK,ID,JHRQ,JTZZ,LAFX_LD,LJDZ,LLY,LXDH,NAME,NXDH,POLICENUMBER,RESERVE1,RESERVE10,RESERVE9,SCCP,SEX,SFJH,SFQBY,SFZ,SPJB,YL_22,ZJ,ZW,ZZMM FROM TB_UD_USER

關鍵點說明

1. 查詢 USER_COL_COMMENTS 取得欄位名稱，配合 ORDER BY 保證順序穩定。
2. 使用隱式游標屬性 %ISOPEN、%NOTFOUND 控制迴圈。
3. 字串拼接用 ||，首欄不加逗號。
4. 輸出長度可能超過 DBMS_OUTPUT 預設緩衝區，建議執行前 SET SERVEROUTPUT ON SIZE 1000000。

進階擴充

• 加入資料類型、註解資訊，產生建表 DDL 或 INSERT 語句。
• 封裝為函數 GET_COL_LIST(p_table_name) RETURN CLOB 供動態 SQL 直接調用。
• 注意權限：需具備 SELECT ANY DICTIONARY 或為表擁有者。