1.SQL遞歸
在SQL Server中,我們可以利用表表達式來實現遞歸算法,一般用於阻止機構的加載及相關性處理。
-->實現:
假設OrganiseUnit(組織機構表)中主要的三個字段為OrganiseUnitID(組織機構主鍵ID)、ParentOrganiseUnitID(組織機構父ID)、OrganiseName(組織機構名稱)
復制代碼 代碼如下:
with organise as
(select * from OrganiseUnit where OrganiseUnit.OrganiseUnitID = @OrganiseUnitID
union all select OrganiseUnit.* from organise, OrganiseUnit
where organise.OrganiseUnitID = OrganiseUnit.ParentOrganiseUnitID)
select OrganiseName from organise
上述sql語句實現了, 傳入組織機構主鍵ID,查詢出其對應組織機構名稱和其 全部下級組織機構名稱。
2.存儲過程中 In 參數傳遞
-->情景
① 通過剛才的SQL遞歸方式,我們已經可以將一個組織機構和其全部下級單位查詢出來;假設每個組織機構還有一個字段為OrganiseCode(組織機構代碼);
② 當我們需要按照組織機構代碼進行篩選數據時,我們會用到 In 這個查詢條件,例如select * from OrganiseUnit where OrganiseCode in ('10000001','10000003','10000002')
③但是in()中條件不可能總是固定不變的,有時我們需要用參數傳遞進去;我們可能會想到設定一個變量參數@OrganiseCode,然後按照'10000001','10000003','10000002'的格式拼參數不就行了嗎 ?
④in使用參數時會強制轉換參數類型與條件字段一致,不支持構造字符串(如果字段本身為varchar、char型,則in相當於只有一個條件值,而不是一組)
-->實現
①可以使用exec,把整個sql當做參數來執行,例如:exec ('select * from OrganiseUnit where OrganiseCode in ('+@OrganiseCode+')');這樣存儲過程修改復雜,沒有防注功能。
②我們采用另一種方案來解決,先寫一個SQL函數,功能是分割字符串
復制代碼 代碼如下:
create function SplitIn(@c varchar(2000),@split varchar(2))
returns @t table(col varchar(20))
as
begin
while(charindex(@split,@c)<>0)
begin
insert @t(col) values (substring(@c,1,charindex(@split,@c)-1))
set @c = stuff(@c,1,charindex(@split,@c),'')
end
insert @t(col) values (@c)
return
end
我們為這個函數傳入字符串和分隔符,他就能將字符串按指定符號分割並作為查詢結果返回。
例如:執行select col from SplitIn('10000001,10000002,10000003',',')
返回:
10000001
10000002
10000003
③有了這個函數,我們就有了新的解決方案了
定義參數@OrganiseCode,為其傳入字符串,這個參數由一個或多個OrganiseCode構成,中間用“,”分割;
調用方式:select * from OrganiseUnit where OrganiseCode in (select col from SplitIn(@OrganiseCode,','))