About
ORACLE supports procedures (functions) which returns result sets. Thus, a stored procedure can act as a table or a view.
Articles Related
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.
hr@orcl>