Подзапрос столбцов в Select

#sql #oracle #select

Мне необходимо в запросе вывести значения непустых столбцов (в таблице более 200
столбцов, но заполнены из них около 10).

SELECT (SELECT  t.column_name
FROM    user_tab_columns t
WHERE   t.nullable = 'Y'
        AND t.table_name = 'mytable'
        AND t.num_distinct != 0) FROM mytable;


Код возвращает ожидаемую ошибку: 


  ORA-01427: подзапрос одиночной строки возвращает более одной строки
  01427. 00000 -  "single-row subquery returns more than one row"


"Подзапрос" корректно возвращает список непустых колонок.
    

Ответы

Ответ 1

Подзапрос в листе SELECT может вернуть только один столбец и одну строку, т.е. подзапросом
достичь желаемого невозможно. Можно создать запрос динамиески в PL/SQL. Это наверное
единственная возможность построить запрос, если имена и количество столбцов незвестны.
Например так:    

declare
    cols varchar2(1999);
    cur sys_refcursor;
    curid number;
    type colvals_type is table of varchar2(32);
    colvals colvals_type := colvals_type();
    desctab  dbms_sql.desc_tab;
    colcnt number;
begin
    for col in (
        SELECT column_name
        FROM user_tab_columns 
        WHERE nullable = 'Y'
        AND table_name = upper('mytable')
        AND num_distinct != 0) loop
        cols := cols || col.column_name || ','; 
    end loop;
    cols := regexp_replace(cols, ',$', '');
    open cur for 'SELECT ' || cols || ' FROM mytable';
    curid := dbms_sql.to_cursor_number(cur);
    dbms_sql.describe_columns(curid, colcnt, desctab);
    for idx in 1..colcnt loop 
        colvals.extend; 
        dbms_sql.define_column(curid, idx, colvals(idx), 32); 
    end loop;
    dbms_output.put_line(regexp_replace(cols, ',', chr(9)));
    while dbms_sql.fetch_rows(curid) > 0 loop
        for idx  in 1..colcnt loop 
            dbms_sql.column_value(curid, idx, colvals(idx));
            dbms_output.put(colvals(idx) || chr(9));
            --desctab(idx) - вся инфа о столбце - имя, длина, тип и т.д
        end loop;
        dbms_output.new_line();
    end loop;
end;
/


PS в JAVA было бы по моему проще, хотя смысл тот же. Ну или, как автор предложил
в комментарии, генерировать каждый раз SQL-скрипт и выпполнять его. Всё зависит от
того, для чего это конкретно нужно.


Ответ 2

Начиная с релиза 18c был введён новый тип табличных функций - PFT (Polymorphic Table
Functions). Это даёт некоторые возможности упростить решение подобных задач как в вопросе.

Подготовим данные, причём заполним только два столбца - par1 и par5:

create table params (id number, par1 number, par2 number, par3 number, par4 number,
par5 number);
insert into params (id, par1, par5) 
    select rownum, rownum+10, rownum+50
    from xmlTable ('1 to 1000')
;
exec dbms_stats.gather_table_stats (user, 'params') 
create or replace type colNameList is table of varchar2 (130);
/  


Сам запрос и вывод результата:

select *
from skipCols.emptyByTableName (params, 'params')
where id <= 3
;

        ID       PAR1       PAR5
---------- ---------- ----------
         1         11         51
         2         12         52
         3         13         53


Реализация табличной функции:

create or replace package skipCols as
    function emptyByTableName (tab table, tableName varchar2) return table pipelined
row polymorphic using skipCols;
    function describe (tab in out dbms_tf.table_t, tableName varchar2) return dbms_tf.describe_t;
end skipCols ; 
/
create or replace package body skipCols as
    function describe (tab in out dbms_tf.table_t, tableName varchar2) return dbms_tf.describe_t as 
        cols colNameList;
    begin 
        select cast (collect ('"'||column_name||'"') as colNameList) into cols
        from user_tab_cols  
        where table_name = upper (tableName)
        and nullable = 'Y'
        and num_distinct = 0
        ;
        for ix in 1..tab.column.count loop
            tab.column(ix).pass_through := tab.column(ix).description.name not member
of cols;
        end loop;
        return null;
    end;
end skipCols;
/