Re: PL/SQL Help

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/06/20
Message-ID: <8iob13$jcn$1@nnrp1.deja.com>#1/1

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