Есть ли какая-либо возможность создания представления или таблицы на основе выходных данных динамически открываемого ссылочного курсора?

ProgramBox

Есть ли какая-либо возможность создания представления или таблицы на основе выходных данных динамически открываемого ссылочного курсора?

Post author:admin
Запись опубликована:3 мая, 2023
Post category:Вопросы по программированию

#sql #oracle #plsql

#sql #Oracle #plsql

Вопрос:

Я узнал, как вернуть динамически открываемый ссылочный курсор. Теперь на основе выходных данных я хочу создать таблицу или представление.

Входная таблица:

    create table sales
    (s_sale_name varchar2(20),
    s_date_sal date,
    s_qty number(10)
    );

Записи

 insert into sales values ('Norb','10-MAR-2019',10000);
insert into sales values ('Bert','10-MAR-2019',5000);
insert into sales values ('Alba','10-MAR-2019',4000);
insert into sales values ('Rob','10-MAR-2019',200000);
insert into sales values ('Norb','11-MAR-2019',5000);
insert into sales values ('Bert','11-MAR-2019',13000);
insert into sales values ('Rob','11-MAR-2019',80000);
insert into sales values ('Norb','12-MAR-2019',1000);
insert into sales values ('Bert','12-MAR-2019',4000);
insert into sales values ('Rob','12-MAR-2019',40000);
insert into sales values ('Alba','12-MAR-2019',2000);

Вывод запроса

 sales_name 10-MAR-2019 11-MAR-2019 12-MAR-2019 
Norb             10000        5000        1000
Bert              5000       13000        4000 
Alba              4000           0        2000 
Rob             200000       80000       40000

Теперь результат должен быть сохранен в таблице или представлении. Я уже научился возвращать динамически открываемый ссылочный курсор.

——Вот процедура, которую я использовал ————

 create or replace package p_sales_pkg 
as 
type rc is ref cursor;
procedure get_query( p_cursor in out rc, p_start date, p_end date );
end;
/

create or replace package body p_sales_pkg
as
 procedure get_query( p_cursor in out rc, p_start date, p_end date )
 is
 l_query long := 'select s_name ';
 begin
  for i in 1 .. trunc(p_end)-trunc(p_start) 1
 loop
 l_query := l_query || ', sum( decode( trunc(s_date), ' ||
 'to_date( ''' || to_char(p_start i-1,'yyyymmdd') ||
 ''', ''yyyymmdd'' ), s_qty, 0 )) "' ||
 to_char(p_start i-1) || '"';
 end loop;
l_query := l_query || ' from sales group by s_name';
 open p_cursor for l_query;
 end;
 end;
 /

set autoprint on



var x refcursor

exec nw_demo_pkg.get_query( :x, '10-MAR-19', '13-MAR-19' );

1. Можете ли вы опубликовать запрос, который привел к этому выводу? Я не вижу никакой симметрии в результате. Динамический sql для создания представления или таблицы зависит от столбцов запроса.

2. Ссылочные курсоры — это конструкция PL / SQL, мы не можем использовать их в обычном SQL. Итак, у вас есть выбор. Если у вас статическая структура результирующего набора и вы всегда хотите заполнять одну и ту же таблицу, используйте коллекции PL / SQL для запуска FORALL insert. Но если вы хотите иметь переменную структуру ссылочного курсора (каждый раз разные столбцы) и, следовательно, разные целевые таблицы, тогда вам нужен метод 4 dynamic SQL, который является высокоразвитым и довольно сложным. Узнайте больше

3. Перечитывая ваш вопрос — он довольно неясен — но, похоже, вы можете захотеть использовать ссылочный курсор для управления ВЫДЕЛЕННОЙ частью СОЗДАННОЙ ТАБЛИЦЫ … КАК оператор SELECT. Ну, мы определенно не можем этого сделать. Также не создавайте представление с помощью ссылочного курсора. Для этого вам пришлось бы использовать динамический SQL.

4. @XIN, спасибо за ваше решение. Я соответствующим образом обновил. Пожалуйста, взгляните, потому что предлагаемое вами решение должно давать ожидаемый результат.

5. @NorbertWupona . К сожалению, вы не можете использовать execute immediate в два раза больше, чем это имеет место в вашем заявлении. Каким-то образом вы можете использовать только Query в методе, показанном ниже;

Ответ №1:

Это действительно очень приятный и сложный вопрос. Я не согласен с пунктом @APC на SELECT part of a CREATE TABLE ... AS SELECT statement. Well we definitely can't do that . Я считаю, что для каждой проблемы в Oracle существует решение.

Ваше требование может быть достигнуто с помощью NESTED TABLE . Смотрите ниже:

Настройка:

 create  table sales
    (s_sale_name varchar2(20),
    s_date_sal date,
    s_qty number(10)
    );

 /     
insert into sales values ('Norb','10-MAR-2019',10000);
insert into sales values ('Bert','10-MAR-2019',5000);
insert into sales values ('Alba','10-MAR-2019',4000);
insert into sales values ('Rob','10-MAR-2019',200000);
insert into sales values ('Norb','11-MAR-2019',5000);
insert into sales values ('Bert','11-MAR-2019',13000);
insert into sales values ('Rob','11-MAR-2019',80000);
insert into sales values ('Norb','12-MAR-2019',1000);
insert into sales values ('Bert','12-MAR-2019',4000);
insert into sales values ('Rob','12-MAR-2019',40000);
insert into sales values ('Alba','12-MAR-2019',2000);

---Created an Object of Sales table to hold intermediate result
create or replace  type  sales_obj is OBJECT
    (obj_sale_name varchar2(20),
     obj_date_sal date,
     obj_qty number(10)
    );
/
-- Table of Sales Object.
create or replace type vtest1Tab is table of sales_obj;
/

Анонимный блок для создания таблицы ccc :

 DECLARE 
 VAR VTEST1TAB ; 
 vsql varchar2(500);

BEGIN 

  vsql := 'create table ccc(col1) NESTED TABLE COL1 STORE AS TAB1 
           as 
           Select cast(multiset(Select * from SALES) as VTEST1TAB )
           from dual           
          ';    

  Execute immediate vsql ; 

END;

Вывод:

 SQL> Select p.* 
     from ccc c, 
     table(c.COL1) p ;

Ответ №2:

В этой ссылке ответ «Zlatko Sirotic» точно описывает, как идентифицировать столбцы курсора и распечатать их.

Ищите «dyn_fetch», поскольку пакет достаточно общий, он может работать с любым запросом на печать данных. Вы можете использовать тот же подход для вставки данных в таблицу, которая создается динамически.