Oracle PL/SQL - Sind NO_DATA_FOUND-Ausnahmen für die Leistung gespeicherter Prozeduren schlecht?

Da SELECT INTO davon ausgeht, dass eine einzelne Zeile zurückgegeben wird, können Sie eine Anweisung des Formulars verwenden:

SELECT MAX(column)
  INTO var
  FROM table
 WHERE conditions;

IF var IS NOT NULL
THEN ...

Das SELECT gibt Ihnen den Wert, wenn einer verfügbar ist, und den Wert NULL anstelle einer NO_DATA_FOUND-Ausnahme. Der durch MAX () eingeführte Overhead ist minimal zu null, da die Ergebnismenge eine einzelne Zeile enthält. Es hat auch den Vorteil, dass es relativ zu einer Cursor-basierten Lösung kompakt ist und nicht anfällig für Parallelitätsprobleme ist, wie die zweistufige Lösung im ursprünglichen Beitrag.

Eine Alternative zu @ Steve's Code. 

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 ;

Die Schleife wird nicht ausgeführt, wenn keine Daten vorhanden sind. Cursor-FOR-Schleifen sind der Weg zu gehen - sie helfen, viel Housekeeping zu vermeiden. Eine noch kompaktere Lösung:

DECLARE
BEGIN
  FOR foo_rec IN (SELECT NEEDED_FIELD WHERE condition) LOOP
     ...
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END ;

Was funktioniert, wenn Sie die vollständige select-Anweisung zur Kompilierzeit kennen.

@DCookie

Ich möchte nur darauf hinweisen, dass Sie die Zeilen mit den Worten weglassen können

EXCEPTION  
  WHEN OTHERS THEN    
    RAISE;

Das gleiche Ergebnis erhalten Sie, wenn Sie den Ausnahmeblock alle zusammen verlassen. Die für die Ausnahme gemeldete Zeilennummer ist die Zeile, in der die Ausnahme tatsächlich ausgelöst wird, nicht die Zeile im Ausnahmeblock, in der sie erneut ausgelöst wurde.

Stephen Darlington macht einen sehr guten Punkt, und Sie können sehen, dass, wenn Sie meinen Benchmark so ändern, dass er eine realistischere Tabelle verwendet, wenn ich die Tabelle auf 10000 Zeilen auffüllte, indem Sie Folgendes verwenden:

begin 
  for i in 2 .. 10000 loop
    insert into t (NEEDED_FIELD, cond) values (i, 10);
  end loop;
end;

Führen Sie dann die Benchmarks erneut aus. (Ich musste die Anzahl der Schleifen auf 5000 reduzieren, um vernünftige Zeiten zu erhalten).

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

Die Methode mit der Ausnahme ist jetzt mehr als doppelt so schnell. Also für fast alle Fälle die Methode:

SELECT NEEDED_FIELD INTO var WHERE condition;
EXCEPTION
WHEN NO_DATA_FOUND....

ist der Weg zu gehen. Es wird korrekte Ergebnisse liefern und ist im Allgemeinen das schnellste. 

Wenn es wichtig ist, müssen Sie wirklich beide Optionen bewerten!

Allerdings habe ich immer die Ausnahmemethode verwendet, weil es besser ist, die Datenbank nur einmal zu schlagen.

Ja, Sie vermissen den Einsatz von Cursorn

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 ;

zwar ist dies mehr Code, aber es wird nicht EXCEPTIONs als Flusskontrolle verwendet, da ich die meisten meiner PL/SQL aus Steve Feuersteins PL/SQL-Programmierbuch gelernt habe, glaube ich, dass es eine gute Sache ist.

Ob das schneller ist oder nicht, weiß ich nicht (ich mache heutzutage sehr wenig PL/SQL).

Anstatt verschachtelte Cursor-Schleifen zu haben, wäre ein effizienterer Ansatz die Verwendung einer Cursor-Schleife mit einem äußeren Join zwischen den Tabellen.

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;

Die erste (exzellente) Antwort lautete:

Die Methode mit count () ist unsicher. Wenn eine andere Sitzung die Zeile löscht, die die Bedingung nach der Zeile mit der Anzahl (*) und vor der Zeile mit Select ... erfüllt, löst der Code eine Ausnahme aus, die nicht behandelt wird.

Nicht so. Innerhalb einer bestimmten logischen Arbeitseinheit ist Oracle vollkommen konsistent. Selbst wenn jemand das Löschen der Zeile zwischen einem Count und einem ausgewählten Oracle festschreibt, erhält Oracle für die aktive Sitzung die Daten aus den Protokollen. Wenn dies nicht möglich ist, erhalten Sie die Fehlermeldung "Schnappschuss zu alt".

sie müssen nicht open verwenden, wenn Sie for-Loops verwenden.

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 ;

oder 

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 ;

Kann ein totes Pferd hier schlagen, aber ich habe den Cursor für die Schleife mit einer Benchmarking versehen, und das hat ungefähr ebenso funktioniert wie die Methode no_data_found:

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-Prozedur wurde erfolgreich abgeschlossen.

Abgelaufen: 00: 00: 02.18

Die Anzahl (*) löst niemals eine Ausnahme aus, da sie immer die tatsächliche Anzahl oder 0 - 0 zurückgibt, egal was passiert. Ich würde zählen zählen.