将复杂的Oracle PL / SQL游标逻辑封装为视图的最佳方法是什么？

我编写了PL / SQL代码来将表反规范化为更易于查询的表单.代码使用临时表来完成一些工作,将原始表中的一些行合并在一起.

根据链接文章的模式,逻辑被写为pipelined table function. table函数使用PRAGMA AUTONOMOUS_TRANSACTION声明来允许临时表操作,并且还接受游标输入参数以将非规范化限制为某些ID值.

然后我创建了一个查询表函数的视图,将所有可能的ID值作为游标传递(该函数的其他用途将更具限制性).

我的问题：这一切真的有必要吗？我完全错过了一个更简单的方法来完成同样的事情吗？

每当我触摸PL / SQL时,我都会觉得我的打字方式太多了.

更新：我将添加我正在处理的表的草图,让每个人都知道我正在谈论的非规范化.该表存储员工作业的历史记录,每个作业都有一个激活行,并且(可能)有一个终止行.员工可以同时拥有多个工作岗位,以及在非连续日期范围内一次又一次地完成相同的工作.例如：

| EMP_ID | JOB_ID | STATUS | EFF_DATE    | other columns...
|      1 |     10 | A      | 10-JAN-2008 |
|      2 |     11 | A      | 13-JAN-2008 |
|      1 |     12 | A      | 20-JAN-2008 |
|      2 |     11 | T      | 01-FEB-2008 |
|      1 |     10 | T      | 02-FEB-2008 |
|      2 |     11 | A      | 20-FEB-2008 |

询问这一点,以确定谁在工作什么工作是非平凡的.因此,对于通过游标传递的任何EMP_ID,我的非规范化函数仅使用每个作业的日期范围填充临时表.传入EMP_ID 1和2会产生以下结果：

| EMP_ID | JOB_ID | START_DATE  | END_DATE    |
|      1 |     10 | 10-JAN-2008 | 02-FEB-2008 |
|      2 |     11 | 13-JAN-2008 | 01-FEB-2008 |
|      1 |     12 | 20-JAN-2008 |             |
|      2 |     11 | 20-FEB-2008 |             |

(END_DATE允许没有预定终止日期的作业使用NULL.)

可以想象,这种非规范化形式更容易查询,但创建它 – 据我所知 – 需要一个临时表来存储中间结果(例如,激活行已经存在的作业记录)发现,但不是终止……使用流水线表函数来填充临时表然后返回它的行是我弄清楚如何做到这一点的唯一方法.

解决方法

我认为解决这个问题的方法是使用分析函数……

我使用以下方法设置您的测试用例：

create table employee_job (
    emp_id integer,job_id integer,status varchar2(1 char),eff_date date
    );  

insert into employee_job values (1,10,'A',to_date('10-JAN-2008','DD-MON-YYYY'));
insert into employee_job values (2,11,to_date('13-JAN-2008','DD-MON-YYYY'));
insert into employee_job values (1,12,to_date('20-JAN-2008','T',to_date('01-FEB-2008',to_date('02-FEB-2008',to_date('20-FEB-2008','DD-MON-YYYY'));

commit;

我已经使用了lead函数来获取下一个日期,然后将它作为子查询包装起来只是为了得到“A”记录并添加结束日期(如果有的话).

select
    emp_id,job_id,eff_date start_date,decode(next_status,next_eff_date,null) end_date
from
    (
    select
        emp_id,eff_date,status,lead(eff_date,1,null) over (partition by emp_id,job_id order by eff_date,status) next_eff_date,lead(status,status) next_status
    from
        employee_job
    )
where
    status = 'A'
order by
    start_date,emp_id,job_id

我确定有一些我错过的用例,但你明白了.分析功能是你的朋友:)

EMP_ID   JOB_ID     START_DATE     END_DATE            
  1        10       10-JAN-2008    02-FEB-2008         
  2        11       13-JAN-2008    01-FEB-2008         
  2        11       20-FEB-2008                              
  1        12       20-JAN-2008