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

can you use ref cursors and user-defined record types?

SQL>
SQL> create or replace package body stuff as   2 procedure opencursor(p_whereclause IN varchar2, p_xy IN number) is
  3 myrec myrectype;
  4 mycur refcur;
  5 begin
  6 open mycur for base_query || 'x = :p' using p_xy;   7 loop
  8 fetch mycur into myrec;
  9 exit when mycur%notfound;
 10 dbms_output.put_line('x = ' || myrec.x || '; y = ' || myrec.y);  11 end loop;
 12 close mycur;
 13 end opencursor;
 14 procedure testit is
 15 begin
 16 opencursor('x = ', 1);
 17 opencursor('y = ', 2);
 18 end testit;
 19 end;
 20 /

Package body created.

SQL> exec stuff.testit;
x = 1; y = 10
x = 2; y = 20

PL/SQL procedure successfully completed.

looks fairly re-factored to me. the idea is to have a generic procedure ("opencursor") that takes parameters and opens, fetches, and closes a dynamic cursor string according to the parameters. it makes use of a general purpose record type ("myrectype") that you can define. is this the kind of thing you were looking for?

martinbertolino_at_yahoo.com (Martin Bertolino) wrote in message news:<95be4880.0208130808.6d3f59a0_at_posting.google.com>...
> 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 Wed Aug 14 2002 - 01:26:17 CEST