我如何编写一个过程来查找这样的一组行？

Question

抱歉问题标题很垃圾。我有一个像这样的表SET_DEFINITIONS：

SETKEY      NOT NULL    NUMBER(38)
SETENTRY    NOT NULL    NUMBER(38)

其中的想法是行定义数字集。例如，表可以包含行：

1 2
1 4
2 1
2 2

这意味着集合 1 是 {2,4}，集合 2 是 {1,2}。我想编写一个函数

function selectOrInsertSet(table of number(38) numbers) return number(38)

，该函数将返回与传入表具有相同成员的集合的键（或者如果不存在则创建这样的集合）。在 PL/SQL 中执行此操作的好方法是什么？

编辑：我目前正在研究的解决方案是这样的（我不确定它是否会起作用）：

1. 选择将第一个元素放入某个集合 c 中的所有键，
2. 通过以下方式细化集合 c与包含其他元素的连续键集相交

Answer 1

找到交集的一种快速方法可能是创建一个全局临时表并用传入的数字表填充它。然后，您可以使用 SET_DEFINITIONS 连接该表来查找所有可能的匹配项。您需要检查每个匹配组中的总数以消除超组。

创建一些基表...

create table set_definitions (
    setkey number,
    setentry number,
    constraint pk_set_definitions primary key (setkey, setentry)
    );

insert into set_definitions values (1,2);
insert into set_definitions values (1,4);
insert into set_definitions values (2,1);
insert into set_definitions values (2,2);
insert into set_definitions values (3,1);
insert into set_definitions values (3,2);
insert into set_definitions values (3,3);

创建一个全局临时表来保存传递的值：

create global temporary table tmp_setentry (
    setentry number, 
    constraint pk_tmp_setentry primary key (setentry));

insert into tmp_setentry values (1);
insert into tmp_setentry values (2);

与 set_definitions 连接以查找匹配的集合：

select
    setkey
from
    (
    select
        setkey,
        count(*) num_matches,
        (select count(*) from set_definitions where setkey = s.setkey)
            num_set_entries,
        (select count(*) from tmp_setentry) num_entries
    from
        set_definitions s
            inner join tmp_setentry t on t.setentry = s.setentry
    group by
        setkey
    )
where
    num_matches = num_entries
and num_set_entries = num_entries

--> 2 (3 is dropped as a superset)

希望这会有所帮助。

Answer 2

Oracle 11g 引入了 LISTAGG 函数，您可以使用它来满足您的需要。
以下面的例子作为想法，因为我不太熟悉 Oracle，但它应该可以工作，可能需要一些小的修正）：

Create table set_definitions (setkey int, setentry int);
Create table searchFor (setentry int);

insert into set_definitions values (1,4);
insert into set_definitions values (2,1);
insert into set_definitions values (2,2);
insert into set_definitions values (3,1);
insert into set_definitions values (3,2);
insert into set_definitions values (3,3);

Insert Into searchFor Values (1);
Insert into searchFor Values (2);

With Prepare as 
(
Select setkey, LISTAGG(setentry, ',') WITHIN GROUP (ORDER BY setentry) as EntryList
  From set_definitions       
 Group by setkey
 Having Count(*)=(Select Count(*) From searchFor) -- Just to eliminate obvious ones 
)
Select setkey
  from Prepare 
 Where EntryList = (Select LISTAGG(setentry, ',')  WITHIN GROUP (ORDER BY setentry) From searchFor); 

Answer 3

您可以在每个集合和数字集合之间使用完整的外连接来查看它们是否相同。该函数执行以下操作：

function selectOrInsertSet(numbers number_tt) return number
is
  l_diff number;
  l_retval number;
begin
  for r in (select distinct setkey from set_definitions)
  loop
     with d as (select column_value from table(numbers)),
          s as (select setentry from set_definitions where setkey=r.setkey)
     select count(*)
     into   l_diff
     from   s
     full outer join d on d.column_value = s.setentry
     where s.setentry is null or d.column_value is null;

     if l_diff = 0 then
        l_retval := r.setkey;
        exit;
     end if;
  end loop;

  return l_retval;
end;

如果找到，则返回 setkey，否则返回 null。

我还没有实现关于创建新集合（如果没有找到）的部分，但这应该很容易。我个人不喜欢有副作用的函数（在本例中，将行插入表中）。