Re: How to pass a record to a Procedure

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)