Re: Processing different cursor types with the same procedure/function

I agree with your response, but what would be the type of the cursor be if you are selecting from more than one table (join as in the example) or your are not selecting all the columns in those tables, just a few selected ones.

In your sample

cursor cur1 is select * from test2;

the type of the cursor is clearly test2%rowtype

but in a scenario like this:

select t0.c0, t0.c1, t1.c0, t2.c0 from table0 t0, table1 t1, table2 t2 where t0.c2 = '...' and t0.c3 = t1.c1 and t0.c3 = t2.c1

what would the cursor type be? Unles I'm missing something there is no %rowtype that represents the columns of the query.

In a nutshell what I want to do is factor out identical code from for loops that process rows from cursors that return identical set of columns but their where clauses are slightly different.

In my specific example I can fnd people by two methods depending on their employer. One is to look them up by SSN, the other method is to look them up by an alternate identifier that is defined by the employer. In both cases I want to retrieve the same data from these employees and process them in the same way, it is just how I find them that is different.

I hope this explains my problem better.

Thanks

Martin

jusungyang_at_yahoo.com (Jusung Yang) wrote in message news:<42ffa8fa.0208121549.36e164c3_at_posting.google.com>...
> I wish all the postings are as complete as yours. Still, I am not
> quite sure exactly what your concern is.
>
> > I looked at cursors with specified return type, but this can only be a
> > table type so I did not know how to do this with joins or with subsets
> > of all the columns.
>
> Can you elaborate on this?
>
> Isn't calling a generic procedure by passing in the record from the
> cursors the simplest way to go? What am I missing?
>
> SQL> declare
> 2
> 3 cursor cur1 is select * from test2;
> 4 procedure proc1(recin test2%rowtype) is
> 5 begin
> 6 dbms_output.put_line('c1 := '||recin.c1||' , c2 := '||recin.c2);
> 7 end;
> 8 begin
> 9 for x in cur1 loop
> 10 proc1(x);
> 11 end loop;
> 12 end;
> 13 /
> c1 := a , c2 := 1
> c1 := b , c2 := 2
> c1 := c , c2 := 3
> c1 := e , c2 := 1
> c1 := a , c2 := 4
> c1 := a , c2 := 5
> c1 := a , c2 := 6
> c1 := a , c2 := 7
> c1 := a , c2 := 6
> c1 := a , c2 := 7
>
> PL/SQL procedure successfully completed.
>
> SQL>
>
>
>
> martinbertolino_at_yahoo.com (Martin Bertolino) wrote in message news:<95be4880.0208120703.68a05c15_at_posting.google.com>...
> > I have some PL/SQL code that has a few cursors that are almost
> > identical - join same tables, return same set of columns - but differ
> > only in their where clauses. For example:
> >
> > -- find all the candidate master records here
> > cursor v_employee_company_cursor is
> > select
> > t0.employee_id,
> > t0.payroll_master_record,
> > t0.benefits_master_record,
> > t1.max_as_of_date,
> > t2.data_source_user_id,
> > rownum -- we use this to index into the candidate records
> > collection
> > from employee_company t0, work_time t1, data_source t2
> > where
> > t0.company_id = p_company_id and
> > t0.identifier = p_employee_id and
> > t0.work_time_list_id = t1.work_time_list_id and
> > t0.data_source_id = t2.data_source_id(+);
> >
> > and the other cursor is:
> >
> > -- find all the candidate master records here
> > cursor v_employee_company_cursor is
> > select
> > t0.employee_id,
> > t0.payroll_master_record,
> > t0.benefits_master_record,
> > t1.max_as_of_date,
> > t2.data_source_user_id,
> > rownum -- we use this to index into the candidate records
> > collection
> > from employee_company t0, work_time t1, data_source t2
> > where
> > t0.company_id = p_company_id and
> > t0.ssn = p_employee_id and
> > t0.work_time_list_id = t1.work_time_list_id and
> > t0.data_source_id = t2.data_source_id(+);
> >
> > Notice that the only change is the where clause on the sss/identifier
> > column.
> >
> > The rows returned by these cursors are then processed in a construct
> > such as:
> >
> > for it in v_employee_company_cursor loop
> > -- processing code (repeated many times for each cursor type)
> > end loop;
> >
> > The problem I have is that I have to have a loop for each of the
> > different cursors but the code within the loop block is identical.
> >
> > I would like to be able to factor out this code into a generic
> > function so I do not have repeated code and when I make changes or
> > fixes they are done in only one place.
> >
> > I looked at cursors with specified return type, but this can only be a
> > table type so I did not know how to do this with joins or with subsets
> > of all the columns. I could also create a procedure/function that
> > takes one argument for each column in the select but this seemed error
> > prone and difficult to maintain.
> >
> > Does anybody have any suggestions on how could this be accomplished?
> > This is just one case of this scenario, my application has other
> > sections that suffer from this code repetition that I would like to
> > avoid.
> >
> > Thanks in advance
> >
> > Martin Bertolino
> > 'martinbertolino'||'_at_'||'yahoo'||'.'||'com'
Received on Tue Aug 13 2002 - 18:08:40 CEST