Pipelined Functions in Oracle

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 

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 

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

  SELECT *

    FROM TABLE (get_employees (20))

ORDER BY empno;

DROP TYPE g_emp_tab;

DROP TYPE g_emp_row;

DROP FUNCTION get_employees ;

Checkout

• Working With Nested Table Oracle