Oracle 通过游标访问动态生成的枢轴列

嗨，汤姆，

我有一个PL/SQL报告，其中列动态更改 (请参阅示例SQL:https://livesql.oracle.com/apex/livesql/s/f8gbm32e2acju7jzvuzjjh3a1) 基于参数。
假设如果报告是为Q1 2018运行的，那么它将有3列 (1月、2月和3月)，它们实际上是表中的行。

我可以使用pivot来获得所需格式的输出，但需要知道如何访问PL/SQL中动态变化的列名以显示在报表输出中。

提前感谢您的解决方案。

您不能动态地将值列表传递给pivot:

create table my_tab(acc varchar2(10), period varchar2(10), revenue number);

insert into my_tab values('100', 'jan-18', 100);
insert into my_tab values('100', 'feb-18', 200);
insert into my_tab values('100', 'mar-18', 300);
insert into my_tab values('101', 'jan-18', 150);
insert into my_tab values('101', 'feb-18', 250);
insert into my_tab values('101', 'mar-18', 350);
insert into my_tab values('101', 'apr-18', 350);
insert into my_tab values('101', 'may-18', 350);

commit;

var p1 number;
var p2 number;
var p3 number;

select *
from   my_tab pivot (
  sum(revenue) rev 
  for period in (
    :p1 p1, :p2 p2, :p3 p3
  ) 
);

SQL Error: ORA-56900: bind variable is not supported inside pivot|unpivot operation
复制

所以你需要去某种形式的动态SQL无论如何。在这种情况下，您称呼它们并不重要!使用open for，无论如何您都可以按位置获取。你只需要知道你要返回多少列。

如果您知道您将始终有三个周期，则可以将它们选择为P1，P2和P3值或类似值:

declare
  cur sys_refcursor;
  type period_res is record (
    acc    varchar2(10),
    p1_rev int,
    p2_rev int,
    p3_rev int
  );
  type period_res_arr is table of period_res index by pls_integer;
  results period_res_arr;
begin
  open cur for q'| select * 
from   my_tab pivot (
  sum(revenue) rev 
  /* replace in list with your dynamic generation */
  for period in ('jan-18' p1, 'feb-18' p2, 'mar-18' p3) 
) |';
  loop
     fetch cur bulk collect into results limit 100;
     exit when results.count = 0;
     
     for res in 1 .. results.count loop
       dbms_output.put_line (
         'Acc ' || results(res).acc || ' P1 ' || results(res).p1_rev || 
         ' P2 ' || results(res).p2_rev || ' P3 ' || results(res).p3_rev
       );
     end loop;
     
  end loop;
  close cur;
end;
/

Acc 101 P1 150 P2 250 P3 350
Acc 100 P1 100 P2 200 P3 300
复制

。.. 除非您足够勇敢地使用XML!)

在这里，您可以使用查询来动态定义字段。但是你得到的结果作为XML。所以你需要转换这个:

set long 100000
select acc, xmlserialize(Document period_xml as varchar2(1000) indent size=2) 
from   my_tab pivot xml (
  sum(revenue) rev 
  for period in (
    select 'jan-18' p1 from dual union all
    select 'feb-18' p2 from dual union all
    select 'mar-18' p3 from dual 
  ) 
);

ACC   XMLSERIALIZE(DOCUMENTPERIOD_XMLASVARCHAR2(1000)INDENTSIZE=2)                                                                                                                                                                                                                                                                 
100   
  
    feb-18
    200
  
  
    jan-18
    100
  
  
    mar-18
    300
  

   
101   
  
    feb-18
    250
  
  
    jan-18
    150
  
  
    mar-18
    350
  
复制

再次，将值提取到通用的P1，P2等变量中。