Re: PL/SQL Help

In article <EPO35.63$oH.65_at_newsfeed.slurp.net>,   "Rich Mycroft" <rich.mycroft_at_synchrologic.com> wrote:
> Just curious, but would there be a problem with opening a single
 select that
> uses your two predicates with an OR between them? If there is
something

performance would most likely suffer (definitely). By giving the optimizer exactly the question you are wanting to ask -- it has the best chance to come up with a good plan...

using the ref cursors as below is the way to allow the optimizer the best chance of getting optimal plans for each query (optimizers HATE or's ;)

> special having to be done on each row you could use an if statement
 based
> upon the salary value and behave slightly differently with each.
 This might
> work better as I suspect you're getting a full table scan in both
 cases. Not
> sure if this will work depending on the details of your app, but it
 might be
> worth a thought.
>
> Rich
>
> "Thomas J. Kyte" <tkyte_at_us.oracle.com> wrote in message
> news:8iob13$jcn$1_at_nnrp1.deja.com...
> > In article <8io9di$i2q$1_at_nnrp1.deja.com>,
> > Ethan Post <epost1_at_my-deja.com> wrote:
> > > Someone please provide a somewhat more elegant solution to the
 problem
> > > in the following sample code. The problem lies in the fact that
 the
> > > exact same work needs to be done on both cursors and the line "Do
 Some
> > > Work" is duplicated in the code. I would like to re-use the loop
 and
> > > somehow pass the cursor into the procedure. I played around with
 REF
> > > CURSOR and such but haven't really found what I'm looking for.
 For
 the
> > > moment I'm just using 1 cursor and using a union statement to tie
 the
> > > SQL together but that is not very elegant.
> > >
> > > PROCEDURE Test IS
> > >
> > > c_cursorA IS SELECT name from emp where sal > 30000;
> > >
> > > c_cursorB IS SELECT name from emp where sal < 30000
> > > and start_dt = '01-Jan-00'
> > >
> > > BEGIN
> > >
> > > For lv_rec in c_cursorA LOOP
> > > Do Some Work;
> > > END LOOP;
> > >
> > > For lv_rec in c_cursorB LOOP
> > > Do Some Work;
> > > END LOOP;
> > >
> > > END Test;
> > >
> > > --
> > > Thanks! - Ethan
> > > http://www.freetechnicaltraining.com
> > > http://www.gnumetrics.com
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> > >
> > ref cursors are the answer. Here are 2 examples. one is a
 procedure
> > that figures out which query it wants to run and then does it. the
> > other is a procedure that lets someone pass a cursor they opened to
 it
> > which it will then process...
> >
> > ops$tkyte_at_8i> create or replace procedure foo
> > 2 as
> > 3 type rc is ref cursor;
> > 4
> > 5 l_cursor rc;
> > 6 l_rec dept%rowtype;
> > 7 begin
> > 8
> > 9 if ( mod(to_char(sysdate,'SSSSS'),2) = 0 )
> > 10 then
> > 11 dbms_output.put_line( 'where rownum < 3' );
> > 12 open l_cursor for
> > 13 select * from dept where rownum < 3;
> > 14 else
> > 15 dbms_output.put_line( 'where deptno > 20' );
> > 16 open l_cursor for
> > 17 select * from dept where deptno > 20;
> > 18 end if;
> > 19
> > 20 loop
> > 21 fetch l_cursor into l_rec;
> > 22 exit when l_cursor%notfound;
> > 23 dbms_output.put_line( l_rec.deptno );
> > 24 end loop;
> > 25
> > 26 close l_cursor;
> > 27 end;
> > 28 /
> >
> > Procedure created.
> >
> > ops$tkyte_at_8i>
> > ops$tkyte_at_8i>
> > ops$tkyte_at_8i> exec foo
> > where deptno > 20
> > 30
> > 40
> >
> > PL/SQL procedure successfully completed.
> >
> >
> > Here is the second example:
> >
> > ops$tkyte_at_8i>
> > ops$tkyte_at_8i>
> > ops$tkyte_at_8i> create or replace package my_pkg
> > 2 as
> > 3 type rc is ref cursor;
> > 4
> > 5 procedure foo( l_cursor in out rc );
> > 6 end;
> > 7 /
> >
> > Package created.
> >
> > ops$tkyte_at_8i>
> > ops$tkyte_at_8i> create or replace package body my_pkg
> > 2 as
> > 3
> > 4
> > 5 procedure foo( l_cursor in out rc )
> > 6 as
> > 7 l_rec dept%rowtype;
> > 8 begin
> > 9
> > 10 loop
> > 11 fetch l_cursor into l_rec;
> > 12 exit when l_cursor%notfound;
> > 13 dbms_output.put_line( l_rec.deptno );
> > 14 end loop;
> > 15
> > 16 close l_cursor;
> > 17 end;
> > 18
> > 19 end;
> > 20 /
> >
> > Package body created.
> >
> > ops$tkyte_at_8i>
> > ops$tkyte_at_8i>
> > ops$tkyte_at_8i> declare
> > 2 my_cursor my_pkg.rc;
> > 3 begin
> > 4 open my_cursor for select * from dept where dname
 like '%
> > R%';
> > 5 my_pkg.foo(my_cursor);
> > 6 end;
> > 7 /
> > 20
> > 40
> >
> > PL/SQL procedure successfully completed.
> > --
> > Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
> > Howtos and such: http://osi.oracle.com/~tkyte/index.html
> > Oracle Magazine: http://www.oracle.com/oramag
> > Opinions are mine and do not necessarily reflect those of Oracle
 Corp
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
>

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.