本存儲(chǔ)過程實(shí)現(xiàn)了多級(jí)分類檔案 級(jí)聯(lián)刪除技術(shù)

本存儲(chǔ)過程的特點(diǎn)是 :

n ????????? 可以在不同的數(shù)據(jù)庫(kù)表上應(yīng)用此存儲(chǔ)過程 , 以 達(dá)到通用化

n ????????? 同時(shí)用戶也可以根據(jù)不同的表結(jié)構(gòu) , 選擇相應(yīng)版本的的存儲(chǔ)過程 , 在這里共有三個(gè)版本可供用戶選擇 , 分別對(duì)應(yīng)表結(jié)構(gòu)中有無節(jié)點(diǎn)的深度字段等情況

進(jìn)行調(diào)用本存儲(chǔ)過程時(shí)只需要傳遞節(jié)點(diǎn)號(hào)及表名即可 , 但是用戶選擇相應(yīng)版本進(jìn)行應(yīng)用時(shí) , 需要針對(duì)實(shí)際情況作出選擇

版本一

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

?

三種版本的比較

版本一適合以下表結(jié)構(gòu)

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

)

版本二和版本三適合以下表結(jié)構(gòu)

create table CustomerClass(

vID??? varchar(30) constraint pkCustomerClass primary key ,

vCustomerClassName varchar(40) NOT NULL,

vRemarks???????? varchar(80) NULL,

vParentIDvarchar(30) NULL

)

另外用戶如果要在 SQL 查詢分析器進(jìn)行測(cè)試時(shí)可用如下方法進(jìn)行測(cè)試 ( 注意執(zhí)行時(shí)三個(gè)語句一起執(zhí)行 ) :

declare @value int

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

select @value

?

存儲(chǔ)過程說明

n????????? 本存儲(chǔ)過程利用輸出參數(shù)來說明級(jí)聯(lián)刪除操作是否成功 , 返回 0 表示成功 ,1 表示失敗

n????????? 本存儲(chǔ)過程利用事務(wù)來進(jìn)行刪除 , 確保操作安全和數(shù)據(jù)的完整性 , 數(shù)據(jù)完整性在這里指的是 , 若某一檔案已經(jīng)在外部引用過了 , 即其他表中的外關(guān)鍵字使用過了 , 則刪除本結(jié)點(diǎn)或是包含這一結(jié)點(diǎn)的結(jié)點(diǎn) , 都會(huì)進(jìn)行事務(wù)控制 , 返回 1, 說明不允許刪除

n????????? 另外本存儲(chǔ)過程對(duì)傳入的結(jié)點(diǎn)進(jìn)行審核 , 確定在本表中有這一結(jié)點(diǎn)時(shí)才進(jìn)行接下來的刪除操作 , 否則返回 1, 說明結(jié)點(diǎn)不存在或傳入結(jié)點(diǎn)有誤

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