Открыто о СУБД Oracle на русском : Руководство по SQL*Plus. Часть III

Выпуск 63

Уважаемые подписчики рассылки!

Это третий выпуск в серии, посвященной утилите SQL*Plus. Посетители моего сайта часто ищут информацию об SQL*Plus или вообще документацию по Oracle, так почему бы им не помочь. Сообщите мне, пожалуйста, свое мнение о данной публикации.

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

Утилита 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> 

Copyleft (no c) 2001-2004 В. Кравчук, OpenXS Initiative, Cоставление, перевод, дополнения, упражнения и примеры

Прежние выпуски

Все вышедшие выпуски рассылки можно найти на сайте рассылки. Там же реализована возможность поиска материалов по ключевым словам (с помощью Google. Работает.)

Интересные материалы на сайте автора

Обновленная (до версии 3.0 от 2 февраля 2004 года) версия перевода культовой статьи Эрика Реймонда "Как правильно задавать вопросы". Кто не читал или читал давно - (пере)читайте обязательно!

Заметно пополнилась также страница со списком замеченных опечаток и неточностей в моем переводе книги Тома Кайта "Expert one-on-one: Oracle". Не забывайте сообщать мне о всех "проблемах", которые вы обнаружили в этой книге.

Обратите внимание также на новые переводы страниц справочного руководства ОС FreeBSD.

В следующем выпуске

Завершающая часть руководства по SQL*Plus.

С наилучшими пожеланиями,

  В.К.