oracle 如何在 PL/SQL 中对关联数组进行排序？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7800880/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to sort an associative array in PL/SQL?
提问by Marian
I have an associative array like this:
我有一个像这样的关联数组：
continent_population('Australia') := 30;
continent_population('Antarctica') := 90;
continent_population('UK') := 50;
How do I sort this array after values in PL/SQL? Thanks!
如何在 PL/SQL 中的值之后对这个数组进行排序？谢谢！
回答by user272735
You can't sort an associative array by values, but you have to convert the data to some other data structure and make the sorting there. The easiest way would have been to convert to another associative array where keys and values swap places, but that requires your key values should be unique too.
您不能按值对关联数组进行排序，但必须将数据转换为其他数据结构并在那里进行排序。最简单的方法是转换为另一个关联数组，其中键和值交换位置，但这要求您的键值也应该是唯一的。
Below is an example adapted to your case from Sorting PL/SQL Collections. Please check that article for the details.
以下是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;
/
Prints:
印刷：
Unsorted:
Antarctica = 90
Australia = 30
UK = 50
USA = 50
Sorted by value:
Australia = 30
UK = 50
USA = 50
Antarctica = 90
回答by Pierre
The accepted answer is outdated. Since Oracle 12c, querying associative arrays using the TABLE operator is possible, as long as the type is declared in a package spec: https://galobalda.wordpress.com/2014/08/02/new-in-oracle-12c-querying-an-associative-array-in-plsql-programs/
接受的答案已过时。从 Oracle 12c 开始，只要在包规范中声明类型，就可以使用 TABLE 运算符查询关联数组：https: //galobalda.wordpress.com/2014/08/02/new-in-oracle-12c-查询关联数组在 plsql 程序/
You cansort an associative array by values, and you don't haveto convert the data: Sorting an index-by table (associative array)
您可以按值对关联数组进行排序，而不必转换数据： Sorting an index-by table (associative array)
