Oracle Database - PL/SQL - Result Sets from Stored Procedures

About

ORACLE supports procedures (functions) which returns result sets. Thus, a stored procedure can act as a table or a view.

Example

Here is an example on the HR schema with a cursor variable:

-- Declaration of the cursor in a package
create or replace package types
as
    -- Cursor strongly bond
    type SrongCursorType is ref cursor RETURN employees%ROWTYPE;
    -- Cursor weakly bound
    type cursorType is ref cursor;
end;
/
-- Function which return a cursor
create or replace function sp_ListEmp return types.cursortype
as
    l_cursor    types.cursorType;
begin
    open l_cursor for select first_name, employee_id from employees order by first_name;
    return l_cursor;
end;
/
-- Procedure which return a cursor
create or replace procedure getemps( p_cursor in out types.cursorType )
as
begin
      open p_cursor for select first_name, employee_id from employees order by first_name;
end;
/

Executing an Oracle stored procedure in SQL Plus

SET SERVEROUTPUT ON
declare
p_cursor types.cursorType;
v_first_name employees.first_name%TYPE;
v_employee_id employees.employee_id%TYPE; 
begin
getemps(p_cursor);
if not p_cursor%isopen then
dbms_output.put_line('the cursor is not open');
else
dbms_output.put_line('the cursor is open');
end if;
fetch p_cursor into v_first_name, v_employee_id;
while p_cursor%found loop
dbms_output.put_line(v_first_name);
dbms_output.put_line(v_employee_id);
fetch p_cursor into v_first_name, v_employee_id;
end loop;
end;
/
...........
...........
166
Sundita
173
Susan
203
TJ
132
Tayler
170
Timothy
190
Trenna
141
Valli
106
Vance
195
William
171
William
206
Winston
180

PL/SQL procedure successfully completed.

[email protected]>

Powered by ComboStrap