纠结的并发动态游标问题
今天在程序中使用动态游标（考虑到SP并发问题），于是将原有的简单动态游标加以处理，使得游标名称也动态执行，发现了新问题，很是纠结，如下：
SET @V_SQL_SUB = 'DECLARE CUR_SCR_SUB_'+@V_BTCH_EXAM_ID+' SCROLL CURSOR FOR '+CHAR(13)+
'SELECT DISTINCT T1.SUB_ID '+CHAR(13)+
'FROM EXAM.SCR_SGMNT_SET_CD T1 '+CHAR(13)+
'where T1.BTCH_EXAM_ID = '+ @V_BTCH_EXAM_ID+
' AND T1.SUB_ID<>-1'
PRINT '@V_SQL_SUB='+@V_SQL_SUB
EXEC (@V_SQL_SUB)
SET @V_END_TIME =GETDATE()
EXEC PUB_PLFM.USP_LOG_CHK_SQL_RUN @SP_ID,@SP_NM,@I_TRM_ID,@I_SCHL_ID,@V_STEP_ID, @V_STEP_DSC,NULL, @V_CRNT_TIME,@V_END_TIME,@@rowcount,NULL,@V_SQL_SUB,NULL
SET @V_TMP_SQL_CUR= 'OPEN CUR_SCR_SUB_'+@V_BTCH_EXAM_ID;
EXEC (@V_TMP_SQL_CUR)
PRINT '共计科目数='+CAST(@@cursor_Rows AS VARCHAR(10))
SET @V_TMP_SQL_CUR='CUR_SCR_SUB_'+@V_BTCH_EXAM_ID
---DECLARE @V_CUR CURSOR;
---SET @V_CUR = 'CUR_SCR_SUB_'+@V_BTCH_EXAM_ID;
SET @V_SUB_CNT = @@cursor_Rows ---获得科目数
IF (@V_SUB_CNT>0) --如果科目大于0，则进行条件判断SQL串组织
BEGIN
SET @i=0;
--FETCH NEXT FROM @V_TMP_SQL_CUR into @V_SUB_ID;
SET @V_TMP_SQL_CUR = 'FETCH NEXT FROM CUR_SCR_SUB_'+@V_BTCH_EXAM_ID +' into @V_SUB_ID '
----报错信息：(1 行受影响)
共计科目数=4
@V_TMP_SQL_CUR=FETCH NEXT FROM CUR_SCR_SUB_20111208140702884 into @V_SUB_ID
消息 137，级别 15，状态 2，第 1 行
Must declare the scalar variable "@V_SUB_ID".PRINT '@V_TMP_SQL_CUR='+@V_TMP_SQL_CUR
EXEC (@V_TMP_SQL_CUR)
WHILE @@FETCH_STATUS = 0
BEGIN
SET @i = @i+1;
IF @V_SET_SUB = ''
SET @V_SET_SUB = CAST(@V_SUB_ID AS nVARCHAR(20))
ELSE
SET @V_SET_SUB =@V_SET_SUB+ ','+CAST(@V_SUB_ID AS nVARCHAR(20))
PRINT '@V_SET_SUB='+@V_SET_SUB
PRINT '@i='+CAST(@i AS VARCHAR(2))
IF (@i = 1)
BEGIN
SET @V_SQL_STR = 'CASE WHEN SUB_ID = '+CAST(@V_SUB_ID AS VARCHAR(20))+' THEN '+CHAR(13)
SET @V_SQL_SGMNT_NM = @V_SQL_STR;
END
ELSE
BEGIN
SET @V_SQL_STR = @V_SQL_STR + 'WHEN SUB_ID = '+CAST(@V_SUB_ID AS VARCHAR(20))+' THEN '+CHAR(13)
SET @V_SQL_SGMNT_NM = @V_SQL_SGMNT_NM+ 'WHEN SUB_ID = '+CAST(@V_SUB_ID AS VARCHAR(20))+' THEN '+CHAR(13)
END
---======================================================================================
--定义分段游标
SET @V_STEP_ID = 10000130+@i;
SET @V_STEP_DSC = '建立分档游标';
SET @V_CRNT_TIME = GETDATE();
-----判断游标是否存在
SELECT @V_CUR_ST=CURSOR_STATUS('global','CUR_SCR_SGMNT_'+@V_BTCH_EXAM_ID)
IF @V_CUR_ST<>-3
BEGIN
SET @V_TMP_SQL_CUR= 'DEALLOCATE CUR_SCR_SGMNT_'+@V_BTCH_EXAM_ID;
EXEC (@V_TMP_SQL_CUR)
END
----定义单科成绩分段游标
SET @V_SQL_CUR= ' DECLARE CUR_SCR_SGMNT_'+@V_BTCH_EXAM_ID+' SCROLL CURSOR FOR '+
' SELECT T.SGMNT_ID,T.LWR_SCR,T.LWR_CNDTN '+
',T.LMT_SCR,T.LMT_CNDTN,T.SGMNT_NM '+
' FROM EXAM.SCR_SGMNT_SET_CD T '+
' WHERE T.BTCH_EXAM_ID = '+@V_BTCH_EXAM_ID +
' AND T.SUB_ID='+CAST(@V_SUB_ID AS VARCHAR(20))+
' ORDER BY SEQ_ID ASC ';
PRINT @V_SQL_CUR
EXEC (@V_SQL_CUR)
----记录SQL日志
SET @V_END_TIME =GETDATE()
EXEC PUB_PLFM.USP_LOG_CHK_SQL_RUN @SP_ID,@SP_NM,@I_TRM_ID,@I_SCHL_ID,@V_STEP_ID, @V_STEP_DSC,NULL, @V_CRNT_TIME,@V_END_TIME,@@rowcount,NULL,@V_SQL_CUR,NULL
SET @V_TMP_SQL_CUR='OPEN CUR_SCR_SGMNT_'+@V_BTCH_EXAM_ID;--打开游标
EXEC (@V_TMP_SQL_CUR);
SET @V_SGMNT_CNT = @@cursor_Rows ---获得分段数
--PRINT '@V_SGMNT_CNT='+CAST(@V_SGMNT_CNT AS VARCHAR(20));
SET @k=0;
SET @V_TMP_SQL_CUR='FETCH NEXT FROM CUR_SCR_SGMNT_'+@V_BTCH_EXAM_ID;
EXEC (@V_TMP_SQL_CUR);
-----------------------------------------------
WHILE (@@FETCH_STATUS = 0)
BEGIN
--FETCH NEXT FROM CUR_SCR_SGMNT into @V_SGMNT_ID,@LWR_SCR,@LWR_CNDTN,@LMT_SCR,@LMT_CNDTN,@V_SGMNT_NM;
SET @V_TMP_SQL_CUR='FETCH NEXT FROM CUR_SCR_SGMNT_'+@V_BTCH_EXAM_ID+' into @V_SGMNT_ID,@LWR_SCR,@LWR_CNDTN,@LMT_SCR,@LMT_CNDTN,@V_SGMNT_NM'
EXEC (@V_TMP_SQL_CUR);
SET @k = @k+1;
PRINT ' @k='+CAST(@k AS VARCHAR(2))
IF (@k = 1)
BEGIN
SET @V_SQL_STR = @V_SQL_STR+CHAR(13)+'( CASE '
SET @V_SQL_SGMNT_NM = @V_SQL_SGMNT_NM+CHAR(13)+'( CASE '
END
SET @V_SQL_STR =@V_SQL_STR+ ' WHEN T.SCR '+ @LWR_CNDTN +CAST(@LWR_SCR AS VARCHAR(20))+' AND T.SCR '+ @LMT_CNDTN + CAST(@LMT_SCR AS VARCHAR(20)) +' THEN '''+ @V_SGMNT_ID+'''' +CHAR(13)
SET @V_SQL_SGMNT_NM =@V_SQL_SGMNT_NM+ ' WHEN T.SCR '+ @LWR_CNDTN +CAST(@LWR_SCR AS VARCHAR(20))+' AND T.SCR '+ @LMT_CNDTN + CAST(@LMT_SCR AS VARCHAR(20)) +' THEN '''+ @V_SGMNT_NM+'''' +CHAR(13)
IF (@k=@V_SGMNT_CNT)
BEGIN
SET @V_SQL_STR = @V_SQL_STR +' ELSE NULL END )'
SET @V_SQL_SGMNT_NM =@V_SQL_SGMNT_NM+' ELSE NULL END )'
END
END;
---关闭成绩分档游标
--CLOSE CUR_SCR_SGMNT;
--DEALLOCATE CUR_SCR_SGMNT;
SET @V_TMP_SQL_CUR= 'CLOSE CUR_SCR_SGMNT_'+@V_BTCH_EXAM_ID
EXEC (@V_TMP_SQL_CUR)
SET @V_TMP_SQL_CUR= 'DEALLOCATE CUR_SCR_SGMNT)_'+@V_BTCH_EXAM_ID
EXEC (@V_TMP_SQL_CUR)
---======================================================================================
IF (@i=@V_SUB_CNT)
BEGIN
SET @V_SQL_STR = @V_SQL_STR +' ELSE NULL END SGMNT_ID '
SET @V_SQL_SGMNT_NM = @V_SQL_SGMNT_NM +' ELSE NULL END SGMNT_NM '
END
SET @V_TMP_SQL_CUR=' FETCH NEXT FROM CUR_SCR_SUB_'+@V_BTCH_EXAM_ID+' into @V_SUB_ID'
EXEC (@V_TMP_SQL_CUR)
END----结束分段循环
---关闭课程游标
--CLOSE CUR_SCR_SUB;
--DEALLOCATE CUR_SCR_SUB;
SET @V_TMP_SQL_CUR= 'CLOSE CUR_SCR_SUB_'+@V_BTCH_EXAM_ID
EXEC (@V_TMP_SQL_CUR)
SET @V_TMP_SQL_CUR= 'DEALLOCATE CUR_SCR_SUB_'+@V_BTCH_EXAM_ID
EXEC (@V_TMP_SQL_CUR)
看那位高人指点一下怎么处理。
（另外一个非嵌套循环的已经成功执行）
[解决办法]
- SQL code
--create table testcursor--(id int)--insert into testcursor--select 1--union --select 2declare @id intdeclare @statement nvarchar(max)declare @name nvarchar(1)set @name=1set @statement= 'Declare test'+@name+' Cursor Fast_Forward For'set @statement=@statement+' select id from testcursor 'set @statement=@statement+ 'Open test'+@nameexec (@statement)SET @statement = 'FETCH NEXT FROM test'+@name+' into @id'Exec sp_ExecuteSQL @statement,N'@id int output ',@id outputWhile @@FETCH_STATUS = 0 Beginselect @idSET @statement = 'FETCH NEXT FROM test'+@name+' into @id'Exec sp_ExecuteSQL @statement,N'@id int output ',@id outputendset @statement='Close test'+@nameset @statement=@statement+' Deallocate test'+@nameexec (@statement)
[解决办法]
不同session的游标名不冲突的。因此即使存储过程同时被不同用户调用。也不会存在游标名冲突。
多返回值
EXEC sp_executesql =@statement,
N'@parm1 type output,
@parm2 type output,
@parm3 type output',
@parm1 output,
@parm2 output,
@parm3 output
注意@statement的类型必须是nvarchar