Ampersand Substitution (Подстановка с использованием амперсанда)

29.09.2009 в 15:59 | Опубликовано в Oracle Database 11g: SQL Fundamentals I | Оставить комментарий
Метки: 1Z0-051

Подстановка переменных при помощи амперсанда (знак &) может пригодиться в том случае если планируется использовать запрос повторно с небольшими изменениями. По идее это полезно если выполнять эти самые запросы из среды типа SQL*Plus. Лично у меня в процессе работы такая потребность возникает крайне редко (если не сказать больше — практически никогда) поэтому тема будет где-то даже интересна. Раз нужно для экзамена, значит пробежимся.

Подстановка с амперсандом

Подстановочные переменные

Базовый способ использования обсуждаемого приема — подстановка с одиночным амперсандом (single ampersand substitution). Для того чтобы использовать эту возможность нужно в запросе указать «переменные» в виде строковых литералов перед которыми вписывается знак амперсанда. Например:

SELECT *
FROM EMP
WHERE EMPNO = &EMPID
  AND NAME LIKE &FIO;

Если такой запрос запустить в SQL*Plus то перед выполнением среда попросит ввести значения для EMPID и FIO примерно таким образом:

Enter value for empid: 1234
old   3: WHERE EMPNO = &EMPID
new   3: WHERE EMPNO = 1234
Enter value for fio: 'SMITH'
old   4: AND NAME LIKE &FIO
new   4: AND NAME LIKE 'SMITH'

Синим цветом выделено то, что будет введено пользователем. После ввода значений будут выданы результаты как будто бы был введен запрос, где вместо &EMPID стояло бы 1234, а вместо &FIO — ‘SMITH’.

Вводимые данные должны подходить по типу. К примеру если ввести SMITH без кавычек, то Oracle выдаст ошибку «ORA-00904: invalid identifier» (недопустимый идентификатор). Однако, если «шаблон» запроса задать следующим образом:

SELECT *
FROM EMP
WHERE EMPNO = &EMPID
  AND NAME LIKE '&FIO';

то ошибкой уже будет ввести SMITH с кавычками.

Если мы хотим использовать одну переменную в запросе более одного раза, то можно использовать подстановку с двойным амперсандом (double ampersand substitution). Проиллюстрировать использование двойного амперсанда можно следующим примером. Допустим, нам нужно найти работника у которого и в имени и в фамилии встречается заданная нами буква или буквы. Для этого мы можем использовать запрос с использованием одиночного амперсанда:

SELECT *
FROM EMP
WHERE FIRST_NAME LIKE '%&SEARCH%'
  AND LAST_NAME  LIKE '%&SEARCH%';

И этот запрос будет работать. Правда будет одна небольшая проблема — переменную &SEARCH система попросит ввести дважды, примерно так:

Enter value for search: G
old   3: WHERE FIRST_NAME LIKE '%&SEARCH%'
new   3: WHERE FIRST_NAME LIKE '%G%'
Enter value for search: G
old   4: AND LAST_NAME LIKE '%&SEARCH%'
new   4: AND LAST_NAME LIKE '%G%'

Если мы хотим чтобы для фамилии и имени вводились разные строки для шаблона, то это нас вполне устроит. Однако, если мы хотим вводить для шаблонов одинаковые значения, то можно использовать двойной амперсанд:

SELECT *
FROM EMP
WHERE FIRST_NAME LIKE '%&&SEARCH%'
  AND LAST_NAME  LIKE '%&SEARCH%';

и тогда система попросит нас ввести значение для  &SEARCH только один раз:

Enter value for search: G
old   3: WHERE FIRST_NAME LIKE '%&&SEARCH%'
new   3: WHERE FIRST_NAME LIKE '%G%'
old   4: AND LAST_NAME LIKE '%&SEARCH%'
new   4: AND LAST_NAME LIKE '%G%'

а интерпретатор сам подставит значение SEARCH во все встречающиеся после первого упоминания переменной с двойным амперсандом.

Подстановка при помощи амперсанда может применяться не только для ввода значений в блок WHERE, а вообще для ввода значений в любое место запроса — в список колонок, список таблиц, список колонок для сортировки. К примеру мы можем задать такой шаблон:

SELECT &SEL
FROM &TAB
WHERE &WHE;

и каждый раз вводить нужные значения для всех запрашиваемых частей запроса — выбираемых колонок, таблиц и блока условий. Более того, можно через амперсанд задать вообще весь запрос, например так:

&SEL;

затем ввести весь текст запроса и он выполнится:

Enter value for sel: SELECT * FROM EMP WHERE NAME = 'SMITH' ORDER BY 1
old   1: &SEL
new   1: SELECT * FROM EMP WHERE NAME = 'SMITH' ORDER BY 1

Установка переменных и верификация

Рассмотрим команды DEFINE, UNDEFINE и VERIFY.

Команда DEFINE может использоваться в двух вариантах — с указанием  переменной и ее нового значения:

DEFINE <переменная>=<значение>;

Тогда для текущей сессии указанной переменной будет присвоено указанное значение. Так же команда может быть выполнена без других параметров:

DEFINE;

В этом случае будет выдан список всех переменных, установленных в данный момент для текущей сессии.

Стоит помнить, что переменные неявно задаются когда вы в данной сессии используете подстановку с двойным амперсандом. Т.о. если вы дважды подряд запустите запрос с использованием какой либо переменной с двойным амперсандом, то система попросит вас ввести это значение только в первый раз. Второй раз она использует значение введенное в прошлый раз. Это может быть не удобно если вы хотите для второго раза ввести другое значение. Для того чтобы уничтожить переменную для сессии используется команда UNDEFINE:

UNDEFINE <переменная>;

которая уничтожает для текущей сессии эту переменную.

Использование подстановки с амперсандом имеет один неприятный эффект — если в запросе встречается строковый литерал со знаком &, то система может запросить значение, посчитав что здесь нужна подстановка. Например проблема могут возникнуть с таким запросом:

SELECT 'Max&Alex Ltd.' COMPANY_NAME FROM DUAL;

система может посчитать что нужно вместо &Alex подставить введенное вами значение и запросит его.

Для того чтобы отключить ввод переменных используются команды SET DEFINE ON|OFF или SET SCAN ON|OFF, которые выключат срабатывание амперсанда. Например, для предыдущего примера можно ввести команды:

SET DEFINE OFF;
SELECT 'Max&Alex Ltd.' COMPANY_NAME FROM DUAL;
SET DEFINE ON;

и проблема с ненужным запросом &Alex будет решена.

И последнее — команда SET VERIFY ON|OFF. Команда нужна для того чтобы включить или выключить режим отображения старого и нового вида запроса после введения значения для переменной с амперсандом.

Если взять один из вышеописанных случаев, то смысл команды можно понять из следующего примера:

SET VERIFY ON;

SELECT *
FROM EMP
WHERE EMPNO = &EMPID
 AND NAME LIKE &FIO

Enter value for empid: 1234
old   3: WHERE EMPNO = &EMPID
new   3: WHERE EMPNO = 1234
Enter value for fio: 'SMITH'
old   4: AND NAME LIKE &FIO
new   4: AND NAME LIKE 'SMITH'

<----результаты запроса---->

SET VERIFY OFF;

SELECT *
FROM EMP
WHERE EMPNO = &EMPID
 AND NAME LIKE &FIO;

Enter value for empid: 1234
Enter value for fio: 'SMITH'

<----результаты запроса---->

Как видим при SET VERIFY ON система выдавала нам строки типа «old:» и «new:»  — для визуальной проверки старого и нового значения строк до и после подстановки. После выполнения SET VERIFY OFF это прекратилось.

Заключение

Знать это все безусловно нужно, хотя бы для того чтобы правильно составлять скрипты DDL. Если в файле с кодом, скажем, пакета программ в каком-то строковом литерале неожиданно встретился амперсанд, а вы для установки его на диск используете команду вида:

sqlplus user/pwd @pkgfile.sql

то нужно не забывать добавлять в начале pkgfile.sql устанавливать команду SET DEFINE OFF. Иначе скрипт будет запрашивать значения для всех встреченных в тексте амперсандов, что может привести к некорректной установке пакета.

Ну и использование установки переменных с помощью DEFINE так же может быть полезно опять же в скриптах DDL.