解决为’*********’ 的游标已存在问题 – 挑战

出现名为’MM_CURSOR’ 的游标已存在。一般出现这样的问题是：1：游标没有 –关闭释放如：SQL代码–关闭游标CLOSEMM_CURSOR–释放游标DEALLOCATEM

来源： 解决为’*********’ 的游标已存在问题 – 挑战 – 博客园

出现名为’MM_CURSOR’ 的游标已存在。
一般出现这样的问题是：
1：游标没有 –关闭释放
如：

SQL代码

–关闭游标
CLOSE MM_CURSOR
–释放游标
DEALLOCATE MM_CURSOR

2：游标已存在同名情况，此时就需要在定义游标时申明一个局部的游标
如：

SQL代码

/*检索已经配置好的新村镇的所有乡级部门*/
—申明游标
DECLARE deptCursor CURSOR
local FOR
SELECT deptname, deptsimplename,distid, deptuncode,deptqueryno,ifreport,deptsort,enable,deptfloor,deptcharacter,caseSMSFlag,deptType
FROM t_department
where PARENTID=250 and deptType=‘2’

其实我的情况都不是这样，只是在使用嵌套多层循环操作时把两个游标全部放在存储过程末后

Sql代码

–关闭游标
CLOSE MM_CURSOR
–释放游标
DEALLOCATE MM_CURSOR
–关闭游标–释放游标
CLOSE deptCursor
–释放游标
DEALLOCATE deptCursor

没有及时关闭导致问题出现！
正确代码如下

Sql代码

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
—drop PROCEDURE copyDept
ALTER PROCEDURE [dbo].[copyDept]
as
declare @deptCode varchar(20)
declare @deptname varchar(10)
declare @deptsimplename varchar(100)
declare @distid bigint
declare @deptuncode varchar(100)
declare @deptqueryno varchar(100)
declare @ifreport varchar(4)
declare @deptsort int
declare @enable varchar(6)
declare @deptfloor smallint
declare @deptcharacter varchar(50)
declare @caseSMSFlag varchar(4)
declare @deptType varchar(1)
declare @DeNo bigint
set nocount on
begin
set @deptcode = ‘2000000’
/*检索已经配置好的新村镇的所有乡级部门*/
—申明游标
DECLARE deptCursor CURSOR
local FOR
SELECT deptname, deptsimplename,distid, deptuncode,deptqueryno,ifreport,deptsort,enable,deptfloor,deptcharacter,caseSMSFlag,deptType
FROM t_department
where PARENTID=250 and deptType=‘2’
—打开游标
OPEN deptCursor
–循环取出游标
FETCH NEXT FROM deptCursor INTO @deptname,@deptsimplename,@distid,@deptuncode,@deptqueryno,@ifreport,@deptsort,@enable,@deptfloor,@deptcharacter,@caseSMSFlag,@deptType
while (@@FETCH_STATUS = 0)
begin
/*检索乡镇行政部门:如赵集镇，龙王乡…*/
—申明游标
Declare MM_CURSOR CURSOR
local FOR
Select DEPTID from t_department where ENABLE= ‘启用’ and DISTID = 1 and deptType=0 and deptid !=250—demo,except 250 — and PARENTID =288–and deptid not in (243,244)–and is_convenience=@tjType jc_dreaming
Order by DEPTCODE /**ONLY VALID DEPARTMENT */
— 打开游标
open MM_CURSOR
–循环取出游标
FETCH NEXT FROM MM_CURSOR INTO @DeNo
while (@@FETCH_STATUS = 0)
BEGIN
set @deptcode = convert(varchar(20),cast(@deptcode as int)+1)
print(@deptcode)
INSERT INTO T_DEPARTMENT (deptcode, deptname, deptsimplename,distid, deptuncode,deptqueryno,ifreport,deptsort,enable,deptfloor,PARENTID,deptcharacter,caseSMSFlag,deptType)
VALUES (@deptcode,@deptname,@deptsimplename,@distid,@deptuncode,@deptqueryno,@ifreport,@deptsort,@enable,@deptfloor,@DeNo,@deptcharacter,@caseSMSFlag,@deptType)
FETCH NEXT FROM MM_CURSOR INTO @DeNo
END
–关闭游标
CLOSE MM_CURSOR
–释放游标
DEALLOCATE MM_CURSOR
FETCH NEXT FROM deptCursor INTO @deptname,@deptsimplename,@distid,@deptuncode,@deptqueryno,@ifreport,@deptsort,@enable,@deptfloor,@deptcharacter,@caseSMSFlag,@deptType
–@deptname,@deptsimplename,@distid,@deptuncode,@deptqueryno,@ifreport,@deptsort,@enable,@deptfloor,@deptcharacter,@caseSMSFlag,@deptType
end
end
–关闭游标
CLOSE deptCursor
–释放游标
DEALLOCATE deptCursor

此外，在刚开始调用存储过程还遇到一个问题:程序处于正在查询状态，近一个小时，我想，数据还没那么复杂，可能出现死循环或某个游标没有移动…
可是看了代码，没有出现这样的情况，
经同事指点：

Sql代码

—申明游标
Declare MM_CURSOR CURSOR
local FOR
Select DEPTID from t_department where ENABLE= ‘启用’ and DISTID = 1 and deptType=0 and deptid !=250—demo,except 250 — and PARENTID =288–and deptid not in (243,244)–and is_convenience=@tjType jc_dreaming
Order by DEPTCODE /**ONLY VALID DEPARTMENT */
— 打开游标
open MM_CURSOR
–循环取出游标
FETCH NEXT FROM MM_CURSOR INTO @DeNo
while (@@FETCH_STATUS = 0)
set @deptcode = convert(varchar(20),cast(@deptcode as int)+1) //把此行代码移至begin代码内即可
BEGIN
print(@deptcode)
INSERT INTO T_DEPARTMENT (deptcode, deptname, deptsimplename,distid, deptuncode,deptqueryno,ifreport,deptsort,enable,deptfloor,PARENTID,deptcharacter,caseSMSFlag,deptType)
VALUES (@deptcode,@deptname,@deptsimplename,@distid,@deptuncode,@deptqueryno,@ifreport,@deptsort,@enable,@deptfloor,@DeNo,@deptcharacter,@caseSMSFlag,@deptType)
FETCH NEXT FROM MM_CURSOR INTO @DeNo
END
–关闭游标
CLOSE MM_CURSOR
–释放游标
DEALLOCATE MM_CURSOR

解决为'*********' 的游标已存在问题 - 挑战 - 博客园

相关推荐

热门标签

分类

链接表

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏