事务+游标内嵌+跳出内游标，循环外层游标

GO
/****** Object:  StoredProcedure [dbo].[Pro_DistributionBatch]    Script Date: 2019-11-20 13:58:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- exec [Pro_DistributionBatch]


ALTER PROCEDURE [dbo].[Pro_DistributionBatch]	
AS
BEGIN
		DECLARE @sumPartNo varchar(50) 
		DECLARE @sumQty decimal --总可用数量
		DECLARE @useQty decimal --总已使用数量
		DECLARE @okQty decimal --总可用数量

		DECLARE @countQty decimal 
		DECLARE @id varchar(50) 
		
		DECLARE moinfo CURSOR 
		FOR 
		 --获取生产任务单第一个工艺 查询当天信息
			select mbomp_material.unode,mo_rmb.mo_id,mo.mo_no,mbomp_material.mtr_part_id,
				p.part_no, p.part_name AS material_part_name,p.part_spec AS material_part_spec,      
				mo_rmb.wkp_id,wkp.wkp_no,wkp.wkp_name,p.part_unit,u.unit_name AS mtr_qty_unit_name,
				mbomp_material.mtr_qty*mo_rmb.wkn_req_qty as llQty, --用量*生产任务单数量 = 领料数量
				mo.part_id as mo_part_id,mo.part_no as mo_part_no,mo.part_name as mo_part_name,mo.part_spec as mo_part_spec
			FROM mo_rmb_mtr mbomp_material 
				INNER JOIN  mo_rmb ON  mbomp_material.unode = mo_rmb.unode 
				LEFT OUTER JOIN pdm_part p ON mbomp_material.mtr_part_id = p.part_id and p.part_no like '01.%'  --暂时只处理 01开头物料
				inner join mo mo on mo.mo_id = mo_rmb.mo_id AND mo.mo_id like 'WORK%'	
				left outer join pdm_wkp wkp on wkp.wkp_id = mo_rmb.wkp_id
				left outer join  av_bas_unit_13 u ON p.part_unit=u.cdvl_id 
				where datediff(dd,mo.crt_time,GETDATE())=0  --and mo_rmb.mo_id='WORK215514' 
				and mo.rmk12 is null	
																									
		OPEN moinfo
		DECLARE @unode varchar(50), @moId varchar(50),@moNo varchar(50), @partId varchar(50),@partNo varchar(50),@partName varchar(50),
				@partSpec varchar(50),@wkpId varchar(50),@wkpNo varchar(50),@wkpName varchar(50),@unit int,@unitName varchar(50),
				@llQty decimal,@moPartId varchar(50),@moPartNo varchar(50),@moPartName varchar(50),@moPartSpec varchar(50)
		FETCH NEXT FROM moinfo INTO @unode, @moId,@moNo, @partId,@partNo,@partName,@partSpec,@wkpId,@wkpNo,@wkpName,@unit,@unitName,@llQty,@moPartId,@moPartNo,@moPartName,@moPartSpec	
		WHILE @@FETCH_STATUS =0						
			BEGIN 			
				if(@partNo is not null)					
					BEGIN						
						SET @okQty = 0
						SET @sumPartNo = ''
						SET @useQty = 0
						SET @countQty =0
						SET @id = ''
											
						select @sumPartNo=part_no,@okQty=sum(CONVERT(decimal,POH_RMK6))-sum(CONVERT(decimal,POH_RMK5)),@useQty=sum(cast(POH_RMK5 as decimal)),
								@sumQty = sum(cast(POH_RMK6 as decimal))
						from srm_po_dlv 
						where part_no=@partNo and si_conclusion = 10  group by part_no having sum(CONVERT(decimal,POH_RMK6))>sum(CONVERT(decimal,POH_RMK5))	
						print(isnull(@okQty,0)) print(@moId)
						
						BEGIN TRY
						BEGIN TRAN																		
							if(@okQty>=@llQty)																		
							BEGIN							
								SET @id = newid()
									insert into mes_mtr_use_h (mo_no,mo_id,crt_host,crt_time,crt_user,mes_mtr_use_h_datetime,mes_mtr_use_h_no,mes_mtr_use_hid,part_id,
									part_name,part_spec,part_no,upd_host,upd_time,upd_user,mes_mtr_use_h_status,mes_mtr_use_h_io_type,wkc_id,wkc_name,wkc_no,wkn)
									values(@moId,@moNo,'sqlserver',getdate(),'sqlserver',getdate(),@moNo,@id,@moPartId,@moPartName,@moPartSpec,@moPartNo,
													'sqlserver',getdate(),'sqlserver',110,0,'sqlserver','sqlserver','sqlserver','sqlserver')																
								DECLARE batchs CURSOR 
								FOR
									select a.po_id,case when b.barcode_id is null then a.rmk1 else b.barcode_id END as batch,cast(a.POH_RMK6 as decimal)- cast(a.POH_RMK5 as decimal) as okQty1,cast(a.POH_RMK5 as decimal) as useQty,a.part_id,a.part_no,a.part_name,a.part_spec,a.po_no,a.rmk2 as poEntity 
									from srm_po_dlv a
									left join av_srm_po_b3 b on a.po_id = b.po_hid
									where a.part_no =@partNo 
									and si_conclusion = 10 
									and CONVERT(decimal,a.POH_RMK5)<CONVERT(decimal,a.POH_RMK6) order by a.crt_time asc
								OPEN batchs
								DECLARE @poId varchar(50),@batch varchar(50),@okQty1 decimal, @useQty1 decimal,@partId1 varchar(50),@partNo1 varchar(50),@partName1 varchar(50),@partSpec1 varchar(50),@po_no varchar(50),@poEntity varchar(10)
								FETCH NEXT FROM batchs INTO @poId,@batch, @okQty1,@useQty1,@partId1,@partNo1,@partName1,@partSpec1,@po_no,@poEntity	
								WHILE @@FETCH_STATUS =0		
								BEGIN 																																							
								print(@poId)  print(@batch) print(@okQty1)  print(@useQty1)   print(@partNo1) print(@partName1) print(@po_no)
								--SET @countQty = isnull(@countQty,0)	 									
								if(@partNo1=@sumPartNo)
									BEGIN
										if(@llQty<=@okQty1)
									BEGIN								
										update srm_po_dlv SET POH_RMK5 = @useQty1+@llQty-isnull(@countQty,0) where po_id=@poId								
										update mo SET rmk12 = 1 where mo_id = @moId --分配批次号数据做上标记防止重复分配
										insert into mes_mtr_use_b(mtr_lot_no, mes_mtr_use_bid, mes_mtr_use_hid, mes_mtr_use_b_status, mes_mtr_use_b_datetime,
																 mtr_qty, crt_user, crt_host, crt_time, upd_user, upd_host, upd_time,upd_user_name,mtr_part_id,mtr_part_no,mtr_part_name,mtr_part_spec)
										values(@batch,newid(),@id,'sqlserver',getdate(),
												 @llQty-isnull(@countQty,0), 'sqlserver','sqlserver',getdate(),'sqlserver','sqlserver',getdate(),'sqlserver',@partId1,@partNo1,@partName1,@partSpec1)																			
										break
										fetch next from moinfo
										
									END
								if(@llQty>@okQty1)
									BEGIN	
										SET @countQty =@countQty + @okQty1															
										if(@countQty>=@llQty)
											BEGIN
												update srm_po_dlv SET POH_RMK5 = @useQty1+(@llQty-(@countQty-@okQty1)) where po_id=@poId
												update mo SET rmk12 = 1 where mo_id = @moId --分配批次号数据做上标记防止重复分配
												insert into mes_mtr_use_b(mtr_lot_no, mes_mtr_use_bid, mes_mtr_use_hid, mes_mtr_use_b_status, mes_mtr_use_b_datetime,
																 mtr_qty, crt_user, crt_host, crt_time, upd_user, upd_host, upd_time,upd_user_name,mtr_part_id,mtr_part_no,mtr_part_name,mtr_part_spec)
												values(@batch,newid(),@id,'sqlserver',getdate(),
														 @llQty-(@countQty-@okQty1), 'sqlserver','sqlserver',getdate(),'sqlserver','sqlserver',getdate(),'sqlserver',@partId1,@partNo1,@partName1,@partSpec1)												
												break
												fetch next from moinfo												
												--fetch next from batchs continue																														
											END
										else
											BEGIN	
												update srm_po_dlv SET POH_RMK5 = @useQty1+@okQty1 where po_id=@poId
												update mo SET rmk12 = 1 where mo_id = @moId --分配批次号数据做上标记防止重复分配
												insert into mes_mtr_use_b(mtr_lot_no, mes_mtr_use_bid, mes_mtr_use_hid, mes_mtr_use_b_status, mes_mtr_use_b_datetime,
																 mtr_qty, crt_user, crt_host, crt_time, upd_user, upd_host, upd_time,upd_user_name,mtr_part_id,mtr_part_no,mtr_part_name,mtr_part_spec)
												values(@batch,newid(),@id,'sqlserver',getdate(),
																 @okQty1, 'sqlserver','sqlserver',getdate(),'sqlserver','sqlserver',getdate(),'sqlserver',@partId1,@partNo1,@partName1,@partSpec1)																										
											END
									END

									END																							
							FETCH NEXT FROM batchs INTO @poId,@batch, @okQty1,@useQty1,@partId1,@partNo1,@partName1,@partSpec1,@po_no,@poEntity	
						END 
						CLOSE batchs 
						DEALLOCATE batchs																
					END	
						COMMIT TRAN
						END TRY
						BEGIN CATCH
						 IF @@TRANCOUNT > 0
						BEGIN
							print('异常----------------------------------------------回滚事务')
							ROLLBACK TRAN
						END 
						END CATCH
				END																								
			FETCH NEXT FROM moinfo INTO @unode, @moId,@moNo, @partId,@partNo,@partName,@partSpec,@wkpId,@wkpNo,@wkpName,@unit,@unitName,@llQty,@moPartId,@moPartNo,@moPartName,@moPartSpec	
			END
		CLOSE moinfo 
		DEALLOCATE moinfo		
END