SQL 为什么我得到“同名游标已经存在”？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28786096/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why do I get "A cursor with the name already exists"?
提问by Erick Asto Oblitas
I have this trigger:
我有这个触发器：
CREATE TRIGGER CHECKINGMAXQTYDAYSVACANCY
ON TDINCI
AFTER INSERT
AS
DECLARE
@incidentCode int,
@dateStart datetime,
@dateEnd datetime,
@daysAccumulated int,
@maxDaysAvailable int
set @daysAccumulated = 0;
select @incidentCode = CO_INCI from inserted;
select @maxDaysAvailable = IN_DIAS_GANA from TCINCI
where CO_INCI = @incidentCode;
declare detailsCursor CURSOR FOR
select FE_INIC, FE_FINA from TDINCI
where CO_INCI = @incidentCode;
open detailsCursor;
if CURSOR_STATUS('variable', 'detailsCursor') >= 0
begin
fetch next from detailsCursor
into @dateStart, @dateEnd;
while @@FETCH_STATUS = 0
begin
set @daysAccumulated = @daysAccumulated + (DATEDIFF(DAY, @dateStart, @dateEnd) + 1);
fetch next from detailsCursor
into @dateStart, @dateEnd;
end
close detailsCursor;
deallocate detailsCursor;
end
IF(@maxDaysAvailable > @daysAccumulated)
BEGIN
RAISERROR ('No se pueden ingresar mas dias de los programados en la cabecera de incidencias.', 16, 1);
ROLLBACK TRANSACTION;
RETURN
END
GO
When I do a Insert to the table TDINCI
当我对表进行插入时 TDINCI
INSERT INTO TDINCI
VALUES (1, '20150101', '20150115', '2015-2015')
I get an error:
我收到一个错误：
A cursor with the name 'detailsCursor' already exists.
名为“detailsCursor”的游标已存在。
I open
我打开
open detailsCursor;
and close the cursor.
并关闭光标。
close detailsCursor;
deallocate detailsCursor;
Maybe there is something with the scope of cursor that I don't manage? Thanks in advance.
也许有一些我无法管理的游标范围？提前致谢。
回答by Ahmad
You are using global cursor that will be defined each time you are calling this procedure and give you the same error.
您正在使用每次调用此过程时都会定义的全局游标，并给您相同的错误。
Define a local cursor. Just put the keyword LOCALafter CURSOR:
定义一个本地游标。只要把关键字LOCAL后CURSOR：
declare detailsCursor CURSOR LOCAL FOR
...
