树(目录)结构处理

表

if   exists  ( select   *   from  dbo.sysobjects  where  id  =   object_id (N ' [dbo].[CateEx] ' )  and   OBJECTPROPERTY (id, N ' IsUserTable ' )  =   1 )
drop   table   [ dbo ] . [ CateEx ]
GO

CREATE   TABLE   [ dbo ] . [ CateEx ]  (
     [ ID ]   [ varchar ]  ( 30 ) COLLATE Chinese_PRC_CI_AS  NOT   NULL  ,
     [ SysID ]   [ int ]   NOT   NULL  ,
     [ Name ]   [ varchar ]  ( 100 ) COLLATE Chinese_PRC_CI_AS  NOT   NULL  ,
     [ OrderID ]   [ varchar ]  ( 30 ) COLLATE Chinese_PRC_CI_AS  NOT   NULL  
)  ON   [ PRIMARY ]
GO

ALTER   TABLE   [ dbo ] . [ CateEx ]   WITH   NOCHECK   ADD  
     CONSTRAINT   [ PK_CateEx ]   PRIMARY   KEY    CLUSTERED  
    (
         [ ID ]
    )   ON   [ PRIMARY ]  
GO

ALTER   TABLE   [ dbo ] . [ CateEx ]   ADD  
     CONSTRAINT   [ DF_CateEx_SysID ]   DEFAULT  ( 0 )  FOR   [ SysID ] ,
     CONSTRAINT   [ DF_CateEx_OrderID ]   DEFAULT  ( 0 )  FOR   [ OrderID ] ,
     CONSTRAINT   [ IX_CateEx ]   UNIQUE    NONCLUSTERED  
    (
         [ OrderID ]
    )   ON   [ PRIMARY ]  
GO

 操作

CREATE    Procedure  AdminRole_Add
(
  @FathID   varchar ( 30 ) = ' 0 ' ,  -- 父接点
  @Name   varchar ( 100 ), -- 接点名称
  @Type   bit = 0 ,
  @AreaID   int = 0
)
as
begin
  if ( Len ( @FathID ) < 3 )
  set   @FathID = ''
  Declare   @MID   varchar ( 30 )
  Declare   @SOID   varchar ( 30 )
      select   @MID = ID, @SOID = ORderID  from  AdminRole  where  ID  like   @FathID + ' % '   and   Len (ID) = Len ( @FathID ) + 3   order   by  ID 
     if ( @MID   is   not   null )     -- 已有子接点
     begin
         set   @MID = Convert ( bigint , @MID ) + 1   -- ID加1
         set   @SOID = convert ( bigint , @SOID ) + 1   -- orderID加1
     end   
     else   -- 创建新的子接点
     begin
         select   @MID = ID, @SOID = ORderID  from  AdminRole  where  ID  =   @FathID   -- 获取父接点
         if ( @MID   is   not   null )     -- 父接点存在
         begin
             set   @MID = @FathID + ' 101 '      -- 在父接点上加101
             set   @SOID = @SOID + ' 101 '      -- 在父排序上加101
         end  
         else      -- 父接点不存在
         begin
             set   @MID = ' 101 '
             set   @SOID = ' 101 '
         end
     end
  insert   into  AdminRole(ID,Name,OrderID,Type,AreaID)  Values ( @MID , @Name , @SOID , @Type , @AreaID )
end  


GO
-- 接点的左右移动
CREATE    Procedure  AdminRole_Move
(
@OrderID   varchar ( 30 ), -- 顺序号
@Oper   int   -- 移动方向 大于0上移、反之下移
)
as
Declare   @SOID   varchar ( 30 )   -- -要交换的orderID
if ( @Oper > 0 )  -- 大于零向上移
select   @SOID = orderID  from  AdminRole  where    Len (ID) = Len ( @OrderID )  and   Left (orderID, len (orderID) - 3 ) = Left ( @OrderID , len ( @OrderID ) - 3 )  and    OrderID < @OrderID   order   by   OrderID
else
select   @SOID = orderID  from  AdminRole  where    Len (ID) = Len ( @OrderID )  and   Left (orderID, len (orderID) - 3 ) = Left ( @OrderID , len ( @OrderID ) - 3 )  and    OrderID > @OrderID   order   by   OrderID   desc

-- print @SID
if ( @SOID   is   not   null )     -- 如果记录存在
begin
   select  ID   into   #TempID  from  AdminRole   where  OrderID  like   @SOID + ' % '   -- 选出要交换的IDXU序列
   update  AdminRole  set   OrderID = Replace (OrderID, @OrderID , @SOID )  where  OrderID  like   @OrderID + ' % '   -- 更改要操作的记录
   update  AdminRole  set   OrderID = Replace (OrderID, @SOID , @OrderID )  where  ID  in  ( select  ID  from  #TempID)     -- 更改向临的记录
   drop   table  #TempID
end



GO
Create   Procedure  AdminRole_Del
(
  @GroupID   Varchar ( 30 )
)
as
delete   from  AdminRole  where  ID  like   @GroupID + ' % '
UPdate  AdminUser  set  GroupID = 0   where  GroupID  like   @GroupID + ' % '


GO