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

> Database > Oracle Database

1 - About

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

3 - 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;
/
Advertising

4 - 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]>
db/oracle/stored_procedure_result_set.txt ยท Last modified: 2017/09/13 16:16 by gerardnico