Pipelined Functions in Oracle

April 12, 2020Posted inORACLE, PLSQL

How to create Pipelined Functions in Oracle?

 Pipeline functions are used to return data in tabular format. It returns the data in the nested table type. Pipeline functions are useful to dump data derived from the complex queries.

  • Create the types
Copy Code
CREATE TYPE g_emp_row AS OBJECT
(
   empno NUMBER (4),
   ename VARCHAR2 (10 BYTE),
   job VARCHAR2 (9 BYTE),
   mgr NUMBER (4),
   hiredate DATE,
   sal NUMBER (7, 2),
   comm NUMBER (7, 2),
   deptno NUMBER (2)
);
/
CREATE TYPE g_emp_tab IS TABLE OF g_emp_row;
/
  • Pipeline function
Copy Code
CREATE OR REPLACE FUNCTION get_employees (pi_deptno IN NUMBER)
   RETURN g_emp_tab
   PIPELINED
AS

   CURSOR c_get_emp (p_deptno NUMBER)
   IS
      SELECT empno,
                        ename,
                        job,
                        mgr,
                        hiredate,
                        sal,
                        comm,
                        deptno
        FROM emp em
       WHERE deptno = p_deptno;
BEGIN
   FOR l_cnt IN c_get_emp (pi_deptno)
   LOOP
      PIPE ROW (g_emp_row (l_cnt.empno,
                           l_cnt.ename,
                           l_cnt.job,
                           l_cnt.mgr,
                           l_cnt.hiredate,
                           l_cnt.sal,
                           l_cnt.comm,
                           l_cnt.deptno));
   END LOOP;

   RETURN ;
END;
/

  • Query Example
Copy Code
  SELECT *
    FROM TABLE (get_employees (20))
ORDER BY empno;

Copy Code
DROP TYPE g_emp_tab;
DROP TYPE g_emp_row;
DROP FUNCTION get_employees ;
Tags: