Cursors Used as Parameters and Variables

Contact Us or call 1-877-932-8228
Cursors Used as Parameters and Variables

Cursors Used as Parameters and Variables

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.

Code Sample:

Stored-Procedures-and-Functions/Demos/out_param_cursor.sql
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;
/

Code Explanation

In this example, the cursor passed in is opened using a query against the employees table.

Code Sample:

Stored-Procedures-and-Functions/Demos/out_param_cursor_call.sql
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;
/

Code Explanation

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.

Next