DBeaver中EXECUTE IMMEDIATE执行动态查询无结果输出的解决方法

我是SQL初学者，在Mac上使用DBeaver v24.1.3连接Oracle数据库。我编写代码生成动态查询，用于计算表中所有列的非空行占比，期望生成包含列名和非空占比的结果表并保存到文件，后续将对多张表执行该操作。我可以通过DBMS_OUTPUT.PUT_LINE(sql_query);打印出构造的查询语句，随后在BEGIN / END;结构中使用EXECUTE IMMEDIATE sql_query;执行该动态查询。代码能正常运行，但未生成输出表；若将打印出的查询语句复制到SQL编辑器中手动执行，却能得到结果表。请问如何通过动态查询生成输出表？

以下是原代码：

DECLARE
    column_name VARCHAR2(255);
    sql_query VARCHAR2(32767) := 'WITH column_non_nulls AS (';
    dynamic_select VARCHAR2(1000);
    total_count NUMBER;
    first_column BOOLEAN := TRUE;
BEGIN
    -- Get the total number of rows in the table
    SELECT COUNT(*) INTO total_count FROM GENERIC.TABLENAME;

    -- Loop through each column in the table and dynamically construct the query
    FOR col IN (
        SELECT column_name
        FROM all_tab_columns
        WHERE table_name = 'TABLENAME'
        AND owner = 'GENERIC'
    )
    LOOP
        column_name := col.column_name;

        -- Construct the dynamic SELECT part for each column
        dynamic_select := 'SELECT ''' || column_name || ''' AS column_name, ' ||
                          'ROUND(100 * COUNT(' || column_name || ') / ' || total_count || ', 2) AS percent_non_null ' ||
                          'FROM GMD.STUDIES WHERE ' || column_name || ' IS NOT NULL';

        -- Append the dynamic SELECT part to the SQL query
        IF first_column THEN
            sql_query := sql_query || dynamic_select;
            first_column := FALSE;
        ELSE
            sql_query := sql_query || ' UNION ALL ' || dynamic_select;
        END IF;
    END LOOP;

    -- Close the CTE and the main SELECT query
    sql_query := sql_query || ') SELECT * FROM column_non_nulls ORDER BY percent_non_null DESC';

    -- Print the dynamically constructed SQL query for debugging purposes
    DBMS_OUTPUT.PUT_LINE(sql_query);

    -- Execute the dynamically generated SQL query
    EXECUTE IMMEDIATE sql_query;

END;

问题原因 #

EXECUTE IMMEDIATE执行查询语句时，默认不会将结果集返回给客户端工具（比如DBeaver），这是它和手动执行SQL的核心区别。手动执行时工具会自动处理结果集展示，但匿名块中的EXECUTE IMMEDIATE只是在数据库内部执行查询，不会主动把结果输出到客户端。

解决方案 #

方案1：将结果插入临时表（适合需要保存结果的场景） #

先创建会话级临时表，把动态查询的结果插入其中，之后查询临时表获取数据：

DECLARE
    column_name VARCHAR2(255);
    sql_query VARCHAR2(32767) := 'WITH column_non_nulls AS (';
    dynamic_select VARCHAR2(1000);
    total_count NUMBER;
    first_column BOOLEAN := TRUE;
BEGIN
    -- 创建会话级临时表，关闭会话自动删除数据
    EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE temp_col_non_null (
        column_name VARCHAR2(255),
        percent_non_null NUMBER(5,2)
    ) ON COMMIT PRESERVE ROWS';

    -- 获取目标表总行数
    SELECT COUNT(*) INTO total_count FROM GENERIC.TABLENAME;

    -- 循环构造动态查询（修正原代码表名错误：GMD.STUDIES改为GENERIC.TABLENAME）
    FOR col IN (
        SELECT column_name
        FROM all_tab_columns
        WHERE table_name = 'TABLENAME'
        AND owner = 'GENERIC'
    )
    LOOP
        column_name := col.column_name;

        dynamic_select := 'SELECT ''' || column_name || ''' AS column_name, ' ||
                          'ROUND(100 * COUNT(' || column_name || ') / ' || total_count || ', 2) AS percent_non_null ' ||
                          'FROM GENERIC.TABLENAME WHERE ' || column_name || ' IS NOT NULL';

        IF first_column THEN
            sql_query := sql_query || dynamic_select;
            first_column := FALSE;
        ELSE
            sql_query := sql_query || ' UNION ALL ' || dynamic_select;
        END IF;
    END LOOP;

    -- 修改SQL为插入临时表的语句
    sql_query := sql_query || ') INSERT INTO temp_col_non_null SELECT * FROM column_non_nulls ORDER BY percent_non_null DESC';

    DBMS_OUTPUT.PUT_LINE(sql_query);

    -- 执行动态SQL插入数据
    EXECUTE IMMEDIATE sql_query;

    -- 提交以保留临时表数据到会话结束
    COMMIT;

END;
/
-- 执行完匿名块后，查询临时表获取结果
SELECT * FROM temp_col_non_null;

方案2：使用REF CURSOR输出结果集（适合DBeaver直接查看导出） #

如果只是需要在DBeaver中查看结果并导出，不需要保存到表，可以用REF CURSOR返回结果集：

DECLARE
    column_name VARCHAR2(255);
    sql_query VARCHAR2(32767) := 'WITH column_non_nulls AS (';
    dynamic_select VARCHAR2(1000);
    total_count NUMBER;
    first_column BOOLEAN := TRUE;
    v_result SYS_REFCURSOR; -- 定义REF CURSOR变量
BEGIN
    SELECT COUNT(*) INTO total_count FROM GENERIC.TABLENAME;

    FOR col IN (
        SELECT column_name
        FROM all_tab_columns
        WHERE table_name = 'TABLENAME'
        AND owner = 'GENERIC'
    )
    LOOP
        column_name := col.column_name;

        dynamic_select := 'SELECT ''' || column_name || ''' AS column_name, ' ||
                          'ROUND(100 * COUNT(' || column_name || ') / ' || total_count || ', 2) AS percent_non_null ' ||
                          'FROM GENERIC.TABLENAME WHERE ' || column_name || ' IS NOT NULL';

        IF first_column THEN
            sql_query := sql_query || dynamic_select;
            first_column := FALSE;
        ELSE
            sql_query := sql_query || ' UNION ALL ' || dynamic_select;
        END IF;
    END LOOP;

    sql_query := sql_query || ') SELECT * FROM column_non_nulls ORDER BY percent_non_null DESC';

    DBMS_OUTPUT.PUT_LINE(sql_query);

    -- 打开REF CURSOR执行查询，DBeaver会自动展示结果
    OPEN v_result FOR sql_query;

END;
/

执行后DBeaver会弹出结果窗口，直接导出即可。

额外优化建议 #

原代码中每个列单独扫描一次表，效率较低。可以改为一次扫描表计算所有列的非空数，适合数据量大的表：

-- 示例优化思路（动态构造时可参考）
SELECT 
    'COL1' AS column_name, ROUND(100 * COUNT(COL1)/total_rows,2) AS percent_non_null
FROM GENERIC.TABLENAME
CROSS JOIN (SELECT COUNT(*) AS total_rows FROM GENERIC.TABLENAME) t
UNION ALL
SELECT 
    'COL2' AS column_name, ROUND(100 * COUNT(COL2)/total_rows,2) AS percent_non_null
FROM GENERIC.TABLENAME
CROSS JOIN (SELECT COUNT(*) AS total_rows FROM GENERIC.TABLENAME) t
-- 后续列以此类推

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