SQL Plus: Создание и выполнение сценариев - Команды для работы с файлами в SQL*Plus

Written on 30 Декабря 2008. Posted in Oracle

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

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

форум oracle