Ефективне кодування на PL / SQL, Інші СУБД, Бази даних, статті

Тригери, які спрацьовують по кілька разів залежно від події, можливість вказати триггерам одного типу послідовність спрацьовування, нова пропозиція CONTINUE – ось деякі з нових можливостей, які спрощують програмування на PL / SQL.

З самого початку PL / SQL була мовою, обраним для програмування в Oracle Database. Через якийсь час стало помітно, що завдяки все більшої функціональності, яка вимагає менше кодування мова розвинувся до ступеня, достатньої до всебічної розробки. Oracle Database 11 g робить кодування на 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
 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, який змінює чотири рядки.

 

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   Y
 
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 більш ефективно.

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


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

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

Ваш отзыв

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

*

*