Pipe line function in PLSQL Oracle

This pipe line function used for call collection in select statement as a table instead of looping row by row.



create type t_row as object(
id number,
name varchar2(100),
dob date);

create type t_tab is table of  t_row;


/* Formatted on 2016/03/29 11:38 (Formatter Plus v4.8.8) */
CREATE or replace FUNCTION g_tab_rf (p_no NUMBER)
   RETURN t_tab
AS
   v_tab   t_tab := t_tab ();
BEGIN
   FOR i IN 1 .. p_no
   LOOP
      v_tab.EXTEND;
      v_tab (v_tab.LAST) := t_row (i, ' NAMe ' || i, SYSDATE + i);
   END LOOP;

   RETURN v_tab;
END;


select * from table(g_tab_rf(10));



CREATE OR REPLACE FUNCTION g_tab_prf (p_no NUMBER)
   RETURN t_tab PIPELINED
AS
BEGIN
   FOR i IN 1 .. p_no
   LOOP
      PIPE ROW (t_row (i, ' Name ' || i, SYSDATE + i));
   END LOOP;

   RETURN;
   EXCEPTION
  WHEN NO_DATA_NEEDED THEN
    RAISE;
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line('OTHERS Handler');
    RAISE;
END;


select * from table(g_tab_prf(10))
where rownum <= 5;

 --Another Example

 -- Build package containing record and table types internally.
CREATE OR REPLACE PACKAGE ptf_api AS
  TYPE t_ptf_row IS RECORD (
    id           NUMBER,
    description  VARCHAR2(50)
  );

  TYPE t_ptf_tab IS TABLE OF t_ptf_row;

  FUNCTION get_tab_ptf (p_rows IN NUMBER) RETURN t_ptf_tab PIPELINED;
END;
/

CREATE OR REPLACE PACKAGE BODY ptf_api AS

  FUNCTION get_tab_ptf (p_rows IN NUMBER) RETURN t_ptf_tab PIPELINED IS
    l_row  t_ptf_row;
  BEGIN
    FOR i IN 1 .. p_rows LOOP
      l_row.id := i;
      l_row.description := 'Description for ' || i;
      PIPE ROW (l_row);
    END LOOP;
 
    RETURN;
  END;
END;
/

SELECT *
FROM   TABLE(ptf_api.get_tab_ptf(10))
ORDER BY id DESC;


SELECT object_name, object_type
FROM   user_objects;




CREATE OR REPLACE FUNCTION get_stat (p_stat IN VARCHAR2) RETURN NUMBER AS
  l_return  NUMBER;
BEGIN
  SELECT ms.value
  INTO   l_return
  FROM   v$mystat ms,
         v$statname sn
  WHERE  ms.statistic# = sn.statistic#
  AND    sn.name = p_stat;
  RETURN l_return;
END get_stat;


-- Test table function.
SET SERVEROUTPUT ON
DECLARE
  l_start  NUMBER;
BEGIN
  l_start := get_stat('session pga memory');

  FOR cur_rec IN (SELECT *
                  FROM   TABLE(g_tab_rf(100000)))
  LOOP
    NULL;
  END LOOP;

  DBMS_OUTPUT.put_line('Regular table function : ' ||
                        (get_stat('session pga memory') - l_start));
                       
                       
l_start := get_stat('session pga memory');    

FOR cur_rec IN (SELECT *
                  FROM   TABLE(g_tab_prf(100000)))
  LOOP
    NULL;
  END LOOP;

  DBMS_OUTPUT.put_line('Regular table function : ' ||
                        (get_stat('session pga memory') - l_start));                  
END;
/