操作集合 [英] Manipulating collections

问题描述

我尝试对不同元素（所有者，表）的列表进行排序。
只需一个即可轻松（非常快）！

I try to sort a list of distinct elements (owner, table). It's easy (and very quick to!) with just one, for example:

declare
TYPE tbl_list IS TABLE OF VARCHAR2(64);
l_tables tbl_list;
i number;
begin
  l_tables:=tbl_list();
  for i in 1..100000
    loop
      l_tables:= l_tables MULTISET UNION DISTINCT tbl_list('myTable');
    end loop;
    for i in l_tables.first..  l_tables.last
      loop
        dbms_output.put_line(l_tables(i));
      end loop;  
end;
/

我尝试与列表相同，但失败：

I try to so the same with a list but it's failed:

create or replace TYPE tbl_list2 IS OBJECT (l_owner  VARCHAR2(64),l_name  VARCHAR2(64));




declare
l_object tbl_list2;
i number;
begin
  l_object:=tbl_list2('','');

  for i in 1..100000
    loop
      l_object:= l_object MULTISET UNION DISTINCT tbl_list2('myOwner','MyTable');
    end loop;
    for i in l_object.first..  l_object.last
      loop
        dbms_output.put_line(l_object(i));
      end loop;  

end;
/

但我捕获以下内容：
PLS-00306 ：调用'MULTISET_UNION_DISTINCT'时出错的参数数量

目标是拥有一个所有不同

The goal is to have a list of all distinct (owner, tables), I don't care if you find any others idea of course.

一个解决方案当然是两个字中的一个字，但我想找到更优雅！

A solution is of course a concatenation in one word of the two, but I would like to find more elegant!

EDIT
@ThinkJet：

EDIT @ThinkJet:

它比我的肮脏的解决方案更优雅。
但是，你的解决方案是比我的时间慢70倍！

I love your solution. It's more elegant than my dirty solution. But, Your solution is bout 70 time slower than mine! So How could we converge to have a elegant ant speed solution?

这里是我的肮脏的一个：

Here my dirty one:

declare
TYPE tbl_list IS TABLE OF VARCHAR2(64);
l_tables tbl_list;
i number;
begin
  l_tables:=tbl_list();
  for i in 1..100000
    loop
      l_tables:= l_tables MULTISET UNION DISTINCT tbl_list('myOwner'||','||'myTable');
    end loop;
    for i in l_tables.first..  l_tables.last
      loop
        dbms_output.put_line('OWNER='||REGEXP_SUBSTR(l_tables(i),'[^,]+', 1, 1));
        dbms_output.put_line('TABLE='||REGEXP_SUBSTR(l_tables(i),'[^,]+', 1, 1));
      end loop;  
end;

/

推荐答案

至少你在第二种情况下丢失了表定义。这个语句：

At least you lost a table definition in second case. This statement:

create or replace TYPE tbl_list2 IS OBJECT (l_owner  VARCHAR2(64),l_name  VARCHAR2(64));

只声明对象（或记录）类型，而不是表。

declares only object (or record) type, not a table.

因此，您需要分两步执行：

So you need to do it in 2 steps:

create or replace TYPE tbl_list_rec IS OBJECT (l_owner  VARCHAR2(64),l_name  VARCHAR2(64));
/

create or replace TYPE tbl_list2 as table of tbl_list_rec;
/

之后，您需要在脚本中进行一些语法修正：

After that you need some syntax corrections in script:

declare
  l_object tbl_list2;
  i        number;
begin
  -- for list initialization it must be filled with constructed objects
  l_object := tbl_list2( tbl_list_rec('','') );

  for i in 1..100000 loop          

    -- 1. select values to variable
    -- 2. Fix constructor for list 
    select 
      l_object MULTISET UNION DISTINCT tbl_list2(tbl_list_rec('myOwner','MyTable'))
    into 
      l_object 
    from 
      dual;

    end loop;

    for i in l_object.first ..  l_object.last loop
      -- output separate fields, there are now default conversion from 
      -- user-defined objects to varchar2.
      dbms_output.put_line(l_object(i).l_owner || ',' || l_object(i).l_name);
    end loop;  

end;
/

UPDATE

上面的解决方案比较慢，因为上下文切换次数大。但是不能在PL / SQL中直接进行复杂对象类型实例的比较，而无需一些额外的工作。

要允许Oracle知道对象实例是相同还是不同，我们需要定义对象类型的映射或排序方法。这两种类型的方法都不允许，所以有必要选择合适的一种。 MAP方法执行速度更快，在我们的情况下不需要排序，所以请：

UPDATE

Solution above relatively slow because of big number of context switches. But comparison of complex object type instances can't be done directly in PL/SQL without some additional work.
To allow Oracle to know if object instances are same or different, we need to define mapping or ordering method for object type. Both types of methods not allowed, so there are need to choose proper one. MAP methods performs faster and there are no need for ordering in our case, so go for it:

create or replace TYPE tbl_list_rec2 AS OBJECT (
  l_owner  VARCHAR2(64),
  l_name  VARCHAR2(64),
  map member function get_key return varchar2
);
/

实施：

create or replace TYPE BODY tbl_list_rec2 AS 

  map member function get_key return varchar2
  is
  begin
    return l_owner||chr(1)||l_name;
  end;

end;
/

之后，可以在PL / SQL代码中测试对象是否相等 varchar2 在问题的第一个示例中：

After that it's possible to test objects for equality in PL/SQL code like simple varchar2 in first example from question:

declare
  l_object tbl_list2a;
  i        number; 
begin
  l_object := tbl_list2a( tbl_list_rec2('','') );

  for i in 1..100000 loop          
    l_object := l_object MULTISET UNION DISTINCT tbl_list2a(tbl_list_rec2('myOwner','MyTable'));
  end loop;

  for i in l_object.first..  l_object.last loop
    dbms_output.put_line(l_object(i).l_owner || ',' || l_object(i).l_name);
  end loop;  

end;
/

这篇关于操作集合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！