Search

TABLE FUNCTIONS

Updated: Sep 6


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





17 views
  • Facebook
  • Twitter
  • LinkedIn