对于Pipelined Table Function 在with语句中不可以多次调用,但如果函数使用常量除外：

SQL> CREATE OR REPLACE PACKAGE BODY pkg_with IS
  2 
  3  FUNCTION f_emp_info (p_deptno emp.deptno%TYPE)
  4  RETURN emp_tab
  5  PIPELINED
  6  IS
  7   lv_cursor emp_tab;
  8   CURSOR c1 IS
  9   SELECT empno
 10         ,sal
 11         ,deptno
 12     FROM emp
 13    WHERE deptno = p_deptno;
 14  BEGIN
 15   OPEN c1;
 16   FETCH c1 BULK COLLECT INTO lv_cursor;
 17   close c1;
 18 
 19   IF lv_cursor.LAST IS NOT NULL
 20   THEN
 21     FOR i IN lv_cursor.FIRST..lv_cursor.LAST LOOP
 22       PIPE ROW (lv_cursor(i));
 23     END LOOP;
 24   END IF;
 25  
 26   RETURN;
 27  END;
 28 
 29  PROCEDURE p_emp
 30    (p_deptno    IN  emp.deptno%TYPE
 31    ,p_resultset OUT type_cursor_variable)
 32  IS
 33  BEGIN
 34    OPEN p_resultset FOR
 35    WITH ee AS (SELECT empno,sal
 36                  FROM TABLE(f_emp_info(10))
 37               )
 38        ,ee_dept AS(SELECT ee.empno,dept.dname
 39                      FROM dept,ee
 40                     WHERE dept.deptno = p_deptno)  
 41        ,avg_sal AS(SELECT AVG(sal) sal_avg
 42                      FROM ee )
 43         SELECT empno,dname,sal_avg
 44           FROM ee_dept,avg_sal;                        
 45  END;
 46 
 47  end pkg_with;
 48  /

Package body created.

SQL> exec pkg_with.p_emp(10,:v_cursor);

PL/SQL procedure successfully completed.

SQL> print v_cursor;

     EMPNO DNAME             SAL_AVG
---------- -------------- ----------
      7782 ACCOUNTING     2916.66667
      7839 ACCOUNTING     2916.66667
      7934 ACCOUNTING     2916.66667

3 rows selected.

SQL>

SQL> CREATE OR REPLACE PACKAGE BODY pkg_with IS
  2 
  3  FUNCTION f_emp_info (p_deptno emp.deptno%TYPE)
  4  RETURN emp_tab
  5  PIPELINED
  6  IS
  7   lv_cursor emp_tab;
  8   CURSOR c1 IS
  9   SELECT empno
 10         ,sal
 11         ,deptno
 12     FROM emp
 13    WHERE deptno = p_deptno;
 14  BEGIN
 15   OPEN c1;
 16   FETCH c1 BULK COLLECT INTO lv_cursor;
 17   close c1;
 18 
 19   IF lv_cursor.LAST IS NOT NULL
 20   THEN
 21     FOR i IN lv_cursor.FIRST..lv_cursor.LAST LOOP
 22       PIPE ROW (lv_cursor(i));
 23     END LOOP;
 24   END IF;
 25  
 26   RETURN;
 27  END;
 28 
 29  PROCEDURE p_emp
 30    (p_deptno    IN  emp.deptno%TYPE
 31    ,p_resultset OUT type_cursor_variable)
 32  IS
 33  BEGIN
 34    OPEN p_resultset FOR
 35    WITH ee AS (SELECT empno,sal
 36                  FROM TABLE(f_emp_info(p_deptno))
 37               )
 38        ,ee_dept AS(SELECT ee.empno,dept.dname
 39                      FROM dept,ee
 40                     WHERE dept.deptno = p_deptno)  
 41        ,avg_sal AS(SELECT AVG(sal) sal_avg
 42                      FROM ee )
 43         SELECT empno,dname,sal_avg
 44           FROM ee_dept,avg_sal;                        
 45  END;
 46 
 47  end pkg_with;
 48  /

Package body created.

SQL> exec pkg_with.p_emp(10,:v_cursor);
BEGIN pkg_with.p_emp(10,:v_cursor); END;

*
ERROR at line 1:
ORA-22905: cannot access rows from a non-nested table item
ORA-06512: at "WWAP.PKG_WITH", line 34
ORA-06512: at line 1