Using a pipelined function with parameters in a view

Your ads will be inserted here by

Easy Plugin for AdSense.

Please go to the plugin admin page to
Paste your ad code OR
Suppress this ad slot.

In the Oracle database you can use pipelined functions to improve performance or decrease complexity. With the table function a pipelined functions can be called:


select * from table(pf_demo(10));

This way it is not possible to wrap the parameterised pipelined function in a view. You can only create a view for a specific parameter value:


create view pf_demo_view  as select * from table(pf_demo(10));

This is not very useful. Luckily there is a way to pass parameters to a pipelined function within a view. This solution uses package variables to store the parameter values and setter functions to set the values.
In our example we create the pipelined function in a package pf_demo_pkg. In the case of NULL-arguments the values of package variables are used. We create the view with a call to the pipelined function without parameters. The view can be queried with a call to the setter function(s) in the where clause. In the case of NULL-arguments the values of package variables are used. We create the view with a call to the pipelined function without parameters. The view can be queried with a call to the setter function(s) in the where clause. This way you can query the
view from PL/SQL.

Your ads will be inserted here by

Easy Plugin for AdSense.

Please go to the plugin admin page to
Paste your ad code OR
Suppress this ad slot.

create view pf_demo_view  as select * from table(pf_demo());

begin
  pf_demo_pkg.set_dept(10);
  for r in ( select * from pf_demo_view ) loop
    ...
  end loop;
end;

Now there is also a solution to use such a view from SQL. This solution utilizes the fact that the where clause of a query is interpreted before the from clause. So when we call the setter(s) in the where-clause the view will pass these parameters to the piplined function. You can see this in action in the code below:

SQL 
SQL create  type emp_rowtype
  2   as object
  3   ( empno number
  4   , ename varchar2(10)
  5   , hiredate date
  6   , deptno number
  7  );
  8  /

Type created.

SQL 
SQL create  type emp_table_type as table of emp_rowtype;
  2  /

Type created.

SQL 
SQL 
SQL create or replace package pf_demo is
  2  
  3    function set_deptno ( p_deptno in number) return number;
  4  
  5    function emp_pf (p_deptno in number default null)return emp_table_type  pipelined;
  6  
  7  end;
  8  /

Package created.

SQL 
SQL  create or replace
  2  package body pf_demo is
  3  
  4    g_deptno    number := null;
  5  
  6    function set_deptno ( p_deptno in number) return number is
  7    begin
  8    g_deptno := p_deptno;
  9         return(p_deptno);
 10    end;
 11  
 12    function emp_pf (p_deptno in number default null)return emp_table_type  pipelined is
 13    cursor c_emp (cp_deptno number) is
 14    select empno
 15         , ename
 16         , hiredate
 17         , deptno
 18    from emp
 19    where deptno = cp_deptno
 20   ;
 21   r_emp     c_emp%rowtype;
 22   r_rec     emp_rowtype;
 23   l_empno     number := null;
 24   l_ename     varchar2(10) := null;
 25   l_hiredate  date := null;
 26   l_deptno    number := null;
 27  begin
 28   l_deptno := nvl(p_deptno,g_deptno);
 29   open c_emp(l_deptno);
 30   loop
 31     fetch c_emp into  l_empno
 32          ,  l_ename
 33          ,  l_hiredate
 34          ,  l_deptno
 35     ;
 36     exit when c_emp%notfound;
 37     r_rec := new emp_rowtype
 38           ( l_empno
 39           , l_ename
 40           , l_hiredate
 41           , l_deptno
 42           );
 43     pipe row (r_rec);
 44   end loop;
 45   close c_emp;
 46   return;
 47   end;
 48  
 49  end pf_demo;
 50  /

Package body created.

SQL 
SQL 
SQL select * from table(pf_demo.emp_pf(10));

     EMPNO ENAME      HIREDATE      DEPTNO                                      
---------- ---------- --------- ----------                                      
      7782 CLARK      09-JUN-81         10                                      
      7839 KING       17-NOV-81         10                                      
      7934 MILLER     23-JAN-82         10                                      

SQL 
SQL select * from table(pf_demo.emp_pf()) where pf_demo.set_deptno(20) is not null;

     EMPNO ENAME      HIREDATE      DEPTNO                                      
---------- ---------- --------- ----------                                      
      7369 SMITH      17-DEC-80         20                                      
      7566 JONES      02-APR-81         20                                      
      7788 SCOTT      09-DEC-82         20                                      
      7876 ADAMS      12-JAN-83         20                                      
      7902 FORD       03-DEC-81         20                                      

SQL 
SQL create view pf_demo_view as select * from table(pf_demo.emp_pf());

View created.

SQL 
SQL select * from pf_demo_view where pf_demo.set_deptno(30) is not null;

     EMPNO ENAME      HIREDATE      DEPTNO                                      
---------- ---------- --------- ----------                                      
      7499 ALLEN      20-FEB-81         30                                      
      7521 WARD2      22-FEB-81         30                                      
      7654 MARTIN     28-SEP-81         30                                      
      7698 BLAKE      01-MAY-81         30                                      
      7844 TURNER     08-SEP-81         30                                      
      7900 JAMES      03-DEC-81         30                                      

6 rows selected.

SQL 
SQL spool off

Hope you can use this, happy coding

Dick

Source Article from http://dickdral.blogspot.com/2015/01/using-pipelined-function-with.html

2 thoughts on “Using a pipelined function with parameters in a view”

Leave a Reply