Declaring REF CURSOR Types and Cursor Variables

To create cursor variables, you define a REF CURSOR type, then declare cursor variables of that type. You can define REF CURSOR types in any PL/SQL block, subprogram, or package. In the following example, you declare a REF CURSOR type that represents a result set from the DEPARTMENTS table:

DECLARE

TYPE DeptCurTyp IS REF CURSOR RETURN departments%ROWTYPE;

REF CURSOR types can be strong (with a return type) or weak (with no return type). Strong REF CURSOR types are less error prone because the PL/SQL compiler lets you associate a strongly typed cursor variable only with queries that return the right set of columns. Weak REF CURSOR types are more flexible because the compiler lets you associate a weakly typed cursor variable with any query. Because there is no type checking with a weak REF CURSOR, all such types are interchangeable. Instead of creating a new type, you can use the predefined type SYS_REFCURSOR.

Once you define a REF CURSOR type, you can declare cursor variables of that type in any PL/SQL block or subprogram.

----------------------------------------------------------------

An example:

create or replace procedure cal(tb varchar2) is

id pls_integer;

total pls_integer := 0;

type emp_cur is ref cursor;

cur emp_cur;

begin

open cur for 'select employee_id from ' || tb;

loop

fetch cur into id;

exit when cur%notfound;

total := total + id;

end loop;

close cur;

dbms_output.put_line(total) ;

end ;