digui.sql

实现递归功能

---------------------------------三思的递归------------------------------
------------------------------------------------------------------------
ALTER  procedure prcRepertories_id @KC_ID int,@KC_ZL decimal(18,2)
as
begin
	declare @PM_ID int--配煤ID
	select @PM_ID = PM_ID from MixCoalInfo where PM_KCID = @KC_ID--在配煤信息表中根据库存ID查询到对应的配煤ID，以便下面查询配方
	if @PM_ID is null--此库存非配煤
	begin
		update Repertories--更新库存
		set KC_ZL = KC_ZL + @KC_ZL
		where KC_ID = @KC_ID
	end
	else
	begin
	/*********************将配方整合成字符串***************************/
		--if (select PM_ZT from MixCoalInfo where PM_ID = @PM_ID) = 0
			--raiserror('该配煤已停用', 16, 1)
		declare @sumRate decimal(18,2) --总比例数值
		select @sumRate = sum(PM_BL) from MixCoal where PM_ID = @PM_ID --查出该配煤的各煤源的比例值总和
		declare @result nvarchar(1000)
		set @result = ''
		declare curMixCoal cursor for --声明游标
		select PM_KCID, PM_BL from MixCoal where PM_ID = @PM_ID--查出该煤的配方(煤源ID,比例)
		open curMixCoal --打开游标
		declare @PM_ID_temp int --组成该煤的原煤种ID
		declare @rate decimal(18,2) --该煤源比例
		declare @weight decimal(18,2) --该煤源重量
		fetch curMixCoal into @PM_ID_temp, @rate --获取第一条记录的数据
		
		while(@@fetch_status = 0)
		begin
			select @weight = @KC_ZL * @rate / @sumRate --计算该煤源重量
			select @result = @result + '[' + ltrim(str(@PM_ID_temp)) + ',' + ltrim(str(@weight, 18, 2)) + ']'
			fetch next from curMixCoal into @PM_ID_temp, @rate --获取下一条记录的数据
		end
		CLOSE curMixCoal
		DEALLOCATE curMixCoal
		
		--print @result--@result为整合后的结果
	/*******************在结果中将所有配方进行递归入库(就是修改重量)***********/
		declare @i int, @j int
		set @i = 0
		set @j = 0
		while(@j < len(@result))
		begin
			declare @cPM_ID nvarchar(12)
			declare @cWeight nvarchar(18)
			declare @part nvarchar(35)
			set @i = charindex('[',@result,@i) + 1
			set @j = charindex(']',@result,@j) + 1
			set @part = substring(@result, @i, @j - @i - 1)
			declare @d int
			set @d = charindex(',',@part)
			set @cPM_ID = substring(@part, 1, @d - 1)
			set @cWeight = substring(@part, @d + 1, len(@part) - @d)
			set @PM_ID_temp = convert(int, @cPM_ID)
			set @weight = convert(decimal(18,2), @cWeight)
			execute prcRepertories_id @PM_ID_temp, @weight
		end
	end
end
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------