Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Linked List Structures in PL/SQL 8??
A copy of this was sent to "T.S.S." <tss_at_webspace.com.au>
(if that email address didn't require changing)
On Wed, 02 Dec 1998 09:38:14 +1030, you wrote:
>I would love to have a dynamic linked list structure, so that I can do
>some binary trees, etc,
>and I would love these to be within memory rather than database based
>(i.e. not resident within
>a tablespace somewhere). The only thing I really, really want, is to
>use the type in a select
>statement without having to use a function.
>
>The only things that would be ok are: Nested Tables, Varying Arrays, and
>Index-By Tables.
>
>But the problem with the first is that it has to be stored in the
>Database.
>The second has an upper limit and can't be used in a select statement.
>And the third cannot be used in a select statement (just like the
>second).
>
>Qu: Does anyone know if it is possible to have a dynamic array like
>structure that I can use
>within a select statement and that doesn't require being saved somewhere
>in the DB?
>
>T.S.S.
>96 919 Fireblade
>
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 demo;
/
Now we can:
SQL> select * from demo_view;
THECOLUMN
WEB$RPPRASAD WEB$GBRADSHA WEB$JBROTHER WEB$KKISER WEB$GDEYOUNG
10 rows selected.
SQL> select * from demo_view order by thecolumn;
THECOLUMN
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
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 Tue Dec 01 1998 - 22:23:36 CST