出现名为'MM_CURSOR' 的游标已存在。
一般出现这样的问题是：
1：游标没有 --关闭 释放
如：
-
- CLOSE MM_CURSOR
-
- DEALLOCATE MM_CURSOR
2：游标已存在同名情况，此时就需要在定义游标时申明一个局部的游标
如：
- /*检索已经配置好的新村镇的所有乡级部门*/
-
- 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'
其实我的情况都不是这样，只是在使用嵌套多层循环操作时把两个游标全部放在存储过程末后
-
- CLOSE MM_CURSOR
-
- DEALLOCATE MM_CURSOR
-
- CLOSE deptCursor
-
- DEALLOCATE deptCursor
没有及时关闭导致问题出现！
正确代码如下
- set ANSI_NULLS ON
- set QUOTED_IDENTIFIER ON
- go
-
-
- 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
- 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
-
- end
-
- end
-
-
- CLOSE deptCursor
-
- DEALLOCATE deptCursor
此外，在刚开始调用存储过程还遇到一个问题:程序处于正在查询状态，近一个小时，我想，数据还没那么复杂，可能出现死循环或某个游标没有移动...
可是看了代码，没有出现这样的情况，
经同事指点：
-
- Declare MM_CURSOR CURSOR
- local FOR
- Select DEPTID from t_department where ENABLE= '启用' and DISTID = 1 and deptType=0 and deptid !=250
- 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
