Re: Sort Alogorithmus

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/06/15
Message-ID: <3585a2d8.1811594@192.86.155.100>#1/1

A copy of this was sent to Josef Huber <josef.huber_at_ennstal.at> (if that email address didn't require changing) On Mon, 15 Jun 1998 10:39:01 +0200, you wrote:

>Hello
>
>I have a plsql-table and need to sort this table have anyone a
>alogorithmus ?
>i can't use the order by of sql statement !!!
>
>Ciao,
>Joe

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 Mon Jun 15 1998 - 00:00:00 CDT