To create a function that returns a table in Oracle you need to follow a few steps:
- Create a type that corresponds to a single record
- Create a type that corresponds to a table and that has the previously declared type as recordtype
- Create the function
Here is an example on how to do it:
- First I create a simple table that will be used for the sample. -- Create a dummy table to show how it works.
-- The table contains a hierarchy between boss
-- and employee.
CREATE TABLE employee
-- Insert dummy data:
INSERT INTO employee VALUES (1, 'Geert Verhoeven', null);
INSERT INTO employee VALUES (2, 'Ford Parker', null);
INSERT INTO employee VALUES (3, 'Al Bino', 1);
INSERT INTO employee VALUES (4, 'April May', 1);
INSERT INTO employee VALUES (5, 'Abbie Birthday', 3);
INSERT INTO employee VALUES (6, 'Gene E. Yuss', 3);
INSERT INTO employee VALUES (7, 'Juana Bea', 3);
INSERT INTO employee VALUES (8, 'Willie Maykit', 4);
INSERT INTO employee VALUES (9, 'Zoltan Pepper', 4);
INSERT INTO employee VALUES (10, 'Scott Shawn DeRocks', 4);
- Then I create the types needed to be able to return a table-- Create a type that corresponds to a row that you want to have returned.
CREATE TYPE ind_employee AS OBJECT (emp_id NUMBER(10), full_name VARCHAR2(50));
Create a type that corresponds to a table of rows with the specified type.
CREATE TYPE ind_employee_table AS TABLE OF ind_employee;
- After this is done, you can create a function that returns a table.-- Create the function.
CREATE OR REPLACE FUNCTION FN_GET_EMPLOYEE_DESCENDANTS (
pin_emp_id IN NUMBER
) RETURN ind_employee_table PIPELINED
TYPE t_ref_cursor IS REF CURSOR;
lr_out_rec ind_employee := ind_employee(NULL, NULL);
OPEN lc_employees FOR
-- The connect by prior handles the hierarchy.
START WITH emp_id = pin_emp_id
CONNECT BY PRIOR emp_id = emp_boss_id;
EXIT WHEN lc_employees%NOTFOUND;
REMARKS: The PIPELINED command and PIPE ROW() method, work together to make sure that each row is returned to the caller immediately after it is processed. This helps to avoid having big objects in memory.
- To use the function, you need to use the TABLE() function.-- Sample query:
SELECT * FROM TABLE(FN_GET_EMPLOYEE_DESCENDANTS(1));
- This gives the following results:EMP_ID FULL_NAME
1 Geert Verhoeven
3 Al Bino
5 Abbie Birthday
6 Gene E. Yuss
7 Juana Bea
4 April May
8 Willie Maykit
9 Zoltan Pepper
10 Scott Shawn DeRocks