最级分类(非递归算法/存储过程版/GUID主键)完整数据库示例_(3)删除记录。无限级分类(非递归算法/存储过程版/GUID主键)完整数据库示例_(2)插入记录。

— =============================
— Author:  <杨俊明,Jimmy.yang@cntvs.com or yjmyzz@126.com>
— Description: <无限级分类_剔除分类>
— Return     : 0勾正常,-1父类下还来子类
— ===========================
Alter PROCEDURE [dbo].[up_Class_DeleteEx]
 @clsId uniqueidentifier, –要去的归类
 @withChild bit     –是否级联删除子类
AS
BEGIN
 SET NOCOUNT ON;
 Declare @so_MaxOrders int;
 Declare @s_Childs int;
 Declare @clsType Nvarchar(50);
 Declare @O_RootId int;
 
 Select @O_RootId = F_RootId,@clsType = F_Type From T_Class where
F_ID=@clsId;

— ========================================
— Author:  <杨俊明,jimmy.yang@cntvs.com>
— Description: <无限级分类插入记录>
— Return     : 成功返回回0,重名返回1
— ========================================
Create PROCEDURE [dbo].[up_Class_InsertEx]
 @newId uniqueidentifier, –新记录Id 
 @classType nvarchar(50), –类型(比如:产品,新闻,地区)
 @parentId uniqueidentifier, –父类Id
 @className nvarchar(50), –分类名
 @classReadMe nvarchar(200) –分类说明 
AS
BEGIN 
 SET NOCOUNT ON; 
 Declare @RootID int;     –根ID(顶级分类的RootID)
 DeClare @ParentName nvarchar(500);  –父类名称
 Declare @Depth int;      –父类深度
 Declare @MaxOrders int;     –同级最特别排序号
 Declare @ParentIdStr nvarchar(500);  –父类Id全路径
 Declare @ParentNameStr nvarchar(500); –父类名称均路径 
 
 –如果是顶级类
 if @parentId=’00000000-0000-0000-0000-000000000000′
 begin
  if not exists(select F_id From T_Class where
F_ClassName=@className and F_type=@ClassType and
F_parentid=’00000000-0000-0000-0000-000000000000′)  
  begin
   set @RootID = 0   
   if exists(select F_id From T_Class Where F_type=@ClassType and
F_parentid=’00000000-0000-0000-0000-000000000000′)
   begin
    select @RootId = max(F_RootID) From T_Class Where
F_type=@ClassType and
F_parentid=’00000000-0000-0000-0000-000000000000′;–得到时顶级分类的极致酷RootId    
   end 
        
   insert into T_Class(
    F_Id,
    F_Type,
    F_parentId,
    F_className,
    F_ReadMe,
    F_parentIdstr,
    F_parentNameStr,
    F_RootId,
    F_orders)
   values(
    @newId,
    @classType,
    ‘00000000-0000-0000-0000-000000000000’,
    @className,
    @classReadMe,
    ‘00000000-0000-0000-0000-000000000000’,
    ‘00000000-0000-0000-0000-000000000000’,
    @rootId+1,
    0)
   return 0;–顶级分类成功插入
  end
  else
   return 1;–顶级分类就有
 end

 If (@WithChild=1)  
  begin
   Select @so_maxOrders=max(F_orders) From T_class Where
F_ID=@clsId or F_ParentIdStr like ‘%’ + Convert(varchar(50),@clsId) +
‘%’; –得到我有关的太酷排序号
   Select @s_Childs= Count(F_ID) from t_Class Where F_ID=@clsId or
F_ParentIdStr like ‘%’ + Convert(varchar(50),@clsId) + ‘%’;
   Delete from T_Class where ‘,’+F_parentIdStr+’,’ like ‘%’ +
convert(varchar(100),@clsId) + ‘%’
   Delete from T_Class Where F_Id=@ClsId 
   Update T_Class Set F_Orders = F_Orders – @s_childs where
F_Type=@clsType And F_RootId=@O_RootId And F_Orders >
@so_maxOrders;
   return 0
  end
 else  
  –如果未级联删除,又有子节点,则归-1 
  If exists(select F_id from T_Class where ‘,’ + F_parentIdStr + ‘,’
like ‘%’ + convert(varchar(100),@clsId) + ‘%’)  
   return -1
  else
   begin
    Select @so_maxOrders=max(F_orders) From T_class Where
F_ID=@clsId or F_ParentIdStr like ‘%’ + Convert(varchar(50),@clsId) +
‘%’; –得到我相关的无限深排序号
    Select @s_Childs= 1;
    Delete from T_Class Where F_Id=@ClsId;
    Update T_Class Set F_Orders = F_Orders – @s_childs where
F_Type=@clsType And F_RootId=@O_RootId And F_Orders >
@so_maxOrders;
    return 0
   end
   
END

 –如果不是顶级类
 if @parentId<>’00000000-0000-0000-0000-000000000000′   
 begin
  –检查父类ID的合法性
  if exists(select F_id From T_Class Where F_id=@parentID)  
  begin
   –检查该节点是否曾经在  
   if not exists(select F_id From T_Class where
F_ClassName=@className and F_type=@ClassType and
F_parentid=@parentId)   
   begin      
    select
@rootId=F_RootId,@ParentName=F_ClassName,@Depth=F_Depth,@parentIdStr=F_parentIdStr,@parentNameStr=F_parentNameStr
From T_Class Where F_Id = @ParentId;    
    set @maxOrders = 0
    –如果父类无子类
    if not exists(select F_id From T_Class where F_ParentIdStr like
‘%’ + Convert(Nvarchar(50),@parentId) + ‘%’)     
     select @maxOrders = F_orders From T_Class where
F_id=@parentId    
    else
     select @maxOrders = Max(F_orders) From T_Class where
F_ParentIdStr like ‘%’ + Convert(Nvarchar(50),@parentId) +
‘%’           
           

    insert into T_Class(
     F_ID,
     F_Type,
     F_parentId,
     F_className,
     F_ReadMe,
     F_parentIdstr,
     F_parentNameStr,
     F_Orders,
     F_Depth,
     F_RootId  
     )
    values(
     @newId,
     @classType,
     @parentId,
     @className,
     @classReadMe,
     @parentIdStr + ‘,’ + convert(nvarchar(50),@parentID),
     @parentNameStr + ‘,’ + @parentName,
     @maxOrders + 1,
     @depth + 1,
     @rootId
     )
 
    update T_class set F_orders = F_orders + 1 where (F_orders >
@maxOrders) And (F_RootId=@RootId) And F_ID<>@NewId 
 
    
    return 0;  
   end
   else
    return 1;
  end
 end 
END

相关文章