本存储过程实现了多级分类档案
级联删除技术
本存储过程的特点是
:
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
posted on 2006-09-12 13:32
SIMONE 阅读(250)
评论(0) 编辑 收藏