NO_DATA_FOUND异常对存储过程性能不好吗?

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?

有提示吗?我是不是错过了另一种可能性?

EDIT I 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循环中的一个select。

12 个解决方案

#1

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的行之前…在代码中，将抛出一个不会被处理的异常。

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.1 on 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.

PL / SQL过程成功完成。

Elapsed: 00:00:02.70

运行: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.

PL / SQL过程成功完成。

Elapsed: 00:00:03.06

运行:00:00:03.06

#2

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()引入的开销将从最小到零，因为结果集包含一行。与基于指针的解决方案相比，它还具有紧凑的优点，并且不容易受到并发问题的影响，如原始post中的两步解决方案。

#3

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语句，则可以使用它。

#4

@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.

如果您将异常块全部删除，您将得到相同的效果，并且为异常报告的行号将是实际抛出异常的行，而不是重新抛出异常的异常块中的行。

#5

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.

这是条路。它会给出正确的结果，通常是最快的。

#6

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.

说到这里，我一直使用异常方法，理由是最好只访问一次数据库。

#7

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.

诚然，这是更多的代码，但它不使用异常作为流控制，我从Steve Feuerstein的PL/SQL编程书中学到了我的大部分PL/SQL，我认为这是一件好事。

Whether this is faster or not I don't know (I do very little PL/SQL nowadays).

我不知道这是否更快(我现在很少使用PL/SQL)。

#8

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;

#9

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 ;

#10

May be beating a dead horse here, but I bench-marked the cursor for loop, and that performed about as well as the no_data_found method:

在这里，我可能遇到了麻烦，但是我把光标标记为loop，它的执行效果和no_data_find方法一样好:

declare
  otherVar  number;
begin
  for i in 1 .. 5000 loop
     begin
       for foo_rec in (select NEEDED_FIELD from t where cond = 0) loop
         otherVar := foo_rec.NEEDED_FIELD;
       end loop;
       otherVar := 0;
     end;
   end loop;
end;

PL/SQL procedure successfully completed.

PL / SQL过程成功完成。

Elapsed: 00:00:02.18

运行:00:00:02.18

#11

The count(*) will never raise exception because it always returns actual count or 0 - zero, no matter what. I'd use count.

count(*)永远不会引发异常，因为无论如何，它总是返回实际的count或0 - 0。我使用计数。

#12

The first (excellent) answer stated -

第一个(极好的)回答说-

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的行之前…在代码中，将抛出一个不会被处理的异常。

Not so. Within a given logical Unit of Work Oracle is totally consistent. Even if someone commits the delete of the row between a count and a select Oracle will, for the active session, obtain the data from the logs. If it cannot, you will get a "snapshot too old" error.

不是这样的。在给定的工作逻辑单元内，Oracle是完全一致的。即使有人提交了一个计数和一个select Oracle之间的行的删除操作，对于活动会话，Oracle也将从日志中获取数据。如果不能，您将得到一个“快照太旧”错误。