存储过程中带游标

最新推荐文章于 2024-10-09 23:10:27 发布

EastWind2017

最新推荐文章于 2024-10-09 23:10:27 发布

阅读量284

点赞数

CC 4.0 BY-SA版权

分类专栏：数据库设计

本文链接：https://blog.youkuaiyun.com/h1534589653/article/details/73885203

数据库设计专栏收录该内容

5 篇文章

订阅专栏

Create Procedure UpdateTimeProperty
@SurroundingRock  nvarchar(200),
@IFClassName  nvarchar(200),
@Prop_StartStation nvarchar(200),
@Prop_EndStatoin nvarchar(200),
@StartTime nvarchar(200),
@UnitTime nvarchar(200)
as
begin
declare @curStartStation nvarchar(200)
declare @curEndStatoin nvarchar(200)
declare @EBS nvarchar(200)
declare getNum cursor
for(select Prop_StartStation,Prop_EndStatoin,EBS from v_ItemProperty
where SurroundingRock=@SurroundingRock and  IfcClassName=@IFClassName)
open getNum;
fetch next from getNum into @curStartStation,@curEndStatoin,@EBS;
while @@FETCH_STATUS = 0
    begin
	  if(
	    ((select dbo.f_GetNum(@curStartStation) as curStartStation) > (select dbo.f_GetNum(@Prop_StartStation) as Prop_StartStation)) and  -----游标中的开始桩号大于参数的开始桩号
		((select dbo.f_GetNum(@curEndStatoin) as curEndStatoin)<(select dbo.f_GetNum(@Prop_EndStatoin) as Prop_EndStatoin) )  and-----游标中的结束桩号要小于参数的结束桩号
	     ((select dbo.f_GetNum(@curStartStation) as curStartStation) <(select dbo.f_GetNum(@Prop_EndStatoin) as Prop_EndStatoin))and-----游标中的开始桩号小于参数的结束桩号
		 ((select dbo.f_GetNum(@curEndStatoin) as curEndStatoin)>(select dbo.f_GetNum(@Prop_StartStation) as Prop_StartStation))    -------游标中的结束桩号大于参数中的开始桩号
	   )
	   update t_ComponentProperty
	   set Value=@curStartStation
	   where t_ComponentProperty.EBS=@EBS and Name='ScheduleStartTime';
	   update t_ComponentProperty
	   set Value=@curEndStatoin
	   where t_ComponentProperty.EBS=@EBS and Name='ScheduleEndTime';
	fetch next from getNum into @curStartStation,@curEndStatoin,@EBS;
	end
	close getNum
	deallocate getNum
	end
go