oracle 关联排序,oracle – 如何在PL / SQL中对关联数组进行排序？

您不能按值对关联数组进行排序,但您必须将数据转换为其他数据结构并在那里进行排序.最简单的方法是转换到另一个关联数组,其中键和值交换位置,但这要求您的键值也应该是唯一的.

以下是从Sorting PL/SQL Collections开始适用于您的案例的示例.请查看该文章了解详细信息.

/* The sorting is done with SQL thus these types have to be SQL types. */

create type sortable_t is object(

continent varchar2(32767),

population number

);

/

create type sortable_table_t is table of sortable_t;

/

declare

type continent_population_t is table of pls_integer index by varchar2(32767);

continent_population continent_population_t;

i varchar2(32767);

sorted sortable_table_t := sortable_table_t();

begin

/* Populate original data. */

continent_population('Australia') := 30;

continent_population('Antarctica') := 90;

continent_population('UK') := 50;

continent_population('USA') := 50;

/* Convert to a helper data type that is used for sorting. */

i := continent_population.first;

while i is not null loop

sorted.extend(1);

sorted(sorted.last) := new sortable_t(i, continent_population(i));

i := continent_population.next(i);

end loop;

/* Show that the content is not sorted yet. */

dbms_output.put_line('Unsorted:');

for j in sorted.first .. sorted.last loop

dbms_output.put_line(sorted(j).continent || ' = ' || sorted(j).population);

end loop;

/* Sorting with SQL. */

select cast(multiset(select *

from table(sorted)

order by 2 asc, 1 asc)

as sortable_table_t)

into sorted

from dual;

/* Show that the content is now sorted. */

dbms_output.put_line('Sorted by value:');

for j in sorted.first .. sorted.last loop

dbms_output.put_line(sorted(j).continent || ' = ' || sorted(j).population);

end loop;

end;

/

打印：

Unsorted:

Antarctica = 90

Australia = 30

UK = 50

USA = 50

Sorted by value:

Australia = 30

UK = 50

USA = 50

Antarctica = 90