Re: Sorting an array

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/05/04
Message-ID: <8ernbr$klc$1@nnrp1.deja.com>#1/1

In article <8eq53e$ub9$1_at_nnrp1.deja.com>,   Sean <dolans_at_my-deja.com> wrote:
> I apologize for the "generalness" of the question, but I am wondering
> the best approach to sorting an array? Oracle 8i, within a stored
> procedure, an array (PL/SQL table) is built. There is no order to the
> array but I would like to "sort" it. Is there documentation for Pro
> C++ that explains this or is there a better way to accomplish it?
>
> Thanks,
> Sean
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

If, instead of using a PLSQL "index by binary_integer" table type -- you create this tabletype at the SQL level (so its an object in the data dictionary) we can use SQL on it. Consider:

ops$tkyte_at_8i> create or replace type myTableType as table of number;   2 /

Type created.

ops$tkyte_at_8i>
ops$tkyte_at_8i>
ops$tkyte_at_8i> create or replace procedure do_something
  2  as
  3      l_data    myTableType := myTabletype();

  4 begin
  5

  6      for i in 1 .. 10 loop
  7          l_data.extend();
  8          l_data(l_data.count) := 10 - i;
  9      end loop;
 10
 11      dbms_output.put_line( 'Unsorted....' );
 12      for j in 1 .. l_data.count loop
 13          dbms_output.put_line( l_data(j) );
 14      end loop;
 15
 16      dbms_output.put_line( 'Sorted....' );
 17      for x in ( select *
 18                   from TABLE ( cast( l_data as myTableType ) )
 19                  order by column_value )
 20      loop
 21          dbms_output.put_line( x.column_value );
 22      end loop;

 23
 24

 25      dbms_output.put_line( 'Sorted.... another syntax...' );
 26      for x in ( select *
 27                   from THE ( select cast( l_data as mytableType )
from dual ) a
 28                  order by column_value )
 29      loop
 30          dbms_output.put_line( x.column_value );
 31      end loop;

 32
 33 end;
 34 /

Procedure created.

ops$tkyte_at_8i>
ops$tkyte_at_8i> exec do_something
Unsorted....
9
8
7
6
5
4
3
2
1
0
Sorted....
0
1
2
3
4
5
6
7
8
9
Sorted.... another syntax...
0
1
2
3
4
5
6
7
8
9

PL/SQL procedure successfully completed.

So, you can in effect "select * from PLSQL-VARIABLE" and sort it, use it in a join, use it in a subquery and so on.

--
Thomas Kyte                              tkyte_at_us.oracle.com
Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Thu May 04 2000 - 00:00:00 CDT