或多或少都做過樹狀目錄、產(chǎn)品分類之類的二級或三級菜單，如果遇到更多級的分類，就一般使用遞歸了。在程序中使用遞歸或多或少會增加一些性能上的開銷。

之前我用asp.net在程序中實(shí)現(xiàn)過非遞歸的無限級分類目錄，但考慮到移植性不強(qiáng)，就改成了存儲過程，發(fā)出來大家共同研究一下，到目前為止，測試過程中還沒發(fā)現(xiàn)問題，另外，代碼方面沒經(jīng)過什么優(yōu)化。

通常情況下，我們更多的操作是讀取目錄，所以，在下面的實(shí)現(xiàn)中，讀取我們只需要一select語句就夠了,不使用遞歸，理論上無限級~！
===================================================
表結(jié)構(gòu)：
表名：tb_column
表結(jié)構(gòu)（所有字段非空）：
column_id     int 主鍵（注：非標(biāo)識）
column_name   nvarchar(50)分類名稱
parent_id     int 父分類id（默認(rèn)值0）
column_path   nvarchar(1000) 分類路徑
column_depth  int分類深度(默認(rèn)值0）
column_order  int排序(默認(rèn)值0)
column_intro  nvarchar(1000)分類說明

================================================
存儲過程一：新建分類

create procedure sp_column_insert
(
@parent_id int,
@column_name nvarchar(50),
@column_intro nvarchar(1000)
)
as
declare @err as int
set @err=0

begin tran
--通過現(xiàn)有記錄獲取欄目id
declare @column_id as int
declare @column_depth as int
select @column_id = max(column_id) from tb_column
if @column_id is not null
set @column_id = @column_id+1
else
set @column_id = 1

--判斷是否是頂級欄目，設(shè)置其column_path和column_order
declare @column_path as nvarchar(1000)
declare @column_order as int
if @parent_id = 0
begin
set @column_path =ltrim(str(@column_id))
select @column_order = max(column_order) from tb_column
if @column_order is not null
set @column_order = @column_order + 1
else --如果沒有查詢到記錄，說明這是第一條記錄
set @column_order = 1

--深度
set @column_depth = 1
end
else
begin
--獲取父節(jié)點(diǎn)的路徑和深度
select @column_path = column_path ,@column_depth = column_depth from tb_column where

[email protected]_id
if @column_path is null
begin
set @err = 1
goto theend
end

--獲取同父節(jié)點(diǎn)下的最大序號
select @column_order = max(column_order) from tb_piccolumn where column_path like

''[email protected]_path+'|%'  or column_id = @parent_id
if @column_order is not null --如果序號存在，那么將該序號后的所有序號都加1
begin
--更新當(dāng)前要插入節(jié)點(diǎn)后所有節(jié)點(diǎn)的序號
update tb_column set column_order = column_order +1 where column_order

>@column_order
--同父節(jié)點(diǎn)下的最大序號加上1，構(gòu)成自己的序號
set @column_order = @column_order + 1
end
else
begin
set @err=1
goto theend
end

--父節(jié)點(diǎn)的路徑加上自己的id號，構(gòu)成自己的路徑
set @column_path = @column_path + '|' + ltrim(str(@column_id))

--深度
set @column_depth = @column_depth+1

end

insert into tb_column(column_id,column_name,parent_id,column_path,column_depth,column_order,column_intro)

values(@column_id,@column_name,@parent_id,@column_path,@column_depth,@column_order,@column_intro)

if @@error<>0
begin
set @err=1
goto theend
end

--更新當(dāng)前記錄之后的記錄的order
--update tb_column set column_order = column_order+1 where column_order  > @column_order

theend:
if @err=0
begin
commit tran
return @column_id
end
else
begin
    rollback tran
return 0
end
go

===================================================
存儲過程二：刪除分類
create procedure sp_column_delete
(
@column_id int
)
as
declare @err as int
set @err = 0
begin tran
--首先查詢該節(jié)點(diǎn)下是否有子節(jié)點(diǎn)
select column_id from tb_column where parent_id = @column_id
if @@rowcount<>0
    begin
    set @err = 1
    goto theend
    end

--獲取該節(jié)點(diǎn)的column_order,為了刪除后整理其他記錄的順序
declare @column_order as int
select @column_order = column_order from tb_column where column_id = @column_id
if @column_order is null
    begin
      set @err =2
      goto theend
    end

--更新其他記錄的column_order
update tb_column set column_order = column_order -1 where column_order >@column_order
if @@error<>0
    begin
      set @err =3
      goto theend
    end

--刪除操作
delete from tb_column where [email protected]_id
if @@error<>0
    begin
      set @err =4
      goto theend
  end

--更新其他記錄的column_id
--update tb_column set column_id= column_id - 1 where column_id >@column_id
--if @@error<>0
--    begin
--      set @err =5
--      goto theend
--    end

theend:
if @err = 0
    begin
      commit tran
      return 0 --刪除成功
    end
else
    begin
      if @err=1
  begin
      rollback tran
      return 1 --有子節(jié)點(diǎn)
end
      else
begin
      rollback tran
      return 2--未知錯(cuò)誤
end
    end
go
=============================================
存儲過程三：編輯分類
create procedure sp_column_update
(
@column_id int,
@parent_id int,
@column_name nvarchar(50),
@column_intro nvarchar(1000)
)
as
declare @err as int
set @err=0

begin tran

--獲取修改前的：parent_id,column_depth,column_order
declare @oparent_id as int
declare @ocolumn_depth as int
declare @ocolumn_order as int
declare @ocolumn_path as nvarchar(1000)

select @oparent_id = parent_id, @ocolumn_depth = column_depth,@ocolumn_order = column_order, @ocolumn_path = column_path  from tb_column where column_id = @column_id
if @oparent_id is null
    begin
    set @err = 1
    goto theend
    end

--如果父id沒有改變，則直接修改欄目名和欄目簡介
if @oparent_id = @parent_id
    begin
    update tb_column set column_name = @column_name,column_intro = @column_intro where column_id = @column_id
    if @@error <> 0
    set @err = 2
    goto theend
    end

declare @ncolumn_path as nvarchar(1000)
declare @ncolumn_depth as int
declare @ncolumn_order as int

--獲取當(dāng)前節(jié)點(diǎn)作為父節(jié)點(diǎn)所包含的節(jié)點(diǎn)數(shù)[包括自身] 注：如果返回 “1” 說明是單節(jié)點(diǎn)
declare @thecount as int
select @thecount = count(column_id) from tb_column where [email protected]_id or column_path like ''[email protected]_path+'|%'
if @thecount is null
begin
    set @err = 3
    goto theend
end

if @parent_id=0 --如果是設(shè)置為頂級節(jié)點(diǎn)，將節(jié)點(diǎn)設(shè)置為最后一個(gè)頂級節(jié)點(diǎn)
begin
--print '設(shè)置為頂級欄目'
set @ncolumn_path = ltrim(str(@column_id))
set @ncolumn_depth =1

select @ncolumn_order = max(column_order) from tb_column
if @ncolumn_order is null
                  begin
     set @err = 4
     goto theend
     end

set @ncolumn_order = @ncolumn_order - @thecount + 1

--更新三部分 1 節(jié)點(diǎn)本身 2 所有子節(jié)點(diǎn) 2 本樹更改之前的后面記錄的順序
--print '更新本欄目之前位置后面的所有欄目[不包括本欄目下的子欄目]的：column_order'
update tb_column set column_order = [email protected] where (column_order >@ocolumn_order) and (column_path not like ''[email protected]_path+'|%')
if @@error <> 0
    begin
    set @err = 7
    goto theend
    end

--print '更新本欄目的：parent_id,column_path,column_depth,column_order,column_name,column_intro'
print 'order : '+ltrim(str(@ncolumn_order))
update tb_column set [email protected]_id,column_path = @ncolumn_path,column_depth = @ncolumn_depth,column_order = @ncolumn_order, column_name = @column_name,column_intro = @column_intro where column_id = @column_id
if @@error <> 0
    begin
    set @err = 5
    goto theend
    end

--print '更新本欄目下的所有子欄目的：column_path,column_depth,column_order'
             update tb_column set column_path = replace(column_path,@ocolumn_path,@ncolumn_path),column_depth = column_depth + (@[email protected]_depth),column_order = column_order+( @[email protected]_order) where column_path like ''[email protected]_path+'|%'
if @@error <> 0
    begin
    set @err = 6
    goto theend
    end

end
else
begin
--獲取未來父節(jié)點(diǎn)的相關(guān)信息,并設(shè)置本節(jié)點(diǎn)的相關(guān)值
select @ncolumn_depth = column_depth,@ncolumn_path = column_path from tb_column where column_id = @parent_id
if @ncolumn_depth is  null or @ncolumn_path is null
      begin
      set @err = 8
      goto theend
      end
set @ncolumn_depth = @ncolumn_depth +1
select @ncolumn_order =max(column_order) from tb_column where column_id = @parent_id or  column_path like ''[email protected]_path+'|%'
if @ncolumn_order is  null
      begin
      set @err = 9
      goto theend
      end

set @ncolumn_path = @ncolumn_path +'|'+ ltrim(str(@column_id))

if @ncolumn_order = @ocolumn_order+1 --如果新的父節(jié)點(diǎn)是原來位置上端最近一個(gè)兄弟,則所有節(jié)點(diǎn)的順序都不改變
                    begin
update tb_column set [email protected]_id,column_path = @ncolumn_path,column_depth = @ncolumn_depth, column_name = @column_name,column_intro = @column_intro where column_id = @column_id
if @@error <> 0
    begin
    set @err = 10
    goto theend
    end
       end

set  @ncolumn_order = @ncolumn_order + 1

--更新三部分 1 本樹更改之前的后面（或前面）記錄的順序 1 節(jié)點(diǎn)本身  3 所有子節(jié)點(diǎn)
--分為向上移或象下移
--print '更新本欄目之前位置后面的所有欄目[或者本欄目之后位置]  [不包括本欄目下的子欄目]的：column_order'
if @ncolumn_order < @ocolumn_order
begin
update tb_column set column_order = [email protected] where column_order<@ocolumn_order  and column_order >[email protected]_order and (column_path not like ''[email protected]_path+'|%') and column_id<>@column_id
if @@error <> 0
        begin
        set @err = 12
        goto theend
        end
end
else
begin
update tb_column set column_order = [email protected] where column_order >@ocolumn_order and column_order<@ncolumn_order  and (column_path not like ''[email protected]_path+'|%') and column_id<>@column_id
if @@error <> 0
        begin
        set @err = 13
        goto theend
        end
end

--print '更新本欄目的：parent_id,column_path,column_depth,column_order,column_name,column_intro'
print 'order : '+ltrim(str(@ncolumn_order))
if @ncolumn_order > @ocolumn_order
set @ncolumn_order = @ncolumn_order - @thecount
update tb_column set [email protected]_id,column_path = @ncolumn_path,column_depth = @ncolumn_depth,column_order = @ncolumn_order, column_name = @column_name,column_intro = @column_intro where column_id = @column_id
if @@error <> 0
    begin
    set @err = 10
    goto theend
    end

--print '更新本欄目下的所有子欄目的：column_paht,column_depth,column_order'
             update tb_column set column_path = replace(column_path,@ocolumn_path,@ncolumn_path),column_depth = column_depth + (@[email protected]_depth),column_order = column_order+(@[email protected]_order) where column_path like ''[email protected]_path+'|%'
if @@error <> 0
    begin
    set @err = 11
    goto theend
    end
end

theend:
if @err<>0 --如果有錯(cuò)誤則返回錯(cuò)誤號
   begin
   rollback tran
   return @err
   end
else     --如果沒有錯(cuò)誤就返回0
   begin
   commit tran
   return 0
   end
go
=========================================
存儲過程四：顯示分類（只是一條select語句）
分類列表：
create procedure sp_column_list
 as
select column_id, column_name, parent_id, column_path, column_depth,
      column_order, column_intro
from tb_column
order by column_order
go

=======================================