Re: PL/SQL Parameter Problem

From: John Beresniewicz <jberesniewicz_at_comcast.net>
Date: Wed, 14 Aug 2002 03:34:20 GMT
Message-ID: <0dk69.208377$vg.9074438@bin2.nnrp.aus1.giganews.com>

Frank --

You probably want to declare the cursor publicly and then anchor references to it using %ROWTYPE:

PACKAGE pkg1
IS

   CURSOR emp_cur
   IS select a.emp_no, b.deptno, max(a.salary) maxi from emp; END pkg1;

PACKAGE BODY pkg2
IS

/* forward declarations */
PROCEDURE update_this_stuff(emp_rec pkg1.emp_cur%ROWTYPE); PROCEDURE do_something_else(emp_rec pkg1.emp_cur%ROWTYPE);

PROCEDURE loop_and_upate
IS
BEGIN
   FOR emp_rec IN pkg1.emp_cur LOOP

      update_this_stuff(emp_rec);
      do_something_else(emp_rec);

   END LOOP;
END loop_and_update;

END pkg2;

Of course the cursor can be declared locally in pkg2, just illustrating that it is not necessary so as long as the cursor is declared in the other package's specification (i.e. is publicly visible).

Also it is good to use naming conventions to make it relatively obvious what types the variables have. So in my convention cursors all have names ending in "_cur" and records "_rec" and so an emp_rec declared as the rowtype of and emp_cur can take as input any records fetched from emp_cur.

HTH. JB

"Frank Zaum" <frankzaum_at_yahoo.de> wrote in message news:4c1e5bbe.0208120436.92a43e6_at_posting.google.com...
> Dear Experts, (8.1.7.3 on Linux Intel)
>
> I read in Bill Pribyls PLSQL book that a function/procedure can accept
> a complete record from a cursor as a parameter.
> This sounds very comfortable, but the examples do not explain of what
> type the parameter should be declared, and i can't figure it out.
>
> i want to do something like this (these functions/procedures are in
> the same package) :
>
> for v_cur in (select a.emp_no, b.deptno, max(a.salary) maxi from emp a
> ,dept b)
> loop
> update_this_stuff(v_cur);
>
> do_something_else(v_cur);
>
> insert_log(v_cur);
>
> delete_everything(v_cur);
>
> end loop;
>
> procedure update_this_stuff(v_cur in out ???? ) is --which type
> is right?
> begin
>
> v_cur.maxi = v_cur.maxi +1000;
>
>
> end;
>
> I know that its not %type , but %rowtype does not seem to fit, too
> because it references just a single element of a record (v_cur).
>
> Any help would be appreciated.
>
> Thanks in advance,
>
> Frank Zaum, Salzgitter
Received on Tue Aug 13 2002 - 22:34:20 CDT