Утилита SQL*Plus. Создание и выполнение сценариев. Часть III

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

Утилита SQL*Plus позволяет создавать командные файлы, запрашивающие у пользователя параметры и подставляющие их значение в команды по ходу выполнения. Для этого используются т.н. пользовательские переменные . Такие переменные определяются в командном файле, в частности, с помощью команды DEFINE. На пользовательские переменные можно ссылаться в командах, предваряя их имя символами & или &&. Конструкцию &<имя переменной> называют подставляемой переменной .

Команды DEFINE и UNDFEFINE

Команда DEFINE позволяет определить пользовательскую переменную строкового типа и задать ей значение типа CHAR, либо получить значение определенной или всех пользовательских переменных. Эта команда имеет следующий синтаксис:

<команда DEFINE > ::=

DEF[INE] [<имя переменной> [= <текст>]]

Если текст, присваиваемый переменной, содержит пробелы или символы пунктуации, его надо брать в апострофы.

При вызове без значения, команда DEFINE выдает значение переменной. Команда DEFINE без параметров выдает значение всех пользовательских переменных SQL*Plus, включая системные. Рассмотрим пример:

SQL> define a=text
SQL> define a
DEFINE A               = "text" (CHAR)
SQL> define b=5
SQL> define
DEFINE _SQLPLUS_RELEASE = "801060000" (CHAR)
DEFINE _EDITOR         = "Notepad" (CHAR)
DEFINE _O_VERSION      = "Oracle8i Enterprise Edition Release 8.1.6.0.0 - 
Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production" (CHAR)
DEFINE _O_RELEASE      = "801060000" (CHAR)
DEFINE A               = "text" (CHAR)
DEFINE B               = "5" (CHAR)
SQL>

Как видите, утилита SQL*Plus автоматически определяет ряд системных пользовательских переменных.

Для удаления как явно определенной пользовательской переменной, так и параметра командной строки сценария (см. описание команды START выше) используется команда UNDEFINE. После применения этой команды к переменной значение переменной теряется, и она становится неопределенной. Команда UNDEFINE имеет следующий простой синтаксис:

<команда UNDEFINE> ::=

UNDEF[INE] <имя переменной>{ <имя переменной>}

Продолжая предыдущий пример:

SQL> undefine a b
SQL> define a
SP2-0135: символ a UNDEFINED
SQL>

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

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> 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. Системные установки, влияющие на подстановку переменных.

Взаимодействие с пользователем

Стандартный механизм запроса значений пользовательских переменных дает ограниченные средства взаимодействия с пользователем - ввод значений в ответ на стандартные приглашения. Утилита SQL*Plus позволяет управлять выдачей сообщений и запросом значений переменных.

Команда PROMPT

Для выдачи на экран произвольного текста используется команда PROMPT со следующим синтаксисом:

<команда PROMPT> ::=

PRO[MPT] [<текст>]

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

Команда ACCEPT

Считать строку и запомнить ее в указанной пользовательской переменной определенного типа (выдавая, при необходимости, приглашение) позволяет команда ACCEPT со следующим синтаксисом:

<команда ACCEPT> ::=

ACC[EPT] <имя переменной> [<тип переменной>]
  [FOR[MAT] <формат>] [DEF[AULT] <стандартное значение>]
  [<приглашение>] [HIDE]

<тип переменной> ::=

NUM[BER] / CHAR / DATE

<приглашение> ::=

PROMPT <текст> / NOPR[OMPT]

Если указанная в команде ACCEPT пользовательская переменная не существует, SQL*Plus создает ее. Опции команды ACCEPT описаны в табл. 14.

Таблица 14. Опции команды ACCEPT.

Рассмотрим простой пример совместного использования команд PROMPT и ACCEPT. Пусть имеется командный файл splus1.sql со следующим содержимым:

REM splus1.sql - пример диалога с пользователем
prompt
prompt Input department number (10, 20, 30)
accept dept_number number prompt 'Dept. #: '

select * from dept 
where deptno = &dept_number;

Вот что происходит при его выполнении:

SQL> @f:\usr\doc\orasdev\splus1

Input department number (10, 20, 30)
Dept. #: q
SP2-0425: "q" не является допустимым числом
Dept. #: 10
прежний 2: where deptno = &dept_number
новый 2: where deptno = 10

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

Команда PAUSE

Команда PAUSE позволяет дождаться подтверждения того, что пользователь прочитал сообщение на экране, выданное командой PROMPT. Для подтверждения необходимо нажать клавишу Enter, после чего выполнение сценария или сеанса SQL*Plus продолжится.

Команда PAUSE имеет следующий синтаксис:

<команда PAUSE> ::=

PAU[SE] [<текст>]

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

Рассмотрим простой пример. Изменим файл splus1.sql следующим образом:

set verify off
prompt Input department number (10, 20, 30)
accept dept_number number prompt 'Dept. #: '
pause Press Enter to view results

select * from dept 
where deptno = &dept_number;

Вот что будет выдано при его выполнении:

SQL> @f:\splus1
Input department number (10, 20, 30)
Dept. #: 30
Press Enter to view results


    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          CHICAGO

Для получения результатов пришлось нажать клавишу Enter после вывода соответствующего приглашения.

Связываемые переменные

Связываемые переменные - это создаваемые в SQL*Plus переменные, на которые можно ссылаться (как на хост-переменные) в блоках PL/SQL. Таким переменным можно, например, присваивать значения в блоках PL/SQL или использовать их значения во включенных в блоки SQL-операторах. Значения связываемых переменных можно затем выдавать в SQL*Plus.

Команда VARIABLE

Для создания связываемой переменной используется команда VARIABLE со следующим синтаксисом:

<команда VARIABLE> ::=

VAR[IABLE] [<имя переменной> [<тип данных>]]

<тип данных> ::=

NUMBER
  / CHAR [(<количество> [<единица измерения>])]
  / NCHAR [(<количество>)]
  / VARCHAR2 (<количество> [<единица измерения>])
  / NVARCHAR2 (<количество>)   / CLOB
  / NCLOB
  / REFCURSOR

<единица измерения> ::=

CHAR / BYTE

При вызове без параметров команда VARIABLE выдает список всех переменных, созданных в сеансе. Если указать только имя переменной, выдается информация только об этой переменной.

Связываемые переменные можно использовать как параметры хранимых процедур или непосредственно, в анонимных PL/SQL-блоках. Их нельзя использовать в команде COPY или присвоить им значение в SQL-операторах, не входящих в PL/SQL-блоки. Вместо связанной переменной, не получившей явно значения, при необходимости подставляется значение NULL.

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

SQL> var
SP2-0568: Не объявлены переменные привязки.
SQL> var dep number
SQL> begin
  2  select deptno into :dep from emp
  3  where sal = (select max(sal) from emp);
  4  end;
SQL> /

Процедура PL/SQL успешно завершена.

SQL> select * from dept where deptno = :dep;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SQL> var dep2 number
SQL> c /:dep/:dep2
  1* select * from dept where deptno = :dep2
SQL> /

строки не выбраны

SQL> var
переменная   dep
тип данных   NUMBER

переменная   dep2
тип данных   NUMBER

Мы использовали связываемую переменную для передачи значения из одного оператора SQL в другой, так и не выдав его на экран. Чтобы значения используемых в команде связываемых переменных выдавались автоматически, необходимо выполнить команду SET AUTOPRINT ON.

Типы данных для связываемых переменных аналогичны соответствующим типам данных PL/SQL. Переменные типа REFCURSOR позволяют работать с курсорными переменными PL/SQL. Утилита SQL*Plus обрабатывает связываемые переменные такого типа особым образом. Курсор, соответствующий курсорной переменной, открывается явно, а закрывается после выдачи значения или при завершении сеанса. При выдаче значения на экран выдается результирующее множество соответствующего запроса. Рассмотрим пример:

SQL> set autoprint on
SQL> var a refcursor;
SQL> begin
  2  open :a for select * from dept;
  3  end;
  4  /

Процедура PL/SQL успешно завершена.


    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 TRAINING       KIEV

SQL> exec open :a for select ename, sal from emp where deptno = :dep;

Процедура PL/SQL успешно завершена.


       DEP
----------
        10




ENAME             SAL
---------- ----------
CLARK            2450
KING             5000
MILLER           1300

Результаты, выдаваемые при показе значений связываемой переменной типа REFCURSOR можно форматировать так же, как и результаты выполнения SQL-оператора SELECT. Значение такой переменной выдается только один раз, - затем результирующее множество надо выбирать повторно.

Для явной выдачи на экран значения связываемой переменной используется команда PRINT.

Команда PRINT

Команда PRINT имеет следующий синтаксис:

<команда PRINT> ::=

PRI[NT] { <имя переменной>}

Эта команда выдает текущее значение перечисленных связываемых переменных. При вызове без параметров выдаются значения всех связываемых переменных.

Продолжая предыдущий пример:

SQL> print a
SP2-0625: Ошибка печати переменной "a"
SQL> print

       DEP
----------
        10


      DEP2
----------


SP2-0625: Ошибка печати переменной "a"
SQL> set autoprint off
SQL> exec open :a for select ename,sal,comm from emp where deptno=:dep;

Процедура PL/SQL успешно завершена.

SQL> print dep a

       DEP
----------
        10

ENAME             SAL       COMM
---------- ---------- ----------
CLARK            2450
KING             5000
MILLER           1300

Трассировка операторов

Утилита SQL*Plus позволяет автоматически получать отчет о способе выполнения оператора, выбранном оптимизатором SQL, а также статистическую информацию о выполнении. Этот отчет выдается после успешного выполнения операторов SELECT, INSERT, UPDATE и DELETE. Такой отчет полезен для контроля и настройки производительности этих операторов.

Для управления данным отчетом используется команда SET AUTOTRACE. Эта команда имеет пять опций:

Для использования этой возможности SQL*Plus необходимо создать в схеме пользователя таблицу PLAN_TABLE и получить роль PLUSTRACE (предоставить ее может только DBA). Выполним следующие действия:

SQL> set autotrace on
SP2-0613: Невозможно проверить формат или существование PLAN_TABLE
SP2-0611: Ошибка разблокирования EXPLAIN report
SP2-0618: Невозможно найти Идентификатор Сеанса.        Проверьте, 
разрешена ли роль PLUSTRACE
SP2-0611: Ошибка разблокирования STATISTICS report

Как видите, по умолчанию эта возможность не поддерживается. Создадим таблицу с помощью сценария $ORACLE_HOME/rdbs/admin/utlxplan.sql:

SQL> @g:\oracle\ora81\rdbms\admin\utlxplan

Таблица создана.

Затем создадим роль PLUSTRACE, дадим ей необходимые привилегии, а затем предоставим ее роли DBA (с помощью сценария $ORACLE_HOME/sqlplus/admin/plustrce.sql):

SQL> connect system/manager as sysdba
Соединено.
SQL> @g:\oracle\ora81\sqlplus\admin\plustrce
SQL> 
SQL> drop role plustrace;
drop role plustrace
          *
ошибка в строке 1:
ORA-01919: роль 'PLUSTRACE' не существует


SQL> create role plustrace;

Роль создана.

SQL> 
SQL> grant select on v_$sesstat to plustrace;

Привилегии предоставлены.

SQL> grant select on v_$statname to plustrace;

Привилегии предоставлены.

SQL> grant select on v_$session to plustrace;

Привилегии предоставлены.

SQL> grant plustrace to dba with admin option;

Привилегии предоставлены.

SQL> set echo off

Теперь предоставим роль PLUSTRACE пользователю, который будет использовать трассировку:

SQL> grant plustrace to scott;

Привилегии предоставлены.

Проверяем, что трассировочный отчет теперь выдается:

SQL> connect scott/tiger
Соединено.
SQL> set autotrace on
SQL> set pagesize 25
SQL> select ename, dname, sal
  2  from emp, dept
  3  where emp.deptno = dept.deptno;

ENAME      DNAME                 SAL
---------- -------------- ----------
SMITH      RESEARCH              800
ALLEN      SALES                1600
WARD       SALES                1250
JONES      RESEARCH             2975
MARTIN     SALES                1250
BLAKE      SALES                2850
CLARK      ACCOUNTING           2450
SCOTT      RESEARCH             3000
KING       ACCOUNTING           5000
TURNER     SALES                1500
ADAMS      RESEARCH             1100
JAMES      SALES                 950
FORD       RESEARCH             3000
MILLER     ACCOUNTING           1300

14 строк выбрано.


План выполнения
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=2 Bytes=88)
   1    0   HASH JOIN (Cost=3 Card=2 Bytes=88)
   2    1     TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=4 Bytes=44)
   3    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=41 Bytes=1353)


Статистика
----------------------------------------------------------
         62  recursive calls
          8  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
       1267  bytes sent via SQL*Net to client
        424  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         14  rows processed

Подробнее использование возможностей трассировки в SQL*Plus рассмотрено в отдельном модуле, посвященном настройке производительности.

Настройка среды SQL*Plus

Среда SQL*Plus - очень гибкая и имеет широкие возможности настройки по требованиям пользователя. Эти настройки выполняются, в основном, с помощью команды SET. Мы неоднократно использовали команду SET в примерах данного модуля. В следующем разделе представлено ее формальное описание.

Команда SET

Команда SET позволяет установить системную переменную, изменяющую свойства среды SQL*Plus для текущего сеанса. Она имеет следующий синтаксис:

<команда SET> ::=

SET <системная переменная> <значение>

Имена, описания и возможные значения основных системных переменных для команды SET представлены в табл. 15.

Таблица 15. Основные системные переменные SQL*Plus

Рассмотрим пример задания некоторых системных переменных SQL*Plus:

SQL> set time on
16:43:39 SQL> set timing on
16:43:43 SQL> set underline off
16:43:55 SQL> set pagesize 1000
16:44:02 SQL> set linesize 128
16:44:12 SQL> select * from dept
16:44:18   2  ;

    DEPTNO DNAME          LOC
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 TRAINING       KIEV

Затрач.время: 00:00:00.80
16:44:20 SQL>