PL/SQL: Best practice for fetching 2 or more joined tables from a cursor?

Question

I've heard that it's a good practice to define your records in PL/SQL by using the %ROWTYPE attribute. This saves typing and allows your package to continue functioning even when a column is added or deleted. (Correct me if I'm wrong!)

However, when I am fetching from a cursor that involves a join, I find that I have to fetch into a programmer-defined record that includes a (quite-possibly long) hand-written list of every column returned by the join.

So my question is: Is it possible to fetch into nested records, or fetch into a list of records, or do something to avoid such an ugly kludge? Everything I've tried leads to an error about the record not matching what's being returned by the cursor.

Returning the result of a join using a cursor seems like such a common use-case to me that it's strange that nothing related to this comes up in a search.

Thank you.

Answer 1

You can user cursor%rowtype.

Sample:

declare
cursor c_c is
select emp.*, dept.* -- use aliasses if columns have same name
from emp
,    dept; -- for sample no join condition

r_c c_c%rowtype; 

begin
  for r_c in c_c loop -- with for loop even the definition of r_c is not needed.
  ...
  end loop;
end;
/

Answer 2

Why even bother with the cursor declaration?

This is equivalent.

begin
  for r_c in (select emp.*, dept.* from emp, dept) loop
  ...
  end loop;
end;

I see in your comment you mention this. But I see the explicit cursor syntax used so much, i think it's important to show.