圖示如下：所使用數據庫 ： sql server 2000，數據表名：te（亂七八糟命名的）表結構： varchar(20) varchar(20) decimal(20,4) assbompoint sl 產成品半成品12 產成品半成品2 3 產成品半成品3 2 半成品1半成品4 2 半成品1半成品5 2 半成品3半成品5 3
最終需要輸出結果
assbompoint sl 產成品 半成品23 產成品 半成品44 產成品 半成品513
即一單位產成品需要底級半成品的數量以及半成品的名稱。比如說半成品4=1單位產成品*2單位半成品1*2單位半成品4。
存儲過程如下：（呵呵，命名就別講究了，我沒有太過于留意）
create procedure getbomtruelist
@assbomname varchar(20),@pointname varchar(20),@expre varchar(500)
as
begin
declare @sl decimal(20,4)
declare @expression varchar(500)
declare @point varchar(20)
declare @bomtop varchar(20)
declare #point_cursor cursor local for
select distinct point,sl from te where assbom = @pointname
open #point_cursor
fetch next from #point_cursor
into @point,@sl
while @@fetch_status = 0
begin
--如果沒有下級節點了，就加入到數據表中
if(exists(select point from te where assbom = @point))
begin
--如果有下級節點，則再次循環
select @expression = rtrim(@expre) + '*' + rtrim(convert(char(25),@sl))
exec('getbomtruelist ' + @assbomname + ',' + @point + ',"' + @expression + '"')
end
else
begin
insert into #bomtemp values (@assbomname,@point,@sl,@expre + '*' + ltrim(rtrim(convert(char(25),@sl))))
end
fetch next from #point_cursor
into @point,@sl
end
close #point_cursor
deallocate #point_cursor
end
go
-------------------------------------------------------------------------------------------------------
create procedure getbomlist
as
begin
create table #bomtemp (assbom varchar(20),point varchar(20),sl decimal(20,4),expression varchar(500))
create table #bomlast (assbom varchar(20),point varchar(20),sl decimal(20,4))
--調用遞歸
declare @bomtop varchar(20)
declare bom_cursor cursor for
select distinct assbom from te where assbom not in (select point from te)
open bom_cursor
fetch next from bom_cursor
into @bomtop
while @@fetch_status = 0
begin
exec('getbomtruelist ' + @bomtop +','[email protected]+','+'1')
fetch next from bom_cursor
into @bomtop
end
close bom_cursor
deallocate bom_cursor
--獲得到數據及運算表達式后，再次利用數據表中的表達式運算一下
declare @assbomt varchar(20)
declare @point varchar(20)
declare @expression varchar(500)
declare @value decimal(20,4)
declare @execupdate varchar(500)
declare bom_cursor_end cursor for
select assbom,point,expression from #bomtemp
open bom_cursor_end
fetch next from bom_cursor_end
into @assbomt,@point,@expression
while @@fetch_status = 0
begin
set @execupdate = 'insert into #bomlast values(' + char(39) + rtrim(@assbomt) + char(39) + ',' + char(39) + rtrim(@point) + char(39) + ',' + rtrim(@expression) + ')'
--set @execupdate = 'update #bomtemp set sl = ' + rtrim(@expression) + ' where assbom = ' + char(39) + rtrim(@assbomt) + char(39) + ' and point = ' + char(39) + rtrim(@point) + char(39)
exec(@execupdate)
fetch next from bom_cursor_end
into @assbomt,@point,@expression
end
close bom_cursor_end
deallocate bom_cursor_end
select assbom,point,sum(sl) as sl from #bomlast group by assbom,point
drop table #bomtemp
drop table #bomlast
end
-------------------------------------------------------------------------
設計思路：
首先第一步就是得到頂級的產成品，由于在測試的表中有多個產成品，因此需要對頂級的產成品加一個游標，一行行處理。
得到一個產成品后，再去獲取其下級的半成品，并且得到需要的半成品數量，將其數量先設成一個表達式，如1*3，如果半成品還有半成品，那只有對該半成品作同樣的處理了，注意，這里面就傳入了一個頂級的產成品，最終就可以得到頂級產成品到底級產成品的關系了，最終會獲取一個表達式。
將所有的關系存入到數據表中后，需要再進一步處理，就是計算表達式，得到數值為多少。
由于原表設計時沒有考慮到主鍵，因此使用update語句可能會造成計算錯誤，沒有什么好的辦法，只有加入一個新的臨時表，一行行往里面加入記錄。
最后對新表執行一次group by操作，就是想要的結果了。
-------------------------------------------------------------------------------------
問題：
該存儲過程在vb里面沒有辦法使用，不知道為什么，也不知道這是不是ado的一個bug，就是在select * from 臨時表時，recordset總是返回錯誤說什么對象關閉時不允許操作。碰到這樣的問題也比較煩，因此我最終想出一個辦法就是加入一個永久的表，表里面增加一個列為hostid，用hostid來標識操作者的機器，最終返回hostid相關的值。
但在c#里面測試一切正常。
------------------------------------------------------------------------------------
在設計過程中碰到的麻煩
1、游標問題：在第一個存儲過程中有一個紅色的標記local，當時編寫時沒有加入該標識，總是提示游標已創建。加入后，問題解決。
2、表達式問題：在構建一個表達式后，需要進行計算，在sql server里面似乎沒有公式可以計算表達式，然后再返回值的。最終不得不再加入一個游標來進行處理。
3、存儲過程中執行存儲過程的問題：很是奇怪，為什么不能直接寫入存儲過程的名稱，而必須使用exec？也是郁悶了許久。
-------------------------------------------------------------------------------------
相關的csdn中的貼子鏈接
點擊此處
