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

Источник: ln

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

Команда BREAK

Команда BREAK позволяет разбить выдаваемые строки на группы по значению столбца, разделяя группы пустыми строками, а также управлять выдачей дублирующихся значений и значений, подсчитанных с помощью команды COMPUTE. Она имеет следующий синтаксис:

<команда BREAK> ::=: BRE[AK] {ON <элемент отчета>{ <действие>}}
<элемент отчета> ::=: <столбец> / <выражение> / ROW / REPORT
<действие> ::=: SKI[P] <количество строк> [<дубликаты>]
/ SKI[P] PAGE [<дубликаты>]
<дубликаты> ::=: NODUP[LICATES] / DUP[LICATES]

Команда BREAK без параметров выдает свои текущие параметры (параметры разрыва). Каждый последующий вызов BREAK с параметрами отменяет предыдущий. Для отмены параметров разрыва используется команда CLEAR BREAKS. Назначение опций команды BREAK описано в табл. 7.

Таблица 7. Основные варианты вызова команды BREAK.

Конструкция	Описание
ON <столбец> { <действие>}	Задает действия, которые выполняются при изменении значения указанного столбца. Столбец задается по имени или псевдониму, без уточнения именем объекта. Если действие не указано, подавляется выдача повторяющихся значений и отмечается место, где выполняется вычисление, заданное в соответствующей команде COMPUTE.
ON <выражение> { <действие>}	Задает действия, которые выполняются при изменении значения выражения из списка выбора. Выражение надо задавать буквально, как в списке выбора. Если действие не указано, подавляется выдача повторяющихся значений и отмечается место, где выполняется вычисление, заданное в соответствующей команде COMPUTE.
ON ROW <действие>{ <действие>}	Задает действия, которые выполняются при выдаче каждой строки. Необходимо задать хотя бы одно действие.
ON REPORT { <действие>}	Отмечает место в отчете, где SQLPlus выполнит вычисление, заданное в соответствующей команде COMPUTE*. Позволяет выдавать суммарные значения.
SKIP <количество строк>	Пропускает указанное количество строк (вставляет столько пустых строк) перед строкой, в которой происходит разрыв.
SKIP PAGE	Пропускает столько строк, сколько задано в качестве размера страницы (задается с помощью SET PAGESIZE). После последней строки данных строки не пропускаются.
NODUPLICATES	Выдает пробелы вместо значения в столбце, если оно совпадает со значением в предыдущей строке.
DUPLICATES	Выдает значение столбца в каждой строке, независимо от дублирования.

Конструкцию ON <столбец> можно задавать в одной команде BREAK несколько раз. При этом столбцы проверяются в порядке указания. Действия же выполняются в обратном порядке, от самого внутреннего разрыва. Если при вводе необходимо перенести опции на следующую строку, укажите дефис (-) в конце первой строки.

Обычно команда используется с операторами SELECT, содержащими конструкцию ORDER BY. Рассмотрим пример (обратите внимание на пустые строки):

SQL> break on deptno skip on sal skip 1
SQL> break
break on deptno пропустить 1 nodup
          on sal пропустить 1 nodup

SQL> select deptno, ename, sal from emp order by deptno;

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 CLARK            2450

           KING             5000

           MILLER           1300

        20 SMITH             800

           ADAMS            1100

           FORD             3000
           SCOTT

           JONES            2975

        30 ALLEN            1600

           BLAKE            2850

           MARTIN           1250

           JAMES             950

    DEPTNO ENAME             SAL
---------- ---------- ----------

        30 TURNER           1500

           WARD             1250


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

Команда COMPUTE

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

<команда COMPUTE> ::=: COMP[UTE]{ <функция> [LAB[EL] <текст>]}
OF <ссылка на столбец>{ <ссылка на столбец>}
ON <ссылка на место>{ <ссылка на место>}
<ссылка на место> ::=: <ссылка на столбец> / REPORT / ROW

Функции, которые можно использовать при вычислении в команде COMPUTE, представлены в табл. 8.

Таблица 8. Функции в команде COMPUTE.

Функция	Назначение	Допустимые типы данных
AVG	Среднее среди непустых значений	Числовые
COU[NT]	Количество непустых значений	Все
MIN[IMUM]	Минимальное значение	Числовые и строковые
MAX[IMUM]	Максимальное значение	Числовые и строковые
NUM[BER]	Количество строк	Все
SUM	Сумма непустых значений	Числовые
STD	Среднеквадратичное отклонение непустых значений	Числовые
VAR[IANCE]	Дисперсия непустых значений	Числовые

Назначение основных конструкций команды COMPUTE описано в табл. 9.

Таблица 9. Основные конструкции команды COMPUTE.

Конструкция	Описание
LABEL <текст>	Задает метку вычисляемого значения. Если эта конструкция не указана, выдается полное имя функции (см. табл. 8). Максимальная длина текста - 500 символов. Если текст содержит пробелы и символы пунктуации, его надо брать в одиночные кавычки. Метка выравнивается влево и усекается до меньшего из значений ширины столбца или длины строки. Метка для вычисляемого значения выдается в столбце, по которому выполняется BREAK. Чтобы метка не выдавалась, необходимо задать опцию NOPRINT в команде COLUMN для этого столбца. Если вычисления выполняются по ON ROW или ON REPORT, вычисляемое значение выдается в первом столбце и метка не выдается. Чтобы метка была выдана, включите в список выбора первым фиктивный столбец.
OF <ссылка на столбец>	Задает столбцы или выражения, функция от которого вычисляется. В конструкции OF можно сослаться на выражение в списке выбора, взяв его в двойные кавычки. Имя или псевдоним столбца в кавычки брать не надо.
ON <ссылка на место>	Задает событие, которое утилита SQLPlus будет считать местом для вычисления. При ссылке на столбец его имя нельзя уточнять, - при необходимости надо использовать псевдонимы. При достижении места вычисления (т.е. когда изменяется значение столбца или выражения, извлекается новая строка или достигается конец отчета) команда COMPUTE* выдает вычисленное значение и начинает вычисление сначала. Если для одного и того же столбца задано несколько команд COMPUTE, применяется последняя из них. В конструкции ON можно сослаться на выражение в списке выбора, взяв его в двойные кавычки. Имя или псевдоним столбца в кавычки брать не надо. Если в качестве события задано ON ROW или ON REPORT, необходимо, чтобы в последней команде BREAK также использовался критерий разрыва ROW или REPORT.

Для удаления всех определений COMPUTE используется команда CLEAR COMPUTES.

Рассмотрим простой пример вычисления итоговых значений и редактирования SQL-операторов в SQL*Plus:

SQL> set pagesize 55
SQL> break on deptno skip 1
SQL> compute avg label 'Средняя' of sal on deptno
SQL> select deptno, ename, sal
  2  from emp
  3  order by deptno;

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 CLARK            2450
           KING             5000
           MILLER           1300
**********            ----------
Средняя               2916,66667

        20 SMITH             800
           ADAMS            1100
           FORD             3000
           SCOTT            3000
           JONES            2975
**********            ----------
Средняя                     2175

        30 ALLEN            1600
           BLAKE            2850
           MARTIN           1250
           JAMES             950
           TURNER           1500
           WARD             1250
**********            ----------
Средняя               1566,66667

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

SQL> compute sum of sal on report
SQL> /
...
тот же результат, поскольку не изменили условие BREAK

SQL> break on deptno skip 1 on report
SQL> list 2
  2* from emp
SQL> list *
  2* from emp
SQL> i where deptno in (10, 20)
SQL> /

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 CLARK            2450
           KING             5000
           MILLER           1300
**********            ----------
Средняя               2916,66667

        20 SMITH             800
           ADAMS            1100
           FORD             3000
           SCOTT            3000
           JONES            2975
**********            ----------
Средняя                     2175

                      ----------
sum                        19625

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

Команда CLEAR

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

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

BRE[AKS] / BUFF[ER] / COL[UMNS] / COMP[UTES] / SCR[EEN] / SQL / TIMI[NG]

Назначение опций команды CLEAR представлено в табл. 10.

Таблица 10. Опции команды CLEAR.

Опция	Назначение
BREAKS	Сбрасывает определения групп, установленные командой BREAK.
BUFFER	Очищает буфер SQL. Аналогично CLEAR SQL, если только не используется несколько буферов (см. SET BUFFER).
COLUMNS	Сбрасывает в стандартные значения атрибуты представления данных всех столбцов, установленные командами COLUMN.
COMPUTES	Удаляет все определения итоговых функций, установленные командой COMPUTE.
SCREEN	Очищает экран SQL*Plus.
SQL	Очищает буфер SQL. Аналогично CLEAR BUFFER, если только не используется несколько буферов (см. SET BUFFER).
TIMING	Удаляет все таймеры, созданные командой TIMING.

Команды BTITLE и TTITLE

Утилита SQL*Plus позволяет задавать заголовок для показа в качестве верхнего (команда TTITLE) и нижнего (команда BTITLE) колонтитула на каждой странице отчета. Эти команды имеют следующий синтаксис:

<команда BTITLE> ::=: BTI[TLE] [<спецификации печати> <текст или переменная>{ <текст или переменная>}] [<вкл-выкл>]
<команда TTITLE> ::=: TTI[TLE] [<спецификации печати> <текст или переменная>{ <текст или переменная>}] [<вкл-выкл>]
<спецификации печати> ::=: <спецификация печати>{ <спецификация печати>}
<спецификация печати> ::=: COL <позиция>
  / S[KIP] [<количество строк>]
  / TAB <количество табуляций>
  / LE[FT]
  / CE[NTER]
  / R[IGHT]
  / BOLD
  / FORMAT <формат>
<текст или переменная> ::=: <текст>
/ SQL.LNO / SQL.PNO / SQL.RELEASE / SQL.SQLCODE / SQL.USER

Текст колонтитула необходимо брать в одиночные кавычки, если он состоит из нескольких слов. При выдаче одной из встроенных переменных SQL.* можно указывать конструкцию FORMAT. Соответствующие элементы формата см. в разделе, посвященном команде COLUMN.

Конструкция <вкл-выкл> (ON / OFF) позволяет включать и отключать вывод колонтитулов, не влияя на их определения.

Назначение спецификаций печати команд BTITLE и TTITLE представлено в табл. 11.

Таблица 11. Спецификации печати в командах BTITLE, TTITLE, REPHEADER и REPFOOTER.

Спецификация	Назначение
COL	Выравнивает текст по указанной символьной позиции.
SKIP	Выводит указанное количество пустых строк. По умолчанию - одну пустую строку. Значение 0 означает возврат к началу строки.
TAB	Пропускает указанное количество столбцов (позиций табуляции). Если указано отрицательное значение, смещает текущую позицию на соответствующее количество столбцов влево.
LEFT CENTER RIGHT	Задает выравнивание текущей строки (влево, по центру, вправо, соответственно). Все следующие элементы колонтитула (до конца спецификации или до следующей спецификации LEFT, CENTER, RIGHT или COL) выравниваются вместе, как группа. При этом используется длина строки, заданная с помощью команды SET LINESIZE.
BOLD	Выделяет данные. На терминале для этого одинаковые данные печатаются в трех последовательных строках. При выводе на печать строка обычно выдается жирным шрифтом.
FORMAT	Задает модель формата для последующих данных. Допустимые элементы модели формата см. в табл. 6 выше.

При вызове без параметров эти команды выдают текущий формат и признак вывода соответствующего колонтитула.

Если спецификации колонтитула необходимо перенести на следующую строку, предыдущая строка завершается дефисом (-).

Рассмотрим простой пример задания верхнего колонтитула для отчета:

SQL> ttitle left 'Список отделов' center '6 декабря 2001 г.' -
> right 'Стр.: ' format 999 SQL.PNO
SQL> select * from dept;

Список отделов             6 декабря 2001 г.                Стр.:    1
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

Команды REPHEADER и REPFOOTER

Команды REPHEADER и REPFOOTER позволяют задать текст, выдаваемый в начале и в конце каждого отчета, соответственно. При этом используются те же спецификации печати, что и в командах BTITLE и TTITLE (см. табл. 11 выше). Команды имеют следующий синтаксис:

<команда REPHEADER> ::=: REP[HEADER] [PAGE] [<спецификации печати>
<текст или переменная>{ <текст или переменная>}] [<вкл-выкл>]
<команда REPFOOTER> ::=: REP[FOOTER] [PAGE] [<спецификации печати>
<текст или переменная>{ <текст или переменная>}] [<вкл-выкл>]

Если указана опция PAGE, то соответствующий текст выдается на отдельной странице.

При вызове без параметров эти команды выдают текущий формат и признак вывода текста в начале и в конце отчета.

Копирование данных из одной базы данных в другую

Утилита SQL*Plus предлагает собственные средства копирования данных из одной базы данных в другую и из одной таблицы в другую. Для этого используется команда COPY, которая позволяет:

копировать данные с удаленной базы данных в текущую;
копировать данные из текущей базы данных на удаленную;
копировать данные из одной удаленной базы данных в другую.

Примечание: В общем случае, предполагалось копирование данных между базой данных Oracle и базой данных другого производителя. В среде Oracle для копирования данных можно использовать SQL-операторы (CREATE TABLE AS и INSERT).

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

<команда COPY> ::=: COPY <направление копирования> <действие копирования>
<имя целевой таблицы> [(<имя столбца>{, <имя столбца>})] USING <запрос>
<направление копирования> ::=: FROM <база данных>
/ TO <база данных>
/ FROM <база данных> TO <база данных>
<база данных> ::=: <имя пользователя>[/<пароль>]@<строка связи>
<действие копирования> ::=: APPEND / CREATE / INSERT / REPLACE

Она копирует данные, возвращаемые запросом, в таблицу в локальной или удаленной базе данных. Поддерживаются только базовые типы данных (CHAR, DATE, LONG, NUMBER, VARCHAR2) и в будущих версиях (после 9.0.1) поддержка этой команды не гарантируется.

Если в конструкции <база данных> пароль не указан, SQL*Plus запросит его перед выполнением команды.

Действия копирования описаны в табл. 12.

Таблица 12. Действия копирования команды COPY.

Действие	Описание
APPEND	Вставляет возвращенные запросом строки в целевую таблицу, если она существует. Если же таблица не существует, она создается.
CREATE	Вставляет возвращенные запросом строки в целевую таблицу, предварительно создавая ее. Если же таблица уже существует, возвращается сообщение об ошибке.
INSERT	Вставляет возвращенные запросом строки в целевую таблицу. Если таблица не существует, возвращается сообщение об ошибке. В список выбора запроса должно входить по одному столбцу для каждого столбца целевой таблицы.
REPLACE	Заменяет целевую таблицу и ее содержимое строками, возвращенными запросом. Если целевая таблица не существует, она создается. В противном случае, существующая таблица удаляется и вместо нее создается новая, содержащая скопированные данные.

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

По умолчанию, утилита SQL*Plus выполняет фиксацию транзакции после успешного завершения команды COPY. Если выполнить команду SET COPYCOMMIT n, где n - положительное целое число, фиксация будет выполняться после каждого n-го пакета записей. Размер пакета задается с помощью команды SET ARRAYSIZE.

Вся команда COPY должна поместиться на одной строке. Если необходимо перенести команду на следующую строку в конце строки необходимо ввести дефис (-).

Рассмотрим простой пример копирования таблицы:

SQL> copy from scott/tiger@training -
> create emp10 -
> using select * from emp where deptno = 10;

Размер массива выборки/привязки равен 15. (arraysize равен 15)
Фиксация по завершении. (copycommit равен 0)
Максимальная длина равна 80. (long равна 80)
Таблица EMP10 создана.

   3 строк выбрано из scott@training.
   3 строк вставлено в EMP10.
   3 строк зафиксировано в EMP10 по соединению DEFAULT HOST.

SQL> set linesize 100
SQL> select * from emp10;

     EMPNO ENAME  JOB           MGR HIREDATE        SAL   COMM  DEPTNO
---------- ------ --------- ------- -------- ---------- ------  ------
      7782 CLARK  MANAGER      7839 09.06.81       2450             10
      7839 KING   PRESIDENT         17.11.81       5000             10
      7934 MILLER CLERK        7782 23.01.82       1300             10

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

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

Не размещайте комментарии среди первых нескольких ключевых слов оператора SQL.
Не размещайте комментарии после символа-завершителя команды SQL*Plus (точки, точки с запятой или косой).
Не размещайте символы-завершители команды в конце строки комментария или после комментария в SQL-операторе или PL/SQL-блоке.
Не используйте в комментариях метасимвол &, - утилита 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 (c 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.

Ссылки по теме