SQL状态'02000'太早了！ [英] SQL status '02000' too early!
问题描述
大家好，
我在这里遇到一个非常奇怪的问题。我无法弄清楚
世界中会发生什么。
我有这个打开的光标，上面有一些记录。我循环记录
，直到我得到状态代码''02000''。问题是，我太早得到这个
状态代码，这意味着，在我处理光标中的所有记录之前，我已经离开了循环。
如何解释这个？！是否有解决方法？
为了简单起见，我只会列出存储的
程序中的相关代码。
开始代码片段：
DECLARE NOT_END NUMERIC（1）DEFAULT 1; - 用于检查结束
数据
DECLARE AT_END条件''02000''; - 数据结束条件
DECLARE CONTINUE HANDLER AT_END SET NOT_END = 0; - 退出
循环
OPEN cursorHoldings;
WHILE（NOT_END<> 0 ）DO
FETCH cursorHoldings INTO var1，var2，（...等等）
IF（NOT_END<> 0）那么
/ *
这里做的事情
* /
END IF;
END WHILE;
关闭cursorHoldings;
：结束代码片段
顺便说一下，我尝试了不同的方法，比如改变继续
处理程序声明
DECLARE CONTINUE HANDLER FOR NOT FOUND SET NOT_END = 0; - 离开
循环
它没用。
我以为我' '得到结果中的行数并循环某个
次，但是GET DIAGNOSTICS varName = ROW_COUNT"返回0.
我搜索了一些东西，告诉我行号，以便我可以存储
当前行号，并在下一次迭代中检查是否我我会在
相同的行号中退出，但我找不到任何要告诉我
哪一行我现在正在。此外，我不需要这样做，它应该像我现在这样做的方式工作。至少，这是引用的方式。
任何帮助都表示赞赏。
Hi everyone,
I''m facing a really wierd issue here. I can''t figure out what in the
world could be going on.
I have this open cursor with some records on it. I loop on the records
until I get a status code of ''02000''. The problem is, I get this
status code too early, meaning, I get out of the loop before I process
all the records in the cursor.
How can this be explained?! Is there a work around for that?
To keep it simple, I''ll only list relevant code from the stored
procedure.
Start code snippet:
DECLARE NOT_END NUMERIC(1) DEFAULT 1; -- used to check for end of
data
DECLARE AT_END CONDITION FOR ''02000''; -- condition for end of data
DECLARE CONTINUE HANDLER FOR AT_END SET NOT_END = 0; -- to get out of
loop
OPEN cursorHoldings;
WHILE (NOT_END <> 0) DO
FETCH cursorHoldings INTO var1, var2, (...and so on)
IF (NOT_END <> 0) THEN
/*
Do things here
*/
END IF;
END WHILE;
CLOSE cursorHoldings;
:End code snippet
By the way, I tried different approaches, like changing the continue
handler declaration to
DECLARE CONTINUE HANDLER FOR NOT FOUND SET NOT_END = 0; -- to get out
of loop
It didn''t work.
I thought I''d get the number of rows in the result and loop a certain
number of times, but "GET DIAGNOSTICS varName = ROW_COUNT" returned 0.
I searched for something to tell me the row number so that I can store
the current row number and in the next iteration check if I''m in the
same row number I would exit, but I can''t find anything to tell me
which row I''m on now. Besides, I don''t need to do that, it should work
the way I''m doing it now. At least, this is how the reference does it.
Any help is appreciated.
推荐答案
" Tarek M. Nabil" <钽********* @ itworx.com>在消息中写道
news：c8 ************************** @ posting.google.c om ...
"Tarek M. Nabil" <Ta*********@itworx.com> wrote in message
news:c8**************************@posting.google.c om...
大家好，
我在这里遇到了一个非常奇怪的问题。我无法弄清楚
世界中会发生什么。
我有这个打开的光标，上面有一些记录。我循环记录
直到我得到状态代码''02000''。问题是，我得到这个状态代码太早了，这意味着，在我处理光标中的所有记录之前，我已经离开了循环。
这怎么可能是解释？是否有解决方法？
为了简单起见，我只会列出存储的
程序中的相关代码。
启动代码段：
DECLARE NOT_END NUMERIC（1）DEFAULT 1; - 用于检查
数据的结束
声明''02000''的条件AT_END条件; - 数据结束的条件
对于AT_END设置NOT_END = 0的DECLARE CONTINUE HANDLER; - 退出
循环
OPEN cursorHoldings;
WHILE（NOT_END<> 0）DO
FETCH cursorHoldings INTO var1，var2 ，（......等等）
IF（NOT_END<> 0）那么
/ *
这里做的事情
* /
END IF; <结束时光线控制;
关闭cursorHoldings;
：结束代码片段
顺便说一下，我尝试了不同的方法，比如改变继续
DECLARE CONTINUE HANDLER FOR NOT FOUND SET NOT_END = 0; - 走出循环
它没用。
我以为我会得到结果中的行数并循环一定次数，但GET DIAGNOSTICS varName = ROW_COUNT返回0.
我搜索了一些东西，告诉我行号，以便我可以存储当前的行号，并在下一次迭代中检查我是否在
我会退出相同的行号，但我找不到任何要告诉我
我现在在哪一行。此外，我不需要这样做，它应该像我现在这样做的方式工作。至少，这是引用的方式。
任何帮助都表示赞赏。
Hi everyone,
I''m facing a really wierd issue here. I can''t figure out what in the
world could be going on.
I have this open cursor with some records on it. I loop on the records
until I get a status code of ''02000''. The problem is, I get this
status code too early, meaning, I get out of the loop before I process
all the records in the cursor.
How can this be explained?! Is there a work around for that?
To keep it simple, I''ll only list relevant code from the stored
procedure.
Start code snippet:
DECLARE NOT_END NUMERIC(1) DEFAULT 1; -- used to check for end of
data
DECLARE AT_END CONDITION FOR ''02000''; -- condition for end of data
DECLARE CONTINUE HANDLER FOR AT_END SET NOT_END = 0; -- to get out of
loop
OPEN cursorHoldings;
WHILE (NOT_END <> 0) DO
FETCH cursorHoldings INTO var1, var2, (...and so on)
IF (NOT_END <> 0) THEN
/*
Do things here
*/
END IF;
END WHILE;
CLOSE cursorHoldings;
:End code snippet
By the way, I tried different approaches, like changing the continue
handler declaration to
DECLARE CONTINUE HANDLER FOR NOT FOUND SET NOT_END = 0; -- to get out
of loop
It didn''t work.
I thought I''d get the number of rows in the result and loop a certain
number of times, but "GET DIAGNOSTICS varName = ROW_COUNT" returned 0.
I searched for something to tell me the row number so that I can store
the current row number and in the next iteration check if I''m in the
same row number I would exit, but I can''t find anything to tell me
which row I''m on now. Besides, I don''t need to do that, it should work
the way I''m doing it now. At least, this is how the reference does it.
Any help is appreciated.
我不知道你的编程问题是什么是的。
但是，ROW_COUNT为0的原因是DB2在获取它们之前不知道选择游标中的整个
行数（除非光标
实现是必要的，以返回第一行）。在某些情况下，
取决于隔离级别，答案集可能会在
首次提取和最后一次提取之间发生变化。
DB2确实会在SQL
语句中返回更新或删除的行数。
I don''t know what your programming problem is.
However, the reason that ROW_COUNT is 0 is that DB2 does not know the entire
number of rows in a select cursor until you fetch them (unless cursor
materialization is necessary to return the first row). In some cases,
depending on the isolation level, the answer set could change between the
first fetch and the last fetch.
DB2 does return to you the number of rows updated or deleted in an SQL
statement.
ROW_COUNT只是我在我尝试的内容努力解决
问题，现在根据这个，它显然不是解决方案。
如果DB2有一些它也会有所帮助变量可以告诉我
行，就像Oracle中的ROWNUM一样。这也可能是一种解决方法。
但是，这仍然不是原来的问题，原来的问题是
为什么SQLSTATE'02000的处理程序当光标中还有
行时，会调用''。
Mark A < ma@switchboard.net>在消息新闻中写道：< _F ***************** @ news.uswest.net> ...
ROW_COUNT was just something I tried in my efforts to work around the
problem, and now according to this, it''s obviously not the solution.
It would also help if DB2 had some variable that can tell me the row
number, like ROWNUM in Oracle. That could also be a work around.
But still, this is not the original problem, the original problem is
why the handler for SQLSTATE ''02000'' is called while there are still
rows left in the cursor.
"Mark A" <ma@switchboard.net> wrote in message news:<_F*****************@news.uswest.net>...
" Tarek M. Nabil" <钽********* @ itworx.com>在消息中写道
新闻：c8 ************************** @ posting.google.c om ...
"Tarek M. Nabil" <Ta*********@itworx.com> wrote in message
news:c8**************************@posting.google.c om...
大家好，
我在这里遇到了一个非常奇怪的问题。我无法弄清楚
世界中会发生什么。
我有这个打开的光标，上面有一些记录。我循环记录
直到我得到状态代码''02000''。问题是，我得到这个状态代码太早了，这意味着，在我处理光标中的所有记录之前，我已经离开了循环。
这怎么可能是解释？是否有解决方法？
为了简单起见，我只会列出存储的
程序中的相关代码。
启动代码段：
DECLARE NOT_END NUMERIC（1）DEFAULT 1; - 用于检查
数据的结束
声明''02000''的条件AT_END条件; - 数据结束的条件
对于AT_END设置NOT_END = 0的DECLARE CONTINUE HANDLER; - 退出
循环
OPEN cursorHoldings;
WHILE（NOT_END<> 0）DO
FETCH cursorHoldings INTO var1，var2 ，（......等等）
IF（NOT_END<> 0）那么
/ *
这里做的事情
* /
END IF; <结束时光线控制;
关闭cursorHoldings;
：结束代码片段
顺便说一下，我尝试了不同的方法，比如改变继续
DECLARE CONTINUE HANDLER FOR NOT FOUND SET NOT_END = 0; - 走出循环
它没用。
我以为我会得到结果中的行数并循环一定次数，但GET DIAGNOSTICS varName = ROW_COUNT返回0.
我搜索了一些东西，告诉我行号，以便我可以存储当前的行号，并在下一次迭代中检查我是否在
我会退出相同的行号，但我找不到任何要告诉我
我现在在哪一行。此外，我不需要这样做，它应该像我现在这样做的方式工作。至少，这是引用的方式。
任何帮助都表示赞赏。
Hi everyone,
I''m facing a really wierd issue here. I can''t figure out what in the
world could be going on.
I have this open cursor with some records on it. I loop on the records
until I get a status code of ''02000''. The problem is, I get this
status code too early, meaning, I get out of the loop before I process
all the records in the cursor.
How can this be explained?! Is there a work around for that?
To keep it simple, I''ll only list relevant code from the stored
procedure.
Start code snippet:
DECLARE NOT_END NUMERIC(1) DEFAULT 1; -- used to check for end of
data
DECLARE AT_END CONDITION FOR ''02000''; -- condition for end of data
DECLARE CONTINUE HANDLER FOR AT_END SET NOT_END = 0; -- to get out of
loop
OPEN cursorHoldings;
WHILE (NOT_END <> 0) DO
FETCH cursorHoldings INTO var1, var2, (...and so on)
IF (NOT_END <> 0) THEN
/*
Do things here
*/
END IF;
END WHILE;
CLOSE cursorHoldings;
:End code snippet
By the way, I tried different approaches, like changing the continue
handler declaration to
DECLARE CONTINUE HANDLER FOR NOT FOUND SET NOT_END = 0; -- to get out
of loop
It didn''t work.
I thought I''d get the number of rows in the result and loop a certain
number of times, but "GET DIAGNOSTICS varName = ROW_COUNT" returned 0.
I searched for something to tell me the row number so that I can store
the current row number and in the next iteration check if I''m in the
same row number I would exit, but I can''t find anything to tell me
which row I''m on now. Besides, I don''t need to do that, it should work
the way I''m doing it now. At least, this is how the reference does it.
Any help is appreciated.
我不知道你的编程问题是什么。但是，ROW_COUNT为0的原因是DB2在获取它们之前不知道选择游标中的整个行数（除非游标需要实现才能返回第一排）。在某些情况下，
取决于隔离级别，答案集可能会在首次提取和最后一次提取之间发生变化。
DB2确实会返回更新的行数或者在SQL
语句中删除。
I don''t know what your programming problem is.
However, the reason that ROW_COUNT is 0 is that DB2 does not know the entire
number of rows in a select cursor until you fetch them (unless cursor
materialization is necessary to return the first row). In some cases,
depending on the isolation level, the answer set could change between the
first fetch and the last fetch.
DB2 does return to you the number of rows updated or deleted in an SQL
statement.
" Tarek M. Nabil" <钽********* @ itworx.com>在消息中写道
news：c8 ************************** @ posting.google.c om ...
"Tarek M. Nabil" <Ta*********@itworx.com> wrote in message
news:c8**************************@posting.google.c om...
ROW_COUNT只是我努力解决
问题的尝试，现在根据这个，它显然不是解决方案。
如果DB2有一些变量可以告诉我行号，就像Oracle中的ROWNUM一样，这也会有所帮助。这也可能是一种解决方法。
但是，这仍然不是原始问题，最初的问题是为什么SQLSTATE''02000''的处理程序在仍然存在的情况下被调用
游标中留下的行。
ROW_COUNT was just something I tried in my efforts to work around the
problem, and now according to this, it''s obviously not the solution.
It would also help if DB2 had some variable that can tell me the row
number, like ROWNUM in Oracle. That could also be a work around.
But still, this is not the original problem, the original problem is
why the handler for SQLSTATE ''02000'' is called while there are still
rows left in the cursor.
DB2中有一个行号。看看row_number（）over声明。
但我认为修复你的程序会更好。
There is a row number in DB2. Look at the "row_number () over" statement.
But I think it would be better to fix your program.
这篇关于SQL状态'02000'太早了！的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
