REF Cursors in Oracle with Examples

Introduction to REF CURSOR
A REF CURSOR is basically a data type.  A variable created based on such a data type is generally called a cursor variable.  A cursor variable can be associated with different queries at run-time.  The primary advantage of using cursor variables is their capability to pass result sets between sub programs (like stored procedures, functions, packages etc.).
Let us start with a small sub-program as follows:

Introduction to REF CURSOR

A REF CURSOR is basically a data type.  A variable created based on such a data type is generally called a cursor variable.  A cursor variable can be associated with different queries at run-time.  The primary advantage of using cursor variables is their capability to pass result sets between sub programs (like stored procedures, functions, packages etc.).
Let us start with a small sub-program as follows:
declare
  type r_cursor is REF CURSOR;
  c_emp r_cursor;
  en emp.ename%type;
begin
  open c_emp for select ename from emp;
  loop
      fetch c_emp into en;
      exit when c_emp%notfound;
      dbms_output.put_line(en);
  end loop;
  close c_emp;
end;
Let me explain step by step.  The following is the first statement you need to understand:
  type r_cursor is REF CURSOR;
The above statement simply defines a new data type called "r_cursor," which is of the type REF CURSOR.  We declare a cursor variable named "c_emp" based on the type "r_cursor" as follows:
  c_emp r_cursor;
Every cursor variable must be opened with an associated SELECT statement as follows:
  open c_emp for select ename from emp;
To retrieve each row of information from the cursor, I used a loop together with a FETCH statement as follows:
  loop
      fetch c_emp into en;
      exit when c_emp%notfound;
      dbms_output.put_line(en);
  end loop;
I finally closed the cursor using the following statement:
  close c_emp;
%ROWTYPE with REF CURSOR
In the previous section, I retrieved only one column (ename) of information using REF CURSOR.  Now I would like to retrieve more than one column (or entire row) of information using the same.  Let us consider the following example:
declare
  type r_cursor is REF CURSOR;
  c_emp r_cursor;
  er emp%rowtype;
begin
  open c_emp for select * from emp;
  loop
      fetch c_emp into er;
      exit when c_emp%notfound;
      dbms_output.put_line(er.ename || ' - ' || er.sal);
  end loop;
  close c_emp;
end;
In the above example, the only crucial declaration is the following:
  er emp%rowtype;
The above declares a variable named "er," which can hold an entire row from the "emp" table.  To retrieve the values (of each column) from that variable, we use the dot notation as follows:
      dbms_output.put_line(er.ename || ' - ' || er.sal);