понедельник, 25 октября 2010 г.

Oracle PL/SQL: Обработка ошибок

Создадим пакет и в нем тестовую процедуру, которая будет генерировать исключение (спецификацию пакета я опускаю, т.к. она очевидна):

  create or replace package body VTESTPKG is
  
    procedure Test1
    is
    begin
      raise NO_DATA_FOUND;
    end Test1;
  
  end VTESTPKG;

Теперь, вызовем VTESTPKG.Test1 и запишем текст исключения в output:

  begin
    VTESTPKG.Test1;
  exception
    when others then
      dbms_output.put_line(sqlerrm);
  end;

Результат будет выглядеть так:

ORA-01403: данные не найдены

Код и текст ошибки в SQLERRM есть, однако нам бы очень хотелось увидеть место возникновения ошибки - в идеале номер строки и стек вызовов. Ниже приведен код функции ERR_WHENCE, которая возвращает место возникновения ошибки, и стандартный обработчик ошибок RaiseError, который я использую при обработке всех ошибок в моем коде на PL/SQL.

воскресенье, 24 октября 2010 г.

Oracle SQL: INSERT WHEN

В Oracle SQL есть интересная конструкция "INSERT WHEN...", о которой не все знают.

INSERT
WHEN ([Condition]) THEN
INTO [TableName] ([ColumnName])
VALUES ([VALUES])
ELSE
INTO [TableName] ([ColumnName])
VALUES ([VALUES])
SELECT [ColumnName] FROM [TableName];

Во первых, эта конструкция позволяет с помощью одной каманды INSERT вставлять данные в разные таблицы.

Во вторых, попробуем решить следующую задачу: пусть имеется таблица VTEST с полем A VARCHAR2(10) и нужно  написать команду INSERT, которая будет вставлять значение 'AAA', отсутствующее в этой таблице (если такое значение уже есть, то новую запись создавать не нужно). Решение этой задачи может выглядеть примерно так:

INSERT INTO VTEST (A)
  SELECT 'AAA' FROM dual
    WHERE NOT EXISTS (SELECT * FROM VTEST WHERE A='AAA')

Такая конструкция, конечно, работает. Но плохо в ней то, что значение 'AAA' фигурирует дважды: в SELECT 'AAA' FROM dual и в SELECT * FROM VTEST WHERE A='AAA'.

А вот так выглядит та же операция, но с использованием INSERT WHEN:

INSERT
  WHEN NOT EXISTS (SELECT * FROM VTEST WHERE A=A0)
   THEN INTO VTEST (A) VALUES (A0)
  SELECT 'AAA' A0 FROM dual

Здесь 'AAA' присутствует только 1 раз.

===
Перепечатка материалов блога разрешается с обязательной ссылкой на blog.coolsoftware.ru