Oracle PL/SQL - NO_DATA_FOUND 异常对存储过程性能有害吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/221909/
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
Oracle PL/SQL - Are NO_DATA_FOUND Exceptions bad for stored procedure performance?
提问by AJ.
I'm writing a stored procedure that needs to have a lot of conditioning in it. With the general knowledge from C#.NET coding that exceptions can hurt performance, I've always avoided using them in PL/SQL as well. My conditioning in this stored proc mostly revolves around whether or not a record exists, which I could do one of two ways:
我正在编写一个存储过程，它需要有很多条件。根据 C#.NET 编码中异常会损害性能的一般知识，我一直避免在 PL/SQL 中使用它们。我在这个存储过程中的调节主要围绕记录是否存在，我可以通过以下两种方式之一进行处理：
SELECT COUNT(*) INTO var WHERE condition;
IF var > 0 THEN
SELECT NEEDED_FIELD INTO otherVar WHERE condition;
....
-or-
-或者-
SELECT NEEDED_FIELD INTO var WHERE condition;
EXCEPTION
WHEN NO_DATA_FOUND
....
The second case seems a bit more elegant to me, because then I can use NEEDED_FIELD, which I would have had to select in the first statement after the condition in the first case. Less code. But if the stored procedure will run faster using the COUNT(*), then I don't mind typing a little more to make up processing speed.
第二种情况对我来说似乎更优雅一些，因为这样我就可以使用 NEEDED_FIELD，我必须在第一种情况下的条件之后的第一个语句中选择它。更少的代码。但是如果使用 COUNT(*) 存储过程会运行得更快，那么我不介意多输入一点来弥补处理速度。
Any hints? Am I missing another possibility?
任何提示？我错过了另一种可能性吗？
EDITI should have mentioned that this is all already nested in a FOR LOOP. Not sure if this makes a difference with using a cursor, since I don't think I can DECLARE the cursor as a select in the FOR LOOP.
编辑我应该提到这一切都已经嵌套在 FOR 循环中。不确定这是否与使用游标有所不同，因为我认为我无法将游标声明为 FOR LOOP 中的选择。
回答by RussellH
I would not use an explicit cursor to do this. Steve F. no longer advises people to use explicit cursors when an implicit cursor could be used.
我不会使用显式游标来做到这一点。Steve F. 不再建议人们在可以使用隐式游标时使用显式游标。
The method with count(*)is unsafe. If another session deletes the row that met the condition after the line with the count(*), and before the line with the select ... into, the code will throw an exception that will not get handled.
的方法count(*)是不安全的。如果另一个会话删除了在带有 的行之后和带有 的行count(*)之前满足条件的行select ... into，代码将抛出一个不会得到处理的异常。
The second version from the original post does not have this problem, and it is generally preferred.
原帖的第二个版本没有这个问题，一般是首选。
That said, there is a minor overhead using the exception, and if you are 100% sure the data will not change, you can use the count(*), but I recommend against it.
也就是说，使用异常会产生很小的开销，如果您 100% 确定数据不会更改，您可以使用count(*)，但我建议不要这样做。
I ran these benchmarks on Oracle 10.2.0.1on 32 bit Windows. I am only looking at elapsed time. There are other test harnesses that can give more details (such as latch counts and memory used).
我在32 位 Windows上的Oracle 10.2.0.1上运行了这些基准测试。我只看经过的时间。还有其他测试工具可以提供更多详细信息（例如闩锁计数和使用的内存）。
SQL>create table t (NEEDED_FIELD number, COND number);
Table created.
表已创建。
SQL>insert into t (NEEDED_FIELD, cond) values (1, 0);
1 row created.
已创建 1 行。
declare
otherVar number;
cnt number;
begin
for i in 1 .. 50000 loop
select count(*) into cnt from t where cond = 1;
if (cnt = 1) then
select NEEDED_FIELD INTO otherVar from t where cond = 1;
else
otherVar := 0;
end if;
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.70
PL/SQL 过程成功完成。
经过：00:00:02.70
declare
otherVar number;
begin
for i in 1 .. 50000 loop
begin
select NEEDED_FIELD INTO otherVar from t where cond = 1;
exception
when no_data_found then
otherVar := 0;
end;
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.06
PL/SQL 过程成功完成。
已用时间：00:00:03.06
回答by Noah Yetter
Since SELECT INTO assumes that a single row will be returned, you can use a statement of the form:
由于 SELECT INTO 假定将返回单行，因此您可以使用以下形式的语句：
SELECT MAX(column)
INTO var
FROM table
WHERE conditions;
IF var IS NOT NULL
THEN ...
The SELECT will give you the value if one is available, and a value of NULL instead of a NO_DATA_FOUND exception. The overhead introduced by MAX() will be minimal-to-zero since the result set contains a single row. It also has the advantage of being compact relative to a cursor-based solution, and not being vulnerable to concurrency issues like the two-step solution in the original post.
如果可用，SELECT 将为您提供值，以及 NULL 值而不是 NO_DATA_FOUND 异常。MAX() 引入的开销将最小为零，因为结果集包含单行。相对于基于游标的解决方案，它还具有紧凑的优点，并且不易受到并发问题的影响，如原始帖子中的两步解决方案。
回答by DCookie
An alternative to @Steve's code.
@Steve 代码的替代方法。
DECLARE
CURSOR foo_cur IS
SELECT NEEDED_FIELD WHERE condition ;
BEGIN
FOR foo_rec IN foo_cur LOOP
...
END LOOP;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END ;
The loop is not executed if there is no data. Cursor FOR loops are the way to go - they help avoid a lot of housekeeping. An even more compact solution:
如果没有数据，则不执行循环。光标 FOR 循环是要走的路 - 它们有助于避免大量的内务处理。更紧凑的解决方案：
DECLARE
BEGIN
FOR foo_rec IN (SELECT NEEDED_FIELD WHERE condition) LOOP
...
END LOOP;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END ;
Which works if you know the complete select statement at compile time.
如果您在编译时知道完整的 select 语句，这将起作用。
回答by RussellH
@DCookie
@DCookie
I just want to point out that you can leave off the lines that say
我只想指出，你可以省略说
EXCEPTION
WHEN OTHERS THEN
RAISE;
You'll get the same effect if you leave off the exception block all together, and the line number reported for the exception will be the line where the exception is actually thrown, not the line in the exception block where it was re-raised.
如果您将异常块放在一起，您将获得相同的效果，并且为异常报告的行号将是实际抛出异常的行，而不是异常块中重新引发异常的行。
回答by RussellH
Stephen Darlington makes a very good point, and you can see that if you change my benchmark to use a more realistically sized table if I fill the table out to 10000 rows using the following:
斯蒂芬·达林顿 (Stephen Darlington) 提出了一个很好的观点，您可以看到，如果我使用以下内容将表填充到 10000 行，则更改我的基准以使用更实际大小的表：
begin
for i in 2 .. 10000 loop
insert into t (NEEDED_FIELD, cond) values (i, 10);
end loop;
end;
Then re-run the benchmarks. (I had to reduce the loop counts to 5000 to get reasonable times).
然后重新运行基准测试。（我不得不将循环次数减少到 5000 以获得合理的次数）。
declare
otherVar number;
cnt number;
begin
for i in 1 .. 5000 loop
select count(*) into cnt from t where cond = 0;
if (cnt = 1) then
select NEEDED_FIELD INTO otherVar from t where cond = 0;
else
otherVar := 0;
end if;
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:04.34
declare
otherVar number;
begin
for i in 1 .. 5000 loop
begin
select NEEDED_FIELD INTO otherVar from t where cond = 0;
exception
when no_data_found then
otherVar := 0;
end;
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.10
The method with the exception is now more than twice as fast. So, for almost all cases,the method:
有例外的方法现在快两倍多。因此，对于几乎所有情况，该方法：
SELECT NEEDED_FIELD INTO var WHERE condition;
EXCEPTION
WHEN NO_DATA_FOUND....
is the way to go. It will give correct results and is generally the fastest.
是要走的路。它会给出正确的结果，而且通常是最快的。
回答by Stephen Darlington
If it's important you really need to benchmark both options!
如果这很重要，您真的需要对这两个选项进行基准测试！
Having said that, I have always used the exception method, the reasoning being it's better to only hit the database once.
话虽如此，我一直使用异常方法，理由是最好只访问一次数据库。
回答by Steve Bosman
Yes, you're missing using cursors
是的，您缺少使用游标
DECLARE
CURSOR foo_cur IS
SELECT NEEDED_FIELD WHERE condition ;
BEGIN
OPEN foo_cur;
FETCH foo_cur INTO foo_rec;
IF foo_cur%FOUND THEN
...
END IF;
CLOSE foo_cur;
EXCEPTION
WHEN OTHERS THEN
CLOSE foo_cur;
RAISE;
END ;
admittedly this is more code, but it doesn't use EXCEPTIONs as flow-control which, having learnt most of my PL/SQL from Steve Feuerstein's PL/SQL Programming book, I believe to be a good thing.
诚然，这是更多的代码，但它没有使用异常作为流控制，从史蒂夫·费尔斯坦的 PL/SQL 编程书中学到了我的大部分 PL/SQL，我相信这是一件好事。
Whether this is faster or not I don't know (I do very little PL/SQL nowadays).
我不知道这是否更快（我现在很少使用 PL/SQL）。
回答by pablo
Rather than having nested cursor loops a more efficient approach would be to use one cursor loop with an outer join between the tables.
与嵌套游标循环不同，一种更有效的方法是使用一个游标循环和表之间的外部连接。
BEGIN
FOR rec IN (SELECT a.needed_field,b.other_field
FROM table1 a
LEFT OUTER JOIN table2 b
ON a.needed_field = b.condition_field
WHERE a.column = ???)
LOOP
IF rec.other_field IS NOT NULL THEN
-- whatever processing needs to be done to other_field
END IF;
END LOOP;
END;
回答by pablo
you dont have to use open when you are using for loops.
当您使用 for 循环时，您不必使用 open。
declare
cursor cur_name is select * from emp;
begin
for cur_rec in cur_name Loop
dbms_output.put_line(cur_rec.ename);
end loop;
End ;
or
或者
declare
cursor cur_name is select * from emp;
cur_rec emp%rowtype;
begin
Open cur_name;
Loop
Fetch cur_name into Cur_rec;
Exit when cur_name%notfound;
dbms_output.put_line(cur_rec.ename);
end loop;
Close cur_name;
End ;
回答by Art
The count(*) will never raise exception because it always returns actual count or 0 - zero, no matter what. I'd use count.
count(*) 永远不会引发异常，因为它总是返回实际计数或 0 - 零，无论如何。我会使用计数。
