#sql #oracle
#sql #Oracle
Вопрос:
У меня есть требование, при котором я создаю таблицу динамически (количество столбцов может меняться в зависимости от входного параметра моей процедуры, с помощью которой я создаю эту таблицу) с данными в таблице, как показано ниже.
PK col1 col2 col3
A null 1-2 3-4
B null null 4-5
C null 5-6 null
Теперь требуется, чтобы я извлекал только столбцы, в которых должна быть хотя бы 1 запись без null, и загружал все данные в файл. Мой вывод должен быть таким, как показано ниже (col1 исключен из вывода, поскольку он содержит все нули).
PK col2 col3
A 1-2 3-4
B null 4-5
C 5-6 null
Кто-нибудь может предоставить какие-либо подсказки для достижения этого. Заранее спасибо.
Комментарии:
1. Я не знаю никакого способа использовать обычный SQL. Вы также пробовали PL / SQL?
2. Недостаточно разбираетесь в динамическом sql, но вы должны быть в состоянии получить список соответствующих столбцов из словаря dba_tab_cols (или all_tab_cols, если у вас нет доступа), выполнив команду select column_name из dba_tab_cols, где num_nulls <> 0 и table_name = ‘xyz’ (это предполагает, что статистика таблицы актуальна).
3. @BrianDeMilia — Я не получу желаемый результат, если использую приведенный выше запрос, поскольку мне нужно исключить столбцы со всеми нулевыми значениями. Но я могу получить общее количество записей в таблице и сравнить его с num_nulls.
Ответ №1:
Я подозреваю, что это будет не очень эффективно, но вы можете использовать COUNT (), чтобы определить, есть ли в столбце только нули, потому что COUNT (column_here) добавит только 1 для каждого ненулевого значения. Следовательно, если счетчик равен нулю, в этом столбце будут только нули.
Затем это может быть объединено в запрос для генерации действительного оператора select, который затем немедленно выполняется (соблюдая осторожность, конечно, чтобы избежать внедрения sql).
В любом случае, вот пример:
select
'select '
|| substr((
select
case when count(COL1) > 0 then ',col1' else '' end
|| case when count(COL2) > 0 then ',col2' else '' end
|| case when count(COL3) > 0 then ',col3' else '' end
from a_table
),2,8000)
|| ' from '
|| ' a_table'
as sql_string
from dual
;
смотрите этот sqlfiddle
результатом приведенного выше является:
| SQL_STRING |
|--------------------------------|
| select col2,col3 from a_table |
Ответ №2:
Вот попытка. Сначала создайте функцию для генерации вашего запроса, возвращающую КУРСОР ССЫЛКИ:
create or replace function select_non_nulls() return sys_refcursor as
myQuery varchar2(500);
myCur sys_refcursor;
begin
select 'select ' || listagg(col, ', ') within group (order by col) || ' from test'
into myQuery
from
(
select case when max(col1) is null then null else 'col1' end col from test
union all
select case when max(col2) is null then null else 'col2' end col from test
union all
select case when max(col3) is null then null else 'col3' end col from test
)
;
open myCur for myQuery;
return myCur;
end;
/
Затем используйте его в SQL * Plus:
SQL> var rc refcursor
SQL> exec :rc := select_non_nulls;
SQL> print rc;
Ответ №3:
Я использовал num_nulls из all_tab_cols и добился результата в соответствии с моим требованием. Спасибо.