游标不关，没的下班!游标没有关闭，提示游标已存在！

q85958341 2009-12-17 05:48:24

这段游标有问题，
DC_CONTROL 表中只有2行记录
第一遍：
消息 16915，级别 16，状态 1，过程 proc_MachineAnnouncer_Set，第 61 行
名为 'CTLID_cursor' 的游标已存在。
消息 16905，级别 16，状态 1，过程 proc_MachineAnnouncer_Set，第 62 行
游标已打开。

第二遍：
up

(1 行受影响)

第三遍：

消息 16915，级别 16，状态 1，过程 proc_MachineAnnouncer_Set，第 61 行
名为 'CTLID_cursor' 的游标已存在。
消息 16905，级别 16，状态 1，过程 proc_MachineAnnouncer_Set，第 62 行
游标已打开。up

(1 行受影响)

第四遍：

消息 16915，级别 16，状态 1，过程 proc_MachineAnnouncer_Set，第 61 行
名为 'CTLID_cursor' 的游标已存在。
消息 16905，级别 16，状态 1，过程 proc_MachineAnnouncer_Set，第 62 行
游标已打开。



--判断是否存在

    if not Exists(select * from DC_CONTROL where CTLID like '%'+LTRIM(@MachineID)+'%')	

      begin

        Set @ErrMsg='未能找到该设备，设置失败！'

        Return -1 

      end

--如果存在，则给改人员设置所有查找出的设备权限（批量设置）

    else

      begin

		declare CTLID_cursor cursor for 

		select CTLID from DC_CONTROL where CTLID like '%'+LTRIM(@MachineID)+'%'

		OPEN CTLID_cursor

		FETCH NEXT FROM CTLID_cursor 

		INTO @MachineID

		WHILE @@FETCH_STATUS = 0

		BEGIN

		 if Exists(select * from MachineAnnouncer_Set where MachineID=@MachineID and AnnouncerID=@AnnouncerID)

			begin

			  begin Tran Tran_MachineAnnouncer_Set_UP

print ('up')

			  update MachineAnnouncer_Set set ISDown=@ISDown where MachineID=@MachineID and AnnouncerID=@AnnouncerID

			  if @@Error<>0 

				begin

				  Rollback Tran Tran_MachineAnnouncer_Set_UP

				  select @ErrMsg='设置权限失败！'

				  Return -1 

				end

			  else

				begin

				  Commit Tran Tran_MachineAnnouncer_Set_UP

				  Return 0

				end

			end

		  else

			begin

			  begin Tran Tran_MachineAnnouncer_Set_insert

print ('in')

			  insert into MachineAnnouncer_Set(MachineID,AnnouncerID,PurVal,ISDown) 

				values (@MachineID,@AnnouncerID,@PurVal,@ISDown);

			  if @@Error<>0 

				begin

				  Rollback Tran Tran_MachineAnnouncer_Set_insert

				  select @ErrMsg='设置权限失败！'

				  Return -1 

				end

			  else

				begin

				  Commit Tran Tran_MachineAnnouncer_Set_insert

				  Return 0

				end

			end

      end

	  FETCH NEXT FROM CTLID_cursor 

	  INTO @MachineID

	END

	CLOSE CTLID_cursor

	DEALLOCATE CTLID_cursor

...全文

295 15 打赏收藏转发到动态举报

写回复

用AI写文章

15 条回复

切换为时间正序

请发表友善的回复…

发表回复

q85958341 2009-12-18

打赏
举报

谢谢大家,是我 begin end 位子问题！
后来重新整理了代码~发现的！

数据库编写好麻烦~
有没什么工具可以自动整理这个格式！

maxthon2009 2009-12-17

打赏
举报

select * from stu2

pt1314917 2009-12-17

打赏
举报



--try:



--判断是否存在

if not Exists(select * from DC_CONTROL where CTLID like LTRIM(@MachineID)+'%')    

begin

	Set @ErrMsg='设备无法确认！'

	Return -1 

end

--如果存在，则给改人员设置所有查找出的设备权限（批量设置）

else

begin

	declare CTLID_cursor cursor for select CTLID from DC_CONTROL where CTLID like LTRIM(@MachineID)+'%'

	OPEN CTLID_cursor

	FETCH NEXT FROM CTLID_cursor INTO @MachineID

	begin Tran Tran_MachineAnnouncer_Set_insert

	WHILE @@FETCH_STATUS = 0

	BEGIN

		if Exists(select * from MachineAnnouncer_Set where MachineID=@MachineID and AnnouncerID=@AnnouncerID)

		begin

			print ('up')

			update MachineAnnouncer_Set set ISDown=@ISDown where MachineID=@MachineID and AnnouncerID=@AnnouncerID

			if @@Error<>0 

			begin

				  Rollback Tran Tran_MachineAnnouncer_Set_insert

				  CLOSE CTLID_cursor

				  DEALLOCATE CTLID_cursor  

				  select @ErrMsg='设置权限失败！'

				  Return -1 

			end

		end

		else

		begin

			print ('in')

			insert into MachineAnnouncer_Set(MachineID,AnnouncerID,PurVal,ISDown) values (@MachineID,@AnnouncerID,@PurVal,@ISDown);

			if @@Error<>0 

			begin

				Rollback Tran Tran_MachineAnnouncer_Set_insert

				CLOSE CTLID_cursor

				DEALLOCATE CTLID_cursor

				select @ErrMsg='设置权限失败！'

				Return -1 

			end

		end

		FETCH NEXT FROM CTLID_cursor INTO @MachineID

	end

	Commit Tran Tran_MachineAnnouncer_Set_insert

	CLOSE CTLID_cursor

	DEALLOCATE CTLID_cursor 

	Return 0 

END

q85958341 2009-12-17

打赏
举报

头晕了~~

nianran520 2009-12-17

打赏
举报

来点数据测试下

q85958341 2009-12-17

打赏
举报



	  FETCH NEXT FROM CTLID_cursor 

	  INTO @MachineID

        Commit Tran Tran_MachineAnnouncer_Set_insert

	Return 0

	CLOSE CTLID_cursor

	DEALLOCATE CTLID_cursor 

	END

	     

    end

没用呀！T.T.

SQL77 2009-12-17

打赏
举报

--判断是否存在
if not Exists(select * from DC_CONTROL where CTLID like '%'+LTRIM(@MachineID)+'%')
begin
Set @ErrMsg='未能找到该设备，设置失败！'
Return -1
end
--如果存在，则给改人员设置所有查找出的设备权限（批量设置）
else
begin
declare CTLID_cursor cursor for
select CTLID from DC_CONTROL where CTLID like '%'+LTRIM(@MachineID)+'%'
OPEN CTLID_cursor
FETCH NEXT FROM CTLID_cursor
INTO @MachineID
WHILE @@FETCH_STATUS = 0
BEGIN
if Exists(select * from MachineAnnouncer_Set where MachineID=@MachineID and AnnouncerID=@AnnouncerID)
begin
begin Tran Tran_MachineAnnouncer_Set_UP
print ('up')
update MachineAnnouncer_Set set ISDown=@ISDown where MachineID=@MachineID and AnnouncerID=@AnnouncerID
if @@Error<>0
begin
Rollback Tran Tran_MachineAnnouncer_Set_UP
select @ErrMsg='设置权限失败！'
Return -1
end
else
begin
Commit Tran Tran_MachineAnnouncer_Set_UP
Return 0
end
end
else
begin
begin Tran Tran_MachineAnnouncer_Set_insert
print ('in')
insert into MachineAnnouncer_Set(MachineID,AnnouncerID,PurVal,ISDown)
values (@MachineID,@AnnouncerID,@PurVal,@ISDown);
if @@Error<>0
begin
Rollback Tran Tran_MachineAnnouncer_Set_insert
select @ErrMsg='设置权限失败！'
Return -1
end
else
begin
Commit Tran Tran_MachineAnnouncer_Set_insert
Return 0
end
end
end

FETCH NEXT FROM CTLID_cursor
INTO @MachineID
CLOSE CTLID_cursor
DEALLOCATE CTLID_cursor
END
CLOSE CTLID_cursor
DEALLOCATE CTLID_cursor

放这里试试

q85958341 2009-12-17

打赏
举报

[Quote=引用 7 楼 guguda2008 的回复:]
恭喜
[/Quote]
....到底哪里关闭游标呀！！！！！

guguda2008 2009-12-17

打赏
举报

恭喜

q85958341 2009-12-17

打赏
举报

现在是停不下来了

1 行受影响)
up

(1 行受影响)
up

(1 行受影响)
up

(1 行受影响)
up

(1 行受影响)
up

(1 行受影响)
up

(1 行受影响)
up

(1 行受影响)
up

(1 行受影响)
up

(1 行受影响)
up

(1 行受影响)
up

(1 行受影响)
up

(1 行受影响)
up

(1 行受影响)
up

(1 行受影响)
up

(1 行受影响)
up

(1 行受影响)
up

(1 行受影响)
up

(1 行受影响)
up

(1 行受影响)
up

(1 行受影响)
up

(1 行受影响)
up

(1 行受影响)
up

(1 行受影响)
up

(1 行受影响)
up

(1 行受影响)
up

(1 行受影响)
up

(1 行受影响)

q85958341 2009-12-17

打赏
举报



--判断是否存在

    if not Exists(select * from DC_CONTROL where CTLID like LTRIM(@MachineID)+'%')	

      begin

        Set @ErrMsg='设备无法确认！'

        Return -1 

      end

--如果存在，则给改人员设置所有查找出的设备权限（批量设置）

    else

      begin

		declare CTLID_cursor cursor for 

		select CTLID from DC_CONTROL where CTLID like LTRIM(@MachineID)+'%'

		OPEN CTLID_cursor

		FETCH NEXT FROM CTLID_cursor 

		INTO @MachineID

		begin Tran Tran_MachineAnnouncer_Set_insert

		WHILE @@FETCH_STATUS = 0

		BEGIN

		 if Exists(select * from MachineAnnouncer_Set where MachineID=@MachineID and AnnouncerID=@AnnouncerID)

			begin

print ('up')

			  update MachineAnnouncer_Set set ISDown=@ISDown where MachineID=@MachineID and AnnouncerID=@AnnouncerID

			  if @@Error<>0 

				begin

				  Rollback Tran Tran_MachineAnnouncer_Set_insert

				  CLOSE CTLID_cursor

	              DEALLOCATE CTLID_cursor  

				  select @ErrMsg='设置权限失败！'

				  Return -1 

				end

			end

		  else

			begin

print ('in')

			  insert into MachineAnnouncer_Set(MachineID,AnnouncerID,PurVal,ISDown) 

				values (@MachineID,@AnnouncerID,@PurVal,@ISDown);

			  if @@Error<>0 

				begin

				  Rollback Tran Tran_MachineAnnouncer_Set_insert

				  CLOSE CTLID_cursor

	              DEALLOCATE CTLID_cursor

				  select @ErrMsg='设置权限失败！'

				  Return -1 

				end

			end

      end

	  FETCH NEXT FROM CTLID_cursor 

	  INTO @MachineID

	END

	Commit Tran Tran_MachineAnnouncer_Set_insert

	Return 0

	CLOSE CTLID_cursor

	DEALLOCATE CTLID_cursor