I have come across the table functions in Oracle during the initial years of my career but rarely spared time to delve deeper into it until very recently when Oracle Introduced the Polymorphic Table Functions. This Post doesn't although talks about the new feature but it's just for a brush-up of Table functions.
Table function as we all know is a function that returns a collection. They can be used like database tables in the FROM clause of a query or in the SELECT list of a query as a column name. But it can also be used in ETL operations. Lets first check out a simple table function through a use case.
Revisit Table Functions.
CREATE OR REPLACE TYPE emp_det_typ IS OBJECT
(
employee_id number,
First_name varchar2(100),
Salary number
);
CREATE OR REPLACE TYPE emp_tb IS TABLE OF emp_det_typ;
create or replace function EMPLOYEES_row(
EMPLOYEE_ID_in IN EMPLOYEES.EMPLOYEE_ID%type)
return emp_tb
is
t_tab emp_tb;
Begin
SELECT emp_det_typ(EMPLOYEE_ID, FIRST_NAME, SALARY)
BULK COLLECT INTO t_tab
FROM EMPLOYEES
where EMPLOYEE_ID=EMPLOYEE_ID_in;
return t_tab;
end;
select * from table(EMPLOYEES_row(100));
-----------------------------------------
EMPLOYEE_ID FIRST_NAME SALARY
100 Steven 24000
Pipelining Table Functions:
performance of table functions can be improved by the implementation of pipelining. Pipelining allows rows to be passed out of table functions as they are produced, rather than waiting for whole collections to be produced before the results are returned. The outcome is a reduction in the time taken for the first rows to be produced and a reduction in the total amount of memory consumed by the table function.
Table functions can accept input parameters including collection types and REF CURSORS. Accepting these parameters allows them to be chained together to perform complex transformation pipelines or streams. These transformation pipelines can be used as a replacement for traditional Extraction Transformation Load (ETL) processes, removing the need for intermediate staging areas.
Let's take an example of pipeline table function using sys_refcursor as an i/p
CREATE OR REPLACE PACKAGE pkg_test1 AUTHID DEFINER IS
TYPE refcur_t1 IS REF CURSOR RETURN employees%ROWTYPE;
TYPE refcur_t2 IS REF CURSOR RETURN departments%ROWTYPE;
TYPE refcur_t3 IS REF CURSOR RETURN locations%ROWTYPE;
TYPE outrec_typ IS RECORD (
var_num NUMBER(6),
var_char1 VARCHAR2(100),
var_char2 VARCHAR2(100)
);
TYPE outrecset IS TABLE OF outrec_typ;
FUNCTION g_trans (p1 refcur_t1, p2 refcur_t2, p3 refcur_t3) RETURN outrecset PIPELINED;
END pkg_test1;
/
CREATE OR REPLACE PACKAGE BODY pkg_test1 IS
FUNCTION g_trans(p1 refcur_t1, p2 refcur_t2, p3 refcur_t3) RETURN outrecset
PIPELINED IS
out_rec outrec_typ;
in_rec1 p1%ROWTYPE;
in_rec2 p2%ROWTYPE;
in_rec3 p3%ROWTYPE;
BEGIN
LOOP
FETCH p2
INTO in_rec2;
EXIT WHEN p2%NOTFOUND;
END LOOP;
CLOSE p2;
LOOP
FETCH p3
INTO in_rec3;
EXIT WHEN p3%NOTFOUND;
END LOOP;
CLOSE p3;
LOOP
FETCH p1
INTO in_rec1;
EXIT WHEN p1%NOTFOUND;
-- first row
out_rec.var_num := in_rec1.employee_id;
out_rec.var_char1 := in_rec1.first_name;
out_rec.var_char2 := in_rec1.last_name;
PIPE ROW(out_rec);
-- second row
out_rec.var_num := in_rec2.department_id;
out_rec.var_char1 := in_rec2.department_name;
out_rec.var_char2 := TO_CHAR(in_rec2.location_id);
PIPE ROW(out_rec);
-- third row
out_rec.var_num := in_rec3.location_id;
out_rec.var_char1 := in_rec3.street_address;
out_rec.var_char2 := in_rec3.city;
PIPE ROW(out_rec);
END LOOP;
RETURN;
END g_trans;
END pkg_test1;
SELECT * FROM TABLE (
pkg_test1.g_trans (
CURSOR (SELECT * FROM employees where department_id =10),
CURSOR (SELECT * FROM departments where department_id =10),
CURSOR (SELECT * FROM LOCATIONS WHERE location_id = 1700)
)
);
--------------------------------------------------------------
VAR_NUM VAR_CHAR1 VAR_CHAR2
1 200 Jennifer Whalen
2 10 Administration 1700
3 1700 2004 Charade Rd Seattle
Table Functions as Transformation Function:
Using the cursor variable, the function fetches an input row. Using the PIPEROW statement, the function pipes the transformed row or rows to the invoker. If the FETCH and PIPEROW statements are inside a loop statement, the function can transform multiple input rows. In the case below the pipelined table function transforms each selected row of the Debit table to Two nested table rows(one with acct_no, name and amount while other with address), which it pipes to the select statement that invokes it. The actual parameter that corresponds to the formal cursor variable parameter is cursor expression.
CREATE TABLE DEBIT
(
Customer_Name varchar2(30),
Acct_Num number,
Amount number(10,2),
Date_Received date,
Address varchar2(100)
);
-------------------------------------------------
-- Create source table DEBIT
-------------------------------------------------
INSERT INTO DEBIT (CUSTOMER_NAME, ACCT_NUM, AMOUNT, DATE_RECEIVED, ADDRESS) VALUES ('Rahul Dravid', '89001', '780000', TO_DATE('06-DEC-10', 'DD-MON-RR'), 'Residence Add');
INSERT INTO DEBIT (CUSTOMER_NAME, ACCT_NUM, AMOUNT, DATE_RECEIVED, ADDRESS) VALUES ('Sourabh Ganguly', '75002', '903330', TO_DATE('10-DEC-12', 'DD-MON-RR'), 'Office Add');
INSERT INTO DEBIT (CUSTOMER_NAME, ACCT_NUM, AMOUNT, DATE_RECEIVED, ADDRESS) VALUES ('Sourabh Ganguly', '75001', '9000', TO_DATE('10-DEC-12', 'DD-MON-RR'), 'News paper');
INSERT INTO DEBIT (CUSTOMER_NAME, ACCT_NUM, AMOUNT, DATE_RECEIVED, ADDRESS) VALUES ('Sourabh Ganguly', '75002', '9030', TO_DATE('18-DEC-12', 'DD-MON-RR'), 'Office Add');
INSERT INTO DEBIT (CUSTOMER_NAME, ACCT_NUM, AMOUNT, DATE_RECEIVED, ADDRESS) VALUES ('Rahul Dravid', '89001', '502253', TO_DATE('17-DEC-12', 'DD-MON-RR'), 'London Address');
CREATE OR REPLACE PACKAGE ref_pkg AUTHID DEFINER IS
TYPE refcur_t IS REF CURSOR RETURN DEBIT%ROWTYPE;
TYPE outrec_typ IS RECORD (
var_num NUMBER(6),
var_char1 VARCHAR2(30),
var_char2 VARCHAR2(30)
);
TYPE outrecset IS TABLE OF outrec_typ;
FUNCTION f_trans (p refcur_t) RETURN outrecset PIPELINED;
END ref_pkg;
CREATE OR REPLACE PACKAGE BODY ref_pkg IS
FUNCTION f_trans (p refcur_t) RETURN outrecset PIPELINED IS
out_rec outrec_typ;
in_rec p%ROWTYPE;
BEGIN
LOOP
FETCH p INTO in_rec; -- input row
EXIT WHEN p%NOTFOUND;
out_rec.var_num := in_rec.ACCT_NUM;
out_rec.var_char1 := in_rec.CUSTOMER_NAME;
out_rec.var_char2 := in_rec.AMOUNT;
PIPE ROW(out_rec); -- first transformed output row
out_rec.var_char1 := in_rec.CUSTOMER_NAME;
out_rec.var_char2 := in_rec.ADDRESS;
PIPE ROW(out_rec); -- second transformed output row
END LOOP;
CLOSE p;
RETURN;
END f_trans;
END ref_pkg;
------------------------------------------------------------------
SELECT * FROM TABLE (
ref_pkg.f_trans (
CURSOR (SELECT * FROM DEBIT WHERE CUSTOMER_NAME = 'Rahul Dravid')
)
);
---------------------------------------------------------------------
VAR_NUM VAR_CHAR1 VAR_CHAR2
1 89001 Rahul Dravid 780000
2 89001 Rahul Dravid Residence Add
3 89001 Rahul Dravid 502253
4 89001 Rahul Dravid London Address
Stay Tuned for more on Table function - Streaming Pipelined function.
#PLSQL #ORACLE#ORACLE19c#TableFunction,#pipelinedFunction, #Oracle19c