Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: How to pass a record to a Procedure

Re: How to pass a record to a Procedure

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Wed, 17 Nov 2004 22:15:34 -0800
Message-ID: <1100758450.77344@yasure>


Keith wrote:

> I have a very large program (2000+ lines) and I want to try to break
> it down into smaller pieces, make it more modular. I have a cursor
> with 80 fields. While in the cursor loop, I want to call a procedure
> and pass the whole cursor record. It will do some processing on the
> record and then return the record to the calling procedure so that the
> updated values can be used. How can I do this?
>
> ...Thanks
>
> For Example:
>
> CREATE OR REPLACE PROCEDURE proc1
> IS
> v_result NUMBER;
> CURSOR emp_cursor
> IS
> SELECT * FROM rmp_table;
>
> BEGIN
> FOR emp_rec IN emp_cursor LOOP
> IF emp_rec.termination_date > '01-jan-2004 THEN
> proc2(emp_rec, v_result);
> END IF;
> IF v_result > 0 THEN
> DBMS_OUTPUT.PUT_LINE(emp_rec.emp_no || ',' || emp_rec.last_name
> ...
> END IF;
> NEXT;
> END;
> /
>
> PROCEDURE proc2(emp_rec IN OUT SomeTpyeOfRec, v_result OUT NUMBER)
> IS
> BEGIN
> IF emp_rec.pension_type = 'DC' THEN
> emp_rec.pension_type := 'Defined Contribution';
> v_result := v_result + 1;
> END IF;
> IF emp_rec.ins_type = '1x' THEN
> emp_rec.ins_type := 'One Times Salary';
> v_result := v_result + 1;
> END IF;
> etc...
> END;
> /

Turn it into a package.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Thu Nov 18 2004 - 00:15:34 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US