Oracle 动态 SQL 列

Question

我正在编写一个存储过程，我需要根据所报告的数据填充一个表。

在这种情况下，我将每天为某个日期范围内的某个代码提取三个值。

假设在该存储过程的某个运行中，我有一个日期范围的代码值 X、Y 和 Z，如下所示：

select abc.code, 
       abc.date, 
       abc.val_1, 
       abc.val_2, 
       abc.val_3
  from data.abc
 where abc.date BETWEEN '01-OCT-2009' AND '31-OCT-2009'

因此，对于日期范围内的每一天，我有代码 x、y 和 z 的三个记录。

在我的最终表中，我需要将其从行转换为列。通常我会使用解码函数，但在这里我想根据返回的数据动态创建最终表。

在这种情况下，该范围内的每一天都会有一条记录，并且还有 9 列（val_1_X、val_2_x、val_3_x、val_1_y 等）。

我想动态地设置它，以便在引入新的“代码”时不需要重新打开我的存储过程，这样在报告的每个实例上，只有该实例上返回的“代码”该报告包含在决赛表中。

通过动态sql可以实现吗？我使用的是 Oracle 版本 10g。

Answer 1

据我了解，2009 年 10 月 1 日到 2009 年 10 月 31 日只有代码 x、y 和 z，而一直到 30 -NOV-2009 也可能会给你带来w。

最终，您想要一个像这样的查询：

CREATE TABLE t1 AS
  SELECT
    abc.date,
    MAX(DECODE(code, 'x', val_1, NULL)) AS abc_val_1_x,
    MAX(DECODE(code, 'x', val_2, NULL)) AS abc_val_2_x,
    MAX(DECODE(code, 'x', val_3, NULL)) AS abc_val_3_x,
    MAX(DECODE(code, 'y', val_1, NULL)) AS abc_val_1_y,
    MAX(DECODE(code, 'y', val_2, NULL)) AS abc_val_2_y,
    MAX(DECODE(code, 'y', val_3, NULL)) AS abc_val_3_y,
    ...
  FROM data.abc
  WHERE abc.date BETWEEN '01-OCT-2009' AND '31-OCT-2009'
  GROUP BY abc.date

我不是 Oracle 人员，也无权在 Oracle 上进行测试，因此您可能会发现很多语法错误等。您还需要将硬编码日期更改为变量，声明一些变量并实际运行创建的查询。

-- Figure out all valid codes for date range.
-- Might also require ordering depending on your report.
CURSOR c1
  IS
    SELECT
      abc.code
    FROM data.abc
    WHERE abc.date BETWEEN '01-OCT-2009' AND '31-OCT-2009'
    GROUP BY abc.code;

query1 := 'CREATE TABLE abc_report AS SELECT date';
LOOP
    FETCH c1 INTO code1
    EXIT WHEN c1%NOTFOUND;
    query1 := query1 || ', DECODE(code, ''' || code1 || ''', val_1, NULL)) AS abc_val_1_' || code1 ||
        ', DECODE(code, ''' || code1 || ''', val_2, NULL)) AS abc_val_2_' || code1 ||
        ', DECODE(code, ''' || code1 || ''', val_3, NULL)) AS abc_val_3_' || code1
END LOOP;
query1 := query1 || ' FROM data.abc WHERE date BETWEEN ''01-OCT-2009'' AND ''31-OCT-2009'''

Answer 2

非常感谢您的帮助，但事实证明我终究无法走这条路。存储的过程将由报告工具启动和使用，我需要为此报告的多个实例同时运行的可能性做好准备，所以我不能重新使用表名，我不会'不允许有一个存储过程来创建每个运行实例唯一的表。

不过再次感谢您的帮助！

Answer 3

对于多个实例，请使用全局临时表，并根据需要选择提交删除行或提交保留。无论选项如何，记录都仅适用于该会话。