erp基础档案管理模块中实现多级分类档案级联删除技术
本存储过程实现了多级分类档案级联删除技术
本存储过程的特点是:
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 '[email protected] +' where vid = '''+ltrim(rtrim(@vsourceid))+''''
exec(@sqlstring)
if @@rowcount > 0
begin
set @sqlstring=n'select @idepth=idepth from '[email protected]+ ' 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 '[email protected] +' where vparentid in (select vid from #t where [email protected])'
while @@rowcount > 0
begin
set @[email protected]+1
exec sp_executesql @sqlstring,n'@idepth int',@idepth
end
set @sqlstring=n'delete from '[email protected] +' 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 '[email protected] +' 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 '[email protected] +' where vparentid in (select vid from #t where [email protected]) '
while @@rowcount > 0
begin
set @[email protected]+1
exec sp_executesql @sqlstring,n'@level int',@level
end
set @sqlstring=n'delete from '[email protected] +' 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 '[email protected] +' 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 '[email protected] +' 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 '[email protected] +' 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,
vparentid varchar(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,
vparentid varchar(30) null
)
另外用户如果要在sql查询分析器进行测试时可用如下方法进行测试(注意执行时三个语句一起执行):
declare @value int
exec prcdelete '002','customerclass',@[email protected] output
select @value
存储过程说明
n 本存储过程利用输出参数来说明级联删除操作是否成功,返回0表示成功,1表示失败
n 本存储过程利用事务来进行删除,确保操作安全和数据的完整性,数据完整性在这里指的是,若某一档案已经在外部引用过了,即其他表中的外关键字使用过了,则删除本结点或是包含这一结点的结点,都会进行事务控制,返回1,说明不允许删除
n 另外本存储过程对传入的结点进行审核,确定在本表中有这一结点时才进行接下来的删除操作,否则返回1,说明结点不存在或传入结点有误
