A table function is a PL/SQL function which will behave like a row source when queried. So you can perform transformations to the data before it is returned in the result set.
To write a table function, first we need to define an object as the row structure:
|
1 2 3 4 5 |
CREATE OR REPLACE TYPE emp_table_row AS OBJECT( employee_id NUMBER(6), first_name VARCHAR2(20), last_name VARCHAR2(25)); |
Because our function will return a table, we’ll also define a type:
|
1 |
CREATE OR REPLACE TYPE emp_table AS TABLE OF emp_table_row; |
This table will be used to return the rows from the our function. Here’s the sample table function which will accept department id as parameter and return the employees in the chosen department:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CREATE OR REPLACE FUNCTION employees_list (dept_id NUMBER) RETURN emp_table IS result emp_table := NEW emp_table(); BEGIN FOR r IN (SELECT employee_id, first_name, last_name FROM employees WHERE department_id = dept_id) LOOP result.EXTEND; result(result.COUNT) := NEW emp_table_row( r.employee_id, r.first_name, r.last_name); END LOOP; RETURN result; END; |
Let’s call this function:
|
1 |
SELECT * FROM TABLE( employees_list( 50 )); |
Oracle provides PIPELINED option. This option tells Oracle to return the results of the function as soon as they are processed. So the table function requires less memory to produce even large result sets.
Here’s the pipelined version of the above function:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE OR REPLACE FUNCTION employees_list (dept_id NUMBER) RETURN emp_table PIPELINED IS result emp_table_row := NEW emp_table_row (NULL, NULL, NULL); BEGIN FOR r IN (SELECT employee_id, first_name, last_name FROM employees WHERE department_id = dept_id) LOOP result.employee_id := r.employee_id; result.first_name := r.first_name; result.last_name := r.last_name; PIPE ROW (result); END LOOP; RETURN; END; |
