Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 создание коллекции на основании динамического sql  [new]
Rus23
Member

Откуда:
Сообщений: 1
Коллеги, приветствую.
Есть вопрос.
У меня есть анонимный блок (прикладываю ниже), далее он будет преобразован в процедуру внутри пакета.
Нужно результат этого запроса куда-либо сохранять, с учётом того, что количество столбцов, возвращаемых запросом, может меняться (т.е. количество столбцов в p_pivot1 и p_pivot2 может меняться).
Есть вариант в процедуре прописать create table example as
далее её дропать
и делать так каждое выполнение процедуры,
но хочется попробовать более лаконичный вариант
есть ли возможность создать коллекцию с вариативным количеством столбцов, т.е. сформировать её на основании это динамического запроса? (честно говоря в плане коллекций слабо подкован, материалы читал, но по своему вопросу ничего не нашел).
Далее хочу джоинить эту коллекцию к другой табле.
Спасибо.


declare
p_sql clob default null;
p_pivot1 clob default null;
p_pivot2 clob default null;

begin

select listagg(xxx ||' '||REGEXP_REPLACE(zzz, '\s+', '_'), ', ') within group (order by xxx) list_for_pivot2
into p_pivot1
from tabl1
where cpd_id not in (3,4);

select listagg(xxx ||' '||REGEXP_REPLACE(zzz, '\s+', '_'), ', ') within group (order by xxx) list_for_pivot1
into p_pivot2
from tabl2
where enabled = 1;

p_sql :=
'
insert into test_pivot
select *
from
(
with actual_rates as
(
select t.*
from
(
select *
from com_prop cp
join com_m cm
on cm.id = cp.id
and cm.enabled = 1
where 1=1
and cp.enabled = 1
)
pivot
(
max(value)
for type in
(
'|| p_pivot1 ||'
)
) t
where t.id = 6
and t.type = 1
and t.status = 1
)
select *
from
(
select *
from actual_rates ar
join values cbv
on ar.id = cbv.id
and cbv.enabled = 1
join properties cmp
on cmp.profile = cbv.id
and cmp.enabled = 1
)
pivot
(
max(cbp_value)
for cbp_type_id in
(
'|| p_pivot2 ||'
)
)
)';

execute immediate p_sql;

end;
12 ноя 21, 20:25    [22395333]     Ответить | Цитировать Сообщить модератору
 Re: создание коллекции на основании динамического sql  [new]
SQL*Plus
Member

Откуда: Россия, Москва
Сообщений: 8544
Rus23
Коллеги, приветствую.
Есть вопрос.
У меня есть анонимный блок (прикладываю ниже), далее он будет преобразован в процедуру внутри пакета.
Нужно результат этого запроса куда-либо сохранять, с учётом того, что количество столбцов, возвращаемых запросом, может меняться (т.е. количество столбцов в p_pivot1 и p_pivot2 может меняться).
Есть вариант в процедуре прописать create table example as
далее её дропать
и делать так каждое выполнение процедуры,
но хочется попробовать более лаконичный вариант
есть ли возможность создать коллекцию с вариативным количеством столбцов, т.е. сформировать её на основании это динамического запроса? (честно говоря в плане коллекций слабо подкован, материалы читал, но по своему вопросу ничего не нашел).
Далее хочу джоинить эту коллекцию к другой табле.
Спасибо.

declare
  p_sql clob default null;
  p_pivot1 clob default null;
  p_pivot2 clob default null;

 begin
 
         select listagg(xxx ||' '||REGEXP_REPLACE(zzz, '\s+', '_'), ', ') within group (order by xxx) list_for_pivot2
            into p_pivot1
        from tabl1
        where cpd_id not in (3,4);
 
        select listagg(xxx ||' '||REGEXP_REPLACE(zzz, '\s+', '_'), ', ') within group (order by xxx) list_for_pivot1
            into p_pivot2
        from  tabl2
        where enabled = 1;
     
        p_sql := 
    '
    insert into test_pivot 
    select *
    from
    (
    with actual_rates as
    (
    select t.*
    from 
    (
    select *
    from com_prop cp
    join com_m cm
        on cm.id = cp.id
        and cm.enabled = 1
    where 1=1
    and cp.enabled = 1
    )
    pivot
    (
    max(value)
    for type in
    (
    '|| p_pivot1 ||'
    )
    ) t
    where t.id = 6
    and t.type = 1
    and t.status = 1
    )
    select *
    from
    (
    select *
    from actual_rates ar
    join values cbv
        on ar.id = cbv.id
        and cbv.enabled = 1
    join properties cmp
        on cmp.profile = cbv.id
        and cmp.enabled = 1
    )
    pivot
    (
    max(cbp_value)
    for cbp_type_id in
    (
    '|| p_pivot2 ||'
    )
    )
    )';

  execute immediate p_sql;
 
 end;


Для оформления кода используйте, пожалуйста, тэг SRC.
12 ноя 21, 22:02    [22395381]     Ответить | Цитировать Сообщить модератору
 Re: создание коллекции на основании динамического sql  [new]
maglevdevice
Member

Откуда:
Сообщений: 9
Если вот так в лоб решать, то DBMS_SQL. Вроде такого:
declare
  lcCursor integer default DBMS_SQL.OPEN_CURSOR;
  lnColCnt number;
  ltDescTab DBMS_SQL.DESC_TAB;
  lsSQL varchar2(4000);
begin
  lsSQL := '
    select 1 a from dual
  ';
  DBMS_SQL.PARSE(lcCursor, lsSQL, DBMS_SQL.NATIVE);
  DBMS_SQL.DESCRIBE_COLUMNS(lcCursor, lnColCnt, ltDescTab);
  -- Now ltDescTab collection contains a list of columns 
  DBMS_SQL.CLOSE_CURSOR(lcCursor);
end;
/
18 ноя 21, 14:09    [22397494]     Ответить | Цитировать Сообщить модератору
 Re: создание коллекции на основании динамического sql  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10337
Rus23
Далее хочу джоинить эту коллекцию к другой табле.


И как же ты будешь джоинить эту коллекцию не зная число полей? Смотри в сторону SQL MACRO и/или polymorphic function если версия позволяет.

SY.
18 ноя 21, 14:21    [22397506]     Ответить | Цитировать Сообщить модератору
 Re: создание коллекции на основании динамического sql  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 19924
На "чистых" коллекциях есть вариант запилить на базе anydata/anydataset, но стоит ли овчинка выделки...
Также возможно генерировать XML или JSON с последующим выходом на join xmltable/json_table
18 ноя 21, 14:58    [22397528]     Ответить | Цитировать Сообщить модератору
 Re: создание коллекции на основании динамического sql  [new]
Leonid Kudryavtsev
Member

Откуда:
Сообщений: 9889
andrey_anonymous

Также возможно генерировать XML или JSON с последующим выходом на join xmltable/json_table


IMHO мисье знает толко в практологии и ремонте двигателей автомобилей через выхлопную трубу )))
18 ноя 21, 15:12    [22397537]     Ответить | Цитировать Сообщить модератору
 Re: создание коллекции на основании динамического sql  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 3279
Rus23

Далее хочу джоинить эту коллекцию к другой табле.


сразу (до пивот) джоинить не судьба?

.....
stax
18 ноя 21, 15:34    [22397553]     Ответить | Цитировать Сообщить модератору
 Re: создание коллекции на основании динамического sql  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 19924
Leonid Kudryavtsev
мисье знает толк

Даже не сомневайтесь.
18 ноя 21, 16:33    [22397584]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить