SQL Plus: Создание и выполнение сценариев - Использование подставляемых переменных

Использование подставляемых переменных

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

SQL> select &func.(&col.) from &tab; 
Введите значение для func: max 
Введите значение для col: sal 
Введите значение для tab: emp 
прежний 1: 
select &func.(&col.) from &tab 
новый 1: 
select max(sal) from emp 

   MAX(SAL) 
---------      5000 

Как видите, если в команде встречается не определенная явно ранее подставляемая переменная, SQL*Plus запрашивает ее значение. Затем на экран выдается вид команды до и после подстановки всех значений (это можно отключить с помощью команды SET VERIFY OFF), и команда выполняется. В нашем примере мы подставили в команду конкретную функцию агрегирования, имя столбца, по которому выполняется агрегирование, и имя таблицы.

Обратите внимание, что если необходимо вставить значение подставляемой переменной перед не пробельным символом, необходимо указать точку (.) после имени переменной.

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

SQL*Plus читает данные с клавиатуры, даже если входной и выходной потоки терминала перенаправлены в файлы. Если же сценарий запущен в пакетном режиме, данные читаются из соответствующего файла.

Если введенное значение совпадает с подставляемой переменной (начинается с &), то (по крайней мере, в версии 8.1.6) выдается сообщение об ошибке:

SQL> define emp = dept 
SQL> select * from &tab; 
Введите значение для tab: &emp 
прежний   1:
select * from &tab 
новый   1: 
select * from &emp 
SP2-0552: Переменная привязки "EMP" не описана. 

Подстановка без повторного запроса

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

SQL> select max(&col), avg(&col), min(&col) from &tab;
Введите значение для col: sal
Введите значение для col: sal
Введите значение для col: sal
Введите значение для tab: emp
прежний 1: select max(&col), avg(&col), min(&col) from &tab
новый 1: select max(sal), avg(sal), min(sal) from emp

  MAX(SAL)   AVG(SAL)   MIN(SAL)
---------- ---------- ----------
      5000 2073,21429        800

Чтобы значение переменной запрашивалось только один раз, используется подстановка с двумя амперсантами (&&):

SQL> c /(&col/(&&col
1* select max(&&col), avg(&col), min(&col) from &tab
SQL> c /(&col/(&&col
1* select max(&&col), avg(&&col), min(&col) from &tab
SQL> c /(&col/(&&col
1* select max(&&col), avg(&&col), min(&&col) from &tab
SQL> /
Введите значение для col: sal
Введите значение для tab: emp
прежний 1: select max(&&col), avg(&&col), min(&&col) from &tab
новый 1: select max(sal), avg(sal), min(sal) from emp

  MAX(SAL)   AVG(SAL)   MIN(SAL)
---------- ---------- ----------
      5000 2073,21429        800

Подстановка параметров командных файлов

Подстановка переменных выполняется и для позиционных параметров, переданных при вызове сценария. На эти параметры можно ссылаться как на &1, &2 и т.д. Если значение для них в командной строке не передано, SQL*Plus запрашивает значения при вызове сценария. Подстановка позиционных параметров выполняется только при вызове сценария командами START (или ее сокращенными формами @, @@).

Рассмотрим пример:

SQL> clear buffer
buffer очищена
SQL> input
  1 select &1 from &2
  2 .
SQL> save f:\subst.sql
Создано файл f:\subst.sql
SQL> @f:\subst max(sal) emp
прежний   1: select &1 from &2
новый   1: select max(sal) from emp

  MAX(SAL)
----------
      5000
SQL> @f:\subst
прежний 1: select &1 from &2
новый 1: select max(sal) from emp

  MAX(SAL)
----------
      5000
SQL> undef 1 2
SQL> @f:\subst
Введите значение для 1: min(sal)
Введите значение для 2: emp
прежний   1: select &1 from &2
новый   1: select min(sal) from emp

  MIN(SAL)
----------
       800

Ограничения

Подставляемые переменные нельзя использовать в командах редактирования буфера SQL (APPEND, CHANGE, DEL, INPUT) и в других командах, где эта подстановка "не имеет смысла", в частности, в комментариях. Команды редактирования буфера считают символ & обычным и используют его буквально (см. пример выше).

Системные переменные, влияющие на подстановку

В табл. 13 представлены системные установки, влияющие на подстановку пользовательских переменных.

Таблица 13. Системные установки, влияющие на подстановку переменных.

Установка	Описание
SET DEFINE	Задает символ подстановки (вместо стандартного - &) и позволяет включать и отключать подстановку.
SET ESCAPE	Задает символ маскировки, позволяющий маскировать символ подстановки. Стандартным символом маскировки является обратная косая (\).
SET VERIFY	Позволяет включать и отключать выдачу каждой строки команды до и после подстановки пользовательских переменных.
SET CONCAT	Задает символ, отделяющий имя подставляемой переменной от следующей непосредственно за ним строки. По умолчанию используется точка (.).