As mentioned earlier, a cursor references a private area in memory which holds information for processing a given SQL statement. An implicit cursor is used every time you execute a SQL statement within PL/SQL. PL/SQL also allows you to declare explicit cursors. Cursors can also be passed as parameters. This is a standard way that sets of data are retrieved from stored procedures.
create or replace procedure return_a_cursor ( p_cursor out sys_refcursor) is begin open p_cursor for select * from employees; end return_a_cursor; /
In this example, the cursor passed in is opened using a query against the employees table.
DECLARE c sys_refcursor; rec employees%rowtype; BEGIN RETURN_A_CURSOR(P_CURSOR => c); loop fetch c into rec; exit when c%notfound; dbms_output.put_line('Name: ' || rec.first_name || ' ' || rec.last_name); end loop; close c; END; /
This anonymous block handles the cursor returned by the procedure by populating a record and displaying the first and last name of each employee. The cursor could be populated in many different ways. There is no problem as long as the record used when fetching the cursor has corresponding columns.