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

Question

I wrote PL / SQL code to denormalize a table in a form with more ease and query. The code uses a temporary table to do some of its work, combining several rows from the original table.

The logic is written as a pipeline function of a table , following the pattern from a related article. The table function uses the PRAGMA AUTONOMOUS_TRANSACTION to allow temporary manipulation of tables, and also takes a cursor input parameter to restrict denormalization to specific ID values.

Then I created a view to query the table function, passing all possible ID values ​​as a cursor (other usage functions will be more restrictive).

My question is: is all this really necessary? Am I completely missing the much easier way to accomplish the same thing?

Every time I touch PL / SQL, I get the impression that I type too much.

Update:. I will add a sketch of the table I'm dealing with to give everyone an idea of ​​the denormalization I'm talking about. The table stores the history of the employee’s work, each of which has an activation line and (possibly) a completion line. It is possible that an employee has several simultaneous tasks, as well as the same job again and again in endless date ranges. For instance:

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

A request to find out who works when what work is non-trivial. Thus, my denormalization function fills in the temporary table with only date ranges for each job, for any EMP_ID passed though the cursor. Passing in EMP_ID 1 and 2 will result in 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 NULL for jobs that do not have a specified end date.)

As you can imagine, this denormalized form is much easier to request, but its creation - as far as I can tell - requires a temporary table to store intermediate results (for example, work records for which an activation string was found, but not the end ... for now) . Using the pipeline table function to populate a temporary table and then return its rows is the only way I figured out how to do this.

Answer 1

I think the approach to this is to use analytic functions ...

I installed 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 used the lead function to get the next date, and then wrapped it as a subquery to get the “A” records and add the end date, if any.

 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 am sure that I missed some use cases, but you understood this idea. Analytical 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 

Answer 2

Instead of having the input parameter as a cursor, I would have a table variable (I don’t know if Oracle has such a thing, I’m a TSQL guy) or fill out another temporary table with identification values ​​and join it in the view / function or where you need it.

The only time cursors in my honest opinion is when you need loops. And when you need to go in cycles, I always recommend doing this outside the database in the application logic.

Answer 3

It looks like you are giving some reading sequence here, that is: it will be possible for the contents of your temporary table not to synchronize with the original data if you have a modification of the modification data at the same time.

Without knowing the requirements or the complexity of what you want to achieve. I would try

• to define a view that contains (possibly complex) logic in SQL, otherwise I will add PL / SQL to the mix;
• Pipeline table function, but using SQL collection type (instead of temporary table). A simple example: http://asktom.oracle.com/pls/asktom/f?p=100:11:59:::::P11_QUESTION_ID:4447489221109

Number 2 will provide you with less moving parts and solve the problem of consistency.

Matthew Butler

Answer 4

The real problem here is the “write-only” tabular design, which I mean, is easy to insert data into it, but is difficult and inefficient to extract useful information from it! Your "temporary" table has the structure that the "permanent" table should have.

Could you do this:

• Create a persistent table with a better structure
• Fill it in accordance with the data in the first table
• Define a database trigger in the source table to now synchronize the new table

Then you can simply select from a new table to run your reports.

Answer 5

I could not agree with you anymore, HollyStyles. I'm also used to being a TSQL guy, and finding some Oracle features is more than a little perplexing. Unfortunately, temporary tables in Oracle are not so convenient, in which case other existing SQL logic expects a direct query on the table, so I give it this view. On this system, there really is no application logic that exists outside the database.

Oracle developers seem to use cursors much more readily than I thought. Given the nature of slavery and the PL / SQL discipline, this is all the more surprising.

Answer 6

The simplest solution:

1. Create a global temporary table containing only the identifiers you need:

    CREATE GLOBAL TEMPORARY TABLE tab_ids (id INTEGER) ON COMMIT DELETE ROWS; 

2. Fill in the temporary table with the required identifiers.

3. Use the EXISTS operation in your procedure to select rows that are only in the identifier table:

     SELECT yt.col1, yt.col2 FROM your\_table yt WHERE EXISTS ( SELECT 'X' FROM tab_ids ti WHERE ti.id = yt.id ) 

You can also pass a comma-separated string of identifiers as a parameter to the function and parse it into a table. This is done by one SELECT. Want to know more - ask me how :-) But this should be a separate issue.