Эффективное программирование на PL/SQL
ОГЛАВЛЕНИЕ
Составные триггеры
Рассмотрим гостиничную базу данных: журналы комнат гостиницы хранятся в таблице BOOKINGS. Нужно также записывать изменения этой таблице для её контроля - что-то наподобие аудита, но с одной особенностью: нужно делать это транзакционно. Триггеры подходят для этого лучше всего.Тем самым нужен небольшой триггер события after-update для строки, который записывает старые и новые значения в таблицу BOOKINGS_HIST, а также того, кто внёс изменение. Пока всё хорошо.
Есть, однако, небольшая проблемка. Триггер after-update-row срабатывает для каждой строки, а некоторые записи изменяются в массовом порядке, изменяя сотни строк за одну транзакцию. Отдельные срабатывания триггера after-update-row для каждой из строк и выполнение каждый раз вставки записи в таблицу bookings_hist делают производительность не оптимальной.
Лучше было бы сложить вместе эти вставки в таблицу bookings_hist и выполнить их кучей. Это можно осуществить через сложную серию триггеров. Суть в том, что надо поместить значения, предназначенные для таблицы bookings_hist, в коллекцию в триггере на каждую стоку, а затем загрузить данные из коллекции в таблицу bookings_hist с помощью триггера after-update для предложения, который срабатывает только один раз. Так как фактически вставка происходит только один раз, процесс выполняется быстрее, чем вставка каждой отдельной строки.
Но это два различных триггера с различными кодами. Есть только один способ передать переменную с коллекцией из одного триггера в другой - создать пакет с переменной-коллекцией, такой как массив или PL/SQL-таблица, в спецификации пакета, заполнить её в строчном триггере after-update и считать в триггере after на предложение - а это непростая задача. Не проще было бы вместо этого поместить все триггеры в одном коде?
В Oracle Database 11 g можно использовать compound(составные) триггеры . Составные триггеры представляют собой четыре различных триггера, объявленных как один. Например, составной UPDATE-триггер имеет before для предложения, before для строки, after для предложения и after для строки, одновременно присутствующие в одном составном триггере. Вот часть кода, описывающая, как можно передать переменные будто бы внутри одного монолитного PL/SQL-кода.
Рассмотрим пример. Номера строк добавлены, чтобы было проще его объяснять.
1 create or replace trigger tr_bookings_trackЧтобы лучше понять работу триггера, выполним демонстрационный update, который изменяет четыре строки.
2 for update of booking_dt
3 on bookings
4 compound trigger
5 type ty_bookings_hist is table of bookings_hist%rowtype
6 index by pls_integer;
7 coll_bookings_hist ty_bookings_hist;
8 ctr pls_integer := 0;
9 before statement is
10 begin
11 dbms_output.put_line('In before statement');
12 end before statement;
13 before each row is
14 begin
15 dbms_output.put_line('In before each row');
16 end before each row;
17 after each row is
18 begin
19 ctr := ctr + 1;
20 dbms_output.put_line('In after each row. booking_id='//:new.booking_id);
21 coll_bookings_hist(ctr).booking_id := :new.booking_id;
22 coll_bookings_hist(ctr).mod_dt := sysdate;
23 coll_bookings_hist(ctr).mod_user := user;
24 coll_bookings_hist(ctr).old_booking_dt := :old.booking_dt;
25 coll_bookings_hist(ctr).new_booking_dt := :new.booking_dt;
26 end after each row;
27 after statement is
28 begin
29 dbms_output.put_line('In after statement');
30 forall counter in 1..coll_bookings_hist.count()
31 insert into bookings_hist
32 values coll_bookings_hist(counter);
33 end after statement;
34 end tr_bookings_track;
update bookingsВот результат:
set booking_dt = sysdate
where booking_id between 100 and 103;
In before statement
In before each row
In after each row. booking_id=100
In before each row
In after each row. booking_id=101
In before each row
In after each row. booking_id=102
In before each row
In after each row. booking_id=103
In after statement
Заметьте, как выполняется составной триггер. Он имеет четыре секции:
Before Statement
... выполняется один раз перед предложением...
Before Row
... выполняется один раз для каждой строки перед самим действием...
After Row
... выполняется один раз для каждой строки после действия...
After Statement
... выполняется один раз для предложения...
Как видите, этот код един, но каждая секция выполняется в разное время.
В предыдущем примере я поместил предложения dbms_output в различных местах, чтобы показать, как каждая секция выполняется в этих точках. Я изменил четыре строки с booking_ids 100, 101, 102 и 103, и видно, что триггеры before- и after для предложения сработали каждый по одному разу, а триггеры для строки (before и after) по одному разу на строку. (В предыдущем примере триггеры before для предложения и строки не нужны, но я их написал для иллюстрации функциональности).
Если посмотреть в таблицу bookings_hist, то можно увидеть, что в ней теперь четыре записи - одна для каждого booking_id - но эти четыре записи были вставлены кучей в конце предложения, а не при изменении каждой строки:
BOOKING_ID MOD_DT MOD_USER OLD_BOOKI NEW_BOOKI
---------- --------- ------------------------------ --------- ---------
100 27-SEP-07 ARUP 28-AUG-07 27-SEP-07
101 27-SEP-07 ARUP 06-AUG-07 27-SEP-07
102 27-SEP-07 ARUP 04-SEP-07 27-SEP-07
103 27-SEP-07 ARUP 15-JUN-07 27-SEP-07
Одна очень полезная возможность составных триггеров состоит в том, что внутренние объекты PL/SQL-кода, такие как переменные, пакеты и т.п., создаются при срабатывании триггера, а в конце работы триггера их состояние очищено. В примере выше видно, что коллекция не инициализировалась и содержимое коллекции не удалялось. Всё это было сделано автоматически без моего вмешательства.
Последовательность выполнения триггеров
Начиная с Oracle8 появилась возможность описать несколько триггеров одинакового типа на одной таблице - например два строчных after-триггера при вставке в одну таблицу. Тип триггеров определяет порядок выполнения: перед предложением, перед строкой, после предложения и после строки. Однако, если есть два строчных after-триггера, T1 и T2, то какой из них сработает первым?
Выполнение триггеров одинакового типа произвольно или по крайне мере не гарантированно следует шаблону. Является ли это проблемой? Давайте рассмотрим пример таблицы PAYMENTS, показанный ниже:
Name Null? Type
----------------------------------------- -------- ----------------
PAY_ID NUMBER(10)
CREDIT_CARD_NO VARCHAR2(16)
AMOUNT NUMBER(13,2)
PAY_MODE VARCHAR2(1)
RISK_RATING VARCHAR2(6)
FOLLOW_UP VARCHAR2(1)
Необходимо вычислить рейтинг риска в зависимости от типа платежей и суммы и сохранить его в столбце RISK_RATING. Следующий простой строчный триггер before-update легко справляется с этой задачей:
before update
on payments
for each row
begin
dbms_output.put_line ('This is tr_pay_risk_rating');
if (:new.amount) < 1000 then
:new.risk_rating := 'LOW';
elsif (:new.amount < 10000) then
if (:new.pay_mode ='K') then
:new.risk_rating := 'MEDIUM';
else
:new.risk_rating := 'HIGH';
end if;
else
:new.risk_rating := 'HIGH';
end if;
end;
/
Теперь кто-нибудь добавляет ещё одно требование: некоторые значения, зависящие от столбцов RISK_RATING, PAY_MODE, и др. должны быть в столбце FOLLOW_UP помечены признаком повышения. Триггер необходимо модифицировать, но лучше не трогать существующий код, а создать новый триггер такого же типа (строчный before-update), как показано ниже. (Я поместил в код dbms_output, чтобы показать, как триггеры будут срабатывать).
create or replace trigger tr_pay_follow_up
before update
on payments
for each row
begin
dbms_output.put_line ('This is tr_pay_follow_up');
if (
(:new.risk_rating = 'HIGH' and :new.pay_mode = 'C')
or (:new.risk_rating = 'MEDIUM' and :new.pay_mode = 'K')
or (substr(:new.credit_card_no,1,5) = '23456')
) then
:new.follow_up := 'Y';
else
:new.follow_up := 'N';
end if;
end;
/
Теперь если выполнить обновление таблицы:
SQL> get upd_pay
1 update payments set
2 credit_card_no = '1234567890123456',
3 amount = 100000,
4* pay_mode = 'K'
SQL> @upd_pay
This is tr_pay_follow_up
This is tr_pay_risk_rating
1 row updated.
SQL> select * from payments;
PAY_ID CREDIT_CARD_NO AMOUNT P RISK_R F
---------- ---------------- ---------- - ------ -
1 1234567890123456 100000 C HIGH N
Что же случилось? Столбец risk_rating имеет значение HIGH, а столбец pay_mode - значение "C", которые означают, что столбец FOLLOW_UP должен быть "Y", а не "N". Почему? Чтобы ответить на этот вопрос, посмотрите, в каком порядке сработали триггеры: tr_pay_follow_up сработал раньше, чем tr_pay_risk_rating. Последний установил значение столбца как высокий рейтинг. Поэтому, когда первый сработал, он нашёл null (или "N") в столбце risk_rating и поэтому посчитал, что условие удовлетворяется.
В этом случае порядок выполнения триггеров очень важен. Если tr_pay_risk_rating не сработает раньше второго, корректные значения не будут установлены, а то, что установится, будет неверной реализацией требований. Единственной простой возможностью была бы замена всей логики на один код и выполнение её в нужном порядке.
В Oracle Database 11 g можно в скрипте создания триггера указать выражение, которое устанавливает порядок триггеров. Вот верхняя часть триггера, включающая это выражение:
create or replace trigger tr_pay_follow_up
before update
on payments
for each row
follows tr_pay_risk_rating
begin
... and so on...
Это выражение (FOLLOWS <название_триггера>) заставляет триггер срабатывать после указанного триггера. Протестируем это, выполнив скрипт обновления, показанный ранее.
SQL> @upd_pay
This is tr_pay_risk_rating
This is tr_pay_follow_up
1 row updated.
SQL> select * from payments;
PAY_ID CREDIT_CARD_NO AMOUNT P RISK_R F
---------- ---------------- ---------- - ------ -
1 1234567890123456 100000 C HIGH
1 row selected.
Как и ожидалось, столбец заполнен корректно. Заметьте также корректный порядок триггеров, который подтверждает, что выражение работает.
Порядок триггеров позволяет применить преимущества модульного кода, позволяя также гарантировать, что они выполнятся в правильной последовательности.
Когда выполнять нечего, выполняем CONTINUE
Наряду со всеми своими возможностями до настоящего времени в PL/SQL была упущена одна важная часть грамматики: как показать, что ничего делать не надо, а надо перейти в конец цикла и продолжить его выполнение.
В Oracle Database 11 g в PL/SQL есть новая конструкция CONTINUE, которая используется в цикле. Это предложение перемещает логику в конец цикла, а затем на начало цикла. Вот небольшой пример, который показывает, как управление передаётся на конец цикла, когда счётчик не кратен 10.
begin
for ctr in 1..100 loop
continue when mod(ctr,10) != 0;
dbms_output.put_line ('ctr='//ctr);
end loop;
end;
/
Результат:
ctr=10
ctr=20
ctr=30
... и так далее ...
Другой вариант CONTINUE - это использование Названия Цикла.
begin
<<OuterLoop>>
for outer in 1..10 loop
dbms_output.put_line ('-> outer='//outer);
for inner in 1..10 loop
continue OuterLoop when mod(inner,3) = 0;
dbms_output.put_line ('..-> inner='//inner);
end loop;
end loop;
end;
/
Результат:
-> outer=1
..-> inner=1
..-> inner=2
-> outer=2
..-> inner=1
..-> inner=2
-> outer=3
..-> inner=1
..-> inner=2
... и так далее ...
Вместо использования предопределённой конструкции, такой как mod(inner,3), можно использовать функцию, которая выполняет некоторое вычисление.
begin
<<OuterLoop>>
for outer in 1..10 loop
dbms_output.put_line ('-> outer='//outer);
for inner in 1..10 loop
continue OuterLoop when (myfunc = 1);
dbms_output.put_line ('..-> inner='//inner);
end loop;
end loop;
end;
/
Излишне говорить, что эту конструкцию можно использовать только внутри цикла, где она имеет смысл. Если попытаться использовать её снаружи цикла, то появится ошибка компиляции.
Прямые последовательности
Когда ранее в программе на PL/SQL использовалась последовательность, нужно было использовать конструкцию типа SELECT <последовательность>.NEXTVAL INTO <название_переменной> FROM DUAL вплоть до этого релиза.
declare
trans_id number(10);
begin
select myseq.nextval
into trans_id
from dual;
end;
Больше не нужно. Вы можете непосредственно присвоить переменной следующее значение последовательности:
declare
trans_id number(10);
begin
trans_id := myseq.nextval;
end;
/
Вот что я называю простотой.
Предложение "When OTHERS Then" делает что-нибудь
Многие PL/SQL-программисты прибегают к опасной практике, оставляя исключение OTHERS проигнорированным, как показано ниже:
when OTHERS then
NULL;
Это говорит примерно о следующем: "Когда возникает ошибка, ничего делать не надо; только проигнорировать или сделать вид, что этого никогда не случится и что это не случится повторно". Если бы только мир был так прост! Эта практика приводит к потенциально ошибочному нестабильному коду.
Oracle Database 11 g помогает немного в этом направлении. В нём есть новое замечание PLW-06009, уведомляющее о такой проблеме во время компиляции. Вот пример:
create or replace procedure myproc as
l_dummy varchar2(1);
begin
select dummy
into l_dummy
from dual;
exception
when OTHERS then
null;
end;
При компиляции процедура компилируется без замечаний, как было в 10 g . Чтобы включить это замечание, необходимо установить параметр сессии.
SQL> alter session set plsql_warnings = 'enable:all'
2 /
Session altered.
SQL> @others1
SP2-0804: Procedure created with compilation warnings
SQL> show error
Errors for PROCEDURE MYPROC:
LINE/COL ERROR
-------- -----------------------------------------------------------------
8/7 PLW-06009: procedure "MYPROC" OTHERS handler does not end in
RAISE or RAISE_APPLICATION_ERROR
Заметьте, что новое замечание PLW-06009 возникает во время компиляции. Причём это только замечание; компиляция в целом выполнена успешно. Процедуру выполнить можно, но имейте в виду замечание!
Отключенные триггеры
В широко распространённых производственных системах часто используется окно для редактирования, с помощью которого вносятся изменения. Вот сценарий типичной несостоявшийся "Catch-22" [ упоминание известного романа Джозеф а Хеллер а " Уловка-22 " - прим.ред . ] в таких средах: вы хотите добавить триггер на таблицу и для этого применяете скрипт, однако, когда триггер создан в редакторе, он показывает ошибки компиляции из-за некоторых нежелательных причин, например, пропущенного синонима. Вы хотите создать триггер раньше синонима, но когда он создаётся, то сразу включается, и вы ничего не можете сделать вне этого редактора. Что в этом случае можно предпринять?
В Oracle Database 11 g этот сценарий больше не является проблемой. Можно создать триггер изначально отключенным, что позволяет протестировать все ошибки компиляции. А позже при редактировании включить его. Вот как его можно создать:
create or replace trigger tr_t
after insert on t
for each row
disable
begin
insert into t1 (a) values (:new.col_a);
end;
/
Теперь можно проверить его статус:
SQL> select status
2> from user_triggers
3> where trigger_name = 'TR_T'
4> /
STATUS
--------
DISABLED
Даже несмотря на то, что триггер создан отключенным, он должен быть без ошибок. Поэтому, если попытаться создать его с ошибкой (например, используя таблицу "M", которая не существует):
1 create or replace trigger tr_t
2 after insert on t
3 for each row
4 disable
5 begin
6 insert into m (a) values (:new.col_a);
7* end;
SQL> /
Warning: Trigger created with compilation errors.
SQL> show error
Errors for TRIGGER TR_T:
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/3 PL/SQL: SQL Statement ignored
2/15 PL/SQL: ORA-00942: table or view does not exist
Эта особенность очень полезна в процессе контроля изменений. Другое замечательное применение этой возможности - включение триггеров в определённый момент. Например, с помощью триггеров вы создаёте решение для аудита и audit_table ещё не очищена от старых записей. Триггеры можно создать отключенными, а включить их позже, когда таблица будет готова.
Именованные параметры функции
Рассмотрим простую функцию:
create or replace function myfunc
(
p_param1 number,
p_param2 number
)
return number
is
begin
return p_param1 + p_param2;
end;
/
Эта функция делает очень простую операцию, но она хорошо демонстрирует концепцию. Так как в этой функции два параметра, то её можно вызвать, передав параметры, как позиционные значения, а именно:
myfunc (1,2)
Или как именованные параметры:
myfunc ( p_param1 => 1, p_param2 => 2)
Однако, в конце концов возникает проблема, если использовать её в select-предложениях. Если в Oracle Database 10 g выполнить следующее предложение:
SQL> select myfunc (p_param1=>1,p_param2=>1) from dual;
возникнет ошибка:
select myfunc (p_param1=>1,p_param2=>1) from dual
*
ERROR at line 1:
ORA-00907: missing right parenthesis
В Oracle Database 11 g вы вправе использовать нотацию:
SQL> select myfunc (p_param1=>1,p_param2=>1) from dual;
MYFUNC(P_PARAM1=>1,P_PARAM2=>1)
-------------------------------
2
1 row selected.
...которая работает правильно. Можно указывать именованную нотацию в конце, а первые параметры должны быть позиционными. Например, следующий вызов, где параметр p_param1 равен 1, будет корректным:
select myfunc (1,p_param2=>2) from dual
А этот нет (позиционный параметр в конце):
SQL> select myfunc (p_param1=>1,2) from dual;
select myfunc (p_param1=>1,2) from dual
*
ERROR at line 1:
ORA-06553: PLS-312: a positional parameter association may not follow a named association
Взаимозаменяемость динамического курсора и REF-курсора
Вы знаете, каким полезным может быть Native Dynamic Cursor, особенно, когда до вызова не знаешь точно, что будет запрашиваться. Динамический PL/SQL можно также использовать через DBMS_SQL. Оба метода имеют свои преимущества. Но что будет, если вы начали разрабатывать программу, в которой используется сначала один метод, а затем необходимо переключиться на другой?
В Oracle Database 11 g этот процесс необыкновенно прост. Поддерживаемый пакет DBMS_SQL имеет новую функцию, TO_REFCURSOR, которая конвертирует динамический курсор DBMS_SQL в ref-курсор. Вот пример такой конвертации:
1 create or replace procedure list_trans_by_store
2 (
3 p_store_id number
4 )
5 is
6 type num_tab is table of number index by binary_integer;
7 type type_refcur is ref cursor;
8 c_ref_trans_cur type_refcur;
9 c_trans_cur number;
10 trans_id num_tab;
11 trans_amt num_tab;
12 ret integer;
13 l_stmt clob;
14 begin
15 c_trans_cur := dbms_sql.open_cursor;
16 l_stmt :=
17 'select trans_id, trans_amt from trans where store_id = :store_id';
18 dbms_sql.parse(c_trans_cur, l_stmt, dbms_sql.native);
19 dbms_sql.bind_variable(c_trans_cur, 'store_id', p_store_id);
20 ret := dbms_sql.execute(c_trans_cur);
21 c_ref_trans_cur := dbms_sql.to_refcursor(c_trans_cur);
22 fetch c_ref_trans_cur bulk collect into trans_id, trans_amt;
23 for ctr in 1.. trans_id.count loop
24 dbms_output.put_line(trans_id(ctr) // ' ' // trans_amt(ctr));
25 end loop;
26 close c_ref_trans_cur;
27* end;
Предположим, нужно написать общую процедуру, которая не знает списка столбцов в select-выражении во время компиляции. Это тот случай, когда native dynamic SQL становится необходимым. Можно описать для него ref-курсор. Теперь, чтобы стало интереснее, предположим, что вы не знаете всех bind-переменных, для этого случая больше всего подходит dbms_sql. Как выполнить это сложное требование, написав минимум кода? Просто: начните с dbms_sql для bind-переменных, а затем конвертируйте в ref-курсор.
Аналогично, чтобы конвертировать Native Dynamic SQL в REF-курсор, необходимо вызвать другую функцию, TO_CURSOR_NUMBER:
cur_handle := dbms_sql.to_cursor_number (c_ref_cur);
Ref-курсор, определённый в переменной c_ref_cur, должен быть открыт раньше этого вызова. После этого вызова жизнь ref-курсора закончена; манипулировать можно только dbms_sql-курсором.
Предположим, что вы знаете bind-переменные во время компиляции, но не знаете списка select; вы можете начать с native dynamic sql, с ref-курсора, а потом заменить его на dbms_sql, чтобы описать и извлечь столбцы из курсора.
Заключение
Как видите, Oracle Database 11 g содержит несколько улучшений, которые помогают писать код на PL/SQL более эффективно.