SQL Plus: Создание и выполнение сценариев

ОГЛАВЛЕНИЕ

С помощью SQL*Plus можно:

  • вводить, редактировать, запоминать, загружать и выполнять команды SQL и блоки PL/SQL;
  • форматировать, создавать, сохранять, печатать и публиковать в Web результаты выполнения запросов (отчеты);
  • получать описание (имена и типы столбцов) любой таблицы и представления;
  • обращаться к удаленным базам данных и копировать из них данные;
  • посылать и принимать сообщения от конечных пользователей;
  • администрировать базу данных.

Базовые понятия

При работе с SQL*Plus используются следующие базовые понятия:

Команда Команда SQL*Plus или оператор SQL Oracle.
Блок PL/SQL Группа взаимосвязанных операторов PL/SQL, оформленная в виде анонимного блока.
Таблица Базовая единица хранения данных в Oracle.
Запрос SQL-оператор SELECT, выбирающий информацию из одной или нескольких таблиц.
Результаты запроса Данные, возвращенные запросом.
Отчет Результаты запроса, сформатированные с помощью команд SQL*Plus.

Расширенные формулы Бэкуса-Наура

При описании синтаксиса команд SQL*Plus использованы расширенные формулы Бэкуса- Наура (БНФ). Расширенные БНФ являются способом сокращенной записи контекстно- свободных грамматик. Нетерминальные символы (определяемые понятия, конструкции и команды SQL*Plus) взяты в угловые скобки. Терминальные символы выделены жирным шрифтом. Учтите, что команды SQL*Plus не зависят от регистра терминальных символов.

Метасимвол ::= означает "по определению есть". Метасимвол / обозначает, что данная формула является сокращенной записью нескольких формул с одинаковыми левыми частями и различными цепочками в правой части (т.е. возможен один из разделенных этим символом вариантов). Пара метасимволов [ ] используется для обозначения того, что находящаяся между ними цепочка может содержаться, а может и не содержаться в правой части формулы. Пара метасимволов { } означает, что содержащаяся между ними цепочка может повторяться ноль или более раз. Приоритет метасимвола / выше, чем у пар [ ] и { }.


Запуск SQL*Plus и выход из него

Для запуска утилиты SQL*Plus используется команда операционной системы, обычно - sqlplus. Запуск выполняется из командного интерпретатора или окна командной строки.

В версиях для операционных систем Microsoft использовались другие имена команды для вызова SQL*Plus, например, sqlplus30. В настоящее время (начиная с версии 8.1.5) в этих операционных системах имеется две версии этой утилиты: sqlplus, работающая в окне командной строки, и оконная версия, sqlplusw.

Общий синтаксис вызова SQL*Plus имеет вид:

sqlplus [<опции>] [<регистрационная информация>] [<начальный сценарий>]

где:

<опции> ::=
-H[ELP]
  / -V[ERSION]
  / -M[ARKUP] <опция разметки> [-R[ESTRICT] <уровень> [-S[ILENT]]]
<опция разметки> ::=
HTML [<вкл-выкл>] [HEAD <текст>] [BODY <текст>] [TABLE <текст>]
  [ENTMAP <вкл-выкл>] [SPOOL <вкл-выкл>] [PRE[FORMAT] <вкл-выкл>]
<вкл-выкл> ::=
ON / OFF
<уровень> ::=
1 / 2 / 3
<регистрационная информация> ::=
<пользователь и база данных> [AS <роль пользователя>]
  / /NOLOG
<пользователь и база данных> ::=
<имя пользователя>[/<пароль>][@<строка связи>] / / <роль пользователя> ::=
SYSOPER / SYSDBA
<начальный сценарий> ::=
@<имя или ссылка> [<аргументы сценария>]
<имя или ссылка> ::=
<имя файла>[.<расширение>] / <URI>
<аргументы сценария> ::=
<аргумент>{<пробелы><аргумент>}

Опции командной строки

Как следует из синтаксиса, при вызове SQL*Plus можно указать ряд опций. Они кратко описаны в табл. 1.

Таблица 1. Опции командной строки утилиты SQL*Plus.

Опция  Назначение 
HELP  Выдает информацию об использовании и синтаксисе вызова команды sqlplus и завершает работу.
VERSION  Выдает информацию о версии утилиты SQL*Plus и завершает работу.
MARKUP  Опция MARKUP позволяет генерировать полную HTML-страницу по результатам запроса или сценария.
RESTRICT  Позволяет отключить ряд команд, взаимодействующих с операционной системой. Если указан уровень ограничения 3, сценарий login.sql в текущем каталоге не читается. Стандартный сценарий запуска glogin.sql читается, но ограниченные команды в нем не работают. Команды, недопустимые на каждом из уровней ограничения, представлены ниже в табл. 2.
SILENT  Подавляет выдачу всех информационных сообщений и приглашений SQL*Plus, включая приглашение командной строки, эхо команд и начальную информацию, обычно выдаваемую при входе в SQL*Plus. Используется при вызове SQL*Plus из другой программы, когда его желательно скрыть, и совместно с опцией MARKUP при создании Web- отчетов.

Таблица 2. Команды, отключаемые уровнями ограничения.

Команда  Уровень 1  Уровень 2  Уровень 3 
EDIT  отключена отключена отключена
GET      отключена
HOST, !  отключена отключена отключена
SAVE    отключена отключена
SPOOL    отключена отключена
START, @, @@      отключена
STORE    отключена отключена

Регистрационная информация

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

Если указано имя пользователя и не указан пароль, SQL*Plus запросит только пароль. При установке опции SILENT, приглашения для ввода имени пользователя и пароля не выдаются, введенное имя пользователя отображается, а пароль, как обычно, нет.

Если вместо имени пользователя и пароля указана косая (/), предполагается стандартная регистрация от имени пользователя операционной системы (аутентификация операционной системой). При этом нельзя указывать строку связи. Cервер регистрирует пользователя OPS$ name , где name - регистрационное имя текущего пользователя операционной системы. Префикс "OPS$" можно заменить любым другим с помощью соответствующего параметра инициализации сервера (OS_AUTHENT_PREFIX).

Назначение роли позволяет получить привилегированное подключение пользователям, имеющим системные привилегии SYSOPER или SYSDBA. Такое привилегированное подключение можно получить и при запуске с опциями / или /NOLOG. При указании роли пользователя во многих операционных системах (например, в NT) необходимо взять все аргументы команды в кавычки:

SQLPLUS "/ AS SYSDBA" SQLPLUS "SYSTEM/MANAGER AS SYSOPER" 

Опция /NOLOG позволяет запустить утилиту SQL*Plus без регистрации. Прежде, чем выполнять любые SQL-операторы, придется выполнить команду CONNECT для подключения. Эта опция обычно используется при запуске сценариев, явно подключающихся к базе данных с помощью команды CONNECT.

Начальный сценарий

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

Если для начального сценария не указан суффикс в имени файла, используется суффикс. заданный командой SET SUFFIX (по умолчанию, .sql).

Использование профилей сайта и пользователя

Утилита SQL*Plus поддерживает использование профиля сайта  - командного файла, создаваемого администратором базы даных и автоматически выполняемого при регистрации любого пользователя. Этот файл обычно называется glogin.sql и находится в каталоге $ORACLE_HOME/sqlplus/admin.

Примечание
Здесь и далее $ORACLE_HOME означает начальный каталог, в который установлено используемое программное обеспечение Oracle. При указании имен файлов и каталогов используются соглашения ОС UNIX.

Утилита SQL*Plus поддерживает также использование профиля пользователя  - командного файла, выполняемого автоматически при регистрации пользователя после профиля сайта. Утилита SQL*Plus ищет файл профиля, login.sql, в текущем каталоге или, если не находит его там, в каталогах поиска файлов SQL-сценариев, задаваемых обычно переменной среды SQLPATH.

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

Выход из SQL*Plus

Для выхода из SQL*Plus необходимо ввести команду EXIT. Будет выдана информация о версии сервера, к которому была подключена утилита, и о версии SQL*Plus. Затем работа программы завершается и либо закрывается окно, либо вы получаете приглашение командной строки операционной системы.

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

<команда EXIT> ::=
EXIT [<код возврата>][<завершение транзакции>]
  QUIT [<код возврата>][<завершение транзакции>]
<код возврата> ::=
SUCCESS / FAILURE / WARNING
  / <целое число> / <переменная> / :<связываемая переменная>
<завершение транзакции> ::=
COMMIT / ROLLBACK

По умолчанию при выполнении команды EXIT SQL*Plus фиксирует текущую транзакцию и завершает работу с кодом возврата 0 (SUCCESS).


Подключение к стандартной и удаленной базе данных в SQL*Plus

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

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

Использование команды CONNECT

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

<команда CONNECT> ::=
CONN[ECT] [<пользователь и база данных>] [AS <роль пользователя>]

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

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

В случае если подключение по любой причине не удалось, вы остаетесь отключенными от базы данных, о чем будет сообщено. Можно также явно отключиться от схемы текущего пользователя с помощью команды DISCONNECT.

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

Формат строки связи для удаленной базы данных

К удаленной базе данных можно подключиться двумя способами:

  • из SQL*Plus с помощью команды CONNECT;
  • при запуске SQL*Plus с помощью опций командной строки.

В обоих случаях необходимо указать строку связи , задающую сетевую службу, к которой необходимо подключиться. Формат строки связи зависит от версии сетевых служб Oracle на машине, с которой выполняется подключение. При использовании Net8 обычно строка связи соответствует имени сетевой службы из файла tnsnames.ora, находящегося (в версиях 8.1.x и выше) в каталоге $ORACLE_HOME/network/admin/.

Пусть в файле tnsnames.ora имеется следующая запись для сетевой службы Oracle:

TRAINING =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = creator)(PORT = 1521))     )     (CONNECT_DATA =       (SERVICE_NAME = training)     )   ) 

Тогда для подключения к соответствующей базе данных при запуске SQL*Plus необходимо ввести, например, следующую команду в окне командной строки операционной системы:

sqlplus scott/tiger@training 

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

CONNECT scott/tiger@training 


Основные команды SQL*Plus

После запуска утилита SQL*Plus выдает приглашение командной строки (обычно, это строка "SQL> "), после которого можно вводить команды. Команды состоят из одного или нескольких слов, разделенных произвольным количеством пробелов и/или символов табуляции. Регистр символов в командах обычно не имеет значения. При нажатии клавиши Enter команда (при выполнении определенных условий по ее завершению) выполняется. После выполнения команды снова выдается приглашение.

Некоторые команды, например, блоки PL/SQL, могут занимать несколько строк. Тогда SQL*Plus выдает дополнительные приглашения с номером строки, например, "2> ".

В командной строке SQL*Plus можно вводить команды трех видов:

  • SQL-операторы для работы с информацией в базе данных;
  • блоки PL/SQL для работы с информацией в базе данных;
  • команды SQL*Plus для форматирования результатов запроса, установки опций, редактирования команд и т.д.

От вида команды зависит способ продолжения ее на следующей строке, завершения и выполнения.

Выполнение команд

Буфер SQL

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

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

Выполнение операторов SQL

Оператор SQL можно завершить тремя способами:

  • точкой с запятой (;)
  • строкой, содержащей только косую черту (/)
  • пустой строкой

Точка с запятой в конце строки означает выполнение оператора. Утилита SQL*Plus выполняет оператор и запоминает его в буфере SQL. Этот символ является признаком завершения команды и после него нельзя вводить комментарий.

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

Пустая строка в операторе SQL или сценарии SQL*Plus обычно означает, что ввод команды завершен, но выполнять ее пока не надо (это поведение можно изменить с помощью команды SQL*Plus SET SQLBLANKLINES). Команда помещается в буфер SQL и остается там, пока не будет введена другая команда.

Выполнение блоков PL/SQL

Для ввода блоков PL/SQL необходимо работать в режиме PL/SQL. Утилита SQL*Plus переходит в это режим, если:

  • в командной строке введено ключевое слово DECLARE или BEGIN;
  • введена одна из команд SQL, создающая хранимую программную единицу, например, CREATE PROCEDURE.

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

Выполнение команд SQL*Plus

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

Если необходимо ввести длинную команду SQL*Plus, ее можно перенести на следующую строку. Для этого в конце строки необходимо ввести дефис (-) и только затем нажать клавишу Enter. Будет выдано приглашение, и ввод команды можно будет продолжить.

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

Прекращение команды по ходу работы

Для прекращения долго выполняющейся команды по ходу работы, необходимо ввести символ прерывания , обычно, Ctrl+C. При этом SQL*Plus прекратит выдачу результатов команды и выдаст приглашение.

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

Получение информации о времени выполнения команды

Для сбора и выдачи данных о вычислительных ресурсах, использованных для выполнения одной или нескольких команд или блоков, предназначена команда TIMING. Она имеет следующий синтаксис:

<команда TIMING> ::=
TIMI[NG] <команда таймера>
<команда таймера> ::=
START [<имя таймера>] / SHOW / STOP

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

Таблица 3. Команды таймера SQL*Plus.

Команда  Назначение 
START  Запускает таймер и дает ему указанное имя. Можно использовать несколько активных таймеров, запуская дополнительные с помощью команды START прежде, чем останавливать исходный. Последний запущенный таймер становится текущим.
SHOW  Выдает имя и данные (время работы) текущего таймера.
STOP  Выдает имя и данные (время работы) текущего таймера, а затем останавливает и удаляет таймер. Если активно несколько таймеров, следующий, предпоследний по времени запуска, становится текущим.

Команда TIMING без параметров выдает количество активных таймеров.

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

SQL> timing start first
SQL> select * from dept;

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

SQL> timing show
таймер для: first
Затрач.время: 00:00:16.74
SQL> timing stop
таймер для: first
Затрач.время: 00:00:21.20
SQL>

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

Можно также автоматически выдавать время работы каждой команды помощью установки SET AUTOTRACE. Подробнее см. далее в разделе "Настройка среды SQL*Plus".

Выполнение команд базовой операционной системы

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

Команду HOST можно сокращать до первых двух букв. Если команда введена без параметров, открывается сеанс стандартного командного интерпретатора операционной системы. В нем можно выполнять любое количество команд. Для выхода из командного интерпретатора UNIX достаточно выполнить команду EXIT или ввести Ctrl+D.

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

Получение оперативной справки

Утилита SQL*Plus позволяет получить справочную информацию по своим командам, а также основным объектам схем.

Команда HELP

Для получения справки по командам SQL*Plus используется команда HELP. Она может вызываться с одним необязательным аргументом. Если аргумент не указан, выдается справка по справочной системе SQL*Plus. Если аргумент указан, то выдается справка по всем командам SQL*Plus, начинающимся с указанного аргумента. Например, команда HELP EX выдает сначала справку по команде EXECUTE, а затем - по команде EXIT.

Команда HELP INDEX выдает список всех команд SQL*Plus. Команда HELP TOPICS выдает список команд вместе с однострочным описанием назначения.

Команда DESCRIBE

Команда DESCRIBE позволяет получить описание таблицы или представления, а также спецификацию типа, пакета, функции или процедуры:

<команда DESCRIBE> ::=
DESC[RIBE] [[<схема>.]<объект>[@<строка связи>]]

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

  • имя каждого столбца;
  • допускаются ли в столбце значение NULL;
  • типы данных столбцов;
  • длина или точность (и масштаб, для числовых) столбцов.

Например:

SQL> desc emp
 Имя                                       Пусто?   Тип
 ----------------------------------------- -------- --------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

Команда DESCRIBE позволяет описывать объекты рекурсивно, причем глубина задается командой SET DESCRIBE. Можно также выдавать номер строки и сдвигать имя столбца или атрибута, если объект содержит несколько объектных типов.

Длину выдаваемой строки описания можно контролировать с помощью команды SET LINESIZE.

Описание функций и процедур содержит следующую информацию:

  • тип программной единицы (процедура или функция);
  • имя функции или процедуры;
  • тип возвращаемого значения для функций;
  • имена, типы, режим передачи и стандартные значения аргументов.

Например:

PROCEDURE sys.subptxt
 Имя Аргумента                  Тип                     В/Из   По-умолч
 ------------------------------ ----------------------- ------ --------
 NAME                           VARCHAR2                IN
 SUBNAME                        VARCHAR2                IN
 USR                            VARCHAR2                IN
 TXT                            VARCHAR2                IN/OUT


Редактирование команд SQL*Plus

Команды SQL*Plus не попадают в буфер, поэтому редактировать их можно только по ходу ввода с помощью клавиши Backspace.

Утилита SQL*Plus поддерживает несколько команд редактирования текста в буфере. Они представлены в табл. 4.

Таблица 4. Команды редактирования SQL*Plus.

Команда  Назначение 
A[PPEND] <текст>  Добавляет <текст> в конце строки.
C[HANGE] /<старая>[/<новая>[/]]  Заменяет в строке подстроку <старая> строкой <новая>. Если <новая> не указана, просто удаляет подстроку <старая>.
CL[EAR] BUF[FER Удаляет все строки из буфера.
DEL [* Удаляет текущую строку.
DEL <n> [* / <m> / LAST Удаляет строку с номером <n>, или со строки <n> до текущей, до строки <m> или до последней строки, соответственно.
DEL * [<n> / LAST Удаляет с текущей строки по строку <n> или до конца, соответственно.
DEL LAST  Удаляет последнюю строку.
ED[IT] [<имя файла>[.<расширение>]]  Вызывает текстовый редактор операционной системы и загружает в него буфер или указанный файл. Если редактируется буфер, то в текущем каталоге создается файл afiedt.buf, в котором и находится результат редактирования. Загрузка изменений в буфер выполняется автоматически после выхода из редактора. Стандартный редактор в ОС UNIX задается переменной среды _EDITOR. По умолчанию в ОС UNIX используется редактор ed, а в Windows - Notepad.
I[NPUT] [<текст>]  Вставляет строку или <текст>, в том числе, из нескольких строк, в буфер после текущей строки.
LIST [<n> / * / LAST Выдает, соответственно, все строки в буфере SQL, строку с указанным номером, текущую или последнюю строку.
LIST <n> [* / <m> / LAST Выдает строку с номером <n>, все строки, начиная со строки с номером <n>, до текущей, строки с номером <m> или до конца буфера, соответственно.
LIST * [<n> / LAST Выдает все строки с текущей строки до строки с номером <n> или до последней строки, соответственно.

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

SQL> select ename, salary from emp;
select ename, salary from emp
              *
ошибка в строке 1:
ORA-00904: неверно имя столбца

SQL> c /salary/sal
  1* select ename, sal from emp
SQL> i where deptno=30;
SQL> /

ENAME             SAL
---------- ----------
ALLEN            1600
WARD             1250
MARTIN           1250
BLAKE            2850
TURNER           1500
JAMES             950

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

SQL> list
  1  select ename, sal from emp
  2* where deptno=30


Форматирование результатов запросов SQL*Plus

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

Стандартный формат столбцов

Ширина числовых столбцов соответствует максимуму из ширины заголовка столбца, ширины, заданной с помощью опции COLUMN FORMAT, плюс один символ для знака, и ширины, заданной командой SET NUMWIDTH (по умолчанию, 10 символов). Если количество значащих цифр в числе больше, чем допускается шириной числового столбца, утилита SQL*Plus округляет число.

Для столбцов других типов ширина столбца соответствует его ширине в базе данных. Все эти столбцы по умолчанию выравниваются влево. Для столбцов типа DATE формат определяется соответствующими NLS-параметрами. Если они не заданы, предполагается формат A9 (см. табл. 6).


Команда COLUMN

Команда COLUMN для управления форматом выдачи столбца имеет следующий синтаксис:

<команда COLUMN> ::=
COL[UMN] [<ссылка на столбец> {<опция> }]
<ссылка на столбец> ::=
<имя столбца> / <псевдоним> / <выражение>
<опция> ::=
ALI[AS] <псевдоним>
  / CLE[AR]
  / ENTMAP [<вкл./откл.>]
  / FOLD_A[FTER]
  / FOLD_B[EFORE]
  / FOR[MAT] <формат>
  / HEA[DING] <текст>
  / JUS[TIFY] <выравнивание>
  / LIKE <выражение> / <псевдоним>
  / NEWL[INE]
  / NEW_V[ALUE] <переменная>
  / NOPRI[NT]
  / PRI[NT]
  / NUL[L] <текст>
  / OLD_V[ALUE] <переменная>
  / <вкл./откл.>
  / WRA[PPED]
  / WOR[D_WRAPPED]
  / TRU[NCATED]
<вкл./откл.> ::=
ON / OFF
<выравнивание> ::=
L[EFT] / C[ENTER] / C[ENTRE] / R[IGHT]

Назначение опций форматирования столбца кратко описано в табл. 5.

Таблица 5. Основные опции команды COLUMN.

Опция  Назначение 
ALIAS  Присваивает столбцу указанный псевдоним. По этому псевдониму на столбец можно ссылаться в дальнейшем в командах BREAK, COMPUTE и COLUMN.
CLEAR  Сбрасывает атрибуты указанного столбца в стандартные значения.
ENTMAP  Позволяет включать и отключать форматирование значений столбца для HTML-отчета. Если эта опция для столбца включена, в значениях будут заменяться символы, имеющие управляющее значение в HTML (<, >, & и т.д.).
FOLD_AFTER  Вставляет перевод строки после заголовка столбца и каждого значения в данном столбце. Перевод строки не вставляется, если указанный столбец - последний в списке выбора.
FOLD_BEFORE  Вставляет перевод строки перед заголовком столбца и каждым значением в данном столбце. Перевод строки не вставляется, если указанный столбец - первый в списке выбора.
FORMAT  Задает формат вывода значений столбца. Основные элементы формата представлены в табл. 6 ниже.
HEADING  Задает заголовок столбца. Если эта опция не используется, в качестве заголовка используются начальные символы (до ширины столбца) имени столбца или выбираемого выражения. Если в тексте есть пробелы или символы пунктуации, его необходимо брать в одинарные или двойные кавычки. Вместо каждого вхождения символа HEADSEP (по умолчанию - "/") в тексте заголовка вставляется перевод строки.
JUSTIFY  Задает выравнивание столбца. По умолчанию столбцы типа NUMBER выравниваются вправо, все остальные - влево.
LIKE  Копирует особенности форматирования указанного столбца, не заданные для текущего столбца явно.
NEWLINE  Вставляет перевод строки перед выдачей значения столбца аналогично FOLD_BEFORE.
NEW_VALUE  Задает переменную, в которой будет храниться значение столбца. Эту переменную можно использовать в команде TTITLE в качестве элемента верхнего колонтитула. Сам столбец необходимо при этом указать в команде BREAK с действием SKIP PAGE.
NOPRINT
PRINT 
Управляет выдачей столбца. Опция NOPRINT отключает выдачу столбца на экран и в отчет. Опция PRINT восстанавливает выдачу столбца.
NULL  Задает текст, выдаваемый утилитой SQL*Plus вместо пустых значений в столбце. По умолчанию используется пробел.
OLD_VALUE  Задает переменную, в которой будет храниться значение столбца. Эту переменную можно использовать в команде BTITLE в качестве элемента нижнего колонтитула. Сам столбец необходимо при этом указать в команде BREAK с действием SKIP PAGE.
ON
OFF 
Управляет применением особенностей (атрибутов) форматирования столбца. Значение OFF отключает применение особенностей форматирования, не отменяя их. Значение ON снова включает применение заданных особенностей форматирования.
WRAPPED
WORD_WRAPPED
TRUNCATED 
Задает правила работы со значениями, превышающими ширину столбца. Допускается перенос на следующую строку по границе столбца, по границе слова или усечение по границе столбца.

Таблица 6. Основные элементы формата опции FORMAT.

Элемент  Пример  Описание 
A<ширина>  A20  Этот элемент формата позволяет изменить стандартную ширину столбца строкового типа и типа DATE. Если значение столбца не помещается в заданную <ширину>, оно усекается или переносится, в зависимости от соответствующих установок.
9  9999  Представляет значащую цифру в числовом значении. Вместо начальных нулей выдаются пробелы. Нулевое значение представляется цифрой 0.
0  0999  Выдает начальный нуль.
$  $999  Выдает перед числовым значением символ доллара.
L  9999L  Выдает символ локальной денежной единицы в данной позиции.
.  9999.99  Выдает разделитель целой и дробной части (десятичную запятую) в данной позиции
,  9,999  Выдает запятую (разделитель разрядов) в данной позиции.
DATE  DATE  Выдает числовое значение (представляющее дату в Юлианском формате) как дату в формате MM/DD/YY.
EEEE  9.999EEEE  Выдает значение в экспоненциальном формате (обязательно указывать ровно четыре E).

Рассмотрим простой пример использования команды COLUMN для форматирования значений и заголовков столбцов:

SQL> col sal format 9,999.99
SQL> select sal, ename from emp;

      SAL ENAME
--------- ----------
   800.00 SMITH
 1,600.00 ALLEN
 1,250.00 WARD
 2,975.00 JONES
 1,250.00 MARTIN
 2,850.00 BLAKE
 2,450.00 CLARK
 3,000.00 SCOTT
 5,000.00 KING
 1,500.00 TURNER
 1,100.00 ADAMS
   950.00 JAMES
 3,000.00 FORD
 1,300.00 MILLER

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

SQL> col sal clear
SQL> col ename heading "Зарплата/товарища"
SQL> a where deptno=30
  1* select sal, ename from empwhere deptno=30
SQL> /
select sal, ename from empwhere deptno=30
                                      *
ошибка в строке 1:
ORA-00933: неверное завершение SQL-предложения

SQL> c/where/ where/
  1* select sal, ename from emp where deptno=30
SQL> /

           Зарплата
       SAL товарища
---------- ----------
      1600 ALLEN
      1250 WARD
      1250 MARTIN
      2850 BLAKE
      1500 TURNER
       950 JAMES

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

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

SQL> col ename COLUMN   ename ON HEADING  'Зарплата/товарища' headsep '/' 


Команда 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 { <действие>}  Отмечает место в отчете, где SQL*Plus выполнит вычисление, заданное в соответствующей команде 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 <ссылка на место>  Задает событие, которое утилита SQL*Plus будет считать местом для вычисления. При ссылке на столбец его имя нельзя уточнять, - при необходимости надо использовать псевдонимы. При достижении места вычисления (т.е. когда изменяется значение столбца или выражения, извлекается новая строка или достигается конец отчета) команда 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*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).

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

Утилита 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*Plus или SQL-оператора, кроме первого слова в командной строке (по первому слову утилита SQL*Plus определяет тип команды). Когда SQL*Plus встречает в командной строке подставляемую переменную, она подставляет значение этой переменной (запрашивая его, если переменная не определена). Используются подставляемые переменные для получения более гибких, интерактивных сценариев SQL*Plus. Рассмотрим простой пример:

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*Plus запрашивает ее значение. Затем на экран выдается вид команды до и после подстановки всех значений (это можно отключить с помощью команды SET VERIFY OFF), и команда выполняется. В нашем примере мы подставили в команду конкретную функцию агрегирования, имя столбца, по которому выполняется агрегирование, и имя таблицы.

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

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

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

Если введенное значение совпадает с подставляемой переменной (начинается с &), то (по крайней мере, в версии 8.1.6) выдается сообщение об ошибке:

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

Установка  Описание 
SET DEFINE  Задает символ подстановки (вместо стандартного - &) и позволяет включать и отключать подстановку.
SET ESCAPE  Задает символ маскировки, позволяющий маскировать символ подстановки. Стандартным символом маскировки является обратная косая (\).
SET VERIFY  Позволяет включать и отключать выдачу каждой строки команды до и после подстановки пользовательских переменных.
SET CONCAT  Задает символ, отделяющий имя подставляемой переменной от следующей непосредственно за ним строки. По умолчанию используется точка (.).


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

Стандартный механизм запроса значений пользовательских переменных дает ограниченные средства взаимодействия с пользователем - ввод значений в ответ на стандартные приглашения. Утилита 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.

Опция  Назначение 
NUMBER  Задает переменной тип NUMBER. Если введенное значение не приводится к этому типу, команда ACCEPT выдает сообщение об ошибке и запрашивает значение снова.
CHAR  Задает переменной тип CHAR. Длина строки-значения не должна превышать 240 байтов.
DATE  Задает переменной тип DATE. Если введенное значение не преобразуется в тип DATE с учетом текущего формата даты в сеансе (NLS_DATE_FORMAT), команда ACCEPT выдает сообщение об ошибке и запрашивает значение снова.
FORMAT  Явно задает формат, которому должно соответствовать введенное значение. Формат задается так же, как для команды COLUMN. Если значение не соответствует формату, команда ACCEPT выдает сообщение об ошибке и запрашивает значение снова.
DEFAULT  Задает стандартное значение, если оно не будет введено. Стандартное значение должно соответствовать стандартному или указанному формату.
PROMPT  Выдает на экран указанный текст перед ожиданием ввода значения переменной.
NOPROMPT  Выдает перевод строки и ждет ввода значения, не выдавая приглашений.
HIDE  Подавляет выдачу вводимого значения на экран по ходу ввода.

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

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

OFF  Отчет не выдается. Это стандартный режим работы SQL*Plus.
ON EXPLAIN  Выдается только выбранный оптимизатором план выполнения оператора.
ON STATISTICS  Выдается только статистическая информация о выполнении оператора.
ON  Выдается план выполнения запроса и статистическая информация о выполнении оператора.
TRACEONLY  Выдается отчет о статистике, но не выдаются результаты выполнения оператора. Используется для трассировки запросов, возвращающих большие объемы данных.

Для использования этой возможности 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

Переменная  Значения  Назначение 
APPI[NFO ON / OFF / <текст> Устанавливает автоматическую регистрацию командных файлов с помощью пакета DBMS_APPLICATION_INFO. Это позволяет контролировать производительность и использование ресурсов каждым командным файлом. При отключенной регистрации файлов в качестве имени модуля используется строка "SQL*Plus" или заданный в этой переменной текст. По умолчанию регистрация отключена.
ARRAY[SIZE <размер пакета> Устанавливает размер пакета строк, извлекаемых утилитой SQL*Plus из базы данных за один прием. Диапазон допустимых значений - от 1 до 5000. По умолчанию извлекается по 15 строк.
AUTO[COMMIT ON / OFF / IMM[EDIATE] / <к-во операторов> Управляет фиксацией изменений в базе данных. По умолчанию (OFF) изменения необходимо фиксировать явно. Изменения могут фиксироваться немедленно после успешного выполнения оператора или блока (ON, IMM), или после успешного выполнения указанного количества операторов или блоков PL/SQL. Значение должно быть в диапазоне от 0 до 2000000000.
AUTOP[RINT ON / OFF  Устанавливает автоматическую выдачу значений использованных в команде связываемых переменных.
AUTORECOVERY  ON / OFF  При установке значения ON команда RECOVER будет автоматически использовать стандартные имена файлов архивных журналов повторного выполнения. По умолчанию, имена файлов при восстановлении придется вводить вручную, в ответ на запросы SQL*Plus.
AUTOT[RACE ON / OFF / TRACE[ONLY] [EXP[LAIN]] [STAT[ISTICS]] Управляет выдачей отчета о выполнении успешного оператора SELECT, INSERT, UPDATE или DELETE. В отчет может включаться план выполнения и статистическая информация о выполненных действиях.
BLO[CKTERMINATOR . / <символ> Задает не алфавитно-цифровой символ, используемый для завершения блока PL/SQL. По умолчанию используется точка.
CMDS[EP ; / ON / OFF / <символ> Задает не алфавитно-цифровой символ, используемый для разделения нескольких команд SQL*Plus в одной строке. ON/OFF управляет возможностью обработки нескольких команд в строке. Значение ON означает, кроме того, установку стандартного разделителя - точки с запятой.
COLSEP  <текст> Задает текст, который будет выдаваться между выбранными столбцами. Если текст содержит пробелы или символы пунктуации, его необходимо брать в апострофы. По умолчанию используется один пробел.
COM[PATIBILITY V7 / V8 / NATIVE  Задает версию Oracle, к которой подключена утилита SQL*Plus. Значение NATIVE, принятое по умолчанию, указывает, что версия определяется базой данных.
CON[CAT . / ON / OFF / <символ> Задает символ, который позволяет отделить имя подставляемой переменной от остальной части строки. Стандартный символ - точка.
COPYC[OMMIT <к-во пакетов> Задает количество пакетов (размер задается системной переменной ARRAYSIZE), после копирования которых команда COPY фиксирует изменения в базе данных. Значение должно быть в диапазоне от 0 до 5000. При использовании стандартного значения, 0, фиксация выполняется только по завершении копирования.
DEF[INE & / ON / OFF / <символ> Задает символ-префикс подставляемой переменной. При указании значения ON используется стандартный префикс - &.
DESC[RIBE [DEPTH 1 / ALL / <глубина>]
[LINENUM ON / OFF]
[INDENT ON / OFF]
Задает количество уровней рекурсивного описания объекта. Допустимы значения от 1 до 50. ALL означает максимальное количество уровней, 50. Можно также указывать номер строки и отступы для имени столбца или атрибута, когда объект содержит несколько компонентов объектных типов.
ECHO  ON / OFF  Управляет выдачей на экран каждой команды в командном файле, выполняемом с помощью START.
EDITF[ILE <имя файла>[.<расширение>] Устанавливает стандартное имя файла для команды EDIT.
EMB[EDDED ON / OFF  Задает выдачу каждого нового отчета с новой страницы (стандартное значение, OFF) или как продолжение на текущей странице (ON).
ESC[APE \ / ON / OFF / <символ> Задает маскирующий символ. Значение ON задает стандартный маскирующий символ, обратную косую.
FEED[BACK ON / OFF / <к-во строк> Управляет выдачей количества строк, возвращенных запросом. Информация выдается, если возвращено не менее указанного количества строк. Значение ON эквивалентно 1.
FLU[SH ON / OFF  Управляет буферизацией результатов. При стандартном значении ON результаты выдаются клиенту немедленно.
HEA[DING ON / OFF  Управляет выдачей заголовков столбцов в результатах.
HEADS[EP / / ON / OFF / <символ> Задает символ перевода строки для команд, задающих колонтитулы. Можно задавать любой символ, кроме алфавитно-цифровых и пробела. По умолчанию используется вертикальная черта.
LIN[ESIZE <длина строки> Задает количество символов в выдаваемой строке. Если выдаваемая строка длиннее, остаток переносится на новую строку. Это значение также используется для выравнивания колонтитулов и заголовков отчетов.
LOGSOURCE  [<путь>] Задает местонахождение архивных файлов журнала повторного выполнения для восстановления. Если значение не указано, будет использоваться путь, указанный в файле параметров инициализации.
LONG  <ширина столбца> Задает максимальную ширину (в байтах) для выдачи значений типа LONG, CLOB и NCLOB (а также для копирования значений типа LONG). Значение не может превосходить 2 Гбайта.
NEWP[AGE NONE / <к-во строк> Задает количество пустых строк, выдаваемых в начале страницы отчета, перед верхним колонтитулом. Стандартное значение - 1 строка. Если указано значение NONE, пустые строки не выдаются. Если же указано значение 0, при печати пропускается страница, а при выдаче на экран он очищается.
NULL  <текст> Задает текст, представляющий пустое значение в результатах выполнения оператора SELECT.
NUMF[ORMAT <формат> Задает стандартный формат выдачи чисел (о форматах см. в описании команды COLUMN)
NUM[WIDTH <ширина> Задает стандартную ширину числовых столбцов.
PAGES[IZE <к-во строк> Задает количество строк на странице. Если установить значение 0, подавляется выдача заголовков, колонтитулов и начальных пустых строк.
PAU[SE ON / OFF / <текст> Позволяет управлять прокруткой при показе отчетов. При установке значения ON, утилита SQL*Plus приостанавливает работу перед выводом каждой страницы отчета, ожидая нажатия клавиши Enter. Можно также задать текст, который будет при этом выводиться.
RECSEP  WR[APPED] / EA[CH] / OFF  Устанавливает выдачу разделителей между записями (после перенесенных на несколько строк, после всех или никогда).
RECSEPCHAR  <символ> Задает символ, из которого состоит разделитель записей. По умолчанию используется пробел. Разделитель записей представляет собой строку символов RECSEPCHAR, повторенных LINESIZE раз.
SERVEROUT[PUT ON / OFF
[SIZE <к-во строк>]
[FOR[MAT] WRA[PPED] / WOR[D_WRAPPED] / TRU[NCATED]]
Управляет показом результатов хранимых процедур или анонимных блоков PL/SQL (выданных с помощью пакета DBMS_OUTPUT), размером буфера и переносом строк. Размер буфера должен быть в пределах от 2000 (стандартное значение) до 1000000.
SHOW[MODE ON / OFF  Управляет выдачей значений системных переменных при изменении. Если задано значение ON, при изменении выдается старое и новое значение.
SQLBL[ANKLINES ON / OFF  Разрешает или запрещает использование пустых строк в сценариях и командах SQL.
SQLC[ASE MIX[ED] / LO[WER] / UP[PER] Управляет изменением регистра символов в команде перед выполнением. По умолчанию (MIXED) регистр символов остается неизменным. Иначе все символы, включая литералы в кавычках, переводятся в соответствующий регистр. Содержимое буфера SQL при этом не меняется.
SQLCO[NTINUE <текст> Задает текст, выдаваемый утилитой SQL*Plus в качестве приглашения для продолжения ввода команды. По умолчанию используется символ >.
SQLN[UMBER ON / OFF  Управляет выдачей приглашения для ввода второй и последующих строк команды или блока PL/SQL. Если указано значение ON, в качестве приглашения выдается номер строки, если OFF - значение системной переменной SQLPROMPT.
SQLPRE[FIX <символ> Задает префиксный символ SQL*Plus (по умолчанию - #). Если по ходу ввода команды или блока PL/SQL ввести в отдельной строке команду, начинающуюся префиксным символом, эта команда немедленно выполнится. Префиксный символ должен быть не алфавитно-цифровым.
SQLP[ROMPT <текст> Задает приглашение командной строки SQL*Plus (по умолчанию - "SQL> ").
SQLT[ERMINATOR <символ> / ON / OFF  Задает символ, завершающий и посылающий на выполнение команду SQL. По умолчанию используется точка с запятой. Если задать значение OFF, то для завершения придется вводить пустую строку или BLOCKTERMINATOR, если установлена системная переменная SQLBLANKLINES.
SUF[FIX <текст> Задает стандартное расширение командного файла. По умолчанию используется sql.
TAB  ON / OFF  Управляет использованием символа табуляции для форматирования результатов запросов в SQL*Plus.
TERM[OUT ON / OFF  Управляет выдачей результатов команд на экран. Если задать значение OFF, результат выполнения команд (кроме интерактивных) не будет выдаваться на экран, но будет записываться в файл, указанный в команде SPOOL.
TI[ME ON / OFF  Управляет выдачей текущего времени. При установке значения ON в приглашении выдается текущее время.
TIMI[NG ON / OFF  Управляет выдачей статистики о времени. При установке значения ON после выполнения команды или командного файла выдается время выполнения с точностью до сотых долей секунды.
TRIM[OUT ON / OFF  Управляет усечением хвостовых пробелов в выдаваемых строках. При установке значения ON (принято по умолчанию), хвостовые пробелы усекаются. Эта переменная учитывается только при выводе на экран.
TRIMS[POOL ON / OFF  Управляет усечением хвостовых пробелов в строках, сбрасываемых в файл или на принтер, аналогично TRIMOUT.
UND[ERLINE ON / OFF / <символ> Задает символ, используемый для подчеркивания заголовков столбцов. По умолчанию используется символ дефиса. Значение OFF отключает подчеркивание заголовков.
VER[IFY ON / OFF  Управляет выдачей текста SQL-операторов и PL/SQL-блоков до и после замены подставляемых переменных значениями.
WRA[P ON / OFF  Управляет усечением слишком длинных строк результатов. По умолчанию, остатки строк переносятся на следующую строку. При задании значения OFF - усекаются.

Рассмотрим пример задания некоторых системных переменных 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.

Опция  Назначение 
ALL  Выдает значения всех опций SHOW, кроме ERRORS и SGA, и всех системных переменных.
BTI[TLE Выдает текущее определение BTITLE (нижнего колонтитула).
ERR[ORS Выдает ошибки компиляции хранимой программной единицы. При компиляции выдается сообщение, что ошибки есть. Для получения детальной информации об ошибках необходимо выполнить команду SHOW ERRORS. При выполнении этой команды без аргументов, выдается информация об ошибках при компиляции последней созданной или измененной программной единицы. Если же указать тип и имя программной единицы, будут выданы ошибки, полученные при ее компиляции.

Выдается номер строки/столбца (в столбце LINE/COL), в которых обнаружена ошибка, и текст сообщения об ошибке (в столбце ERROR). Форматом выдачи можно управлять с помощью команды COLUMN.

LNO  Показывает номер текущей строки на странице.
PARAMETERS  Выдает имя, тип и текущее значение одного или нескольких параметров инициализации. При вызове без дополнительных параметров, эта команда выдает информацию обо всех параметрах инициализации. Если указать части имен параметров, будет выдана информация только о параметрах, в имена которых указанные части присутствуют.

Для использования этой опции необходима объектная привилегия SELECT на представление V_$PARAMETER.

PNO  Выдает номер текущей страницы результатов.
REL[EASE Выдает номер версии сервера Oracle, к которой подключена утилита SQL*Plus.
REPF[OOTER Выдает текущее определение REPFOOTER.
REPH[EADER Выдает текущее определение REPHEADER.
SGA  Выдает информацию об области SGA экземпляра, к которому подключена утилита SQL*Plus.

Для использования опции SGA необходима объектная привилегия SELECT на представление V_$SGA.

SPOO[L Выдает информацию о том, направляются ли результаты в файл или на принтер.
SQLCODE  Выдает код завершения последнего оператора SQL.
TTI[TLE Выдает текущее определение TTITLE (верхнего колонтитула).
USER  Выдает имя текущего пользователя.

Рассмотрим пример использования ряда опций команды 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.

Опция  Назначение 
FORCE  Останавливает (с опцией ABORT) и потом перезапускает экземпляр. Это единственная опция команды STARTUP, которую можно применять к работающему экземпляру. Используется при отладке и в экстраординарных ситуациях.
RESTRICT  Позволяет подключаться к базе данных после запуска только пользователям, обладающим системной привилегией RESTRICTED SESSION. Это ограничение в дальнейшем можно снять с помощью команды ALTER SYSTEM.
PFILE  Задает нестандартный файл параметров инициализации. Если эта опция не указана, используется стандартный файл (в ОС UNIX это обычно $ORACLE_HOME/admin/dbs/init$ORACLE_SID.ora, а в Windows - %ORACLE_HOME%\database\init%ORACLE_SID%.ora).
MOUNT  Монтирует указанную (стандартную локальную - значение параметра инициализации DB_NAME) базу данных, но не открывает ее.
OPEN  Монтирует и открывает указанную базу данных.
NOMOUNT  Экземпляр запускается, но база данных не монтируется.
RECOVER  Требует выполнить восстановление носителей, если необходимо, перед запуском экземпляра. Применение этой опции аналогично выполнению команды RECOVER DATABASE с последующим обычным запуском. Так можно выполнять только полное восстановление.

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


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

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

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

<команда SHUTDOWN> ::=
SHUTDOWN <режим остановки>
<режим остановки> ::=
ABORT / IMMEDIATE / NORMAL / TRANSACTIONAL [LOCAL]

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

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

Опция  Назначение 
ABORT  Выполняет немедленную остановку базы данных, не дожидаясь завершения транзакций и отключения пользователей. Эта опция используется при аварийном завершении одного из фоновых процессов или при невозможности нормальной остановки. При перезапуске потребуется восстановление.
IMMEDIATE  Не ждет завершения транзакций (автоматически их откатывает) и отключения пользователей. Новые подключения запрещаются. База данных закрывается и демонтируется, затем останавливается экземпляр. При перезапуске восстановление не потребуется.
NORMAL  Ждет отключения всех пользователей от базы данных (новые подключения запрещены). База данных закрывается и демонтируется, затем останавливается экземпляр. При перезапуске восстановление не потребуется.

Эта опция используется по умолчанию.

TRANSACTIONAL [LOCAL Ждет завершения активных транзакций. При попытке начать новую транзакцию происходит отключение сеанса. После завершения всех активных транзакций все сеансы автоматически отключаются. Затем остановка идет как при вводе опции IMMEDIATE.

Режим LOCAL задает такой режим остановки только для локальных транзакций. Сервер не ждет завершения удаленных транзакций.

Эту команду может выполнять только пользователь, подключившийся как 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.

Опция  Назначение 
LIST  Выдает информацию о режиме архивирования, каталоге, в котором создаются файлы архива, номерах текущей, последней заархивированной и требующей архивирования группы журналов. Вид выдаваемой информации представлен в примере ниже.
STOP  Останавливает автоматическое архивирование. Если экземпляр по- прежнему работает в режиме ARCHIVELOG и все группы журналов повторного выполнения заполнены, работа базы данных приостанавливается, пока файл журнала повторного выполнения не будет заархивирован (например, командами ARCHIVE LOG NEXT или ARCHIVE LOG ALL).
START  Включает автоматическое архивирование. Запускает фоновый процесс ARCH, выполняющий автоматическое архивирование при необходимости. Если запускается процесс ARCH и в команде указано имя файла, этот файл становится новым стандартным местом назначения для архива. Процесс ARCH запускается автоматически при запуске экземпляра, если параметр инициализации LOG_ARCHIVE_START имеет значение TRUE.
NEXT  Явно архивирует следующую заполненную, но еще не заархивированную оперативную группу файлов журнала повторного выполнения.
ALL  Явно архивирует все заполненные, но еще не заархивированные оперативные группы файлов журнала повторного выполнения.
номер журнала  Вызывает архивирование любой еще доступной оперативной группы файлов журнала повторного выполнения с указанным номером последовательности. Если такая группа не найдена, выдается сообщение об ошибке. Эта опция позволяет повторно выполнить архивирование группы.

Если место назначения для архива не указано явно в командной строке, используется параметр инициализации 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'


Восстановление базы данных (команда RECOVER)

Если база данных работала в режиме 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.

Опция  Назначение 
AUTOMATIC  Автоматически генерирует имя архивного файла журнала повторного выполнения, необходимого для продолжения операции восстановления. Для этого используются значения параметров конфигурации LOG_ARCHIVE_DEST и LOG_ARCHIVE_FORMAT (или соответствующие стандартные значения). Если файл с таким именем не найден, SQL*Plus запрашивает имя файла, выводя автоматически сгенерированное в качестве подсказки. Имя запрашивается также, если не указана ни опция AUTOMATIC, ни опция LOGFILE. Если заранее известно, что архивирование выполнялось в файл с нестандартным именем, имеет смысл сразу указать опцию LOGFILE.
FROM <местонахождение>  Задает местонахождение архивных файлов журнала повторного выполнения. По умолчанию используется значение параметра инициализации LOG_ARCHIVE_DEST. Можно также задать местонахождение архивных файлов с помощью команды SQL*Plus SET LOGSOURCE.
LOGFILE  Продолжает восстановление носителей, применяя указанный файл журнала повторного выполнения. При восстановлении в интерактивном режиме (AUTORECOVERY OFF), запрашивает новое имя файла, если указанный файл журнала не найден.
TEST ALLOW <целое число> CORRUPTION  В случае повреждения файла журнала указывает, при скольких поврежденных блоках еще можно продолжать восстановление. В ходе обычного восстановления это значение не должно быть более 1.
CONTINUE  Продолжает восстановление нескольких экземпляров после прерывания для отключения восстановления одного из них.
CONTINUE DEFAULT  Продолжает восстановление, используя автоматически сгенерированное имя архивного файла журнала повторного выполнения, если оно не указано явно. Аналогично опции AUTOMATIC, но не запрашивает альтернативное имя файла, если файл не найден.
CANCEL  Прерывает восстановление, ведущееся до CANCEL (см. опцию UNTIL CANCEL).
STANDBY DATABASE  Восстанавливает резервную базу данных, используя управляющий файл и архивные файлы журнала повторного выполнения основной базы данных. Резервная база данных должна быть смонтирована, но не открыта.
DATABASE  Восстанавливает всю базу данных в целом.
UNTIL CANCEL  Задает неполное восстановление, до прерывания администратором. Необходимо указать или подтвердить автоматически сгенерированные имена файлов журналов повторного выполнения. Восстановление завершится, если указать CANCEL вместо очередного имени файла.
UNTIL TIME  Задает неполное восстановление до момента времени. Момент времени указывается в одиночных кавычках по формату 'YYYY-MM-DD:HH24:MI:SS'.
UNTIL CHANGE  Задает неполное восстановление до указанного по номеру изменения (SCN), не включая его.
USING BACKUP CONTROLFILE  Указывает, что вместо текущего управляющего файла должна использоваться его резервная копия.
TABLESPACE  Восстанавливает указанные табличные пространства текущей базы данных (до 16).
DATAFILE  Восстанавливает любое количество указанных файлов данных.
STANDBY TABLESPACE  Реконструирует потерянное или поврежденное табличное пространство на резервной базе данных, используя архивные файлы журнала повторного выполнения и управляющий файл основной базы данных.
STANDBY DATAFILE  Реконструирует потерянный или поврежденный файл данных на резервной базе данных, используя архивные файлы журнала повторного выполнения и управляющий файл основной базы данных.
UNTIL CONSISTENT WITH CONTROLFILE  Указывает, что восстановление старого табличного пространства или файла данных использует текущий управляющий файл резервной базы данных.
MANAGED STANDBY DATABASE  Задает режим устойчивого восстановления резервной базы данных. В этом режиме предполагается, что резервная база данных является активным компонентом. В таком режиме можно восстанавливать только носители.
NODELAY  Немедленно применяет отложенный архивный журнал к резервной базе данных, независимо от установки параметра DELAY в параметре инициализации LOG_ARCHIVE_DEST_n в основной базе данных.
TIMEOUT  Задает период ожидания (в минутах) для операции устойчивого восстановления. Если за это время не станет доступным архивный журнал повторного выполнения, процесс восстановления завершается с ошибкой. Если эта конструкция не указана, резервная база данных остается в состоянии ожидания восстановления, пока не будет повторно выполнена команда RECOVER с конструкцией CANCEL или пока не произойдет остановка или сбой экземпляра.
CANCEL  При управляемом восстановлении конструкция CANCEL прерывает восстановление резервной базы данных после применения текущего архивного файла повторного выполнения. Приглашение SQL*Plus снова появится после остановки процесса восстановления.
CANCEL IMMEDIATE  Прерывает управляемое восстановление резервной базы данных после применения текущего архивного файла повторного выполнения или после прочтения следующего, в зависимости от того, какое событие произойдет раньше. Приглашение SQL*Plus снова появится после остановки процесса восстановления. Команду RECOVER CANCEL IMMEDIATE нельзя выполнять из того же сеанса, из которого была выполнена команда RECOVER MANAGED STANDBY DATABASE.
CANCEL NOWAIT  Прерывает управляемое восстановление резервной базы данных после прочтения следующего файла журнала повторного выполнения и выдает приглашение SQL*Plus.
DISCONNECT FROM SESSION  Указывает, что в ходе управляемого восстановления архивные файлы повторного выполнения должны применяться отдельным фоновым процессом, не блокирующим текущий сеанс.
FINISH  Немедленно восстанавливает резервные файлы текущего журнала повторного выполнения резервной базы данных. Используется при сбое основной базы данных.
NOWAIT  Возвращает управление немедленно, не дожидаясь завершения процесса восстановления.

Для выполнения команды 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). Затем сценарий должен выдать (в указанный при вызове файл) отчет о сотрудниках с указанием средней зарплаты по отделам. Снабдите столбцы отчета заголовками на русском языке.