Re: Q: Sorting collections in memory

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 27 Aug 1999 11:18:24 GMT
Message-ID: <37cb73e1.10587674@newshost.us.oracle.com>

A copy of this was sent to "Johan Wegener" <xjw_at_xdde.xdk> (if that email address didn't require changing) On Fri, 27 Aug 1999 08:43:33 +0200, you wrote:

>Hello,
>
>I want to know whether it is possible to manipulate collections using SQL
>insert and select?
>
>Please take a look at these definitions:
>
>CREATE OR REPLACE TYPE BEREGN_RESULTAT_OBJECT AS OBJECT
> (
> ID NUMBER(32)
> ,PERIODE_FRA DATE
> ,PRIS NUMBER
> );
>
>and
>
>CREATE TYPE FOUND_BEREGN_RESULTAT AS TABLE OF BEREGN_RESULTAT_OBJECT;
>
>Now I want to sort a collection (this example does NOT work):
>
>declare
> source FOUND_BEREGN_RESULTAT;
> dest FOUND_BEREGN_RESULTAT;
>begin
> -- Fill the source table.......
> ...
> ...
> -- Sort it:
> insert into dest
> select * from source
> order by period_fra;
>end;
>
>But it does not work! ?
>
>Does anybody know how to achieve this effect without having to store the
>collection in a temporary database table?
>
>Thanks,
>Johan
>
>
>

Here is one way. this sorts L_a into L_b. I use SQL to populate l_a to show how to do that as well:

tkyte_at_8i> create or replace type myScalarType as object 2 ( x int, y int );
3 /

Type created.

tkyte_at_8i> create or replace type myTableType as table of myScalarType; 2 /

Type created.

tkyte_at_8i> 
tkyte_at_8i> 
tkyte_at_8i> declare
  2      l_a    myTableType;
  3      l_b    myTableType;
  4  begin
  5      select cast( multiset( select rownum, user_id
  6                               from all_users
  7                              where rownum < 11 ) as myTableType )
  8        into l_a
  9        from dual;

10
11

 12      for i in 1 .. l_a.count loop
 13          dbms_output.put_line( 'a('||i||')='||l_a(i).x
 14                                  || ' ' || l_a(i).y );
 15      end loop;
 16  
 17          l_b := myTableType();
 18          for c in ( select a.x, a.y
 19                   from the ( select cast( l_a as myTabletype ) from dual ) a
 20                  order by a.y desc )
 21          loop
 22                  l_b.extend;
 23                  l_b(l_b.count) := myScalarType( c.x, c.y );
 24          end loop;
 25  
 26      for i in 1 .. l_b.count loop
 27          dbms_output.put_line( 'b('||i||')='||l_b(i).x
 28                               || ' ' || l_b(i).y );
 29      end loop;

30
31 end;
32 /

a(1)=1 0
a(2)=2 5
a(3)=3 11
a(4)=4 18
a(5)=5 20
a(6)=6 21
a(7)=7 35
a(8)=8 23
a(9)=9 24
a(10)=10 25
b(1)=7 35
b(2)=10 25
b(3)=9 24
b(4)=8 23
b(5)=6 21
b(6)=5 20
b(7)=4 18
b(8)=3 11
b(9)=2 5
b(10)=1 0

PL/SQL procedure successfully completed.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Aug 27 1999 - 06:18:24 CDT