Mastering Oracle PL / SQL: Ефективна обробка даних, Інші СУБД, Бази даних, статті

Уникайте неявного перетворення типів


Гарантована сувора типізація всіх змінних також дає певне підвищення продуктивності. 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-операторів на основі записів.

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*