本存储过程实现了多级分类档案 级联删除技术

本存储过程的特点是 :

n           可以在不同的数据库表上应用此存储过程 , 以 达到通用化

n           同时用户也可以根据不同的表结构 , 选择相应版本的的存储过程 , 在这里共有三个版本可供用户选择 , 分别对应表结构中有无节点的深度字段等情况

进行调用本存储过程时只需要传递节点号及表名即可 , 但是用户选择相应版本进行应用时 , 需要针对实际情况作出选择

版本一

CREATE procedure prcDelete(@vSourceID varchar(30),@Table varchar(20), @iOK int output)

as

declare @iDepth int

declare @SQLString nvarchar(500)

set @SQLString=N'select vID from '+@Table +' where vID = '''+ltrim(rtrim(@vSourceID))+''''

exec(@SQLString)

if @@rowcount > 0

begin

      set @SQLString=N'select @iDepth=iDepth from '+@table+ ' where vID = '''+ltrim(rtrim(@vSourceID))+''''

         exec sp_executesql @SQLString,N'@iDepth int output',@iDepth output

         select @vSourceID as vID,@iDepth as iDepth into #t

         set @SQLString=N'insert #t select vID,iDepth from '+@Table +' where vParentID in (select vID from #t where iDepth=@iDepth-1)'

         while @@rowcount > 0

         begin

                   set @iDepth=@iDepth+1

           exec sp_executesql @SQLString,N'@iDepth int',@iDepth

         end

         set @SQLString=N'delete from '+@Table +' where vID in (select vID from #t) '

         begin tran

         exec sp_executesql @SQLString

         if @@error<>0 goto Err

         commit

         select @iOK=0

         return

         Err:

            rollback

            select @iOK=1

end

else

begin

         select @iOK=1

end

 

版本二

CREATE procedure prcDelete(@vSourceID varchar(30),@Table varchar(20), @iOK int output)

as

declare @level int

declare @SQLString nvarchar(500)

set @SQLString=N'select vID from '+@Table +' where vID = '''+ltrim(rtrim(@vSourceID))+''''

exec(@SQLString)

if @@rowcount > 0

begin

         set @level=0

         select @vSourceID as vID,@level as level into #t

         set @SQLString=N'insert #t select vID,@level from '+@Table +' where vParentID in (select vID from #t where level=@level-1) '

         while @@rowcount > 0

         begin

                   set @level=@level+1

           exec sp_executesql @SQLString,N'@level int',@level

         end

         set @SQLString=N'delete from '+@Table +' where vID in (select vID from #t) '

         begin tran

         exec sp_executesql @SQLString

         if @@error<>0 goto Err

         commit

         select @iOK=0

         return

         Err:

             rollback

            select @iOK=1

end

else

begin

         select @iOK=1

end

 

版本三

CREATE procedure prcDelete(@vSourceID varchar(30),@Table varchar(20), @iOK int output)

as

declare @vParentID varchar(30)

declare @SQLString nvarchar(500)

set @SQLString=N'select vID from '+@Table +' where vID = '''+ltrim(rtrim(@vSourceID))+''''

exec(@SQLString)

if @@rowcount > 0

begin

         select @vSourceID as vID,@vParentID as vParentID into #t

         set @SQLString=N'insert #t select vID,vParentID from '+@Table +' where vParentID in (select vID from #t) and vID not in(select vID from #t)'

         while @@rowcount > 0

           exec(@SQLString)

         set @SQLString=N'delete from '+@Table +' where vID in (select vID from #t) '

         begin tran

         exec sp_executesql @SQLString

         if @@error<>0 goto Err

         commit

         select @iOK=0

         return

         Err:

            rollback

            select @iOK=1

end

else

begin

         select @iOK=1

end

 

三种版本的比较

版本一适合以下表结构

create table CustomerClass(

vID    varchar(30) constraint pkCustomerClass primary key ,

vCustomerClassName varchar(40) NOT NULL,

vRemarks         varchar(80) NULL,

vParentIDvarchar(30) NULL,

iDepth       Int NOT NULL

)

版本二和版本三适合以下表结构

create table CustomerClass(

vID    varchar(30) constraint pkCustomerClass primary key ,

vCustomerClassName varchar(40) NOT NULL,

vRemarks         varchar(80) NULL,

vParentIDvarchar(30) NULL

)

另外用户如果要在 SQL 查询分析器进行测试时可用如下方法进行测试 ( 注意执行时三个语句一起执行 ) :

declare @value int

exec prcDelete '002','CustomerClass',@iOK=@value output

select @value

 

存储过程说明

n          本存储过程利用输出参数来说明级联删除操作是否成功 , 返回 0 表示成功 ,1 表示失败

n          本存储过程利用事务来进行删除 , 确保操作安全和数据的完整性 , 数据完整性在这里指的是 , 若某一档案已经在外部引用过了 , 即其他表中的外关键字使用过了 , 则删除本结点或是包含这一结点的结点 , 都会进行事务控制 , 返回 1, 说明不允许删除

n          另外本存储过程对传入的结点进行审核 , 确定在本表中有这一结点时才进行接下来的删除操作 , 否则返回 1, 说明结点不存在或传入结点有误

Copyright 2003-2005 www.sohozu.com All Rights Reserved