Re: PL/SQL Help

From: Rich Mycroft <rich.mycroft_at_synchrologic.com>
Date: 2000/06/20
Message-ID: <EPO35.63$oH.65@newsfeed.slurp.net>#1/1

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 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.
Received on Tue Jun 20 2000 - 00:00:00 CDT