oracle 存储过程异常处理
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7596860/
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
Stored procedure exception handling
提问by user472625
SQL> DECLARE
2 TotalUpd NUMBER(36) := 0;
3 BEGIN
4 dbms_output.put_line ('Job Start time............... : ' || to_char(SYSDATE, ' hh24:mi:ss'));
5 UPDATE Asset SET _status = 'PROGRESS' WHERE status is null;
6 TotalUpd := SQL%ROWCOUNT;
7 dbms_output.put_line('Total Records Updated. : ' || TotalUpd);
8 COMMIT;
9 EXCEPTION
10 WHEN NO_DATA_FOUND THEN
11 dbms_output.put_line ('No more data to update.');
12 WHEN OTHERS THEN
13 dbms_output.put_line ('Error while status as SUCCESS ');
14 END ;
15 /
The result for the above procedure is Job Start time............... : 04:41:41 Total Records Updated. : 0
上述过程的结果是作业开始时间......... : 04:41:41 Total Records Updated。: 0
But my expected result is "No more row to be updated" must be printed,since i have truncated the table Asset.Please tell where I went wrong in this.
但我的预期结果是“没有更多的行要更新”必须打印，因为我已经截断了表资产。请告诉我哪里出错了。
回答by Kevin Burton
it is as simple as the update does not geneate an error if there is no data.
它就像如果没有数据更新不会产生错误一样简单。
you need to look at the value of TotalUpdif you want to control the flow of your code
TotalUpd如果你想控制你的代码流，你需要查看的值
DECLARE
TotalUpd NUMBER(36) := 0;
BEGIN
dbms_output.put_line ('Job Start time............... : '
|| TO_CHAR(SYSDATE, ' hh24:mi:ss'));
UPDATE Asset SET _status = 'PROGRESS' WHERE status IS null;
TotalUpd := SQL%ROWCOUNT;
IF TotalUpd = 0 THEN
dbms_output.put_line ('No more data to update.');
ELSE
dbms_output.put_line('Total Records Updated. : '
|| TotalUpd);
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line ('Error while status as SUCCESS ');
END;
回答by René Nyffenegger
The NO_DATA_FOUNDerror is not thrown in update statements.
将NO_DATA_FOUND在更新语句不抛出错误。
It is thrown in select intostatements if the select statement would return nothing.
select into如果 select 语句不返回任何内容，则在语句中抛出它。
See also Tahiti on select intounder select_item: *If the SELECT INTO statement returns no rows, PL/SQL raises the predefined exception NO_DATA_FOUND.*
另请参阅Tahiti on select intounder select_item：*如果 SELECT INTO 语句不返回任何行，PL/SQL 将引发预定义的异常 NO_DATA_FOUND。*
Oracle does not consider it an exception if an update statement does not update anything, hence no exception is thrown. However, if a select into statement cannot fill the variables, it is considered an error (and therefore in this case the NO_DATA_EXCEPTION is thrown(
如果更新语句没有更新任何内容，Oracle 不会将其视为异常，因此不会引发异常。但是，如果 select into 语句无法填充变量，则会被视为错误（因此在这种情况下会抛出 NO_DATA_EXCEPTION（
回答by vc 74
NO_DATA_FOUNDis thrown if a select intodoes not return any row, not if no rows were updated after an update statement.
NO_DATA_FOUND如果 aselect into不返回任何行，则抛出，如果在更新语句后没有更新任何行，则抛出。
I suggest you move your logic for handling this exception after the update itself:
我建议您在更新后移动处理此异常的逻辑：
IF (SQL%ROWCOUNT = 0) THEN
dbms_output.put_line ('No more data to update.');
回答by OTTA
Think NO_DATA_FOUND exception is only raised by a SELECT statement which you aren't using. Try testing SQL%COUNT and output as required.
认为 NO_DATA_FOUND 异常仅由您未使用的 SELECT 语句引发。尝试测试 SQL%COUNT 并根据需要输出。
