Mastering Oracle PL/SQL: Эффективная обработка данных

Источник: ln

Избегайте неявного преобразования типов

Гарантированная строгая типизация всех переменных также дает определенное повышение производительности. PL/SQL-машина очень благосклонна к разработчикам при работе с типами данных (что, по моему мнению, плохо). Попытайтесь присвоить строку числовой переменной, и сервер Oracle молча попытается преобразовать ее в число и выполнить присвоение. Присвойте дату переменной типа VARCHAR2, и она будет автоматически преобразована в строку.

Помимо того, что это - не лучшая практика программирования, преобразования типов данных заметно снижают производительность. Рассмотрим пример наиболее типичного среди ленивых разработчиков приема: преобразования строк в даты. Используя регистрацию текущего времени до и после выполнения с помощью процедуры DBMS_UTILITY.GET_TIME, мы можем создать процедуру, которая будет определять, сколько времени требуется на выполнение 1000000 преобразований типов данных.

create or replace
procedure data_type_test is
  x date;
  y varchar2(12) := '01.03.03';
  t number := dbms_utility.get_time;
  begin
    for i in 1 .. 1000000 loop
      x := y; --  неявное преобразование char в date 
    end loop;
    dbms_output.put_line((dbms_utility.get_time-t)//' cs');
  end;
/

Мы включим вывод результатов сервера и затем выполним тест.

SQL> set serverout on
SQL> exec data_type_test;
1160 cs

Процедура PL/SQL успешно завершена.

Это - впечатляющий результат. Он означает, что на старом Pentium III, на котором выполнялся этот тест, можно выполнить порядка 86000 преобразований типов в секунду. Очевидно, часть из 11,6 секунд общего времени выполнения ушла на PL/SQL-код, а не на преобразование типов. Какая же часть из 11,6 секунд была потрачена на преобразование типов? Следующий тест позволит ответить на этот вопрос. Мы пересоздадим процедуру, чтобы выполнялись те же действия, но без преобразования типов, поскольку типы совпадают.

create or replace
procedure data_type_test is
  x date;
  y x%type := to_date('01.03.03');
  t number := dbms_utility.get_time;
  begin
    for i in 1 .. 1000000 loop
      x := y;
    end loop;
    dbms_output.put_line((dbms_utility.get_time-t)//' cs');
  end;
/

Выполним эту более правильную версию.

SQL> exec data_type_test
99 cs

Процедура PL/SQL успешно завершена.

Ух, ты! 91 процент времени выполнения ушло исключительно на преобразование типов данных. Преобразование типов данных, хотя выполняется и быстро, но все равно требует существенных вычислительных ресурсов.

Замечание про переменные-счетчики цикла

Учтите, что переменная-счетчик цикла (представленная как "i" в следующих примерах) имеет тип PLS_INTEGER. Давайте присвоим переменную I переменной X, определенной как INTEGER и, если бы счетчик цикла был типа INTEGER, следующая процедура была бы оптимальна, и преобразование типов не понадобилось бы:

SQL> create or replace
  2    procedure num_test_as_integer is
  3    x integer;
  4    t number := dbms_utility.get_time;
  5    begin
  6      for i in 1 .. 10000000 loop
  7        x := i;
  8      end loop;
  9      dbms_output.put_line((dbms_utility.get_time-t)//' cs');
 10    end;
 11  /

Процедура создана.

SQL> exec num_test_as_integer
3124 cs

Процедура PL/SQL успешно завершена.

Теперь давайте повторим тест, но на этот раз определим переменную X типа PLS_INTEGER.

SQL> create or replace
  2    procedure num_test_as_pls is
  3    x pls_integer;
  4    t number := dbms_utility.get_time;
  5    begin
  6      for i in 1 .. 10000000 loop
  7        x := i;
  8      end loop;
  9      dbms_output.put_line((dbms_utility.get_time-t)//' cs');
 10   end;
 11  /

Процедура создана.

Если эта процедура работает быстрее предыдущей, есть большая вероятность, что переменная-счетчик цикла, фактически, имеет тип PLS_INTEGER:

SQL> exec num_test_as_pls
2181 cs

Процедура PL/SQL успешно завершена.

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

SQL> begin
  2    for i in power(2,31) .. power(2,31)+10 loop
  3      x := i;
  4    end loop;
  5  end;
  6  /
begin
*
ошибка в строке 1:
ORA-01426: переполнение числа
ORA-06512: на  line 2

Таким образом, мы можем быть уверены, что счетчик цикла имеет тип PLS_INTEGER, а не INTEGER, как и утверждается в руководстве (Как минимум, с версии 9.0.1. Авторы в оригинале, кстати, писали об ошибке в документации... Раньше (в версиях Oracle 7) документация по PL/SQL, действительно, была полна ошибок. Я даже целую книгу написал, в основном, об этом... Но тут проверил, и убедился, что документация давно корректна в этом отношении. - прим. В.К. ).:

Internally, PL/SQL assigns the values of the bounds to temporary PLS_INTEGER variables, and, if necessary, rounds the values to the nearest integer. The magnitude range of a PLS_INTEGER is -2**31 .. 2**31. So, if a bound evaluates to a number outside that range, you get a numeric overflow error...

От полей к строкам - использование атрибута %ROWTYPE

Регулярное использование атрибута %TYPE в PL/SQL-коде гарантирует, что изменения поля (или столбца) в базе данных будут автоматически учитываться в PL/SQL-приложениях. А что, если мы добавим или удалим целые столбцы из таблицы? Все PL/SQL-программы, выбирающие или обрабатывающие целые строки, могут перестать работать. Еще одна мощная возможность PL/SQL - защита от этих еще более серьезных изменений в базе данных путем объявления переменных с помощью атрибута %ROWTYPE. Рассмотрим следующий фрагмент SQL-кода:

select *
into var1, var2, ..., varN
from table
where ...

Можно использовать атрибут %TYPE в объявлении каждой из переменных VAR1, VAR2 и т.д., чтобы защититься от изменений типа данных столбцов таблицы, но что, если в таблице произойдут структурные изменения, например, будет добавлен или удален столбец? Код почти на любом другом языке, кроме PL/SQL, несомненно, перестанет работать.

Язык PL/SQL предлагает для простого решения этой проблемы атрибут %ROWTYPE. Он раз и навсегда защищает PL/SQL-программу от множества возможных изменений в базе данных. Рассмотрим процедуру, WITH_ROWTYPE, выбирающую строку из простой таблицы T.

SQL> create table T (
  2   c1 number,
  3   c2 number);

Таблица создана.

SQL> insert into T values (1,2);

1 строка создана.

SQL> create or replace
  2    procedure WITH_ROWTYPE is
  3      r T%ROWTYPE;
  4    begin
  5      select *
  6        into r
  7        from T
  8       where rownum = 1;
  9    end;
 10  /

Процедура создана.

Переменную R называют записью , и каждое поле записи соответствует столбцу базовой таблицы. Сначала давайте убедимся, что наша процедура работает при текущем определении таблицы T.

SQL> exec WITH_ROWTYPE

Процедура PL/SQL успешно завершена.

Давайте посмотрим, что происходит при изменении определения таблицы.

SQL> alter table T add c3 number;

Таблица изменена.

SQL> exec WITH_ROWTYPE

Процедура PL/SQL успешно завершена.

Наша процедура по-прежнему работает. Хотя понятно, что с точки зрения функциональности могут потребоваться некоторые изменения кода, по крайней мере, изменение таблицы не привело к возникновению ошибки в приложении. В версии 9 можно даже переименовать столбцы, и наша процедура все равно останется действительной.

SQL> alter table T rename column C1 to C01;

Таблица изменена.

SQL> exec WITH_ROWTYPE

Процедура PL/SQL успешно завершена.

Она продолжает работать даже после такого существенного изменения, как удаление столбца.

SQL> alter table T drop column C2;

Таблица изменена.

SQL> exec WITH_ROWTYPE

Процедура PL/SQL успешно завершена.

Использование атрибута %ROWTYPE делает PL/SQL-программы очень устойчивыми. Даже представления базы данных не так устойчивы к изменениям. Давайте удалим и пересоздадим таблицу T, а затем определим представление V на основе этой таблицы.

SQL> drop table T;

Таблица удалена.

SQL> create table T (
  2    c1 number,
  3    c2 number);

Таблица создана.

SQL> create or replace
  2  view V as select * from T;

Представление создано.

Теперь мы добавляем столбец к базовой таблице T.

  
SQL> alter table T add c3 number

Таблица изменена.

Добавление столбца к базовой таблице делает представление недействительным и требует его перекомпиляции. (Если просто обратиться к представлению, сервер сам его перекомпилирует - прим. В.К. )

SQL> alter view v compile;

Представление изменено.

Теперь давайте сравним наше представление и таблицу.

SQL> desc V
 Имя                                       Пусто?   Тип
 ----------------------------------------- -------- ----------------------------
 C1                                                 NUMBER
 C2                                                 NUMBER

SQL> desc T
 Имя                                       Пусто?   Тип
 ----------------------------------------- -------- ----------------------------
 C1                                                 NUMBER
 C2                                                 NUMBER
 C3                                                 NUMBER

Новый столбец просто отсутствует в перекомпилированном представлении. Причина этого в том, что представление, определенное как SELECT * FROM TABLE, сохраняется в базе данных в момент создания как:

select col1, col2, ..., colN from table

Представление не учитывает нового столбца, поскольку текст представления хранится в базе данных не как SELECT *. Из-за этого, по-видимому, не стоит использовать SELECT * при определении представлений. Подумайте о возможных последствиях, если придется удалить базовую таблицу и пересоздать ее с теми же именами столбцов, но в другом порядке.

Конечно, циничные читатели могут заметить, что часть преимуществ использования переменных, определенных с помощью атрибута %ROWTYPE, исчезают как только мы начинаем ссылаться на отдельные поля в переменной. Например, если мы расширить представленную ранее процедуру WITH_ROWTYPE для передачи результатов из таблицы T в другую таблицу:

SQL> drop table T;

Таблица удалена.

SQL> create table T (
  2   c1 number,
  3   c2 number );

Таблица создана.

SQL> insert into T values (1,2);

1 строка создана.

SQL> create table T1 as select * from T;

Таблица создана.

SQL> create or replace
  2   procedure WITH_ROWTYPE is
  3   r T%ROWTYPE;
  4   begin
  5   select *
  6   into r
  7   from T
  8   where rownum = 1;
  9  
 10   insert into T1
 11   values (r.c1, r.c2);
 12   end;
 13  /

Процедура создана.

SQL> exec WITH_ROWTYPE

Процедура PL/SQL успешно завершена.

Никаких проблем нет, но мы больше не защищены от изменений в базовых таблицах, поскольку ссылаемся на отдельные поля в переменной, объявленной с помощью атрибута %ROWTYPE. Если мы добавим столбец в таблицу T, процедура завершится сбоем при выполнении оператора INSERT, как продемонстрировано далее:

SQL> alter table T add c3 number;

Таблица изменена.

SQL> alter table T1 add c3 number;

Таблица изменена.

SQL> exec WITH_ROWTYPE
BEGIN WITH_ROWTYPE; END;

      *
ошибка в строке 1:
ORA-06550: Строка 1, столбец 7:
PLS-00905: неприемлемый объект SCOTT.WITH_ROWTYPE
ORA-06550: Строка 1, столбец 7:
PL/SQL: Statement ignored

Для версий 7 и 8 сервера Oracle единственным решением были явные ссылки на столбцы таблицы в операторе INSERT. Это позволяет восстановить работоспособность процедуры, но приводит к, вероятно, еще худшим последствиям: при добавлении столбца в таблицу процедура будет молча игнорировать его значения при вставке. Однако новые возможности работы с записями в операторах DML, начиная с версии 9.2, дают отличное решение всех этих проблем.

Использование записей в операторах DML

Хотя выбрать строку в переменную, объявленную с помощью атрибута %ROWTYPE, в PL/SQL можно было всегда, теперь можно также использовать такие переменные в операторах INSERT и UPDATE. Можно переписать процедуру WITH_ROWTYPE так, чтобы использовать новые возможности DML-операторов на базе записей при вставке данных. (Мы пересоздали и заново заполнили данными таблицы T и T1, как в предыдущем примере.)

SQL> create or replace
  2   procedure WITH_ROWTYPE is
  3   r T%ROWTYPE;
  4   begin
  5   select *
  6   into r
  7   from T
  8   where rownum = 1;
  9  
 10   insert into T1
 11   values r;
 12   end;
 13  /

Процедура создана.

Давайте посмотрим, что произойдет при добавлении столбца в таблицы T и T1.

SQL> alter table T add c5 number;

Таблица изменена.

SQL> alter table T1 add c5 number;

Таблица изменена.

Процедура по-прежнему работает, без изменений.

SQL> exec WITH_ROWTYPE

Процедура PL/SQL успешно завершена.

Немного изменив процедуру, мы можем продемонстрировать использование записей в операторе UPDATE. Можно изменить строку на основе всей записи, не ссылаясь на ее отдельные поля.

SQL> create or replace
  2   procedure WITH_ROWTYPE is
  3   r T%ROWTYPE;
  4   begin
  5   select *
  6   into r
  7   from T
  8   where rownum = 1;
  9  
 10   update T1
 11   set row = r
 12   where rownum = 1;
 13   end;
 14  /

Процедура создана.

Аналогичных возможностей использования записей в операторе DELETE нет, потому что DELETE в любом случае всегда удаляет всю строку.

Примечание: Гибкость DML-операторов на основе записей не дается даром. В следующей главе мы рассмотрим ряд проблем, о которых следует помнить при использовании DML-операторов на основе записей.

Ссылки по теме