经典树型表结构之SORT_NO

为什么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 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;
/

转载于:https://my.oschina.net/h2do/blog/268134