Cursor and Ref Cursor

A cursor is a name for a structure in memory called a private SQL area, which the server allocates at run time for each SQL statement. When ever an SQL statement is executed, a Oracle database server automatically allocates a cursor that will hold the context(info about the SQL statement like variables used, memory addesses of those variables, parsed version of SQL statement etc) of that SQL statement. This is known as implicit cursor.

Cursors can also be defined explicitly to handle more than 1 row in a program. They are declared, opened, fetched and closed in a PL/SQL program and can be used for static SQL queries.

e.g
 declare
emp_name varchar2(15);
CURSOR my_cur is select name from emp;
BEGIN
OPEN my_cur;
LOOP
FETCH my_cur INTO emp_name;
EXIT WHEN my_cur%NOTFOUND;
dbms_output.put_line('The employee name is: '||emp_name);
end loop;
CLOSE my_cur;
END;
/

REF CURSOR is used in case of dynamic SQL queries. REF CURSOR is not a cursor. Instead, its a data type that will,at runtime, hold a pointer to a place in memory where a cursor really lives. So basically, its a pointer to a cursor or a reference to a cursor. Its declared as follows:

DECLARE
TYPE refcur IS REF CURSOR;
my_ref refcur;
emp_name varchar2(15);
BEGIN
OPEN my_ref FOR 'select name from emp' ;
LOOP
FETCH my_ref INTO emp_name;
EXIT WHEN my_ref%NOTFOUND;
dbms_output.put_line('The employee name is: '||emp_name);
end loop;
CLOSE my_ref;
END;
/