Other Oracle database topics - Function returning an aray and us it in an where clause

Author

Function returning an aray and us it in an where clause

Björn Wächter

2005-06-27, 3:23 am

Hi all,

I want to write an stored function
which returns an array of numbers.
Afterwards I want to use this function
in an where clause like:

SELECT *
FROM
VI_USER
WHERE
USER_ID IN GetManagers(2)

Is this possible?

Thanks Björn

-------------------------------------
Dipl.-Ing. Björn Wächter
P3 Solutions GmbH
Dennewartstr. 25-27
D-52068 Aachen
Germany
http://www.p3-solutions.com

tel +49(0)241/9437-417
fax +49(0)241/9437-431

Billy

2005-06-27, 3:23 am

Bj=F6rn W=E4chter wrote:
>
> I want to write an stored function
> which returns an array of numbers.
> Afterwards I want to use this function
> in an where clause like:
>
> SELECT *
> FROM
> VI_USER
> WHERE
> USER_ID IN GetManagers(2)

Yes it is. Just make sure that it is performant and scalable and do not
use this feature and code yourself into a corner. Data should live in
Oracle (i.e. tables and temp tables) - not in PL/SQL. Various reasons
such as referential integrity, indexing, scalability, concurrency and
so on.

SQL> create or replace type TList as table of varchar2(4000);
2 /

Type created.

SQL> create or replace function MyList return TList is
2 l TList;
3 begin
4 l :=3D TList( 'Jack', 'John', 'Chris' );
5 return( l );
6 end;
7 /

Function created.

SQL> select * from TABLE( MyList );

COLUMN_VALUE
---------------------------------------------------------------------------=
-----
Jack
John
Chris

SQL> select created from all_users where username in (select
UPPER(column_value) from TABLE( MyList ) );

CREATED
---------
29-SEP-03

--
Billy

Björn Wächter

2005-06-27, 7:23 am

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

Billy

2005-06-27, 7:23 am

Bj=F6rn W=E4chter wrote:

> While testing the code I had a new idea:
> Is it possible to return more than one column
> with such an stored function?

Yes.

SQL> create or replace type TStuff as object
2 (
3 id number,
4 name varchar2(30)
5 );
6 /

Type created.

SQL> create or replace type TStuffArray as table of TStuff;
2 /

Type created.

SQL> create or replace function MyStuff return TStuffArray
2 is
3 array TStuffArray;
4 begin
5 array :=3D TStuffArray( TStuff(1,'Foo1'), TStuff(2,'Foo2'),
TStuff(3,'Foo3') );
6 return( array );
7* end;
SQL> /

Function created.

SQL> select * from TABLE( MyStuff );

ID NAME
---------- ------------------------------
1 Foo1
2 Foo2
3 Foo3

SQL>

However.. you should rather look at pipelined table functions too. It
provides a far superior method for PL/SQL functions to return data
sets, including the ability for the PL/SQL function to be
multi-threaded.

Details in PL/SQL User's Guide and Reference.

> 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.

Join it. E.g.

select
NVL(t. object_name,'*null*'
) as NAME1,
s.name as NAME2
from TABLE(MyStuff) s,
all_objects t
where s.id =3D t.object_id (+);

NAME1 NAME2
------------------------------ ------------------------------
*null* Foo1
*null* Foo2
I_OBJ# Foo3

But be aware that as the data resides in PL/SQL, the SQL engine needs
to make a context switch to the PL/SQL engine, run the code, and then
build a SQL "result set" from it. This data set it not indexed.

This is not really performant when dealing with anything but small data
volumes. Data should rather be stored in Oracle tables.. and temporary
session/processing data can always be stored in temp tables if need be.

--
Billy

Barbara Boehmer

2005-06-27, 8:23 pm

Bj=F6rn W=E4chter 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=F6rn

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

scott@ORA92> CREATE OR REPLACE TYPE emp_typ AS OBJECT
2 (empno NUMBER,
3 hierarchy VARCHAR2(35));
4 /

Type created.

scott@ORA92> CREATE OR REPLACE TYPE emp_tab AS TABLE OF emp_typ;
2 /

Type created.

scott@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) =3D NVL (p_mgr, 0)
13 CONNECT BY PRIOR empno =3D mgr)
14 LOOP
15 PIPE ROW (emp_typ (r.empno, r.hierarchy));
16 END LOOP;
17 RETURN;
18 END GetEmps;
19 /

Function created.

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

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

scott@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@ORA92> SELECT e.ename, e.deptno, e.job, t.hierarchy
2 FROM emp e,
3 TABLE (GetEmps (7566)) t
4 WHERE e.empno =3D 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@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 =3D e.empno
13 START WITH m.empno =3D p_empno
14 CONNECT BY PRIOR m.mgr =3D 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@ORA92> SHOW ERRORS
No errors.
scott@ORA92> -- managers of employee Adams (7876):
scott@ORA92> SELECT * FROM TABLE (GetManagers (7876))
2 /

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

scott@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@ORA92> SELECT e.ename, e.deptno, e.job, t.hierarchy
2 FROM emp e,
3 TABLE (GetManagers (7876)) t
4 WHERE e.empno =3D t.empno
5 /

ENAME DEPTNO JOB HIERARCHY
---------- ---------- --------- -----------------------------------
SCOTT 20 ANALYST SCOTT
JONES 20 MANAGER SCOTT, JONES
KING 10 PRESIDENT SCOTT, JONES, KING

scott@ORA92>

Sponsored Links