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

If I use one of these two set operators to combine the cursors into one wouldn't the resulting operation be more expensive to execute given that both cursor plans have to be executed?

Also in the example I posted, if I have the SSN I don't have the identifier and vice-versa if I have the identifier I don'h have the SSN. I guess I could just supply null values for each of these when I do not have them, but I'm not sure about complicating the execution plan (if my understanding about combining the queries is correct) in order to simplify code and help manintenace.

Thanks

Martin Bertolino

rs_arwar_at_hotmail.com (Rauf Sarwar) wrote in message news:<92eeeff0.0208121447.2105a4c7_at_posting.google.com>...
> If your colums are identical then use a UNION ALL which will combine
> the resultset of both cursors without eliminating duplicate rows OR
> use UNION if only distinct rows are desired.
>
> > 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
> > 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(+)
>
> UNION ALL | UNION
>
> > select
> > t0.employee_id,
> > t0.payroll_master_record,
> > t0.benefits_master_record,
> > t1.max_as_of_date,
> > t2.data_source_user_id,
> > rownum
> > 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(+);
> >
> >
> > for it in v_employee_company_cursor loop
> > -- processing code (repeated many times for each cursor type)
> > end loop;
> >
>
> Now you would need only one cursor and one for loop. BTW..You can have
> more then one UNION's if you like to combine another identical cursor
> with different WHERE clause.
>
> HTH
> //Rauf Sarwar
Received on Tue Aug 13 2002 - 18:18:35 CEST