Note: You can find a simpler version of this in my answer on StackOverflow.
Returning SELECT statement results from procedures/functions?
Everyone is familiar with Oracle functions that return basic data types (simple values with no internal components). Although rarely used, Oracle functions are however capable of returning more complex data types like cursors, arrays and object tables.
How it is done in SQL Server:
SQL Server has had the capability to return the results of one or more select statements like this (since inception?)
--SQL Server procedure returning results from a SELECT CREATE PROCEDURE usp_Select_Clients AS BEGIN SELECT * FROM CLIENT_MASTER; END; --Run the procedure to get the results EXEC usp_Select_Clients
The results itself
CLIENT_CODE CLIENT_ID CLIENT_GROUP AFFILIATE_ID ------------ ---------- ----------------------------------------------------- ------------- 1 HJM Semi-Liberty Group US 1 2 LEK Full Liberty Group 2 3 LYN Boston Logger Co Group 3 4 KAT ABC Incorporated 4 5 ASK ASKncipal Life 5 6 EIK AllPeople 6 7 ADE America Benefit 7 8 DES DES Life Insurance Company 8 9 LIU Dominose Life - LIUcia Group (INACTIVE) 9 10 PIE PIEON USA (SCOR) 10 11 DOE DOE Group 11
How it is done in Oracle traditionally:
In Oracle if we were to do something similar, we would have simply returned a cursor that the caller would then use.
--Create a function that return the results of a SELECT CREATE OR REPLACE FUNCTION fnc_Get_Clients_Cursor RETURN SYS_REFCURSOR AS my_cursor SYS_REFCURSOR; BEGIN OPEN my_cursor FOR SELECT * FROM CLIENT_MASTER; RETURN my_cursor; END fnc_Get_Clients;
Consume the function above that returns a cursor
DECLARE v_Cur SYS_REFCURSOR; v_Rec CLIENT_MASTER%ROWTYPE; BEGIN v_cur := fnc_Get_Clients_Cursor(); LOOP FETCH v_Cur INTO v_Rec; EXIT WHEN v_Cur%NOTFOUND; -- Exit when there is no more data DBMS_OUTPUT.PUT_LINE('Row: '|| v_Rec.CLIENT_Id || ' ' || v_Rec.CLIENT_Group); END LOOP; CLOSE v_Cur; END; /
Row: HJM Semi-Liberty Group US Row: ASK ASKncipal Life Row: ADE America Benefit Row: DES DES Life Insurance Company Row: LIU Dominose Life - LIUcia Group (INACTIVE) Row: DOE DOE Group Row: ALZ Allianz Life Insurance Company of North America ….
How could it be done in Oracle better?
This is great but it is not as easy as doing something like this, if it were possible:
SELECT * FROM fnc_Get_Clients_Cursor();
Enter Oracle pipelined functions – They are designed to do exactly the above. Let us convert the above function that returns a cursor into a pipelined function:
Step 1: Create an object the represents one row of the results:
CREATE OR REPLACE TYPE CLIENT_type AS OBJECT ( CLIENT_id VARCHAR(6), CLIENT_group VARCHAR(60) );
Step 2: Create a collection (table type) of the object type created in step 1
CREATE OR REPLACE TYPE CLIENT_table_type AS TABLE OF CLIENT_type;
Step 3: Create a function that returns the table type from step 3
CREATE OR REPLACE FUNCTION fnc_Get_Clients_Pipelined RETURN CLIENT_table_type PIPELINED AS BEGIN FOR v_Rec IN (SELECT * FROM CLIENT_MASTER) LOOP PIPE ROW (CLIENT_type(v_Rec.CLIENT_Id, v_Rec.CLIENT_Group)); END LOOP; RETURN; END;
Step 4: Consume the function as if it was a “table” or “view”
SELECT CLIENT_id, CLIENT_group FROM TABLE(fnc_Get_Clients_Pipelined()); CLIENT_ID CLIENT_GROUP HJM Semi-Liberty Group US ASK ASKncipal Life ADE America Benefit DES DES Life Insurance Company
Real-world usage: Convert procedures returning REF cursors into Pipelined functions
In the real world, typically, legacy functionality will exist in the form of functions returning cursors after performing some complex processing. It would be easy to wrap those functions using pipelined functions to use them with the more natural SELECT syntax.
Here we wrap the function that returns a cursor into a function that is piplelined.
CREATE OR REPLACE FUNCTION fnc_Get_Clients_PLWrapCursor ( p_Cur IN SYS_REFCURSOR ) RETURN CLIENT_table_type PIPELINED AS v_Cur SYS_REFCURSOR; v_Rec CLIENT_MASTER%ROWTYPE; BEGIN v_cur := fnc_Get_Clients_Cursor(); LOOP FETCH v_Cur INTO v_Rec; EXIT WHEN v_Cur%NOTFOUND; -- Exit the loop when we've run out of data DBMS_OUTPUT.PUT_LINE('Row: '|| v_Rec.CLIENT_Id || ' ' || v_Rec.CLIENT_Group); PIPE ROW (CLIENT_type(v_Rec.CLIENT_Id, v_Rec.CLIENT_Group)); END LOOP; CLOSE v_Cur; RETURN; END fnc_Get_Clients_PLWrapCursor;
Now we are cursor-syntax free….using a function that returns a cursor!
SELECT * FROM TABLE(fnc_Get_Clients_PLWrapCursor(fnc_Get_Clients_Cursor));
This syntax is also easier to consume in .NET and Java.
Alternatively, we could have chosen to implement the function as one that returns the table type but that would make the .NET and Java consumption all the more difficult although it is not an issue to consume that within Oracle.