Oracle PL / SQL - вредны ли исключения NO_DATA_FOUND для производительности хранимых процедур?

Да, вам не хватает курсоров

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 ;

по общему признанию, это больше кода, но он не использует ИСКЛЮЧЕНИЯ в качестве управления потоком, что, узнав большую часть моего PL / SQL из книги Стива Фейерштейна по программированию PL / SQL, я считаю, что это хорошо.

Быстрее ли это или нет, я не знаю (в настоящее время я очень мало использую PL / SQL).

Если это важно, вам действительно нужно протестировать оба варианта!

Сказав это, я всегда использовал метод исключения, мотивируя это тем, что лучше всего один раз попасть в базу данных.

Альтернатива коду @ 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 ;

Цикл не выполняется, если нет данных. Циклы курсора FOR - это то, что вам нужно - они помогают избежать лишних хлопот. Еще более компактное решение:

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

Что работает, если вы знаете полный оператор select во время компиляции.

Я бы не стал использовать для этого явный курсор. Стив Ф. больше не советует использовать явные курсоры, если можно использовать неявный курсор.

Метод с count(*) небезопасен. Если другой сеанс удаляет строку, удовлетворяющую условию, после строки с count(*) и перед строкой с select ... into, код вызовет исключение, которое не будет обработано.

Вторая версия из исходного сообщения не имеет этой проблемы, и обычно она предпочтительнее.

Тем не менее, использование исключения сопряжено с небольшими накладными расходами, и если вы на 100% уверены, что данные не изменятся, вы можете использовать count(*), но я не рекомендую этого делать.

Я провел эти тесты на Оракул 10.2.0.1 на 32-битная Windows. Я смотрю только на прошедшее время. Существуют и другие средства тестирования, которые могут предоставить более подробную информацию (например, количество защелок и использованную память).

SQL>create table t (NEEDED_FIELD number, COND number);

Table created.

SQL>insert into t (NEEDED_FIELD, cond) values (1, 0);

1 row created.

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

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

@DCookie

Я просто хочу указать, что вы можете оставить строки, в которых говорится

EXCEPTION  
  WHEN OTHERS THEN    
    RAISE;

Вы получите тот же эффект, если полностью откажетесь от блока исключения, и номер строки, сообщенный для исключения, будет той строкой, в которой фактически выбрасывается исключение, а не строкой в ​​блоке исключения, где оно было повторно возбуждено.

Стивен Дарлингтон делает очень хорошее замечание, и вы можете увидеть, что если вы измените мой тест, чтобы использовать таблицу более реалистичного размера, если я заполню таблицу до 10000 строк, используя следующее:

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

Затем повторно запустите тесты. (Мне пришлось уменьшить количество циклов до 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

Метод за исключением теперь более чем в два раза быстрее. Итак, практически для всех случаев метод:

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

это путь. Это дает правильные результаты и, как правило, является самым быстрым.

Может быть, здесь бьет дохлую лошадь, но я пометил курсор для цикла, и он работал примерно так же, как метод 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 успешно завершена.

Прошло: 00:00: 02.18

Поскольку SELECT INTO предполагает, что будет возвращена одна строка, вы можете использовать оператор формы:

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

IF var IS NOT NULL
THEN ...

SELECT даст вам значение, если оно доступно, и значение NULL вместо исключения NO_DATA_FOUND. Накладные расходы, вносимые MAX (), будут минимальными до нуля, поскольку набор результатов содержит единственную строку. Он также имеет то преимущество, что он компактен по сравнению с решением на основе курсора и не уязвим для проблем параллелизма, таких как двухэтапное решение в исходной публикации.

Вместо того, чтобы иметь вложенные циклы курсора, более эффективным подходом было бы использование одного цикла курсора с внешним соединением между таблицами.

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;

вам не нужно использовать open при использовании циклов for.

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 ;

или же

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 ;

Счетчик (*) никогда не вызовет исключение, потому что он всегда возвращает фактическое количество или 0 - ноль, независимо от того, что. Я бы использовал счет.

Первый (отличный) ответ гласил:

Метод с count () небезопасен. Если другой сеанс удаляет строку, удовлетворяющую условию, после строки с count (*) и перед строкой с select ... into, код выдаст исключение, которое не будет обработано.

Не так. В рамках данной логической единицы работы Oracle полностью согласован. Даже если кто-то совершит удаление строки между счетчиком и выбранным, Oracle для активного сеанса получит данные из журналов. В противном случае вы получите ошибку «Снимок слишком старый».