在以下情况需要对经典树型表的sort_no进行重排序:
1、插入节点(插入子树),需调整节点后所有sort_no;
2、删除节点(删除子树),需调整节点后所有sort_no;
3、调整同级节点sub_sort,特别是调整level高的节点,几乎可影响到整棵树;
--update T_TREE set sort_no = null;
call UTIL_E2SAY.sortTree('T_TREE',1);
排序工具包代码:
create or replace package UTIL_E2SAY is
--对指定库表从某个id开始设置sort_no排序号
procedure sortTree(p_TabName in varchar2,
p_StartId in number,
p_SqlCondition in varchar2 default null);
end;
/
create or replace package body UTIL_E2SAY is
procedure sortTree(p_TabName in varchar2,
p_StartId in number,
p_SqlCondition in varchar2 default null) is
type t_Tab is table of number index by binary_integer;
type t_Cur is ref cursor;
m_SqlCondition varchar2(2000) := '';
m_SqlConditionWhere varchar2(2000) := '';
m_SortNo number(6) := 0;
m_ParentIdTab t_Tab;
v_NumCur t_Cur;
v_Id number;
procedure sortChild(p_StartId in number) is
v_IdCur t_Cur;
begin
execute immediate
'update '||p_TabName||' set sort_no = :sort_no where id = :id'||m_SqlCondition
using m_SortNo, p_StartId;
m_SortNo := m_SortNo + 1;
if m_ParentIdTab.exists(p_StartId) then
open v_IdCur for
'select id' ||
' from ' || p_TabName ||
' where pid = :pid' || m_SqlCondition ||
' order by sub_sort'
using p_StartId;
loop
fetch v_IdCur into v_Id;
exit when v_IdCur%notfound;
sortChild(v_Id);
end loop;
close v_IdCur;
end if;
end sortChild;
begin
if p_SqlCondition is not null then
m_SqlCondition := ' and ' || p_SqlCondition;
m_SqlConditionWhere := ' where ' || p_SqlCondition;
end if;
open v_NumCur for
'select sort_no from '||p_TabName||' where id = :id'||m_SqlCondition
using p_StartId;
fetch v_NumCur into m_SortNo;
close v_NumCur;
if m_SortNo = 0 or m_SortNo is null then
m_SortNo := 1;
end if;
open v_NumCur for
'select distinct pid from '||p_TabName||m_SqlConditionWhere;
loop
fetch v_NumCur into v_Id;
exit when v_NumCur%notfound;
m_ParentIdTab(v_Id) := 1;
end loop;
close v_NumCur;
sortChild(p_StartId);
end;
end;
/