Oracle-返回动态列名的表函数

Question

我正在构建一个报告，该报告应根据请求的年份(即2000年至2002年(生成列的动态列表。用户应该能够通过SELECT语句运行报告，如：

SELECT * FROM TABLE(myreport(2002, 3)); -- Get 3 report years starting with 2002

目前，我有一个生成此输出的表函数，但使用静态列名：

-- Row type
CREATE OR REPLACE TYPE typ_myreport AS OBJECT (
myuser VARCHAR(260),
yr1_p DECIMAL(5,2),
yr1_t INTEGER,
yr2_p DECIMAL(5,2),
yr2_t INTEGER,
...
);
-- Table type
CREATE OR REPLACE TYPE tab_myreport AS TABLE OF typ_myreport;
-- Report
CREATE OR REPLACE FUNCTION myreport(pAsOfYear IN INTEGER) 
RETURN tab_myreport PIPELINED AS
BEGIN
FOR vRec IN (
SELECT user_name, yr1_p, yr1_t, y2_p, y2_t, ...
FROM mytable
) 
LOOP
PIPE ROW (
typ_myreport(vRec.user_name, vRec.yr1_p, vRec.yr1_t, vRec.yr2_p, vRec.year2_t, ...)
);
END LOOP;
RETURN;
END;

它产生这样的输出：

User    yr1_p   yr1_t   yr2_p   yr2_t   yr3_p   yr3_t
-----------------------------------------------------
Bobby      25       2      33       2      20       4
Barry      50       4      66       4      50      10
Big Ben    25       2       0       0      30       6

但我希望列根据请求的年份进行动态命名：

User    2000_p  2000_t  2001_p  2001_t  2002_p  2002_t
------------------------------------------------------
Bobby       25       2      33       2      20       4
Barry       50       4      66       4      50      10
Big Ben     25       2       0       0      30       6

输出结果集应该保持完全相同——表结构、数据类型——只是列名应该更改。我的想法是通过一个动态构建的SELECT语句来实现这一点，我可以执行该语句，然后返回结果。我一直在努力让它发挥作用，但没有运气。

有没有办法在函数/过程中使用动态列数和列名编写动态SQL，然后将结果集作为表(而不是脚本(输出返回？

更新
以下是生成yr字段的实际查询的简化片段：

WITH rpt_years AS (SELECT * FROM TABLE(GetReportyears(pAsOfYear)))
SELECT user_name,
SUM(year1_cnt) AS yr1_t,
SUM(COALESCE((year1_cnt / NULLIF(year1_tot,0)),0)) AS yr1_p,
SUM(year2_cnt) AS yr2_t, 
SUM(COALESCE((year2_cnt / NULLIF(year2_tot,0)),0)) AS yr2_p,
...
FROM (
SELECT DISTINCT 
ui.user_name, 
SUM(CASE WHEN s.dist_year = y.year1 THEN 1 END) AS year1_cnt,
COUNT(CASE WHEN s.dist_year = y.year1 THEN 1 END) AS year1_tot,
SUM(CASE WHEN s.dist_year = y.year2 THEN 1 END) AS year2_cnt,
COUNT(CASE WHEN s.dist_year = y.year2 THEN 1 END) AS year2_tot,
...
FROM rpt_years y -- Get report years as single row
INNER JOIN src_table s
...
) src
ORDER BY src.user_name

Answer 1

您可以使用开源程序Method4在SQL上下文中返回动态列数。

要想在一行代码中构建出您想要的东西，需要创建一个新类型。由于这些类型很复杂，我建议将该任务保存以备以后使用。对于第一步，如果只使用现有的类型，则只需要编写一条SQL语句来生成正确的SQL语句。

举个例子，让我们使用这个表来表示您现有的大型查询的结果：

create table MyTable as
select 'Bobby'   user_name, 25 yr1_p, 2 yr1_t, 33 yr2_p, 2 yr2_t, 20 yr3_p, 4  yr3_t from dual union all
select 'Barry'   user_name, 50 yr1_p, 4 yr1_t, 66 yr2_p, 4 yr2_t, 50 yr3_p, 10 yr3_t from dual union all
select 'Big Ben' user_name, 25 yr1_p, 2 yr1_t,  0 yr2_p, 0 yr2_t, 30 yr3_p,  6 yr3_t from dual;

下面的静态查询将把列名转换为您想要的格式：

select user_name, yr1_p "2000_P", yr1_t "2000_T", yr2_p "2001_P", yr2_t "2001_T", yr3_p "2002_P", yr3_t "2002_T"
from myTable;

您可以使用以下查询生成上述查询：

select
'select user_name, ' ||
listagg('yr'||level||'_p "'||(start_year + level - 1)||'_P", yr'||level||'_t "'||(start_year + level - 1)||'_T"', ', ') within group (order by start_year) || 
' from myTable' v_sql
from
(
select 2000 start_year, 3 number_of_years from dual
)
connect by level <= number_of_years;

下一个查询将所有内容放在一起。安装Method4后，DYNAMIC_QUERY函数可以运行另一个查询生成的查询，生成所需的结果和列名。您只需要更改值2000和3即可调整结果。

select * from table(method4.dynamic_query(q'[
select
'select user_name, ' ||
listagg('yr'||level||'_p "'||(start_year + level - 1)||'_P", yr'||level||'_t "'||(start_year + level - 1)||'_T"', ', ') within group (order by start_year) || 
' from myTable' v_sql
from
(
--Only change the values in here:
select 2000 start_year, 3 number_of_years from dual
)
connect by level <= number_of_years
]'));

您还需要将myTable替换为生成中间结果的查询。这并不是你想要的——尽管用户只需要更改两个数字，但他们必须复制并粘贴一个巨大的查询。

如果您不能忍受复制和粘贴，仍然有一种方法可以创建一个只需要一行SQL语句的解决方案。对于该解决方案，您需要创建自己的类型并将其添加到包规范中。大部分工作只是复制文件method4_dynamic_ot.tpb和method4_dynamic_ot.tps，更改名称和参数，修改函数re_evaluate_statement，然后将新类型添加到包规范中。我可以提供帮助，但在使用高级解决方案之前，您应该先检查DYAMIC_QUERY方法是否有效。