如何在PL / SQL中排序关联数组？ [英] How to sort an associative array in PL/SQL?

问题描述

我有一个这样的关联数组：

I have an associative array like this:

continent_population('Australia') := 30;
continent_population('Antarctica') := 90;
continent_population('UK') := 50;

如何在PL / SQL中的值之后对此数组进行排序？谢谢！

How do I sort this array after values in PL/SQL? Thanks!

推荐答案

不能通过值对关联数组排序，但必须将数据转换为其他数据结构并在那里做排序。最简单的方法是转换为另一个关联数组，其中键和值交换位置，但这需要您的键值也应该是唯一的。

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.

下面是一个示例来自排序PL / SQL集合。请检查该文章的详细信息。

Below is an example adapted to your case from Sorting PL/SQL Collections. Please check that article for the details.

/* 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

这篇关于如何在PL / SQL中排序关联数组？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！