Oracle中如何编写SQL仅返回至少含一个非空值的列？

嘿，这个需求我之前也碰到过——要动态筛选出表中至少有一行非空的列，毕竟全空列不固定，静态SQL肯定没法写死列名。下面我给你分享两种实用的实现方式，都是基于Oracle的动态SQL和数据字典来做的：

方法一：用PL/SQL块直接执行查询 #

这个方法适合在SQL*Plus或者PL/SQL Developer这类工具里快速运行，直接生成并执行动态查询语句：

DECLARE
    v_column_list VARCHAR2(32767); -- 用更大的长度避免截断
    v_query_sql   VARCHAR2(32767);
    v_table_name  VARCHAR2(100) := 'YOUR_TABLE_NAME'; -- 替换成你的表名，大小写不敏感
BEGIN
    -- 第一步：收集所有至少有一行非空的列名
    SELECT RTRIM(
               XMLAGG(
                   XMLELEMENT(e, '"' || column_name || '"', ', ') 
                   ORDER BY column_id
               ).EXTRACT('//text()'), 
               ', '
           )
    INTO v_column_list
    FROM user_tab_columns
    WHERE UPPER(table_name) = UPPER(v_table_name)
      AND EXISTS (
          SELECT 1
          FROM dual
          WHERE EXISTS (
              -- 动态检查当前列是否存在非空值
              EXECUTE IMMEDIATE 
                  'SELECT 1 FROM ' || UPPER(v_table_name) || ' WHERE "' || column_name || '" IS NOT NULL'
          )
      );

    -- 第二步：生成并执行查询语句
    v_query_sql := 'SELECT ' || v_column_list || ' FROM ' || UPPER(v_table_name);
    EXECUTE IMMEDIATE v_query_sql;
    
    -- 如果需要在工具里输出结果，可以用游标循环打印（示例）：
    -- FOR rec IN (EXECUTE IMMEDIATE v_query_sql) LOOP
    --     DBMS_OUTPUT.PUT_LINE(rec."Col1" || ' | ' || rec."Col3" || ' | ' || rec."Col4");
    -- END LOOP;
END;
/

注意事项： #

• 替换YOUR_TABLE_NAME为你的实际表名，代码里用了UPPER()来兼容大小写，不用担心表名大小写问题。
• 用XMLAGG代替LISTAGG是为了避免列名过多时超出LISTAGG的4000字符限制，XMLAGG支持更长的拼接结果。
• 列名用双引号括起来，避免列名包含特殊字符（比如空格、关键字）时出错。

方法二：创建存储过程返回结果集 #

如果需要在应用程序里调用，或者要重复使用这个逻辑，可以封装成存储过程，通过REF CURSOR返回结果：

CREATE OR REPLACE PROCEDURE get_non_empty_columns(
    p_table_name IN  VARCHAR2,
    p_result     OUT SYS_REFCURSOR
)
IS
    v_column_list VARCHAR2(32767);
    v_query_sql   VARCHAR2(32767);
BEGIN
    -- 收集符合条件的列名
    SELECT RTRIM(
               XMLAGG(
                   XMLELEMENT(e, '"' || column_name || '"', ', ') 
                   ORDER BY column_id
               ).EXTRACT('//text()'), 
               ', '
           )
    INTO v_column_list
    FROM user_tab_columns
    WHERE UPPER(table_name) = UPPER(p_table_name)
      AND EXISTS (
          SELECT 1
          FROM dual
          WHERE EXISTS (
              EXECUTE IMMEDIATE 
                  'SELECT 1 FROM ' || UPPER(p_table_name) || ' WHERE "' || column_name || '" IS NOT NULL'
          )
      );

    -- 打开游标返回结果
    v_query_sql := 'SELECT ' || v_column_list || ' FROM ' || UPPER(p_table_name);
    OPEN p_result FOR v_query_sql;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- 如果所有列都是空的，返回空结果集
        OPEN p_result FOR SELECT 1 FROM dual WHERE 1=0;
END;
/

使用方式（以SQL*Plus为例）： #

VAR result_cursor REFCURSOR
EXEC get_non_empty_columns('YOUR_TABLE_NAME', :result_cursor)
PRINT result_cursor

核心思路说明 #

整个逻辑的核心就是利用Oracle的USER_TAB_COLUMNS数据字典表获取目标表的所有列，然后对每个列动态执行检查语句，判断是否存在非空值，最后把符合条件的列拼接成SELECT语句执行。这样不管哪些列变成全空，查询都会自动调整返回的列，完全满足你“全空列不固定”的需求。

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