Re: PL/SQL function in a SELECT statement

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 24 Sep 1998 14:03:19 GMT
Message-ID: <360f50c9.69556767@192.86.155.100>

A copy of this was sent to Bruno Pennec <Bruno.Pennec_at_der.edfgdf.fr> (if that email address didn't require changing) On Thu, 24 Sep 1998 14:53:08 +0200, you wrote:

>I would like but i can't because "my_function" contains
>big requests and calculations and it will very inefficient
>to test all values.
>
>have you got a other solution ?
>
>thanks,
> bruno
>
>
>> I think you'd better write something like:
>>
>> SELECT column1 FROM table1 WHERE my_function(column2) = 1
>>
>> >
>> > I need to do a SELECT statement like :
>> >
>> > SELECT column1 FROM table1 WHERE column2 in my_function;
>> >
>> > ( where my_function is a PL/SQL function. )
>> >
>> > My_function must return a list of items.
>> > What type must i use for the result of "my_function" ?
>> > (for example : column2 is a varchar2)
>> >

Sort of. It would look like this (from another example but applicable in your case, you basically want to be able to "select * from PL/SQL-table" and thats what this does)

Actually... there is a way to use SQL on PL/SQL tables (sort of). In order to do this, you need to create a table as such (we only ever need one of these tables per database, consider it similar to DUAL)

create table dummy ( x int primary );
insert into dummy select rownum from all_objects;

so dummy is a table with rows (approx) 1 .. 1,000 or so. You can add more if you want. The important thing is that is has records 1, 2, 3, 4, .... N (all integers, no gaps)

Once you have that, you need to make your pl/sql table callable from SQL. We need a function "getcol" for example that if you say "getcol(5)" it would return the 5'th element from your pl/sql table. We also need a function, say getmax, that tells us how many elements are in the table currently. So a package spec might look like:

create or replace package demo
as

    pragma restrict_references(demo, wnds, rnds, wnps, rnps);

    type array is table of varchar2(255) index by binary_integer;

    procedure set_up_some_data;

    function getcol( x in number ) return varchar2;     pragma restrict_references(getcol,wnds,rnds,wnps);

    function getmax return number;
    pragma restrict_references(getmax,wnds,rnds,wnps); end;
/

the procedure set_up_some_data is just an example, your own code would go there.

Now, we can create a view like this:

create or replace view
demo_view
as
select demo.getcol(x) theColumn
  from dummy
 where x <= ( select demo.getmax from dual ) /

So, this view will select our function on column X for all X's <= the number of elements in our table. that is, we will get rows 1, 2, 3 ... N-Elements in the Table.

Then we implement our package body as such:

create or replace package body demo
as

g_theArray array;
g_theCnt number;

function getcol( x in number ) return varchar2 is
begin

    return g_theArray(x);
end;

function getmax return number
is
begin

    return g_theCnt;
end;

procedure set_up_some_data
as
begin

    for x in ( select username, rownum rnum from all_users where rownum < 11)     loop

        g_theArray(x.rnum) := x.username;
        g_theCnt := x.rnum;

    end loop;
end;

end demo;
/

Now we can:

SQL> select * from demo_view;

THECOLUMN

---

SYS
SYSTEM
DBSNMP
TRACESVR
SCOTT

WEB$RPPRASAD
WEB$GBRADSHA
WEB$JBROTHER
WEB$KKISER
WEB$GDEYOUNG

10 rows selected.

SQL> select * from demo_view order by thecolumn;

THECOLUMN

---

DBSNMP
SCOTT
SYS
SYSTEM
TRACESVR

WEB$GBRADSHA
WEB$GDEYOUNG
WEB$JBROTHER
WEB$KKISER
WEB$RPPRASAD

10 rows selected.

Yes, you can even apply a where clause to it, join it, use it in an IN statement (eg:

SQL> select * from all_users where username in ( select * from demo_view );

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
DBSNMP                                 17 01-SEP-97
SCOTT                                  20 01-SEP-97
SYS                                     0 01-SEP-97
SYSTEM                                  5 01-SEP-97
TRACESVR                               19 01-SEP-97
WEB$GBRADSHA                         1792 01-SEP-97
WEB$GDEYOUNG                         1794 01-SEP-97
WEB$JBROTHER                         1813 01-SEP-97
WEB$KKISER                           1793 01-SEP-97
WEB$RPPRASAD                         1791 01-SEP-97

and so on...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Sep 24 1998 - 09:03:19 CDT