为什么80%的码农都做不了架构师？>>> 
在以下情况需要对经典树型表的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 isprocedure sortTree(p_TabName in varchar2,p_StartId in number,p_SqlCondition in varchar2 default null) istype 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) isv_IdCur t_Cur;beginexecute immediate'update '||p_TabName||' set sort_no = :sort_no where id = :id'||m_SqlConditionusing m_SortNo, p_StartId;m_SortNo := m_SortNo + 1;if m_ParentIdTab.exists(p_StartId) thenopen v_IdCur for'select id' ||' from ' || p_TabName ||' where pid = :pid' || m_SqlCondition ||' order by sub_sort'using p_StartId;loopfetch v_IdCur into v_Id;exit when v_IdCur%notfound;sortChild(v_Id);end loop;close v_IdCur;end if;end sortChild;beginif p_SqlCondition is not null thenm_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_SqlConditionusing p_StartId;fetch v_NumCur into m_SortNo;close v_NumCur;if m_SortNo = 0 or m_SortNo is null thenm_SortNo := 1;end if;open v_NumCur for'select distinct pid from '||p_TabName||m_SqlConditionWhere;loopfetch 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;
/
