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

2004 г

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

Назначение SQL*Plus
Запуск SQL*Plus и выход из него
Подключение к стандартной и удаленной базе данных
Основные команды
Команды для работы с файлами
Параметры командных файлов
Связываемые переменные
Трассировка операторов
Настройка среды SQL*Plus
Команды администрирования базы данных
Дополнительные источники информации
Упражнения

Назначение SQL*Plus

Утилита SQL*Plus позволяет выполнять команды SQL и блоки PL/SQL, а также решать ряд других задач. С помощью 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	Подавляет выдачу всех информационных сообщений и приглашений SQLPlus, включая приглашение командной строки, эхо команд и начальную информацию, обычно выдаваемую при входе в SQLPlus. Используется при вызове SQLPlus из другой программы, когда его желательно скрыть, и совместно с опцией 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 обычно происходит подключение к стандартной базе данных с указанным именем пользователя и паролем. После запуска и успешной регистрации можно в любой момент подключиться к схеме другого пользователя или к другой базе данных. Для этого используется команда 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> "), после которого можно вводить команды. Команды состоят из одного или нескольких слов, разделенных произвольным количеством пробелов и/или символов табуляции. Регистр символов в командах обычно не имеет значения. При нажатии клавиши 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 не попадают в буфер, поэтому редактировать их можно только по ходу ввода с помощью клавиши 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 позволяет управлять форматом столбцов, количеством строк на странице и дополнительными пустыми строками, заголовками страниц и т.д. В следующих разделах подробно рассмотрены наиболее часто используемые команды форматирования. Их применяют, если стандартный формат представления столбцов не подходит.

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

Ширина числовых столбцов соответствует максимуму из ширины заголовка столбца, ширины, заданной с помощью опции 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 { <действие>}	Отмечает место в отчете, где SQLPlus выполнит вычисление, заданное в соответствующей команде COMPUTE*. Позволяет выдавать суммарные значения.
SKIP <количество строк>	Пропускает указанное количество строк (вставляет столько пустых строк) перед строкой, в которой происходит разрыв.
SKIP PAGE	Пропускает столько строк, сколько задано в качестве размера страницы (задается с помощью SET PAGESIZE). После последней строки данных строки не пропускаются.
NODUPLICATES	Выдает пробелы вместо значения в столбце, если оно совпадает со значением в предыдущей строке.
DUPLICATES	Выдает значение столбца в каждой строке, независимо от дублирования.

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

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

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

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

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

           KING             5000

           MILLER           1300

        20 SMITH             800

           ADAMS            1100

           FORD             3000
           SCOTT

           JONES            2975

        30 ALLEN            1600

           BLAKE            2850

           MARTIN           1250

           JAMES             950

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

        30 TURNER           1500

           WARD             1250


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

Команда COMPUTE

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Команда CLEAR

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

<команда CLEAR> ::=: BRE[AKS] | BUFF[ER] | COL[UMNS] | COMP[UTES] | SCR[EEN] | SQL | TIMI[NG]

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

Оглавление