oracle树形结构[已有数据]给同级节点,设置一个排序值

oracle 的 START WITH ID=#{parentId,jdbcType=NUMERIC} CONNECT BY PARENT_ID = PRIOR ID) 使用非常方便。

在一次的数据迁移的时候,同级节点的默认排序值没有。

create or replace procedure tmp_news_category is
i number(11);
k number(11);
rootCount number(11);
categoryId number(19);
tmpId number(19);
allCount number(11);
countCateory number(11);
begin
       select count(id) into allCount from news_category;
       k:=0;
        tmpId:=0;
        countCateory:=0;
       FOR k IN 1..allCount LOOP
           BEGIN
                     select id into tmpId from news_category where id>tmpId and ROWNUM <= 1 ORDER BY id ASC ;
                     select count(id) into rootCount from news_category where parent_id=tmpId;
                     i:=0;
                     if rootCount >0 then
                       categoryId:=0;
                       FOR i IN 1..rootCount LOOP
                         BEGIN
                             select count(id) into countCateory from news_category where parent_id=tmpId and id>categoryId and ROWNUM <= 1 ORDER BY id ASC ;
                             if(countCateory>0) then
                                    select id into categoryId from news_category where parent_id=tmpId and id>categoryId and ROWNUM <= 1 ORDER BY id ASC ;
                                    if categoryId>0 then
                                       update news_category set ORDER_BY = i where id=categoryId;
                                    end if;
                             end if;
                         END;
                       END LOOP;
                      end if;
            END;
         END LOOP;
         update news_category set ORDER_BY = 1 where id=1000;
         update news_category set ORDER_BY = 2 where id=2000;
end tmp_news_category;



草木全
分享到:
共 0 条  此列表为空  当前1/1页

© 2014 究问社区 copyRight 豫ICP备13003319号-1