CODEWITHSUNDEEP
oracle-databaseplsql
Hoan Dang
Hoan Dang

Reputation: 2280

How to pass a complex cursor record into a procedure?

Let say I have declared a cursor like this

CURSOR cur_customer IS
    SELECT c.customer_id, c.name, o.order_date
    FROM customer c, order o
    WHERE c.customer_id = o.customer_id

BEGIN
    FOR rec_customer IN cur_Customer LOOP
        -- invoke procedure here with rec_customer as a parameter
    END LOOP;
END;

For the case where the cursor is fetched from only one table, the parameter could be declared like this

rec_customer customer%ROWTYPE

But in this case, the cursor is fetched from 2 tables. So how do I declare the parameter for this case ? Is it possbile ?

Any suggestions ? Thanks

Upvotes: 0

Views: 4458

Answers (1)

APC
APC

Reputation: 146349

If this is all done within the PL/SQL program you don't need to declare anything. Use implicit cursors and let PL/SQL figure it out:

declare
    tot_sal number(23,2) := 0;
begin
     for r in ( select dept.dept_no
                       , emp.sal
                from dept 
                     join emp (on emp.deptno = dept,deptno) )
     loop
         tot_sal := tot_sal + r.sal;
     end loop;
end;

I'm not recommending this as a good use of a CURSOR FOR loop, it's just as illustration of how little infrastructure is required to make things work.

If you want more structure you can just reference the cursor using the %ROWTYPE keyword like this:

CURSOR cur_customer IS
    SELECT c.customer_id, c.name, o.order_date
    FROM customer c, order o
    WHERE c.customer_id = o.customer_id;

rec_customer cur_customer%ROWTYPE;

That is, you can use your cursor to define the record variable just like you can use a table.

If you want to define a structure which can be shared between program units, especially packages, declare a RECORD type. Something like this:

TYPE emp_rec IS RE(ORD 
     (emp_dept_name dept.dname%type
      , emp_name emp.ename%type
      , emp_sal emp.sql%type );

You could use this to define various things, e.g. program unit parameters, wherever you would use a %ROWTYPE. Put such declarations in a package spec to share them across multiple packgaes.

FUNCTION get_emp (p_id emp.empno%type) return emp_rec%rowtype;

Find out more.

Upvotes: 2

Related Questions