Подстановка амперсанта
Вы разработали хороший запрос и возможно захотите использовать его в будущем. Иногда полезно иметь заготовку запроса, в которой указаны переменные, которые будут заменены на значения при выполнении запросы. Oracle предоставляет такой функционал в виде так называемой заменты переменной (ampersand substitution). Каждый элемент команды SELECT может быть подставлен во время выполнения, и путём оставления в запросе только ключевых элементов и ввода динамических переменных вы можете избежать много скучной и повторяемой работы. Мы рассмотрим подстановку переменной и ключевые слова DEFINE и VERIFY.
Замена переменных
Замену переменных можно рассматривать как установку заглушек. SQL запрос состоит из двух или более частей. Каждую часть можно разбить на подчасти, которые состоят из текста. Любой текст, подчасть или часть можно указать как заглушку.
Замена одиночным амперсандом
Самая простая и популярная форма SQL элемента это замена одинарного амперсанта. Символ амперсанта (&) выбран для назначения переменной в запросе и переменная состоит из амперсанта и названия переменной без пробела между ними. Когда запрос выполняется, серверный процесс Oracle видит переменную для замены и пытается определить её значение двумя способами. Во первых просматривается определена ли переменная в сессии пользователя. (Команду DEFINE мы рассмотрим чуть позже). Если переменная не определена, то пользовательский процесс запрашивает значение на которое будет заменена сооветствующая переменная. После того как значение введено, запрос выполняется сервером Oracle. Замена переменной амперсанта происходит в момент выполнения запроса и иногда называется связывание во время выполнения (runtime binding) или подстановка во время выполнения (runtime substitution).
Вам может требоваться поиск значения телефона по заданной фамилии или номеру сотрудника. Такой запрос может быть написан следующим образом
select employee_id, last_name, phone_number from employees
where last_name = &LASTNAME or employee_id = &EMPNO;
Когда вы запустите запрос, Oracle спросит входное значения для переменной с именем LASTNAME. Вы можете ввести фамилию сотрудника, если вы её знаете, например ‘King’. Если вы не знаете фамилию но знаете номер, вы можете ввести любой значение и нажать OK для ввода значения. Затем Oracle запросит значения для переменной EMPNO. После ввода значения, например 0, и нажатия OK, не остаётся переменных для замены и выполняется следующий запрос
select employee_id, last_name, phone_number from employees
where last_name = ‘King’ or employee_id = 0;
Переменной можно назначить любой символьное значение с валидным именем. Значение-литерала на которое будет произведена замена должно быть соответственного типа данных, иначе вы получите ошибку “ORA-00904: invalid identifier”. Если переменная подразумевает символьное значение или значение данных, то литерал должен быть заключен в одинарные кавычки. Полезным способом избежать ошибки типа данных является обрамление переменной в кавычки при необходимости. Тогда пользователю нет необходимости знать о типе данных.
select employee_id, last_name, phone_number from employees
where last_name = ‘&LASTNAME’ or employee_id = &EMPNO;
Двойной амперсант
Когда переменная используется несколько раз в запросе, Oracle будет запрашивать значение каждый раз когда встречается переменная в запросе. Для сложных запросов, это может быть очень неэффективно и приводить к ошибкам. Следующий запрос выбирает FIRST_NAME и LAST_NAME из таблицы EMPLOYEES который содержит символы в обоих столбцах
select first_name, last_name from employees
where last_name like ‘%&SEARCH%’ and first_name like ‘%&SEARCH%’;
Два условия одинаковые но они проверяются для разных столбцов. Когда запрос будет выполняться, вначале 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
from employees
where job_id in (‘MK_MAN’,’SA_MAN’)
order by &col;
В отличие от символьных литералов и литералов типа данных дата – имя столбца не нуждается в обрамлении одиночными кавычками ни во время определения ни во время указания значения.
Подстановка выражений и текста
Практически все элементы SQL запроса могут быть заменены во время выполнения. Ограничение, установленное Oracle – только первое слово должно быть статичным. В случае команды SELECT, минимальной командой будет ключевое слово SELECT, а всё остальное может быть заменено во время выполнения как в следующем примере
select &rest_of_statement;
Когда команда будет выполняться, будет выведен запрос для ввода значения для переменной с именем REST_OF_STATEMENT, которая добавится к слову SELECT. Лучшими кандидатами для использования подстановки переменных являются запросы, которые выполняются много раз, и незначительно отличаются друг от друга.
Команды DEFINE и VERIFY
Двойной амперсант используется чтобы избежать ввода значения несколько раз для одной и тоже переменной которая используется несколько раз в запросе. Когда встречается двойной амперсант, переменная сохраняется как переменная сессии. Когда запрос выполняется, все остальные вхождения переменной автоматически заменяются, используя сохраненную переменную. Все последующие выполнения запроса в той же сессии автоматически определяют значения переменной из переменной сессии. Это не всегда то что требуется и также ограничивает функционал подстановки переменной. Как-бы там ни было, Oracle предоставляет механизм для очистки переменных сессии. Команда VERIFY это команда с клиентской стороны и она контролирует будут или нет выводиться пользователю заменённые элементы перед выполнением запроса, который использует переменные.
DEFINE и UNDEFINE
Переменные сессии создаются, когда они встречаются в запросах использующих двойной амперсант. Они существуют всё время жизни сессии или пока они явно неудалены. Сессия завершается, когда пользователь выходит из клиентской программы к примеру, SQL *Plus, или, когда пользовательский процесс принудительно завершается.
Проблема постоянно существующей переменной в том, что они скрывают смысл динамических запросов, использующих замену переменной. К счастью, переменные сессии можно удалить, используя команду UNDEFINE. Синтаксис команды для удаления переменной
UNDEFINE variablename
Рассмотрим простой динамический запрос, который выбирает статические столбцы и столбцы-переменные из таблицы EMPLOYEES и сортирует результат на основании столбца-переменной
select last_name, &&COLNAME
from employees where department_id=30 order by &COLNAME;
Первый раз когда запрос будет выполняться, появится запрос на ввод значения для переменной COLNAME. Предположим вы ввели SALARY. Это значение подменяется и запрос выполняется. Все последующие выполнения этого запроса в той же сессии не будут запрашивать значения для переменной COLNAME, так как уже создалась переменная и её значение SALARY. Переменная может быть удалена командой UNDEFINE COLNAME. После того как переменная сессии удалена – следующее выполнение запроса заново запросит пользователя ввести значение для переменной COLNAME.
У команды DEFINE два назначения. Её можно использовать чтобы просмотреть список всех переменных, установленных в текущей сессии, а также для явного задания значения переменной, используемой для подстановки в любых запросах на время жизни сессии. Синтаксис команды для двух этих вариаций
DEFINE;
DEFINE variable=value;
Как показано на рисунке 9-8, переменная с именем EMPNAME явно определяется со значением ‘King’. Команда DEFINE без параметров возвращает список переменных сессии явно заданных и исользованных в запросах с двойным амперсантом.
Рисунок 9-8 – Пример использования команд DEFINE и UNDEFINE
Затем выполняются два простых запроса в которых используется явно определённая переменная EMPNAME. Затем, переменная удаляется.
Поддержка переменных-сессии может быть включена или отключена, когда необходимо с помощью команды SET DEFINE ON|OFF. Команда SET это не команда SQL, это команда управления окружением SQL. Если вы указываете SET DEFINE OFF, клиентская программа (к примеру, SQL *Plus) не сохраняет переменные сессии, а считает, что амперсант — это обычный литерал. Таким образом команда SET DEFINE OFF|ON управляет доступна ли замена переменной для вашей сессии. Следующий запрос использует символ амперсанта как обычный литерал. Когда запрос будет выполняться, будет выведен запрос для ввода значения дла переменной SID.
select ‘Coda&Sid’ from dual;
Но если вы выключите подстановку переменной, то это запрос выполнится без запроса ввода данных
SET DEFINE OFF;
select ‘Coda&Sid’ from dual;
SET DEFINE ON;
После выполнения запроса, команда SET DEFINE ON может быть использована для включения функционала подстановки переменной обратно. Если подстановка переменной выключена и амперсанд используется не как литерал, запрос вернёт ошибку.
Команда VERIFY
Существует два типа команд при работе с Oracle: SQL команды и команды управления окружением SQL. Команда SELECT это команда языка SQL, команда SET управляет окружением. Доступно много параметров команды SET, но для контроля подстановки переменной доступны всего две: DEFINE и VERIFY.
Команда VERIFY управляет выводом введённого значения на экран, чтобы вы могли убедиться (verify) что подстановка осуществлена правильно. Сообщение выводится на экран в формате старого запроса, за ним идёт новый запрос с замененным значением. Команда VERIFY включает или выключает вывод на экран используя параметр OFF или ON. Если проверка выключена, то во время выполнения запроса с использованием амперсанта у вас запрашивается ввод значения, переменная заменяется, запрос выполняется и отображается результат. Если проверка включена и выполняется тот же запрос, то после ввода значения, но перед выводом результата, Oracle отображает раздел, в котором находилась переменная как старое значение с добавлением номеров строк, а затем ниже отображается новое значение с уже замененным значением.