oracle 树型 排序,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;