Re: Function returning an aray and us it in an where clause

From: Barbara Boehmer <baboehme_at_hotmail.com>
Date: 27 Jun 2005 12:33:49 -0700
Message-ID: <1119900829.328217.168420@z14g2000cwz.googlegroups.com>

Björn Wächter wrote:
> Thanks,
>
> i tried the code and it really works greate.
> While testing the code I had a new idea:
> Is it possible to return more than one column
> with such an stored function? The problem: I want
> to use one of the colums like in the first
> example to select from another table/view and the
> secound one should only be displayed.
>
>
>
> Tanks in advance
> Björn

Here are some examples using pipelined functions as mentioned by Billy.

scott_at_ORA92> CREATE OR REPLACE TYPE emp_typ AS OBJECT

  2    (empno	  NUMBER,
  3  	hierarchy VARCHAR2(35));

  4 /

Type created.

scott_at_ORA92> CREATE OR REPLACE TYPE emp_tab AS TABLE OF emp_typ;   2 /

Type created.

scott_at_ORA92> CREATE OR REPLACE FUNCTION GetEmps   2 (p_mgr IN NUMBER DEFAULT NULL)
  3 RETURN emp_tab
  4 PIPELINED
  5 AS
  6 BEGIN
  7 FOR r IN

  8  	 (SELECT empno,
  9  		 SUBSTR (SYS_CONNECT_BY_PATH (ename, ', '), 3)
 10  		   AS hierarchy
 11  	  FROM	 emp
 12  	  START  WITH NVL (mgr, 0) = NVL (p_mgr, 0)
 13  	  CONNECT BY PRIOR empno = mgr)
 14    LOOP
 15  	 PIPE ROW (emp_typ (r.empno, r.hierarchy));

 16 END LOOP;
 17 RETURN;
 18 END GetEmps;
 19 /

Function created.

scott_at_ORA92> SHOW ERRORS
No errors.
scott_at_ORA92> -- employees of manager Jones (7566): scott_at_ORA92> SELECT * FROM TABLE (GetEmps (7566))   2 /

     EMPNO HIERARCHY

---------- -----------------------------------
      7788 SCOTT
      7876 SCOTT, ADAMS
      7902 FORD
      7369 FORD, SMITH

scott_at_ORA92> SELECT * FROM emp
  2 WHERE empno IN

  3  	    (SELECT empno
  4  	     FROM   TABLE (GetEmps (7566)))

  5 /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL
COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7369 SMITH      CLERK           7902 17-DEC-80        800
 0         20
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000
 0         20
      7876 ADAMS      CLERK           7788 12-JAN-83       1100
 0         20
      7902 FORD       ANALYST         7566 03-DEC-81       3000
 0         20

scott_at_ORA92> SELECT e.ename, e.deptno, e.job, t.hierarchy   2 FROM emp e,
  3 TABLE (GetEmps (7566)) t
  4 WHERE e.empno = t.empno
  5 /

ENAME DEPTNO JOB HIERARCHY

---------- ---------- --------- -----------------------------------
SCOTT              20 ANALYST   SCOTT
ADAMS              20 CLERK     SCOTT, ADAMS
FORD               20 ANALYST   FORD
SMITH              20 CLERK     FORD, SMITH

scott_at_ORA92> CREATE OR REPLACE FUNCTION GetManagers   2 (p_empno IN NUMBER DEFAULT NULL)
  3 RETURN emp_tab
  4 PIPELINED
  5 AS
  6 BEGIN
  7 FOR r IN

  8  	 (SELECT m.mgr AS empno,
  9  		 SUBSTR (SYS_CONNECT_BY_PATH (e.ename, ', '), 3)
 10  		   AS hierarchy
 11  	  FROM	 emp m, emp e
 12  	  WHERE  m.mgr = e.empno
 13  	  START  WITH m.empno = p_empno
 14  	  CONNECT BY PRIOR m.mgr = m.empno)
 15    LOOP
 16  	 PIPE ROW (emp_typ (r.empno, r.hierarchy));

 17 END LOOP;
 18 RETURN;
 19 END GetManagers;
 20 /

Function created.

scott_at_ORA92> SHOW ERRORS
No errors.
scott_at_ORA92> -- managers of employee Adams (7876): scott_at_ORA92> SELECT * FROM TABLE (GetManagers (7876))   2 /

     EMPNO HIERARCHY

---------- -----------------------------------
      7788 SCOTT
      7566 SCOTT, JONES
      7839 SCOTT, JONES, KING

scott_at_ORA92> SELECT * FROM emp
  2 WHERE empno IN

  3  	    (SELECT empno
  4  	     FROM   TABLE (GetManagers (7876)))

  5 /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL
COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7566 JONES      MANAGER         7839 02-APR-81       2975
 0         20
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000
 0         20
      7839 KING       PRESIDENT            17-NOV-81       5000
 0         10

scott_at_ORA92> SELECT e.ename, e.deptno, e.job, t.hierarchy   2 FROM emp e,
  3 TABLE (GetManagers (7876)) t
  4 WHERE e.empno = t.empno
  5 /

ENAME DEPTNO JOB HIERARCHY

---------- ---------- --------- -----------------------------------
SCOTT              20 ANALYST   SCOTT
JONES              20 MANAGER   SCOTT, JONES
KING               10 PRESIDENT SCOTT, JONES, KING

scott_at_ORA92> Received on Mon Jun 27 2005 - 14:33:49 CDT