Підстановка амперсанта
Ви розробили хороший запит і, можливо, захочете використовувати його в майбутньому. Іноді корисно мати заготівлю запиту, в якій вказані змінні, які будуть замінені значення при виконанні запити. Oracle надає такий функціонал у вигляді так званої заміни змінної (ampersand substitution). Кожен елемент команди SELECT може бути підставлений під час виконання, і шляхом залишення в запиті тільки ключових елементів та введення динамічних змінних ви можете уникнути багато нудної роботи. Ми розглянемо підстановку змінної та ключові слова DEFINE та VERIFY.
Заміна змінних
Заміну змінних можна як установку заглушек. SQL запит складається із двох або більше частин. Кожну частину можна розбити на частини, що складаються з тексту. Будь-який текст, частина або частина можна вказати як заглушку.
Заміна одиночним амперсандом
Найпростіша і найпопулярніша форма SQL елемента – це заміна одинарного амперсанта. Символ амперсанта (&) обраний для призначення змінної у запиті і змінна складається з амперсанта та назви змінної без пробілу між ними. Коли запит виконується, серверний процес Oracle бачить змінну для заміни та намагається визначити її значення двома способами. По-перше, переглядаєтьсявизначена чи змінна в сесії користувача. (Команду DEFINE ми розглянемо трохи згодом). Якщо змінна не визначена, то процес користувача запитує значення на яке буде замінена відповідна змінна. Коли введено значення, запит виконується сервером Oracle. Заміна змінної амперсанта відбувається в момент виконання запиту і іноді називається зв'язування під час виконання (runtime binding) або підстановка під час виконання (runtimesubstitution).
select employee_id, last_name, phone_number from employees
Коли ви запустите запит, Oracle запитає вхідні значення для змінної з ім'ям LASTNAME. Ви можете ввести прізвище співробітника, якщо ви його знаєте, наприклад 'King'. Якщо ви не знаєте прізвище, але знаєте номер, ви можете ввести будь-яке значення і натиснути OK, щоб ввести значення. Потім Oracle запросить значення змінної EMPNO. Після введення значення, наприклад 0, та натискання OK, не залишається змінних для заміни та виконується наступний запит
select employee_id, last_name, phone_number from employees
where last_name = 'King' або employee_id = 0;
Змінною можна призначити будь-яке символьне значення з валідним ім'ям. Значення-літерала на яке буде зроблено заміну має бути відповідного типу даних, інакше ви отримаєте помилку “ORA-00904: invalid identifier”. Якщо змінна має на увазі символьне значення або значення даних, то літерал повинен бути поміщений в одинарні лапки. Корисним способом уникнути помилки типу даних є обрамлення змінної лапки при необхідності. Тоді користувачеві не потрібно знати про тип даних.
select employee_id, last_name, phone_number from employees
Подвійний амперсант
Коли змінна використовується кілька разів у запиті, Oracle буде запитувати значення кожного разу, коли зустрічається змінна в запиті. Для складних запитів, це може бути дуже неефективним і призвести до помилок. Наступний запит вибирає FIRST_NAME та LAST_NAME з таблиці EMPLOYEES який містить символи в обох стовпцях
select first_name, last_name from employees
Дві умови однакові, але вони перевіряються для різних стовпців. Коли запит буде виконуватися, спочатку Oracle вимагатиме введеннязначення для змінної SEARCH, що використовується в першій умові зі стовпцем LAST_NAME. Потім буде потрібно введення даних для заміни значення змінної SEARCH, що використовується при порівнянні з FIRST_NAME. Це приносить дві проблеми. По-перше це неефективно вводити одне й теж значення двічі, і по-друге, що більш важливо, можна допустити друкарську помилку при повторному введенні, оскільки Oracle не перевіряє ідентичність введення значення для змінної. У цьому прикладі, допущено логічне припущення, що значення змінних має бути однаковим, але той факт, що у змінної однакове ім'я не означає для Oracle, що значення має бути однаковим. На першому прикладі на малюнку 9-7 відображено результат виконання запиту та введення двох різних значень для підстановки змінної. У цьому прикладі результат неправильний, оскільки вихідна вимога була такою, що прізвище та ім'я співробітника повинні містити однакові літерали.
Коли підстановка змінної проводиться кілька разів в одному запиті, і ви знаєте, що значення має бути однаковим при кожній згадці змінної, краще використовувати підстановку подвійного амперсанта. Це вимагає введення двох амперсантів перед ім'ям змінної, яка буде використовуватися кілька разів. Коли Oracle бачить подвійний амперсант, призначається значення змінної сесії і вам не виводитиметься запит при зустрічі змінної далі в запиті.
На другому прикладі на малюнку 9-7 показано, як використовувати змінну SEARCH з двома амперсантами в умові для стовпця LAST_NAME, а потім змінна з тим же ім'ям і одним амперсантом не потребує введення значення. Коли запит буде виконуватися, Oracle запросить значення SEARCH тільки один раз встановить значення змінної SEARCH для сесії введене значення і буде використовувати його далі. Для тогощоб скинути це значення, вам необхідно буде виконати команду UNDEFINE.
Малюнок 9-7 Використання подвійного амперсанта
Підстановка назв стовпців
До цього ми обговорювали підстановку літералів у розділі WHERE, але можна замінювати будь-який елемент SQL запиту. У наступному прикладі стовпці FIRST_NAME та JOB_ID статичні і будуть повернуті в будь-якому випадку, але третій стовпець це змінна для підстановки під час виконання з ім'ям COL. Результат також сортується використовуючи цей стовпець-змінну, вказану в директиві ORDER BY
select first_name, job_id, &&col
where job_id in ('MK_MAN','SA_MAN')
На відміну від символьних літералів та літералів типу даних дата – ім'я стовпця не потребує обрамлення одиночними лапками ні під час визначення ні під час вказівки значення.
Підстановка виразів та тексту
Практично всі елементи запиту SQL можуть бути замінені під час виконання. Обмеження, встановлене Oracle – лише перше слово має стати статичним. У випадку команди SELECT мінімальною командою буде ключове слово SELECT, а все інше може бути замінене під час виконання як у наступному прикладі
Коли команда буде виконуватися, буде виведено запит для введення значення змінної з іменем REST_OF_STATEMENT, яка додасться до слова SELECT. Найкращими кандидатами для використання підстановки змінних є запити, які виконуються багато разів, і трохи відрізняються один від одного.
Команди DEFINEі VERIFY
Подвійний амперсант використовується щоб уникнути введення значення кілька разів для однієї і теж змінної, яка використовується кілька разів у запиті. Коли зустрічається подвійний амперсант, змінна зберігається якзмінна сесії. Коли запит виконується, всі інші входження змінної автоматично замінюються, використовуючи збережену змінну. Усі наступні виконання запиту у тій самій сесії автоматично визначають значення змінної із змінної сесії. Це не завжди те, що потрібно і також обмежує функціонал підстановки змінної. Як би там не було, Oracle надає механізм для очищення змінних сесії. Команда VERIFY це команда з клієнтської сторони і вона контролює чи виводяться користувачеві замінені елементи перед виконанням запиту, який використовує змінні.
DEFINEі UNDEFINE
Змінні сесії створюються, коли вони зустрічаються у запитах, що використовують подвійний амперсант. Вони існують весь час життя сесії або поки вони явно не видалені. Сесія завершується, коли користувач виходить з клієнтської програми наприклад, SQL * Plus, або, коли процес примусово завершується.
Проблема постійно існуючої змінної у цьому, що вони приховують сенс динамічних запитів, які використовують заміну змінної. На щастя, змінні сесії можна видалити за допомогою команди UNDEFINE. Синтаксис команди для видалення змінної
Розглянемо простий динамічний запит, який вибирає статичні стовпці та стовпці-змінні з таблиці EMPLOYEES та сортує результат на підставі стовпця-змінної
select last_name, &&COLNAME
from employees where department_id=30 order by &COLNAME
Перший раз, коли запит буде виконуватися, з'явиться запит на введення значення для змінної COLNAME. Припустимо, ви ввели SALARY. Це значення підміняється та запит виконується. Всі наступні виконання цього запиту в тій же сесії не будуть вимагати значення для змінноїCOLNAME, оскільки вже створилася змінна та її значення SALARY. Змінну можна видалити командою UNDEFINE COLNAME. Після того, як змінну сесію видалено – наступне виконання запиту знову запитає користувача ввести значення для змінної COLNAME.
Як показано малюнку 9-8, змінна з ім'ям EMPNAME явно визначається значенням ‘King’. Команда DEFINE без параметрів повертає список змінних сесії явно заданих та ісользованих у запитах із подвійним амперсантом.
Рисунок 9-8 – Приклад використання команд DEFINE та UNDEFINE
Потім виконуються два простих запити, в яких використовується явно певна змінна EMPNAME. Потім змінна видаляється.
Підтримка змінних-сесії може бути увімкнена або вимкнена, коли необхідно за допомогою команди SET DEFINE ONOFF. Команда SET це команда SQL, це команда управління оточенням SQL. Якщо ви вказуєте SET DEFINE OFF, клієнтська програма (наприклад, SQL * Plus) не зберігає змінні сесії, а вважає, що амперсант - це традиційний літерал. Таким чином, команда SET DEFINE OFFON управляє чи доступна заміна змінної для вашої сесії. Наступний запит використовує символ амперсанта як звичайний літерал. Коли запит буде виконуватися, буде виведено запит для введення значення змінної SID.
Але якщо ви вимкнете підстановку змінної, це запит виконається без запиту введення даних
Після виконання запиту команда SET DEFINE ON може бути використана для включення функціоналу підстановки змінної назад. Якщо підстановка змінної вимкнена і амперсанд не використовується як літерал, запит поверне помилку.
Команда VERIFY
Існує два типи команд при роботі з Oracle: SQL команди та команди управління оточенням SQL.Команда SELECT це команда мови SQL, команда SET управляє оточенням. Доступно багато параметрів команди SET, але для контролю підстановки змінної доступні лише дві: DEFINE та VERIFY.
Команда VERIFY управляє виведенням введеного значення на екран, щоб ви могли переконатися, що підстановка здійснена правильно. Повідомлення виводиться на екран у форматі старого запиту, за ним іде новий запит із заміненим значенням. Команда VERIFY включає або вимикає виведення на екран, використовуючи параметр OFF або ON. Якщо перевірка вимкнена, то під час виконання запиту з використанням амперсанта у вас запитується введення значення, змінна замінюється, запит виконується та відображається результат. Якщо перевірка увімкнена та виконується той же запит, то після введення значення, але перед виведенням результату, Oracle відображає розділ, в якому знаходилася змінна як старе значення з додаванням номерів рядків, а потім нижче відображається нове значення вже заміненого значення.