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,說明結點不存在或傳入結點有誤
