Best way to encapsulate complex Oracle PL/SQL cursor logic as a view?

I’ve written PL/SQL code to denormalize a table into a much-easer-to-query form. The code uses a temporary table to do some of its work, merging some rows from the original table together.

The logic is written as a pipelined table function, following the pattern from the linked article. The table function uses a PRAGMA AUTONOMOUS_TRANSACTION declaration to permit the temporary table manipulation, and also accepts a cursor input parameter to restrict the denormalization to certain ID values.

I then created a view to query the table function, passing in all possible ID values as a cursor (other uses of the function will be more restrictive).

My question: is this all really necessary? Have I completely missed a much more simple way of accomplishing the same thing?

Every time I touch PL/SQL I get the impression that I’m typing way too much.

Update: I’ll add a sketch of the table I’m dealing with to give everyone an idea of the denormalization that I’m talking about. The table stores a history of employee jobs, each with an activation row, and (possibly) a termination row. It’s possible for an employee to have multiple simultaneous jobs, as well as the same job over and over again in non-contiguous date ranges. For example:

| 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 |

Querying that to figure out who is working when in what job is non-trivial. So, my denormalization function populates the temporary table with just the date ranges for each job, for any EMP_IDs passed in though the cursor. Passing in EMP_IDs 1 and 2 would produce the following:

| 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 allows NULLs for jobs that don’t have a predetermined termination date.)

As you can imagine, this denormalized form is much, much easier to query, but creating it–so far as I can tell–requires a temporary table to store the intermediate results (e.g., job records for which the activation row has been found, but not the termination…yet). Using the pipelined table function to populate the temporary table and then return its rows is the only way I’ve figured out how to do it.

Answer

I think a way to approach this is to use analytic functions…

I set up your test case using:

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

commit;

I’ve used the lead function to get the next date and then wrapped it all as a sub-query just to get the “A” records and add the end date if there is one.

select
    emp_id,
    job_id,
    eff_date start_date,
    decode(next_status,'T',next_eff_date,null) end_date
from
    (
    select
        emp_id,
        job_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,1,null) over (partition by emp_id, job_id order by eff_date, status) next_status
    from
        employee_job
    )
where
    status = 'A'
order by
    start_date,
    emp_id,
    job_id

I’m sure there’s some use cases I’ve missed but you get the idea. Analytic functions are your friend 🙂

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