为了在PL/SQL中对这些数据进行排序使用什么数据结构? [英] What data structure to use in order to sort this data in PL/SQL?

问题描述

这是Oracle 11.2g.在PL/SQL函数中，我有一个循环，通过该循环，每次迭代都会创建一个字符串和与该字符串关联的整数.该函数返回所有生成的字符串的最终串联，这些字符串按字母顺序或按整数值排序(取决于函数输入参数).为了给出一个想法，我正在生成这样的东西:

This is Oracle 11.2g. In a PL/SQL function, I've got a loop whereby each iteration, I create a string and an integer associated with that string. The function returns the final concatenation of all the generated strings, sorted (depending on a function input parameter), either alphabetically or by the value of the integer. To give an idea, I'm generating something like this:

Iteration String Integer
        1 Oslo        40
        2 Berlin      74
        3 Rome        25
        4 Paris       10

如果输入参数说要按字母顺序排序，则函数输出应如下所示:

If the input parameter says to sort alphabetically, the function output should look like this :

Berlin, Oslo, Paris, Rome

否则，我们将返回按关联整数值排序的串联字符串:

Otherwise, we return the concatenated strings sorted by the value of the associated integer:

Paris, Rome, Oslo, Berlin

实现这种排序最合适的数据结构是什么?我看过集合，关联数组甚至varray.在Oracle中这似乎很难实现，这让我有些震惊.我看到了这个问题，但对我来说不起作用，因为我需要能够同时按索引和值进行排序:

What is the most appropriate data structure to achieve this sort? I've looked at collections, associative arrays and even varrays. I've been kind of shocked how difficult this seems to be to achieve in Oracle. I saw this question but it doesn't work in my case, as I need to be able to sort by both index and value: How to sort an associative array in PL/SQL? Is there a more appropriate data structure for this scenario, and how would you sort it?

谢谢！

推荐答案

如果将PL/SQL用作SQL而不是其他语言，则非常容易.因此，它非常具体，有时非常好.

It is very easy if you use PL/SQL as SQL and not like other languages. It is quite specific and sometimes is very nice exactly because of that.

有时候我真的很讨厌PL/SQL，但是这种情况绝对是关于爱情的.

Sometimes I really hate PL/SQL, but this case is absolutely about love.

看看它有多简单:

create type it as object (
  iter          number,
  stringval     varchar2(100),
  intval        integer
);

create type t_it as table of it;

declare
  t       t_it := new t_it();
  tmp1    varchar2(32767);
  tmp2    varchar2(32767);
begin
  t.extend(4);
  t(1) := new it(1,'Oslo',40);
  t(2) := new it(2,'Berlin',74);
  t(3) := new it(3,'Rome',25);
  t(4) := new it(4,'Paris',10);

  select listagg(stringval,', ') within group (order by stringval),
         listagg(stringval,', ') within group (order by intval)
  into tmp1, tmp2
  from table(t);

  dbms_output.put_line(tmp1);
  dbms_output.put_line(tmp2);
end;
/

drop type t_it;
drop type it;

在这里您可以看到必须创建全局类型的问题，这就是我讨厌的问题.但是他们说在Oracle 12中可以用本地定义的类型来完成，所以我在等它:)

Here you can see the problem that you must create global types, and this is what I hate it for. But they say in Oracle 12 it can be done with locally defined types so I am waiting for it :)

输出为:

Berlin, Oslo, Paris, Rome
Paris, Rome, Oslo, Berlin

编辑

就您从一开始就不知道迭代的数量而言，唯一的方法是对每个迭代进行扩展(这只是扩展的示例):

As far as you do not know the amount of iterations from the beginning the only way is to do extend on each iteration (this is only example of extending):

declare
  iterator       pls_integer := 1;
begin
  /* some type of loop*/ loop
    t.extend();

    -- one way to assign
    t(t.last) := new it(1,'Oslo',40);

    -- another way is to use some integer iterator
    t(iterator) := new it(1,'Oslo',40);

    iterator := iterator + 1;
  end loop;
end;

我喜欢第二种方法，因为它更快(不会在每次迭代中计算.last).

I prefer the second way because it is faster (does not calculate .last on each iteration).

这篇关于为了在PL/SQL中对这些数据进行排序使用什么数据结构?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！