Аруп Нанда, член-директор Oracle ACE
Тригери, які спрацьовують по кілька разів залежно від події, можливість вказати триггерам одного типу послідовність спрацьовування, нова пропозиція 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-коду.
Розглянемо приклад. Номери рядків додані, щоб було простіше його пояснювати.
Щоб краще зрозуміти роботу тригера, виконаємо демонстраційний update, який змінює чотири рядки.
Зауважте, як виконується складовою тригер. Він має чотири секції:
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 - але ці чотири записи були вставлені купою в кінці речення, а не при зміні кожного рядка:
Одна дуже корисна можливість складових тригерів полягає в тому, що внутрішні об'єкти PL / SQL-коду, такі як змінні, пакети і т.п. створюються при спрацьовуванні тригера, а в кінці роботи тригера їх стан очищено. В наведеному вище прикладі видно, що колекція не ініціалізувати і вміст колекції не віддалялося. Все це було зроблено автоматично без мого втручання.
Послідовність виконання тригерів
Починаючи з Oracle8 з'явилася можливість описати кілька тригерів однакового типу на одній таблиці - наприклад два малих after-тригера при вставці в одну таблицю. Тип тригерів визначає порядок виконання: перед пропозицією, перед рядком, після пропозиції і після рядка. Однак, якщо є два малих after-тригера, T1 і T2, то який із них спрацює першим?
Виконання тригерів однакового типу довільно або по вкрай мері не гарантовано слід шаблоном. Чи є це проблемою? Давайте розглянемо приклад таблиці PAYMENTS, показаний нижче:
Необхідно обчислити рейтинг ризику в залежності від типу платежів і суми і зберегти його в стовпці RISK_RATING. Наступний простий рядковий тригер before-update легко справляється з цим завданням:
Тепер хтось додає ще одну вимогу: деякі значення, що залежать від стовпців RISK_RATING, PAY_MODE, і ін. Повинні бути в стовпці FOLLOW_UP помічені ознакою підвищення. Тригер необхідно модифікувати, але краще не чіпати існуючий код, а створити новий тригер такого ж типу (рядковий before-update), як показано нижче. (Я помістив в код dbms_output, щоб показати, як тригери будуть спрацьовувати).
Тепер якщо виконати оновлення таблиці:
Що ж трапилося? Стовпець 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 можна в скрипті створення тригера вказати вираз, яке встановлює порядок тригерів. Ось верхня частина тригера, що включає цей вислів:
Цей вислів (FOLLOWS <название_триггера>) Змушує тригер спрацьовувати після зазначеного тригера. Протестуємо це, виконавши скрипт оновлення, показаний раніше.
Як і очікувалося, стовпець заповнений коректно. Зауважте також коректний порядок тригерів, який підтверджує, що вираз працює.
Порядок тригерів дозволяє застосувати переваги модульного коду, дозволяючи також гарантувати, що вони виконаються в правильній послідовності.
Коли виконувати нічого, виконуємо CONTINUE
Поряд з усіма своїми можливостями до теперішнього часу в PL / SQL була упущена одна важлива частина граматики: як показати, що нічого робити не треба, а треба перейти в кінець циклу і продовжити його виконання.
В Oracle Database 11 g в PL / SQL є нова конструкція CONTINUE, яка використовується в циклі. Ця пропозиція переміщує логіку в кінець циклу, а потім на початок циклу. Ось невеликий приклад, який показує, як управління передається на кінець циклу, коли лічильник не кратний 10.
Інший варіант CONTINUE - це використання Назви Циклу.
Замість використання визначеної конструкції, такий як mod (inner, 3), можна використовувати функцію, яка виконує деякий обчислення.
Зайве говорити, що цю конструкцію можна використовувати тільки всередині циклу, де вона має сенс. Якщо спробувати використовувати її зовні циклу, то з'явиться помилка компіляції.
Коли раніше в програмі на PL / SQL використовувалася послідовність, потрібно було використовувати конструкцію типу SELECT <последовательность>.NEXTVAL INTO <название_переменной> FROM DUAL аж до цього релізу.
Більше не потрібно. Ви можете безпосередньо привласнити змінної таке значення послідовності:
Ось що я називаю простотою.
Пропозиція "When OTHERS Then" робить що-небудь
Багато PL / SQL-програмісти вдаються до небезпечної практики, залишаючи виключення OTHERS проігнорованим, як показано нижче:
Це говорить приблизно про таке: "Коли виникає помилка, нічого робити не треба, тільки проігнорувати або зробити вигляд, що цього ніколи не станеться і що це не станеться повторно". Якби тільки світ був такий простий! Ця практика призводить до потенційно помилкового нестабільного коду.
Oracle Database 11 g допомагає трохи в цьому напрямку. У ньому є нове зауваження PLW-06009, що повідомляє про таку проблему під час компіляції. Ось приклад:
При компіляції процедура компілюється без зауважень, як було в 10 g. Щоб включити це зауваження, необхідно встановити параметр сесії.
Зауважте, що нове зауваження PLW-06009 виникає під час компіляції. Причому це тільки зауваження; компіляція в цілому виконана успішно. Процедуру виконати можна, але майте на увазі зауваження!
В Oracle Database 11 g цей сценарій більше не є проблемою. Можна створити тригер спочатку відключеним, що дозволяє протестувати всі помилки компіляції. А пізніше при редагуванні включити його. Ось як його можна створити:
Тепер можна перевірити його статус:
SQL> select status
2> from user_triggers
3> where trigger_name = 'TR_T'
4> /
Навіть незважаючи на те, що тригер створений відключеним, він повинен бути без помилок. Тому, якщо спробувати створити його з помилкою (наприклад, використовуючи таблицю "M", яка не існує):
Ця особливість дуже корисна в процесі контролю змін. Інше чудове застосування цієї можливості - включення тригерів в певний момент. Наприклад, за допомогою тригерів ви створюєте рішення для аудиту і audit_table ще не очищена від старих записів. Тригери можна створити відключеними, а включити їх пізніше, коли таблиця буде готова.
Іменовані параметри функції
Розглянемо просту функцію:
Ця функція робить дуже просту операцію, але вона добре демонструє концепцію. Так як в цій функції два параметри, то її можна викликати, передавши параметри, як позиційні значення, а саме:
Або як іменовані параметри:
Однак, врешті-решт виникає проблема, якщо використовувати її в select-пропозиціях. Якщо в Oracle Database 10 g виконати наступну пропозицію:
В Oracle Database 11 g ви маєте право використовувати нотацію:
яка працює правильно. Можна вказувати іменовану нотацію в кінці, а перші параметри повинні бути позиційними. Наприклад, наступний виклик, де параметр p_param1 дорівнює 1, буде коректним:
А цей немає (позиційний параметр в кінці):
Взаємозамінність динамічного курсора і REF-курсора
Ви знаєте, яким корисним може бути Native Dynamic Cursor, особливо, коли до виклику не знаєш точно, що буде запитуватися. Динамічний PL / SQL можна також використовувати через DBMS_SQL. Обидва методи мають свої переваги. Але що буде, якщо ви почали розробляти програму, в якій використовується спочатку один метод, а потім необхідно переключитися на інший?
В Oracle Database 11 g цей процес надзвичайно простий. Підтримуваний пакет DBMS_SQL має нову функцію, TO_REFCURSOR, яка конвертує динамічний курсор DBMS_SQL в ref-курсор. Ось приклад такої конвертації:
Припустимо, потрібно написати загальну процедуру, яка не знає списку стовпців в select-вираженні під час компіляції. Це той випадок, коли native dynamic SQL стає необхідним. Можна описати для нього ref-курсор. Тепер, щоб стало цікавіше, припустимо, що ви не знаєте всіх bind-змінних, для цього випадку найбільше підходить dbms_sql. Як виконати це складне вимога, написавши мінімум коду? Просто: почніть з dbms_sql для bind-змінних, а потім конвертуйте в ref-курсор.
Аналогічно, щоб конвертувати Native Dynamic SQL в REF-курсор, необхідно викликати іншу функцію, TO_CURSOR_NUMBER:
Ref-курсор, визначений у змінної c_ref_cur, повинен бути відкритий раніше цього виклику. Після цього виклику життя ref-курсора закінчена; маніпулювати можна тільки dbms_sql-курсором.
Припустимо, що ви знаєте bind-змінні під час компіляції, але не знаєте списку select; ви можете почати з native dynamic sql, з ref-курсора, а потім замінити його на dbms_sql, щоб описати і витягти стовпці з курсора.
Як бачите, Oracle Database 11 g містить кілька поліпшень, які допомагають писати код на PL / SQL більш ефективно.