database - PL/SQL - 对嵌套自定义对象的集合进行排序

Question

我正在尝试对一组嵌套的自定义对象进行排序。我找到了 this question它帮助我理解了如何对一个简单的对象表进行排序。

我尽量简化了它。在应用程序中，以下每一个都是具有数十个属性的复杂对象。出于这个问题的目的，假设我有一个行对象表，其中每个对象都有一个属性，即列对象表:

create or replace TYPE myCOLUMN AS OBJECT (
    fvalue        NUMBER
);

create or replace TYPE myCOLUMNS AS TABLE OF myCOLUMN;

create or replace TYPE myROW AS OBJECT (
    cols          myCOLUMNS
);

create or replace TYPE myROWS AS TABLE OF myROW;

我的最终目标是能够按多列对这个集合或行进行排序。

declare
  rows        myROWS := myROWS();
begin
  rows.extend(2);
  rows(1) := new myROW(new myCOLUMNS(
    new myCOLUMN(1), new myCOLUMN(2)
  ));
  rows(2) := new myROW(new myCOLUMNS(
    new myCOLUMN(1), new myCOLUMN(1)
  ));

  -- assuming I want to order by the first and second column in ascending order
  -- the second row should be first after the sorting
end;
/

我很好奇，我是否需要从头开始实现它，或者我能否以某种方式受益于在 PL/SQL 代码中使用 SQL 对其进行排序。

感谢任何帮助。

Answer 1

以“保持简单”为座右铭。提供的“解决方案”基于以下假设:

• 您正在使用 Oracle 数据库；
• 你需要像表格这样的数据结构；
• 您仅在 session (用户)级别需要它；
• 需要进行排序或其他“技巧”；

你可以使用 Global temporary table .

例子:

create global temporary table my_temp_table (
  id number,
  name varchar2(3),
  description varchar2(20))
on commit delete rows;

您可以像普通表一样轻松地填充它:

begin
  insert into
    my_temp_table
  select
    tmp_data.*
  from(
    select 1, 'GBL', 'Global' from dual union
    select 2, 'TMP', 'Temporary' from dual union
    select 3, 'TBL', 'Table' from dual) tmp_data;
end;

自由使用(排序、转换、聚合等):

select
  tmp.id,
  tmp.name,
  tmp.description
from
  my_temp_table tmp
order by
  tmp.name

唯一的缺点 - 结构本身必须定义(不是动态的)。但这很容易解决。

更新

尝试坚持您的集合结构并使用 CAST(MULTISET()) 功能，但运气不佳 - 您当前的集合构建太复杂，SQL 无法处理。如果你仍然愿意坚持你的方法 - 你可以阅读它 here .你在 build 什么 - 它是纯粹的 table - 为什么要再次发明轮子(保持简单)？据我了解，您为什么选择这种方法 - 列数可以更改？让我们尝试使用之前提供的解决方案来解决您的问题。

所以，可以说，列数发生了变化——但仍然必须有一个限制。所以，让最大列数可以是 10。可以更少，我们不在乎。值的类型将是数字(如您的示例所示)。因此，结构将是:

create global temporary table my_temp_table (
  row_number number,
  column1 number,
  column2 number,
  column3 number,
  column4 number,
  column5 number,
  column6 number,
  column7 number,
  column8 number,
  column9 number,
  column10 number)
on commit delete rows;

当然，现在的问题是如何获取数据。可以说，您在某处生成(如您的示例所示)。我们填写数据:

begin
  insert into
    my_temp_table(
      row_number,
      column1,
      column2)
  select
    tmp_data.*
  from(
    select 
      1 row_number, 
      1 column1, 
      2 column2
    from dual 
    union
    select 
      2 ron_number, 
      1 column1, 
      1 column2
    from dual) tmp_data;
end;

现在，问题是您要对数据做什么？在计算中使用它？只是为了生成某种有序的报告(XML)？比方说，您只想显示输出:

begin
  dbms_output.put_line('Sorted data');
  for l_rec in(
    select
      tmp.row_number,
      tmp.column1,
      tmp.column2
    from
      my_temp_table tmp
    order by
      tmp.column1,
      tmp.column2)
  loop
    dbms_output.put_line('Row number: '||l_rec.row_number);
    dbms_output.put_line('Column values: '||l_rec.column1 || ', ' ||l_rec.column2);
  end loop;
end;

您可以按照自己的方式使用此表，请记住 - 数据仅在当前打开的 session 中获取(如果您在某处使用提交/回滚 - 数据也会丢失)。