REF Curosr

Ref Cursor is a PL/SQL Datatype. This is used to store result of a query.
Ref cursor is a dynamic cursor with which we can change our query contents at run time.
Ref cursor is used in situations when you want to encapsulate logic within sub-program i.e. your query is changing depending on certain criteria.

Using this ref cursor we can written more then one select statement.

There are 2 types in this.

If a ref cursor should not return any value then it is called as weak ref cursor and if a cursor returns the value then it is strong ref cursor.

Weak ref cursors are more flexible because you can associate a weakly typed cursor variable to any query.

Syntax:

Strong ref cursor Syntax:
             Type Typename is ref cursor return recordtypedatatype;
             Variablename typename;

Weak ref cursor Syntax:
          Type Typename is ref cusrcursor

            Varaiablename Typename;

Examples :
using %Type

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;

using %rowtype
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;

Uses:

1. More easily administer SQL
2. Avoid the use of lexical parameters in your reports
3. Share data sources with other applications, such as Form Builder
4. Increase control and security

5. Encapsulate logic within a subprogram