字段多对多分类问题，求大神帮助

abc_gdong · 发表于 2017-2-24 07:30

newkid 发表于 2017-2-24 05:55
分批commit是没有意义的。
此外不知道你的代码加在哪里，必须把完整的贴出来。

高亮部分是我修改的，代码如下：
create or replace procedure p1  is
TYPE t_str IS TABLE OF VARCHAR2(1000) INDEX BY tmp_replacement.child%TYPE;
parents t_str;

TYPE t_num IS TABLE OF NUMBER INDEX BY tmp_replacement.child%TYPE;
ranks t_num;

TYPE t_result IS TABLE OF tmp_replacement%ROWTYPE INDEX BY PLS_INTEGER;
v_result t_result;

parentX tmp_replacement.node%TYPE;
parentY tmp_replacement.node%TYPE;

rankX int;
rankY int;

last_child tmp_replacement.child%TYPE;

v_output t_str;

vstr tmp_replacement.node%TYPE;

FUNCTION find_parent(p_current IN tmp_replacement.node%TYPE)
RETURN tmp_replacement.node%TYPE
AS
BEGIN
   IF parents(p_current)<>p_current THEN
      parents(p_current) :=find_parent(parents(p_current));
   END IF;
   RETURN parents(p_current);
END find_parent;

BEGIN
SELECT node, child,null BULK COLLECT INTO v_result
   FROM (SELECT LEAST(node,child) node,GREATEST(node, child) child FROM tmp_replacement
         UNION SELECT LEAST(node,child),LEAST(node,child) FROM tmp_replacement
         );

FOR i IN 1..v_result.COUNT LOOP
      parents(v_result(i).node) := v_result(i).node;
      ranks(v_result(i).node) := 0;
END LOOP;

last_child :='*';

FOR cur IN (select LEAST(node, child) AS node, GREATEST(node, child) AS child from tmp_replacement
            UNION SELECT LEAST(node,child),LEAST(node,child) FROM tmp_replacement
            order by 2,1)
LOOP

      if cur.child <> last_child then
         -- find parent for x

         parentX := find_parent(cur.node);
         rankX := ranks(parentX);

         last_child := cur.child;
      ELSE

         -- find parent for y

         parentY := find_parent(cur.node);
         rankY := ranks(parentY);

         --- union x and y
         CASE
         WHEN parentX = parentY THEN
            continue;
         WHEN rankX < rankY THEN
            parents(parentX) := parentY;
            parentX := parentY;
            rankX := rankY;
         WHEN rankX > rankY THEN
            parents(parentY) := parentX;
         WHEN rankX = rankY THEN
            parents(parentY) := parentX;
            ranks(parentX) := ranks(parentX)+1;
         END CASE;
      END IF;

END LOOP;

FOR i IN 1..v_result.COUNT LOOP
      v_result(i).node := find_parent(v_result(i).node);
      update tmp_replacement set fz=v_result(i).node where node=v_result(i).child
      or child = v_result(i).child;
      IF (mod(i,2000)=0) THEN
      commit;
      END IF;
END LOOP;
commit;
END;