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

Команды для работы с файлами

Утилита SQL*Plus позволяет запоминать команды, операторы SQL и блоки PL/SQL в командных файлах (или сценариях ). В дальнейшем эти командные файлы, - текстовые файлы базовой операционной системы - можно выполнять, загружать и редактировать.

Создание командных файлов

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

Команда SAVE

Команда SAVE позволяет сохранить в файле содержимое SQL-буфера и имеет следующий синтаксис:

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

SAV[E] <имя файла>[.<расширение>] [<режим записи>]

<режим записи> ::=

CRE[ATE] / REP[LACE] / APP[END]

Команда SAVE по умолчанию (или в режиме записи CREATE) создает файл с указанным именем и записывает в него содержимое буфера. В режиме REPLACE содержимое существующего файла заменяется содержимым буфера или файл создается. В режиме APPEND содержимое буфера дописывается в конец указанного файла.

Если расширение не указано, предполагается стандартное расширение (SQL или заданное командой SET SUFFIX). Если имя файла совпадает с режимом записи, расширение указывать обязательно.

Команда SAVE добавляет в командный файл строку, содержащую символ косой черты (/).

Команда EDIT

Команда EDIT позволяет вызвать текстовый редактор базовой операционной системы для редактирования указанного файла или содержимого SQL-буфера. Она имеет следующий синтаксис:

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

ED[IT] [<имя файла>[.<раширение>]]

Если расширение не указано, предполагается стандартное расширение (SQL или заданное командой SET SUFFIX). Файл для редактирования ищется в текущем рабочем каталоге. Если в нем такой файл не найден, он создается. При вызове без параметров содержимое буфера помещается в файл afiedt.buf в текущем рабочем каталоге, а затем этот файл загружается в текстовый редактор. Это стандартное имя можно переопределить с помощью команды SET EDITFILE. Если команда вызвана без параметров, а SQL-буфер пустой, выдается следующее сообщение об ошибке:

SP2-0107: Нет ничего для сохранения.

Если редактировался SQL-буфер, после завершения работы редактора содержимое соответствующего файла автоматически загружается в буфер. При этом последний символ последней строки (если она не пустая) усекается.

Имя вызываемого текстового редактора содержится в пользовательской переменной SQL*Plus _EDITOR. Значение этой переменной можно задать с помощью команды DEFINE. Если ее значение не задано, используется стандартный редактор операционной системы (Notepad в Windows; задаваемый переменной среды EDITOR или ed в UNIX).

Загрузка командных файлов

В любой момент в ходе работы с SQL*Plus можно загрузить содержимое любого текстового файла в буфер SQL. Для этого используется команда GET со следующим синтаксисом:

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

GET <имя файла>[.<расширение>] [<режим выдачи>]

<режим выдачи> ::=

LIS[T] / NOL[IST]

Если расширение не указано, предполагается стандартное расширение (SQL или заданное командой SET SUFFIX).

Загружаемый файл должен содержать один оператор SQL или блок PL/SQL. SQL-оператор не должен завершаться точкой с запятой (;). Обычно загружаются командные файлы, созданные с помощью команды SAVE - они автоматически удовлетворяют данным условиям.

Учтите, что команды собственно SQL*Plus в SQL-буфер не попадают, и если они окажутся в файле, загруженном с помощью команды PUT, то при выполнении содержимого буфера будут выданы сообщения об ошибках - команды SQL*Plus не являются операторами SQL! Также ошибка выдается, если файл содержит несколько операторов SQL или PL/SQL-блоков.

По умолчанию и в режиме LIST содержимое загруженного файла выдается на экран. Подавить выдачу содержимого командного файла позволяет режим NOLIST.

Выполнение командных файлов

Хотя команда GET и позволяет загрузить содержимое файла в SQL-буфер, откуда оно в дальнейшем может быть выполнено с помощью команды /, этот способ не является универсальным, так как накладывает существенные ограничения на содержимое командного файла (см. выше). Для загрузки и выполнения командных файлов, содержащих любое количество команд SQL*Plus, SQL-операторов и PL/SQL блоков, используется команда START и ее сокращенные варианты, @ и @@.

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

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

STA[RT] <имя или ссылка> [<аргумент>{ <аргумент>}]

<имя или ссылка> ::=

<имя файла>[.<расширение>] / <URI>

Файл, передаваемый команде START по имени (или по ссылке на Web-сайт в Oracle9i на платформе Windows), может содержать любые команды, которые можно вводить в диалоговом режиме. Если расширение в имени файла не указано, предполагается стандартное расширение (SQL или заданное командой SET SUFFIX).

Указанный по короткому имени файл будет сначала искаться в текущем каталоге, затем - в каталогах, входящих в стандартный путь поиска (обычно задается переменной среды SQLPATH).

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

При вызове командного файла можно передавать параметры в виде аргументов командной строки. Утилита SQL*Plus подставляет значение аргументов командной строки вместо позиционных параметров командного файла (&1, &2 и т.д.). Первый аргумент подставляется вместо параметра &1, второй - вместо &2, и так далее.

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

SQL> clear buffer
buffer очищена
SQL> input
  1 select ename, sal from emp
  2 where deptno = &1
  3
SQL> save test
Создано файл test
SQL> start test 10
прежний 2: where deptno = &1
новый 2: where deptno = 10
ENAME             SAL
---------- ----------
CLARK            2450
KING             5000
MILLER           1300

Сокращенные формы команды START (@, @@)

Утилита SQL*Plus поддерживает две сокращенные формы команды START: @ и @@. Команда @ функционально аналогична команде START:

SQL> @test 20
прежний   2: where deptno = &1
новый   2: where deptno = 20

ENAME             SAL
---------- ----------
SMITH             800
JONES            2975
SCOTT            3000
ADAMS            1100
FORD             3000

Примечание

Команда @ удаляет завершающий символ SQLTERMINATOR (по умолчанию - точка с запятой; см. SET SQLTERMINATOR) из последней команды. Если этот символ необходим, надо его удвоить.

Команда @@ работает так же, как и @, но дополнительно ищет командный файл в том же каталоге, что и командный файл, в котором она вызвана. Поэтому данная команда используется для вложенных вызовов командных файлов.

Рассмотрим типичное использование команды @@ в файле ex_all.sql:

set serverout on

@@ex0304
@@ex0305
@@ex0308
@@ex0309
@@ex0603
@@ex0606
@@ex0607
@@ex0608
@@ex0705
@@excurvar

При его запуске с помощью команды @ из того же каталога будут выполнены все перечисленные в нем командные файлы, а результаты их работы будут выданы на экран:

SQL> @f:\usr\doc\orasdev\ex_all.sql
SCOTT has a second highest salary!

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


Процедура создана.

Ошибок нет.
...

Коды возврата командных файлов

Если в ходе выполнения командного файла происходит ошибка Oracle, может потребоваться вернуть соответствующий код возврата базовой операционной системе. Это позволяет сделать команда WHENEVER SQLERROR со следующим синтаксисом:

<команда WHENEVER SQLERROR> ::=

WHENEVER SQLERROR <реакция на ошибку>

<реакция на ошибку> ::=

<выход>
  / COMMIT
  / ROLLBACK
  / CONTINUE <действие перед продолжением>

<выход> ::=

EXIT [<код возврата>][<завершение транзакции>]

<код возврата> ::=

SUCCESS / FAILURE / WARNING
  / <целое число> / <переменная> / :<связываемая переменная>

<завершение транзакции> ::=

COMMIT / ROLLBACK

<действие перед продолжением> ::=

COMMIT / ROLLBACK / NONE

В ответ на ошибку в сценарии можно, тем самым, выйти из SQL*Plus и вернуть необходимый код возврата операционной системе, зафиксировать или откатить выполненные изменения, либо проигнорировать ошибку и продолжить выполнение сценария (с фиксацией или откатом изменений при необходимости).

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

В командных файлах можно вводить и использовать комментарии трех видов:

• однострочные комментарии, задаваемые командой SQL*Plus REMARK;
• одно- или многострочные комментарии SQL /* ... */;
• однострочные комментарии ANSI/ISO --.

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

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

REM[ARK] [<любые символы до конца строки>]

Команда REMARK должна быть первой командой в строке. Задаваемый ею комментарий продолжается до конца строки.

Комментарии /* ... */ можно вводить в виде отдельных строк в командном файле, в строке оператора SQL или PL/SQL-блока. После пары символов начала комментария (/*) обязательно должен идти пробел, иначе косая рассматривается как команда выполнения содержимого SQL-буфера. Такие комментарии не могут быть вложенными. Комментарий, введенный в командной строке SQL*Plus, не попадает в буфер SQL.

Комментарии в виде -- ... можно вводить в конце строки с часть оператора SQL или PL/SQL-блока (такой комментарий продолжается до конца строки). Таким комментарием нельзя завершать команду SQL*Plus - он должен быть первой командой в строке.

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

1. Не размещайте комментарии среди первых нескольких ключевых слов оператора SQL.
2. Не размещайте комментарии после символа-завершителя команды SQL*Plus (точки, точки с запятой или косой).
3. Не размещайте символы-завершители команды в конце строки комментария или после комментария в SQL-операторе или PL/SQL-блоке.
4. Не используйте в комментариях метасимвол &, - утилита SQL*Plus потребует ввести значение параметра, проинтерпретировав слово после символа & в качестве имени параметра.

Рассмотрим пример командного файла, использующего все виды комментариев:

-- Это однострочный комментарий SQL*Plus
set linesize 128;
REM И это тоже однострочный комментарий SQL*Plus
select ename,
empno /* Это многострочный
** комментарий в
** SQL-операторе */
from emp -- однострочный комментарий в SQL-операторе
where sal = 5000;

Вот результат его выполнения:

SQL> @f:\tmp.txt

ENAME           EMPNO
---------- ----------
KING             7839

Запоминание установок среды SQL*Plus

Утилита SQL*Plus поддерживает многочисленные установки (см. раздел "Настройка среды SQL*Plus"), которые имеет смысл запоминать между сеансами. Для этого используется команда STORE:

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

STORE SET <имя файла>[.<расширение>] [<режим записи>]

Эта команда записывает значения переменных среды SQL*Plus в командный файл базовой операционной системы:

SQL> store set f:\env
Создано file f:\env

Вот примерное содержимое полученного файла f:\env.sql, созданного в SQL*Plus 8.1.6:

Листинг 1. Типичные установки среды SQL*Plus.

set appinfo OFF
set appinfo "SQL*Plus"
set arraysize 15
set autocommit OFF
set autoprint OFF
set autorecovery OFF
set autotrace OFF
set blockterminator "."
set cmdsep OFF
set colsep " "
set compatibility NATIVE
set concat "."
set copycommit 0
set copytypecheck ON
set define "&"
set describe DEPTH 1 LINENUM OFF INDENT ON
set markup HTML OFF SPOOL OFF ENTMAP ON PRE OFF
set echo OFF
set editfile "afiedt.buf"
set embedded OFF
set endbuftoken ""
set escape OFF
set feedback 6
set flagger OFF
set flush ON
set heading ON
set headsep "/"
set linesize 128
set logsource ""
set long 80
set longchunksize 80
set newpage 1
set null ""
set numformat ""
set numwidth 10
set pagesize 25
set pause OFF
set recsep WRAP
set recsepchar " "
set serveroutput ON size 2000 format WORD_WRAPPED
set shiftinout invisible
set showmode OFF
set sqlblanklines OFF
set sqlcase MIXED
set sqlcontinue "> "
set sqlnumber ON
set sqlprefix "#"
set sqlprompt "SQL> "
set sqlterminator ";"
set suffix "sql"
set tab ON
set termout ON
set time OFF
set timing OFF
set trimout ON
set trimspool OFF
set underline "-"
set verify ON
set wrap ON

Режим записи указывает, будет ли файл просто создан (CREATE, используется по умолчанию), переписан, если существует (REPLACE), или же значения установок среды SQL*Plus будут добавлены в конец существующего файла (APPEND). Полученный командный файл может быть выполнен командой START или ее сокращенными формами (@, @@).

Запись в файл и печать результатов выполнения запросов

Утилита SQL*Plus позволяет сбросить результаты выполнения команд в файл и распечатать их на стандартном принтере. Такой сброс называют спулингом . Для этого используется команда SPOOL (управляющая спулингом) со следующим синтаксисом:

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

SPO[OL] [<файл или команда>]

<файл или команда> ::=

<имя файла>[.<расширение>] / OFF / OUT

Команда SPOOL выдает результаты выполнения команд SQL*Plus в указанный файл и, возможно, на стандартный принтер, независимо от их отображения на экране. При вызове без параметров команда выдает состояние спулинга. Если не указано расширение имени файла, используется стандартное расширение (обычно, LST или LIS).

Команда OFF прекращает спулинг. Команда OUT прекращает спулинг и посылает файл на стандартный принтер базовой операционной системы.

Чтобы сбрасываемые в файл результаты не выдавались на экран, необходимо выполнить команду SET TERMOUT OFF.

В листинге 2 представлен пример сценария SQL*Plus, использующего команду SPOOL для выдачи исходного текста хранимой программной единицы в файл с соответствующим именем. Этот файл, в свою очередь, является сценарием, пригодным для повторного создания хранимой программной единицы. Такой прием, - генерация командных файлов в результате выполнения командных файлов - часто используется опытными администраторами баз данных при работе с SQL*Plus.

Листинг 2. Сценарий getcode.sql (© Tom Kyte, http://asktom.oracle.com).

set feedback off
set heading off
set termout off
set linesize 1000
set trimspool on
set verify off
spool &1..sql
prompt set define off
select decode( type//'-'//to_char(line,'fm99999'),
               'PACKAGE BODY-1', '/'//chr(10),
                null) //
       decode(line,1,'create or replace ', '' ) //
       text text
  from user_source
  where name = upper('&&1')
  order by type, line;
prompt /
prompt set define on
spool off
set feedback on
set heading on
set termout on
set linesize 100

Вызывать данный сценарий можно, например, так:

SQL> @f:\getcode ListBlackFridays

Примечание

На платформе Windows при использовании оконной версии SQL*Plus (sqlplusw.exe) файл, указанный в команде SPOOL, по умолчанию (если не задан полный путь) создается в каталоге %ORACLE_HOME%\bin. Вряд ли это подходящее место для таких файлов...

Мы еще вернемся к сценарию getcode.sql в следующих разделах, посвященных параметрам и настройке среды SQL*Plus.

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

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

Команда SHOW

Команда SHOW позволяет получить значение системных переменных SQL*Plus, а также ряд информации о среде, базе данных, к которой подключена утилита, и об ошибках в последней выполненной команде. Эта команда имеет следующий синтаксис:

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

SHO[W] <опция>

<опция> ::=

<имя системной переменной>
  / ALL
  / BTI[TLE]
  / ERR[ORS] [[<объект>] [<схема>.]<имя объекта>]
  / LNO
  / PARAMETERS [<часть параметра>]
  / PNO
  / REL[EASE]
  / REPF[OOTER]
  / REPH[EADER]
  / SGA
  / SPOO[L]
  / SQLCODE
  / TTI[TLE]
  / USER

<объект> ::=

FUNCTION   / PROCEDURE
  / PACKAGE
  / PACKAGE BODY
  / TRIGGER
  / VIEW
  / TYPE
  / TYPE BODY
  / DIMENSION
  / JAVA CLASS

Назначение опций представлено в табл. 16.

Таблица 16. Опции команды SHOW.

Рассмотрим пример использования ряда опций команды SHOW:

17:21:24 SQL> connect system/manager
Соединено.
17:21:47 SQL> show sga

Total System Global Area  152581388 bytes
Fixed Size                    70924 bytes
Variable Size              78741504 bytes
Database Buffers           73691136 bytes
Redo Buffers                  77824 bytes
17:21:49 SQL> show sqlcode
sqlcode 0
17:33:36 SQL> select * from emp;
select * from emp
              *
ошибка в строке 1:
ORA-00942: таблица или представление пользователя не существует


Затрач.время: 00:00:00.40
17:33:43 SQL> show sqlcode
sqlcode 942
17:33:50 SQL> show spool
spool OFF
17:34:52 SQL> show linesize
linesize 128
17:36:07 SQL> show user
USER имеет значение "SYSTEM"
17:36:10 SQL> show parameters buff

NAME                                 TYPE    VALUE
buffer_pool_keep                     string
buffer_pool_recycle                  string
db_block_buffers                     integer 17991
log_buffer                           integer 32768
use_indirect_data_buffers            boolean FALSE

Другие полезные команды

Есть еще несколько полезных команд, не относящихся ни к одной из рассмотренных выше категорий. Вот некоторые из них.

Команда EXECUTE

Эта команда выполняет один оператор PL/SQL. Чаще всего, это вызов хранимой процедуры или функции. Команда, по сути, неявно создает анонимный блок, в котором выполняется оператор. Рассмотрим пример:

18:02:02 SQL> set timing off
18:02:36 SQL> set serverout on
18:02:41 SQL> exec ListBlackFridays(sysdate, 3);
13.09.02
13.12.02
13.06.03

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

Команда PASSWORD

Команда PASSWORD позволяет изменить пароль пользователя, не отображая его на экране. Эта команда имеет следующий простой синтаксис:

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

PASSW[ORD] [<имя пользователя>]

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

18:02:49 SQL> set time off
SQL> connect system/manager
Соединено.
SQL> password scott
Изменение пароля для scott
Новый пароль: *****
Повторите новый пароль: *****
SQL> connect scott/tiger
Соединено.

Команды администрирования базы данных

Ряд команд утилиты SQL*Plus предназначен для АБД. По сути, SQL*Plus - полнофункциональное средство администрирования. Доступные в SQL*Plus команды администрирования рассматриваются в данном разделе.

Запуск базы данных

Начиная с Oracle8, утилиту SQL*Plus можно использовать для запуска и остановки базы данных (ранее для этих целей использовались утилиты svrmgrl и SQL*DBA). Запуск базы данных состоит из трех шагов:

1. Запуск экземпляра.

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

2. Монтирование базы данных.

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

3. Открытие базы данных.

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

Запуск базы данных в SQL*Plus выполняется командой STARTUP, имеющей следующий синтаксис:

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

STARTUP [FORCE] [RESTRICT] [PFILE=<имя файла параметров>] <действие запуска>

<действие запуска> ::=

MOUNT [<имя базы данных>]
  / OPEN [<опция открытия>][<имя базы данных>]
  / NOMOUNT

<опция открытия> ::=

READ ONLY
  / READ WRITE [RECOVER]
  / RECOVER

Все варианты команды STARTUP, так или иначе, запускают экземпляр (выделяется память и запускаются фоновые процессы). Опции команды STARTUP представлены в табл. 17.

Таблица 17. Опции команды STARTUP.

Эту команду может выполнять только пользователь, подключившийся как SYSOPER или SYSDBA к выделенному серверному процессу. По умолчанию используется опция OPEN. Команда STARTUP OPEN RECOVER монтирует и открывает базу данных, даже если полное восстановление закончилось неудачно.

Остановка базы данных

Команда SHUTDOWN в SQL*Plus останавливает текущий экземпляр Oracle, к которому подключен пользователь, и может при этом закрыть и демонтировать базу данных. Эту команду можно применять только для серверов версии 8 и выше.

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

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

SHUTDOWN <режим остановки>

<режим остановки> ::=

ABORT / IMMEDIATE / NORMAL / TRANSACTIONAL [LOCAL]

Назначение опций команды SHUTDOWN описано в табл. 18:

Таблица 18. Опции команды SHUTDOWN.

Эту команду может выполнять только пользователь, подключившийся как SYSOPER или SYSDBA к выделенному серверному процессу. По умолчанию используется опция NORMAL.

Управление архивированием журналов повторного выполнения

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

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

Для управления режимом архивирования журналов повторного выполнения утилита SQL*Plus предлагает команду ARCHIVE LOG, которая имеет следующий синтаксис:

<команда ARCHIVE LOG > ::=

ARCHIVE LOG <команда или журнал> [TO <место назначения>]

<команда или журнал> ::=

LIST / STOP / START / NEXT / ALL / <номер журнала>

Эта команда позволяет начать или остановить автоматическое архивирование оперативных файлов журнала повторного выполнения, явно заархивировать указанные по номерам файлы или выдать информацию о них. Опции команды ARCHIVE LOG описаны в табл. 19.

Таблица 19. Опции команды ARCHIVE LOG.

Если место назначения для архива не указано явно в командной строке, используется параметр инициализации LOG_ARCHIVE_DEST ( стандартное место назначения ). Если новое место назначения указано с опцией START, оно становится стандартным. В остальных случаях туда записываются только соответствующие архивы, инициированные данной командой.

Команду ARCHIVE LOG может выполнять только пользователь, подключившийся как SYSOPER или SYSDBA. Она применяется только к текущему экземпляру. Для управления другими экземплярами и кластером в целом используется SQL-оператор ALTER SYSTEM.

Если все оперативные группы файлов журнала повторного выполнения заполнены и не доступны для повторного использования (т.е. сервер работает в режиме ARCHIVELOG, и они не скопированы) работа базы данных приостанавливается. Явное архивирование решает эту проблему.

Рассмотрим простой пример использования команды ARCHIVE LOG для просмотра информации о текущем состоянии архивирования журналов повторного выполнения:

SQL> archive log list
Режим журнала базы данных	Режим архива
Автоматическое архивирование	Включено
Место размещения архива	g:\oracle\oradata\training
Самая старая последовательность оперативных журналов	745
Следующая последовательность журналов для архивирования 747
Текущая последовательность журналов 747
SQL> archive log 745
ORA-16013: журнал 1 с номером последовательности 745 не требует 
архивирования
ORA-00312: оперативный протокол 1 процесса 1: 
'G:\ORACLE\ORADATA\TRAINING\REDO01.LOG'

Восстановление базы данных

Если база данных работала в режиме ARCHIVELOG, после сбоя носителя ее можно полностью или частично восстановить. Для этого в SQL*Plus предлагается команда RECOVER, имеющая следующий, весьма объемный, синтаксис:

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

RECOVER <режим восстановления> [<степень параллелизма>]

<режим восстановления> ::=

<общее восстановление> / <управляемое восстановление> / END BACKUP

<общее восстановление> ::=

[AUTOMATIC] [FROM <местонахождение>] <команда восстановления>

<команда восстановления> ::=

<вид восстановления> [TEST ALLOW <целое число> CORRUPTION]
  / CONTINUE [DEFAULT]
  / CANCEL

<вид восстановления> ::=

<полное восстановление>
  / <частичное восстановление>
  / LOGFILE <имя файла>

<полное восстановление> ::=

[STANDBY] DATABASE { <уровень восстановления >} <уровень восстановления> UNTIL <точка восстановления>
  / USING BACKUP CONTROLFILE

<точка восстановления> ::=

CANCEL / TIME <дата> / CHANGE <целое число>

<частичное восстановление> ::=

<табличные пространства или файлы данных>
  / STANDBY <табличные пространства или файлы данных>
    UNTIL [CONSISTENT] [WITH] CONTROLFILE

<табличные пространства или файлы данных> ::=

TABLESPACE <табличное пространство>{, <табличное пространство>}
  / DATAFILE <файл данных>{, <файл данных>}

<управляемое восстановление> ::=

MANAGED STANDBY DATABASE <опция управляемого восстановления>

<опция управляемого восстановления> ::=

NODELAY
  / [TIMEOUT] <целое число>
  / CANCEL [IMMEDIATE] [NOWAIT]
  / DISCONNECT [FROM SESSION] [FINISH [NOWAIT]]

<степень параллелизма> ::=

PARALLEL [<целое число>] / NOPARALLEL

Опции команды RECOVER кратко описаны в табл. 20.

Таблица 20. Опции команды RECOVER.

Для выполнения команды RECOVER необходимо обладать ролью SYSDBA и подключиться через выделенный серверный процесс.

Чтобы можно было выполнить восстановление носителей для всей базы данных в целом (для всех табличных пространств), база данных должна быть смонтирована, но закрыта, а все требующие восстановления табличные пространства должны быть включены (online). Для восстановления же отдельного табличного пространства база данных должна быть смонтирована и открыта, а само табличное пространство отключено. Для восстановления файла данных, база данных может оставаться открытой и смонтированной, а поврежденные файлы - отключены (если только они не входят в табличное пространство SYSTEM).

Рассмотрим простой пример остановки, запуска и восстановления носителя из SQL*Plus:

SQL> connect system/manager as sysdba
Соединено.
SQL> shutdown
База данных закрыта.
База данных размонтирована.
Экземпляр ORACLE завершен.
SQL> startup mount
Экземпляр ORACLE запущен.
Total System Global Area  152581388	bytes
Fixed Size                    70924	bytes
Variable Size              78741504	bytes
Database Buffers           73691136	bytes
Redo Buffers                  77824	bytes
База данных смонтирована.
SQL> recover database until time '2001-12-09:00:00:00'
Восстановление носителя завершено.
SQL> alter database open;

Упражнения

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

Упражнение 1

Напишите командный файл SQL*Plus, выдающий все записи указанной таблицы в файл с именем <имя таблицы>.unl в виде строк, поля которых разделены символом вертикальной черты (/). Заголовки столбцов не выдавать.

Например, для таблицы dept содержимое файла должно иметь следующий вид:

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

Файл такого вида подходит для загрузки в базы данных Informix :).

Упражнение 2

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

Упражнение 3

Напишите командный файл SQL*Plus, выдающий на экран данные таблицы emp, предваряя денежные суммы знаком доллара и заменяя неизвестные значения прочерками. Снабдите создаваемый отчет заголовком и колонтитулами. Для каждого отдела выдайте значение средней заработной платы. В конце отчета выдайте сумму начисленных сотрудникам комиссионных.

Упражнение 4

Напишите командный файл SQL*Plus, выдающий по указанному имени представления текст оператора для его создания (CREATE VIEW) в файл с именем <имя>.sql. См. представление USER_VIEWS в словаре данных, описывающее представления данного пользователя. Длина каждой строки в файле не должна превышать 80 символов, чтобы файл было удобно читать.

Упражнение 5

Напишите сценарий SQL*Plus (без применения PL/SQL), увеличивающий вдвое зарплату всем сотрудникам отдела, средняя зарплата в котором ниже, чем средняя зарплата по всей организации (см. таблицы emp и dept). Затем сценарий должен выдать (в указанный при вызове файл) отчет о сотрудниках с указанием средней зарплаты по отделам. Снабдите столбцы отчета заголовками на русском языке.

• Обратиться в "Интерфейс" за дополнительной информацией/по вопросу приобретения продуктов
• Подробнее о продуктах Oracle
• Приобрести продукты Oracle в ITShop.ru
• Курсы обучения по продуктам компании Oracle