sql - 是否有可能根据动态打开的引用游标的输出创建视图或表？

Question

我已经学会了如何返回一个动态打开的引用游标。现在基于输出，我想创建一个表或视图。

输入表：

   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' );

标签： sqloracleplsql

Answer 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 ;