Oracle中执行动态SQL咨询：如何运行DYN_DateCheck中的动态查询

原始生成动态SQL的语句 #

With DYN_DateCheck As
(Select 'Select ' || ''''|| TABLE_NAME ||'''' || ' As Tbl, ' || ''''|| COLUMN_NAME||'''' || ' As Fld, ' || 'Check_Date(' || COLUMN_NAME || ', :dateformat) As ValidDate ' || 
         'From '   || TABLE_NAME  || '_TST ' ||
         'Where '  || COLUMN_NAME || ' IS NOT NULL UNION'
  From  all_tab_columns 
  Where NOT OWNER  In ('SYS', 'XDB', 'GSMADMIN_INTERNAL' ) 
  And TABLE_NAME   In ('EGL_CLIENTDETAIL', 'STG_PLY', 'STG_PLR', 'STG_IAC', 'STG_PPD')
  And   DATA_TYPE   = 'DATE'
  UNION
  Select 'Select ' || '''XYZ''' || ' As Tbl, ' || '''XYZ''' || ' As Fld, NULL As ValidDate From Dual' From Dual
  Order By 1
)
Select * From (Select * From DYN_DateCheck)

用户问题 #

你可能已经知道，上述最终的Select语句并不会实际执行DYN_DateCheck中所有的Select/Union语句，它只是生成动态SQL。请问如何执行DYN_DateCheck中生成的这些动态Select语句？

我尝试过使用Execute Immediate，但不太理解它的用法，也不知道如何使用执行后的结果。

解决方案 #

要执行这些动态生成的SQL并获取结果，需要先把零散的SQL片段拼接成完整的可执行语句，再通过EXECUTE IMMEDIATE结合游标/集合处理结果，以下是具体实现：

1. 拼接完整的动态SQL #

先用LISTAGG函数将DYN_DateCheck生成的所有SQL片段拼接成一个完整的可执行语句：

WITH DYN_DateCheck AS (
    SELECT 'Select '''|| TABLE_NAME ||''' As Tbl, '''|| COLUMN_NAME||''' As Fld, Check_Date('|| COLUMN_NAME ||', :dateformat) As ValidDate From '|| TABLE_NAME ||'_TST Where '|| COLUMN_NAME ||' IS NOT NULL UNION'
    FROM all_tab_columns 
    WHERE OWNER NOT IN ('SYS', 'XDB', 'GSMADMIN_INTERNAL') 
      AND TABLE_NAME IN ('EGL_CLIENTDETAIL', 'STG_PLY', 'STG_PLR', 'STG_IAC', 'STG_PPD')
      AND DATA_TYPE = 'DATE'
    UNION ALL
    SELECT 'Select ''XYZ'' As Tbl, ''XYZ'' As Fld, NULL As ValidDate From Dual' FROM Dual
)
SELECT LISTAGG(sql_text, ' ') WITHIN GROUP (ORDER BY 1) AS full_dynamic_sql
FROM DYN_DateCheck;

2. 执行动态SQL并处理结果 #

提供两种常用的执行方式：

方式一：存储过程遍历输出结果

创建存储过程接收日期格式参数，执行动态SQL并输出每一行结果：

CREATE OR REPLACE PROCEDURE ExecuteDateCheck(p_dateformat IN VARCHAR2)
IS
    v_dynamic_sql VARCHAR2(32767);
    -- 定义结果记录类型
    TYPE result_rec IS RECORD (
        Tbl VARCHAR2(100),
        Fld VARCHAR2(100),
        ValidDate VARCHAR2(100) -- 根据Check_Date函数返回类型调整
    );
    v_result result_rec;
    -- 定义动态游标
    TYPE result_cursor IS REF CURSOR;
    c_result result_cursor;
BEGIN
    -- 拼接完整动态SQL
    WITH DYN_DateCheck AS (
        SELECT 'Select '''|| TABLE_NAME ||''' As Tbl, '''|| COLUMN_NAME||''' As Fld, Check_Date('|| COLUMN_NAME ||', :dateformat) As ValidDate From '|| TABLE_NAME ||'_TST Where '|| COLUMN_NAME ||' IS NOT NULL UNION'
        FROM all_tab_columns 
        WHERE OWNER NOT IN ('SYS', 'XDB', 'GSMADMIN_INTERNAL') 
          AND TABLE_NAME IN ('EGL_CLIENTDETAIL', 'STG_PLY', 'STG_PLR', 'STG_IAC', 'STG_PPD')
          AND DATA_TYPE = 'DATE'
        UNION ALL
        SELECT 'Select ''XYZ'' As Tbl, ''XYZ'' As Fld, NULL As ValidDate From Dual' FROM Dual
    )
    SELECT LISTAGG(sql_text, ' ') WITHIN GROUP (ORDER BY 1) INTO v_dynamic_sql
    FROM DYN_DateCheck;

    -- 打开动态游标并执行
    OPEN c_result FOR v_dynamic_sql USING p_dateformat;
    LOOP
        FETCH c_result INTO v_result;
        EXIT WHEN c_result%NOTFOUND;
        -- 输出结果，也可改为插入临时表等操作
        DBMS_OUTPUT.PUT_LINE('表: ' || v_result.Tbl || ', 字段: ' || v_result.Fld || ', 验证结果: ' || v_result.ValidDate);
    END LOOP;
    CLOSE c_result;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('执行错误: ' || SQLERRM);
END;
/

调用存储过程：

SET SERVEROUTPUT ON;
EXEC ExecuteDateCheck('YYYY-MM-DD'); -- 替换为你的目标日期格式

方式二：BULK COLLECT批量获取结果

如果需要一次性获取所有结果到集合中，可用BULK COLLECT：

DECLARE
    v_dynamic_sql VARCHAR2(32767);
    -- 定义集合类型存储结果
    TYPE result_tab IS TABLE OF VARCHAR2(300) INDEX BY PLS_INTEGER;
    v_results result_tab;
BEGIN
    -- 拼接动态SQL（将多列合并为单字符串方便批量收集）
    WITH DYN_DateCheck AS (
        SELECT 'Select '''|| TABLE_NAME ||'''||''|''||'''|| COLUMN_NAME||'''||''|''||Check_Date('|| COLUMN_NAME ||', :dateformat) From '|| TABLE_NAME ||'_TST Where '|| COLUMN_NAME ||' IS NOT NULL UNION'
        FROM all_tab_columns 
        WHERE OWNER NOT IN ('SYS', 'XDB', 'GSMADMIN_INTERNAL') 
          AND TABLE_NAME IN ('EGL_CLIENTDETAIL', 'STG_PLY', 'STG_PLR', 'STG_IAC', 'STG_PPD')
          AND DATA_TYPE = 'DATE'
        UNION ALL
        SELECT 'Select ''XYZ|XYZ|NULL'' From Dual' FROM Dual
    )
    SELECT LISTAGG(sql_text, ' ') WITHIN GROUP (ORDER BY 1) INTO v_dynamic_sql
    FROM DYN_DateCheck;

    -- 执行并批量收集结果
    EXECUTE IMMEDIATE v_dynamic_sql BULK COLLECT INTO v_results USING 'YYYY-MM-DD';

    -- 遍历输出结果
    FOR i IN 1..v_results.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(v_results(i));
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('错误信息: ' || SQLERRM);
END;
/

注意事项 #

• 确保Check_Date函数存在且参数格式匹配，:dateformat是绑定变量，通过USING传入值可避免SQL注入风险。
• 若生成的动态SQL长度超过32767字符，需将v_dynamic_sql改为CLOB类型，并用XMLAGG替代LISTAGG拼接字符串。
• 结果处理可根据业务需求调整，比如插入临时表、写入文件等，而非仅输出到控制台。

内容的提问来源于stack exchange，提问作者Mark