oracle 如何解决oracle中的fetch乱序问题？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28668646/
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
How to resolve fetch out of sequence in oracle?
提问by Thepallav_abhi
I have a procedure in which I am often getting the following error in oracle 11g:
我有一个程序，我经常在 oracle 11g 中收到以下错误：
ORA-01002: fetch out of sequence ORA-06512:
at "LEAVE.GES_ERP_LEV_FFS_INTERFACE_PRC", line 350 ORA-06512: at line 1.
at line 350 I have-
在第 350 行，我有-
BEGIN
FOR V_INTERFACE_EMP IN CUR_INTERFACE_EMP LOOP (Line 350)
EXIT WHEN CUR_INTERFACE_EMP%NOTFOUND;
V_ERR_FLAG := 'N';
V_LOCAL_EMP := 'Y';
BEGIN
The Cursor CUR_INTERFACE_EMP is declared as below
Cursor CUR_INTERFACE_EMP 声明如下
SELECT GELF.*
FROM GES_ERP_LEV_FFS_INTERFACE_T GELF
WHERE (GELF.BALANCE_FLAG != 'W'
OR GELF.CASE_FLAG = 'S'
OR SELF.BALANCE_FLAG IS NULL)
AND GELF.PROCESS_FLAG = 'N'
AND GELF.DATE_OF_RELEASE <= TRUNC(SYSDATE);
If i update some records of the table with Process_Flag Y,the batch works fine for some time and then again after some days we get this same issue.
如果我使用 Process_Flag Y 更新表的某些记录，则批处理可以正常工作一段时间，然后在几天后再次出现同样的问题。
Please help,let me know in case data is also needed for the mentioned table.
请帮助，如果上述表格还需要数据，请告诉我。
回答by Lalit Kumar B
If i update some records of the table with Process_Flag Y,the batch works fine for some time and then again after some days we get this same issue.
如果我使用 Process_Flag Y 更新表的某些记录，则批处理可以正常工作一段时间，然后在几天后再次出现同样的问题。
You try to fetch from a SELECT FOR UPDATE, however a COMMIT has already been issued before it.
您尝试从 SELECT FOR UPDATE 中获取，但是在它之前已经发出了 COMMIT。
I think you have a COMMITsomewhere INSIDE the LOOPwhich is causing this issue.
我认为您在导致此问题的 LOOP内某处有一个COMMIT。
A quote by Tom Kyte here:
由汤姆·凯特引述这里：
for x in ( select rowid rid, t.* from T ) loop update T set x = x+1 where rowid = x.rid; commit; end loop;That implicit cursor is fetched from "across a commit". It is the practice of keeping a cursor open after committing. It is a bad practice and is a common cause of ORA-1555 (the above looping construct in particular)
for x in ( select rowid rid, t.* from T ) loop update T set x = x+1 where rowid = x.rid; commit; end loop;该隐式游标是从“跨提交”中获取的。这是在提交后保持游标打开的做法。这是一个不好的做法，是 ORA-1555 的常见原因（特别是上面的循环结构）
Also, you are using a CURSOR FOR LOOP. The CURSOR FOR LOOP will terminate when all of the records in the cursor have been fetched. So, you don't need to EXITexplicitly.
此外，您正在使用CURSOR FOR LOOP。当游标中的所有记录都被提取后，CURSOR FOR LOOP 将终止。因此，您不需要显式退出。
You could simply do it as:
你可以简单地这样做：
FOR V_INTERFACE_EMP IN CUR_INTERFACE_EMP
LOOP
V_ERR_FLAG := 'N';
V_LOCAL_EMP := 'Y';
...
END LOOP;
