17,090
社区成员
发帖
与我相关
我的任务
分享
create table t(
org_item varchar(10),
new_item varchar(10)
);
insert into t values('a','d');
insert into t values('b','c');
insert into t values('b','d');
insert into t values('x','y');
declare
v_group_id number;
v_org_item number;
v_new_item number;
v_insert_group_id number;
v_insert_item_id number;
begin
delete t2;
delete t_result;
insert into t2
select org_item, new_item, rownum from t;
loop
if v_group_id is not null then
select max(group_id), max(new_item)
into v_insert_group_id, v_insert_item_id
from (select a.group_id, new_item
from t2 a, t_result b, t_result c
where a.org_item = b.item
and a.new_item = c.item(+)
and b.group_id = v_group_id
and c.item is null
union
select a.group_id, org_item
from t2 a, t_result b, t_result c
where a.new_item = b.item
and a.org_item = c.item(+)
and b.group_id = v_group_id
and c.item is null) a
where rownum = 1;
insert into t_result
select v_insert_item_id, v_group_id
from dual
where v_insert_item_id is not null;
delete t2 where group_id = v_insert_group_id;
end if;
if v_group_id is null or sql%rowcount < 1 then
select max(group_id), max(org_item), max(new_item)
into v_group_id, v_org_item, v_new_item
from t2
where rownum = 1;
if v_group_id is not null then
delete t2 where group_id = v_group_id;
insert into t_result values (v_org_item, v_group_id);
insert into t_result values (v_new_item, v_group_id);
end if;
end if;
exit when v_group_id is null;
end loop;
end;