使用动态列动态创建视图的Oracle PL/SQL过程/函数

我之前有类似的要求，但现在给了我一个需要实施的明确方法。

我需要编写一个过程 (最好是)/函数，它将基于作为参数传递的app_id，在动态视图中更改相应值的列标题。

*** 整个示例数据和结构可在此处获得: ***

https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=63c08123fbf2fb13de81df0bff360c0b

带有列标题的表是DATA_HEADER。

有值的表是DATA_VALUE。

列标题和值需要按顺序枢转。

两个表都有app_id on common。因此，对于app_id = 1，DATA_HEADER中的标签将用于DATA_VALUE中的值。但是，这些值仅基于PID更改，因此它们将保持恒定，每个应用程序id仅会更改标头。

当app_id在proc/funct中传递时，预期的视图应该是:

PID标题组方面EPT IT BU部分类标签值选项注释
120罗伯特代理IT简介是好的标准代码绿色需要改进的细节
120斯坦利代理IT概要是好的标准代码绿色需要改进细节平均

调用proc(app_id =>2)
PID项目理念透视分析现状部门方面3类标签值选项评论备注
120罗伯特代理IT简介是好的标准代码绿色需要改进的细节
121斯坦利代理IT概要是好的标准代码绿色需要改进细节平均

(对结构表示歉意，但我不知道如何格式化表，希望预期的结果很清楚)

因此，基本上，每个app_id的标头都会更改，并且列标头的最大数量将为20。因此，数字或名称应与表DATA_HEADER中的名称不同。
这些值是基于pid唯一标识的。

列标题的顺序将按照DATA_HEADER中的seq列进行。
类似地，值的顺序将按照数据值中的seq列，因此必须遵循并相应地旋转序列。

Oracle版本: 12.1

这应该会让你开始

SQL>
SQL> create table data_header(app_id number,label varchar2(400),seq number);

Table created.

SQL>
SQL> INSERT ALL
  2  INTO DATA_HEADER VALUES(1,'Title',1)
  3  INTO DATA_HEADER VALUES(1,'Group',2)
  4  INTO DATA_HEADER VALUES(1,'Aspect',3)
  5  INTO DATA_HEADER VALUES(1,'EPT',4)
  6  INTO DATA_HEADER VALUES(1,'IT',5)
  7  INTO DATA_HEADER VALUES(1,'BU',6)
  8  INTO DATA_HEADER VALUES(1,'Section',7)
  9  INTO DATA_HEADER VALUES(1,'Class',8)
 10  INTO DATA_HEADER VALUES(1,'Label Value Options',9)
 11  INTO DATA_HEADER VALUES(1,'Comments',10)
 12  INTO DATA_HEADER VALUES(2,'Title',1)
 13  INTO DATA_HEADER VALUES(2,'Group',2)
 14  INTO DATA_HEADER VALUES(2,'Aspect',3)
 15  INTO DATA_HEADER VALUES(2,'EPT',4)
 16  INTO DATA_HEADER VALUES(2,'IT',5)
 17  INTO DATA_HEADER VALUES(2,'BU',6)
 18  INTO DATA_HEADER VALUES(2,'Section',7)
 19  INTO DATA_HEADER VALUES(2,'Class',8)
 20  INTO DATA_HEADER VALUES(2,'Label Value Options',9)
 21  INTO DATA_HEADER VALUES(2,'Comments',10)
 22  INTO DATA_HEADER VALUES(2,'Remarks',11)
 23  select * from dual;

21 rows created.

SQL>
SQL> create table data_value(pid number,value varchar2(4000),app_id number,seq number);

Table created.

SQL>
SQL> INSERT ALL INTO DATA_VALUE VALUES(120,'Robert',1,1)
  2  INTO DATA_VALUE VALUES(120,'Deputy',1,2)
  3  INTO DATA_VALUE VALUES(120,'IT',1,3)
  4  INTO DATA_VALUE VALUES(120,'Synopsis is good',1,4)
  5  INTO DATA_VALUE VALUES(120,'Standard',1,5)
  6  INTO DATA_VALUE VALUES(120,'Code',1,6)
  7  INTO DATA_VALUE VALUES(120,'Green',1,7)
  8  INTO DATA_VALUE VALUES(120,'Needs Improvement for the details ',1,8)
  9  INTO DATA_VALUE VALUES(120,'Robert',2,1)
 10  INTO DATA_VALUE VALUES(120,'Deputy',2,2)
 11  INTO DATA_VALUE VALUES(120,'IT',2,3)
 12  INTO DATA_VALUE VALUES(120,'Synopsis is good',2,4)
 13  INTO DATA_VALUE VALUES(120,'Standard',2,5)
 14  INTO DATA_VALUE VALUES(120,'Code',2,6)
 15  INTO DATA_VALUE VALUES(120,'Green',2,7)
 16  INTO DATA_VALUE VALUES(120,'Needs Improvement for the details ',2,8)
 17  INTO DATA_VALUE VALUES(121,'Stanley',1,1)
 18  INTO DATA_VALUE VALUES(121,'Deputy',1,2)
 19  INTO DATA_VALUE VALUES(121,'IT',1,3)
 20  INTO DATA_VALUE VALUES(121,'Synopsis is good',1,4)
 21  INTO DATA_VALUE VALUES(121,'Standard',1,5)
 22  INTO DATA_VALUE VALUES(121,'Code',1,6)
 23  INTO DATA_VALUE VALUES(121,'Green',1,7)
 24  INTO DATA_VALUE VALUES(121,'Needs Improvement for the details ',1,8)
 25  INTO DATA_VALUE VALUES(121,'Average',1,9)
 26  INTO DATA_VALUE VALUES(121,'Stanley',2,1)
 27  INTO DATA_VALUE VALUES(121,'Deputy',2,2)
 28  INTO DATA_VALUE VALUES(121,'IT',2,3)
 29  INTO DATA_VALUE VALUES(121,'Synopsis is good',2,4)
 30  INTO DATA_VALUE VALUES(121,'Standard',2,5)
 31  INTO DATA_VALUE VALUES(121,'Code',2,6)
 32  INTO DATA_VALUE VALUES(121,'Green',2,7)
 33  INTO DATA_VALUE VALUES(121,'Needs Improvement for the details ',2,8)
 34  INTO DATA_VALUE VALUES(121,'Average',2,9)
 35  SELECT * FROM DUAL;

34 rows created.

SQL>
SQL> set serverout on
SQL> declare
  2    l_view_header clob;
  3    l_view_cols   clob;
  4    l_seq_hwm     int;
  5  begin
  6
  7    select 'create or replace view MY_VIEW ( '|| listagg('"'||label||'"',',') within group ( order by seq ) || ')',
  8           max(seq)
  9    into   l_view_header,
 10           l_seq_hwm
 11    from   data_header
 12    where  app_id = 1;
 13
 14    select ' as select pid, '||listagg('max(decode(seq,'||rownum||',value))'||chr(10),',') within group ( order by level )
 15    into l_view_cols
 16    from   dual
 17    connect by level <= l_seq_hwm;
 18
 19    l_view_cols := l_view_cols || ' from DATA_VALUE where app_id = 1 group by pid';
 20
 21    dbms_output.put_line(l_view_header);
 22    dbms_output.put_line(l_view_cols);
 23  end;
 24  /
create or replace view MY_VIEW ( "Title","Group","Aspect","EPT","IT","BU","Section","Class","Label Value Options","Comments")
as select pid,
max(decode(seq,1,value))
,max(decode(seq,2,value))
,max(decode(seq,3,value))
,max(decode(seq,4,value))
,max(decode(seq,5,value))
,max(decode(seq,6,value))
,max(decode(seq,7,value))
,max(decode(seq,8,v
alue))
,max(decode(seq,9,value))
,max(decode(seq,10,value))
 from DATA_VALUE where app_id = 1 group by pid

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>
复制