如何在Oracle SQL单条查询结果中移除空值列

静态SQL无法实现这个需求，因为SQL语句的列数在编译阶段就已固定，必须通过动态SQL来生成只包含非空值列的查询语句。

方案1：使用PL/SQL块生成并执行动态查询 #

适合在PL/SQL环境中直接执行，快速获取结果：

DECLARE
  v_sql VARCHAR2(4000);
  v_refcur SYS_REFCURSOR;
  -- 根据实际非空列定义接收变量
  v_col1 NUMBER;
  v_col3 NUMBER;
BEGIN
  -- 拼接仅包含非空列的SELECT语句
  SELECT 'SELECT ' || LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY column_id) || ' FROM tbl WHERE rownum = 1'
  INTO v_sql
  FROM user_tab_columns
  WHERE table_name = 'TBL'
    AND EXISTS (
      SELECT 1
      FROM (SELECT XMLTYPE(DBMS_XMLGEN.getxml('SELECT * FROM tbl WHERE rownum = 1')) AS xml_data) x
      WHERE x.xml_data.existsNode('/ROWSET/ROW/' || column_name) = 1
        AND x.xml_data.extract('/ROWSET/ROW/' || column_name || '/text()').getStringVal() IS NOT NULL
    );

  -- 执行动态SQL并读取结果
  OPEN v_refcur FOR v_sql;
  FETCH v_refcur INTO v_col1, v_col3;
  CLOSE v_refcur;

  -- 输出结果（可选）
  DBMS_OUTPUT.PUT_LINE('COL_1: ' || v_col1);
  DBMS_OUTPUT.PUT_LINE('COL_3: ' || v_col3);
END;
/

方案2：创建通用函数返回结果游标 #

更灵活，支持任意表的单行查询，可在客户端或其他SQL语句中调用：

CREATE OR REPLACE FUNCTION get_non_null_row(p_table_name VARCHAR2, p_row_num NUMBER := 1) RETURN SYS_REFCURSOR
IS
  v_sql VARCHAR2(4000);
  v_refcur SYS_REFCURSOR;
BEGIN
  -- 动态生成目标查询语句
  SELECT 'SELECT ' || LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY column_id) || ' FROM ' || UPPER(p_table_name) || ' WHERE rownum = ' || p_row_num
  INTO v_sql
  FROM user_tab_columns
  WHERE table_name = UPPER(p_table_name)
    AND EXISTS (
      SELECT 1
      FROM (
        SELECT XMLTYPE(DBMS_XMLGEN.getxml('SELECT * FROM ' || UPPER(p_table_name) || ' WHERE rownum = ' || p_row_num)) AS xml_data
      ) x
      WHERE x.xml_data.existsNode('/ROWSET/ROW/' || column_name) = 1
        AND x.xml_data.extract('/ROWSET/ROW/' || column_name || '/text()').getStringVal() IS NOT NULL
    );

  -- 打开游标返回结果
  OPEN v_refcur FOR v_sql;
  RETURN v_refcur;
END;
/

调用示例：

-- 查询tbl表第一行的非空列
SELECT get_non_null_row('TBL') FROM dual;

核心原理 #

1. 用DBMS_XMLGEN.getxml将目标行转换为XML格式，方便逐列判断是否存在非空值；
2. 通过user_tab_columns获取表的列元数据，筛选出目标行中值非空的列；
3. 用LISTAGG拼接列名，生成仅包含非空列的动态SELECT语句；
4. 执行动态SQL并返回结果。

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