PL/SQl 嵌套 If 循环内部 if 循环不退出
大家好，我对 PL/SQL 有点陌生，所以有点困惑。我有一个名为 rec 的游标，在循环它时我有两个嵌套的 IF 语句。
CURSOR Cur IS
SELECT Mil.Id,
Mil.Record_Num,
Mil.Status,
Mil.file_processed,
Mil.Updated_By
FROM status_log mil
WHERE Mil.file_processed != 'Processed'
For Update of Mil.file_processed;
FOR Rec IN Cur LOOP
IF (Rec.status = 'Ready' OR Rec.status = 'Go') THEN
IF Length(Rec.Zip) = 5 AND
(Substr(Rec.Zip, 1, 3) = '303' OR
Substr(Rec.Zip, 1, 3) = '304' ) THEN
l_state:= 'ATL';
END IF;
UPDATE status_log mil
SET file_processed = 'Processed'
WHERE current of cur
END IF;
COMMIT;
END LOOP;
现在，只要光标有一条满足第二个 IF 的记录（即 Zip 长度为 5 并且代码以 303 或 304 开头），它就不再命中 update 语句该记录以及此后的所有记录。我什至尝试在 IF 循环中的逻辑之后使用 EXIT 语句，但无济于事。我做错了什么？
Hi everyone I am a bit new to PL/SQL so am a bit confused. I have a Cursor called rec and while looping through it I have two nested IF statements.
CURSOR Cur IS
SELECT Mil.Id,
Mil.Record_Num,
Mil.Status,
Mil.file_processed,
Mil.Updated_By
FROM status_log mil
WHERE Mil.file_processed != 'Processed'
For Update of Mil.file_processed;
FOR Rec IN Cur LOOP
IF (Rec.status = 'Ready' OR Rec.status = 'Go') THEN
IF Length(Rec.Zip) = 5 AND
(Substr(Rec.Zip, 1, 3) = '303' OR
Substr(Rec.Zip, 1, 3) = '304' ) THEN
l_state:= 'ATL';
END IF;
UPDATE status_log mil
SET file_processed = 'Processed'
WHERE current of cur
END IF;
COMMIT;
END LOOP;
Now whenever the cursor has one record that satisfies the second IF(i.e Zip is 5 in length and the code starts with 303 or 304) it doesnt hit the update statement anymore for that records and all record after that. I even tried using the EXIT statement after the logic in the IF loop but to no avail. What am I doing wrong?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
IF 不是循环。这实际上可能是一个重要的点，因为您说您已经尝试使用
EXIT，并且该语句的目的是从立即封闭的循环中退出。在这种情况下，这意味着光标上的循环（除非您的“执行某事的代码”可能包含其他循环）。因此，您放入其中的任何EXIT都会导致整个循环终止。如果它不执行更新，则“执行某些操作的代码”是 (a) 使用影响流控制的显式命令，例如
EXIT、CONTINUE，或GOTO，或 (b) 遇到导致控制切换到某个异常处理程序的错误。如果执行正在更新但失败，那么您应该会看到一个错误 - 除非再次有一个异常处理程序将其隐藏起来。
IFs aren't loops. This may actually be an important point since you say you've tried using an
EXIT, and the purpose of that statement is to exit from the immediately enclosing loop. In this case, that means the loop over the cursor (unless possibly your "code to do something" includes other loops). So anyEXITyou put in there would have caused the entire loop to terminate.If it doesn't execute the update, then the "code to do something" is either (a) using an explicit command that affects the flow control, such as
EXIT,CONTINUE, orGOTO, or (b) encountering an error that is causing control to switch to some exception handler somewhere.If the execute is updating but failing, then you should be seeing an error -- unless, again, there is an exception handler somewhere that is hiding it from you.
要更新的 status_log 行可能被另一个会话锁定。
您可以查询v$session视图的blocking_session列。
“做某事的代码”正在做某事。
file_processed列，但锁定了status列的行。我没有测试过，但这可能是一个问题。it is possible that status_log rows to update are locked by another session.
You can query the blocking_session column of v$session view.
the "code to do something" is doing something.
file_processedbut lock the row for the columnstatus. I've not tested, but that could be an issue.检查您的
CURSOR定义是否包含或排除表status_log...这可能是问题所在。Check to see if your
CURSORdefinition includes or excludes the tablestatus_log... That could be the problem.