按索引表(关联数组)排序 [英] Sorting an index-by table (associative array)

问题描述

我需要对定义为"binary_integer的数字索引表"的关联数组进行排序.我可以手动编写快速排序算法，但是肯定有一种方法可以使用查询(排序依据)?

I need to sort an associative array defined as a "table of number index by binary_integer". I can code a quick sort algorithm by hand, but surely there must be a way to sort my values using a query (order by) ?

我的问题的说明:

我的关联数组类型的定义:

create or replace package my_type is 
   type my_array is table of NUMBER index by binary_integer;
end my_type ;

出于测试目的，让我们生成一个测试数组，其值是否按升序排序.

declare
  test my_array.my_type.;
  i number := 10;

begin
  while (i > 0) loop
       test(10 - i) :=  i;
       i := i - 1;
  end loop;
end;

我想使用带有ORDER BY的查询以升序对数组进行排序.遵循这些原则:

  i := 0;
  for query_result_row in (select 1 as val from table(test) order by 1) loop
     test(i) := query_result_row.val;
     i := i + 1;
  end loop;

这种方法应该可行:"Oracle 12c支持使用TABLE运算符查询关联数组，只要该类型在程序包规范中声明:

This approach should be possible : "Oracle 12c supports querying associative arrays using the TABLE operator, 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/ "

我怀疑问题出在我选择具有序数的列的方式上.显然不可能，但是没有列名(因为它是一个关联数组)，所以我陷入了困境.

I suspect that the problem comes from the way I select the column with ordinals. It isn't possible apparently, but there is no column name (as it is an associative array), so I'm stuck.

推荐答案

您甚至可以使用BULK COLLECT保存一些代码分配行:

You can even save yourself some of the assignment lines of code using BULK COLLECT:

DECLARE
  test my_array.my_type;
  i number := 10;
  CURSOR c IS
    SELECT t.column_value
    FROM table(test) t
    ORDER BY t.column_value;
begin
  -- Generating a test array with values that or not sorted in asc order
  while (i > 0) loop
       test(10 - i) :=  i;
       i := i - 1;
  end loop;

  OPEN c;
  FETCH c BULK COLLECT INTO test;
  CLOSE c;
END;

注意:您不能仅使用BULK COLLECT INTO编写SELECT.看来，Oracle在运行该语句之前会清空该集合.您没有得到任何错误，但是您也没有得到任何结果.

Note: You can't just write a SELECT with BULK COLLECT INTO. It appears that Oracle empties out the collection before the statement is run. You don't get an error, but you also don't get any results.

这篇关于按索引表(关联数组)排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！