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
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;