Что такое SQL*Plus? SQL*Plus — это инструмент (командная строка) для выполнения запросов к базе данных Oracle и получения результатов этих запросов.
Как открыть SQL*Plus?
SQL*Plus открываем так: в командной строке («Выполнить» в Windows) указываем sqlplus, имя пользователя и пароль. В моём случае это так:
Жмём кнопку «OK» и получаем:
SQL*Plus готов к работе.
Как закрыть SQL*Plus?
Закрыть SQL*Plus можно командами «quit» или «exit»:
Как создать новую таблицу?
Создать новую таблицу Item можно так:
Введём этот код в SQL*Plus, получаем:
здесь 2 и 3 — номера строк, они появляются, если строка не завершена точкой с запятой и пользователь жмёт на кнопку Enter. Ставим точку с запятой в конце нашего кода, жмём Enter, получаем сообщение «Table created»:
жмём Enter, получаем сообщение «Table created». Для Linux вместо @ надо поставить START.
К написанию этой статьи меня подтолкнула книга «Oracle SQL*Plus: The Definitive Guide, 2nd Edition», написанная Jonathan Gennick. Как обычно, появилось желание систематизировать информацию и представить ее в удобном виде.
При этом сразу считаю нужным оговориться, что я использую SQLplus для написания и выполнения скриптов на удаленных машинах, в этой статье описываю именно используемые для этого команды.
Параметры, подключение к базе, запуск скриптов
Выполнить несколько строк кода (не передавая отдельный файл со скриптом), unix:
Выполнение SQL запросов
Запрос может состоять из нескольких строк, содержать комментарии, но не может содержать внутри пустые строки.
Запрос может быть выполнен тремя способами:
Выполнение PL/SQL блоков
Пример PL/SQL блока:
Правила выполнения PL/SQL блоков:
Сигнал к выполнению блока может быть подан двумя путями:
Одиночное выражение PL/SQL может быть выполнено, используя:
В случае ошибок, при выполнении процедуры, можно отобразить их выполнив команду:
Работа с переменными
Переменные могут быть заданы двумя способами:
Если уже была определена &&variable, то значение будет подставлено во все дальнейшие переменные как &variable так и &&variable.
ACCEPT можно использовать для валидации:
Для ввода дат в определенном формате:
SQL*Plus поддерживает четыре типа переменных: CHAR, NUMBER, BINARY_FLOAT, and BINARY_DOUBLE. При вводе с клавиатуры переменная будет типа CHAR.
Несмотря на это, можно использовать NEW_VALUE, чтобы задать числовую переменную, полученную как результат запроса.
Bind-переменные могут использоваться для передачи данных между PL/SQL и SQL блоками:
Присвоить bind-переменной значение &-переменной:
Вывести значение bind-переменной:
Присвоить &-переменной значение bind-переменной:
Получаем OUT-параметр процедуры в bind-переменную:
Условное выполнение в SQLplus:
Пример скрипта, принимающего несколько переменных на вход в формате c возможностью задать дефолтные значения:
Настройки выполнения скриптов
Действуют на протяжении сессии в SQLplus.
Запомнить настройки перед выполнением скрипта и вернуть обратно по завершении выполнения:
Другие команды
Взаимодействие с unix shell
Обработать результат выполнения SQLplus-скрипта в Unix:
Вернуть код ответа в Unix:
Вывод из скрипты в переменную командной строки unix:
Утилита SQL*Plus. Создание и выполнение сценариев.Часть 1
Утилита SQL*Plus позволяет выполнять команды SQL и блоки PL/SQL, а также решать ряд других задач. С помощью SQL*Plus можно:
При работе с SQL*Plus используются следующие базовые понятия:
Команда
Команда SQL*Plus или оператор SQL Oracle.
Блок PL/SQL
Группа взаимосвязанных операторов PL/SQL, оформленная в виде анонимного блока.
Таблица
Базовая единица хранения данных в Oracle.
Запрос
SQL-оператор SELECT, выбирающий информацию из одной или нескольких таблиц.
Результаты запроса
Данные, возвращенные запросом.
Отчет
Результаты запроса, сформатированные с помощью команд SQL*Plus.
Метасимвол ::= означает «по определению есть». Метасимвол / обозначает, что данная формула является сокращенной записью нескольких формул с одинаковыми левыми частями и различными цепочками в правой части (т.е. возможен один из разделенных этим символом вариантов). Пара метасимволов [ ] используется для обозначения того, что находящаяся между ними цепочка может содержаться, а может и не содержаться в правой части формулы. Пара метасимволов < >означает, что содержащаяся между ними цепочка может повторяться ноль или более раз. Приоритет метасимвола / выше, чем у пар [ ] и < >.
Общий синтаксис вызова SQL*Plus имеет вид:
Как следует из синтаксиса, при вызове SQL*Plus можно указать ряд опций. Они кратко описаны в табл. 1.
Выдает информацию об использовании и синтаксисе вызова команды sqlplus и завершает работу.
VERSION
Выдает информацию о версии утилиты SQL*Plus и завершает работу.
MARKUP
Опция MARKUP позволяет генерировать полную HTML-страницу по результатам запроса или сценария.
RESTRICT
Позволяет отключить ряд команд, взаимодействующих с операционной системой. Если указан уровень ограничения 3, сценарий login.sql в текущем каталоге не читается. Стандартный сценарий запуска glogin.sql читается, но ограниченные команды в нем не работают. Команды, недопустимые на каждом из уровней ограничения, представлены ниже в табл. 2.
SILENT
Подавляет выдачу всех информационных сообщений и приглашений SQL*Plus, включая приглашение командной строки, эхо команд и начальную информацию, обычно выдаваемую при входе в SQL*Plus. Используется при вызове SQL*Plus из другой программы, когда его желательно скрыть, и совместно с опцией MARKUP при создании Web- отчетов.
Таблица 2. Команды, отключаемые уровнями ограничения.
По умолчанию при выполнении команды EXIT SQL*Plus фиксирует текущую транзакцию и завершает работу с кодом возврата 0 ( SUCCESS ).
Стандартная база данных конфигурируется на уровне операционной системы, путем установки переменной среды или редактирования файла конфигурации Oracle.
Команда CONNECT имеет следующий синтаксис:
Информация о пользователе и базе данных, к которым необходимо подключиться, вводится в том же формате, что и при запуске (см. синтаксис вызова утилиты SQL*Plus выше). При подключении, как и при запуске, можно указать роль пользователя, от имени которого оно выполняется.
Если пароль пользователя устарел, при подключении придется ввести новый пароль. Если же учетная запись заблокирована, то выдается сообщение об этом и подключение не происходит, пока администратор не разблокирует учетную запись.
К удаленной базе данных можно подключиться двумя способами:
Пусть в файле tnsnames.ora имеется следующая запись для сетевой службы Oracle:
Тогда для подключения к соответствующей базе данных при запуске SQL*Plus необходимо ввести, например, следующую команду в окне командной строки операционной системы:
Для подключения к соответствующей базе данных из SQL*Plus необходимо выполнить команду вида:
После запуска утилита SQL*Plus выдает приглашение командной строки (обычно, это строка » SQL> «), после которого можно вводить команды. Команды состоят из одного или нескольких слов, разделенных произвольным количеством пробелов и/или символов табуляции. Регистр символов в командах обычно не имеет значения. При нажатии клавиши Enter команда (при выполнении определенных условий по ее завершению) выполняется. После выполнения команды снова выдается приглашение.
Некоторые команды, например, блоки PL/SQL, могут занимать несколько строк. Тогда SQL*Plus выдает дополнительные приглашения с номером строки, например, » 2> «.
В командной строке SQL*Plus можно вводить команды трех видов:
От вида команды зависит способ продолжения ее на следующей строке, завершения и выполнения.
Для повторного выполнения содержимого буфера SQL используется команда RUN или косая черта ( / ). Команда RUN выдает содержимое буфера в стандартный выходной поток, а затем выполняет. Косая черта вызывает просто выполнение команды из буфера.
Оператор SQL можно завершить тремя способами:
Точка с запятой в конце строки означает выполнение оператора. Утилита SQL*Plus выполняет оператор и запоминает его в буфере SQL. Этот символ является признаком завершения команды и после него нельзя вводить комментарий.
Косая черта в качестве первого и единственного символа очередной строки означает, что оператор надо выполнить. Утилита SQL*Plus выполняет оператор и запоминает его в буфере SQL.
Пустая строка в операторе SQL или сценарии SQL*Plus обычно означает, что ввод команды завершен, но выполнять ее пока не надо (это поведение можно изменить с помощью команды SQL*Plus SET SQLBLANKLINES ). Команда помещается в буфер SQL и остается там, пока не будет введена другая команда.
Для ввода блоков PL/SQL необходимо работать в режиме PL/SQL. Утилита SQL*Plus переходит в это режим, если:
Команды SQL*Plus выполняются сразу и в буфер SQL не попадают. Завершать команды SQL*Plus точкой с запятой можно, но не обязательно. Большинство команд SQL*Plus можно сокращать до одной или нескольких первых букв, достаточных для однозначного определения команды.
::= TIMI [ NG ] ::= START [ ] / SHOW / STOP
Назначение команд таймера описано в табл. 3.
Таблица 3. Команды таймера SQL*Plus.
Команда
Назначение
START
Запускает таймер и дает ему указанное имя. Можно использовать несколько активных таймеров, запуская дополнительные с помощью команды START прежде, чем останавливать исходный. Последний запущенный таймер становится текущим.
SHOW
Выдает имя и данные (время работы) текущего таймера.
STOP
Выдает имя и данные (время работы) текущего таймера, а затем останавливает и удаляет таймер. Если активно несколько таймеров, следующий, предпоследний по времени запуска, становится текущим.
Команда TIMING без параметров выдает количество активных таймеров.
Рассмотрим пример использования команды TIMING:
Для удаления всех таймеров используется команда CLEAR TIMING.
Доступ к команде HOST можно запретить, запуская утилиту SQL*Plus с любым уровнем ограничения.
Утилита SQL*Plus позволяет получить справочную информацию по своим командам, а также основным объектам схем.
Команда HELP INDEX выдает список всех команд SQL*Plus. Команда HELP TOPICS выдает список команд вместе с однострочным описанием назначения.
Команда DESCRIBE позволяет получить описание таблицы или представления, а также спецификацию типа, пакета, функции или процедуры:
Описание таблицы, представления или синонима содержит следующую информацию:
Описание функций и процедур содержит следующую информацию:
Утилита SQL*Plus поддерживает несколько команд редактирования текста в буфере. Они представлены в табл. 4.
Таблица 4. Команды редактирования SQL*Plus.
Результаты запросов в SQL*Plus можно дополнительно сформатировать. Утилита SQL*Plus позволяет управлять форматом столбцов, количеством строк на странице и дополнительными пустыми строками, заголовками страниц и т.д. В следующих разделах подробно рассмотрены наиболее часто используемые команды форматирования. Их применяют, если стандартный формат представления столбцов не подходит.
Для столбцов других типов ширина столбца соответствует его ширине в базе данных. Все эти столбцы по умолчанию выравниваются влево. Для столбцов типа DATE формат определяется соответствующими NLS-параметрами. Если они не заданы, предполагается формат A9 (см. табл. 6).
Команда 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.
Таблица 6. Основные элементы формата опции FORMAT.
Рассмотрим простой пример использования команды COLUMN для форматирования значений и заголовков столбцов:
::= BRE [ AK ] < ON < >> ::= / / ROW / REPORT ::= SKI [ P ] [ ] / SKI [ P ] PAGE [ ] ::= NODUP [ LICATES ] / DUP [ LICATES ]
Таблица 7. Основные варианты вызова команды BREAK.
Команда COMPUTE позволяет вычислять и выдавать итоговые значения. При вызове без параметров выдает все заданные вычисления. Команда COMPUTE имеет следующий синтаксис:
::= COMP [ UTE ] < [ LAB [ EL ] ]> OF < > ON < >::= / REPORT / ROW
Таблица 8. Функции в команде COMPUTE.
Функция
Назначение
Допустимые типы данных
AVG
Среднее среди непустых значений
Числовые
COU [ NT ]
Количество непустых значений
Все
MIN [ IMUM ]
Минимальное значение
Числовые и строковые
MAX [ IMUM ]
Максимальное значение
Числовые и строковые
NUM [ BER ]
Количество строк
Все
SUM
Сумма непустых значений
Числовые
STD
Среднеквадратичное отклонение непустых значений
Числовые
VAR [ IANCE ]
Дисперсия непустых значений
Числовые
Назначение основных конструкций команды COMPUTE описано в табл. 9.
Таблица 9. Основные конструкции команды COMPUTE.
В конструкции ON можно сослаться на выражение в списке выбора, взяв его в двойные кавычки. Имя или псевдоним столбца в кавычки брать не надо.
Рассмотрим простой пример вычисления итоговых значений и редактирования SQL-операторов в SQL*Plus:
Команда CLEAR позволяет сбросить значение ряда опций утилиты SQL*Plus, в частности, связанных с форматированием результатов. Она имеет следующий синтаксис:
::= BRE [ AKS ] / BUFF [ ER ] / COL [ UMNS ] / COMP [ UTES ] / SCR [ EEN ] / SQL / TIMI [ NG ]
Назначение опций команды CLEAR представлено в табл. 10.
Таблица 10. Опции команды CLEAR.
Утилита SQL*Plus позволяет задавать заголовок для показа в качестве верхнего (команда TTITLE ) и нижнего (команда BTITLE ) колонтитула на каждой странице отчета. Эти команды имеют следующий синтаксис:
::= BTI [ TLE ] [ < >] [ ] ::= TTI [ TLE ] [ < >] [ ] ::= < >::= COL / S [ KIP ] [ ] / TAB / LE [ FT ] / CE [ NTER ] / R [ IGHT ] / BOLD / FORMAT ::= / SQL.LNO / SQL.PNO / SQL.RELEASE / SQL.SQLCODE / SQL.USER
Конструкция ( ON / OFF ) позволяет включать и отключать вывод колонтитулов, не влияя на их определения.
Назначение спецификаций печати команд BTITLE и TTITLE представлено в табл. 11.
Таблица 11. Спецификации печати в командах BTITLE, TTITLE, REPHEADER и REPFOOTER.
При вызове без параметров эти команды выдают текущий формат и признак вывода соответствующего колонтитула.
Рассмотрим простой пример задания верхнего колонтитула для отчета:
Команды REPHEADER и REPFOOTER позволяют задать текст, выдаваемый в начале и в конце каждого отчета, соответственно. При этом используются те же спецификации печати, что и в командах BTITLE и TTITLE (см. табл. 11 выше). Команды имеют следующий синтаксис:
При вызове без параметров эти команды выдают текущий формат и признак вывода текста в начале и в конце отчета.
Команда COPY имеет следующий синтаксис:
Если в конструкции пароль не указан, SQL*Plus запросит его перед выполнением команды.
Действия копирования описаны в табл. 12.
Таблица 12. Действия копирования команды COPY.
Действие
Описание
APPEND
Вставляет возвращенные запросом строки в целевую таблицу, если она существует. Если же таблица не существует, она создается.
CREATE
Вставляет возвращенные запросом строки в целевую таблицу, предварительно создавая ее. Если же таблица уже существует, возвращается сообщение об ошибке.
INSERT
Вставляет возвращенные запросом строки в целевую таблицу. Если таблица не существует, возвращается сообщение об ошибке. В список выбора запроса должно входить по одному столбцу для каждого столбца целевой таблицы.
REPLACE
Заменяет целевую таблицу и ее содержимое строками, возвращенными запросом. Если целевая таблица не существует, она создается. В противном случае, существующая таблица удаляется и вместо нее создается новая, содержащая скопированные данные.
Копировать данные можно не во все столбцы (тогда они должны быть совместимы по количеству и типам со списком выбора запроса), а только в указанные по именам. Если имя столбца содержит символы нижнего регистра или пробелы, его необходимо брать в двойные кавычки. Если список столбцов не задан, а целевую таблицу необходимо создавать, то их имена и типы будут такими же, как и в исходных таблицах.
Национальная библиотека им. Н. Э. Баумана Bauman National Library
Персональные инструменты
SQL*Plus
SQL*Plus
Создатели:
Саймон Уотт
Выпущена:
June 2005 ; 16 years ago ( 2005-06 )
Состояние разработки:
Активна
Веб-сайт
Документация
Содержание
История
Предшественником SQL * Plus является UFI («дружественный интерфейс»). База данных Oracle включала в себя UFI вплоть до 4 версии. Позже были добавлены новые возможности и название UFI изменили на SQL*Plus. Интерфейс UFI был чрезвычайно примитивным с точки зрения современного пользователя, если оператор был введен неправильно, UFI выдавал ошибку и откатывал всю транзакцию. [Источник 2]
Назначение
Утилита SQL*Plus позволяет выполнять команды SQL и блоки PL/SQL, а также решать ряд других задач. [Источник 3] С помощью SQL*Plus можно:
Базовые понятия
При работе с SQL*Plus используются следующие базовые понятия [Источник 3] :
Команда
Команда SQL*Plus или оператор SQL Oracle
Блок PL/SQL
Группа взаимосвязанных операторов PL/SQL, оформленная в виде анонимного блока
Таблица
Базовая единица хранения данных в Oracle
Запрос
SQL-оператор SELECT, выбирающий информацию из одной или нескольких таблиц
Результаты запроса
Данные, возвращенные запросом
Отчет
Результаты запроса, сформатированные с помощью команд SQL*Plus
Опции командной строки
Как следует из синтаксиса, при вызове SQL*Plus можно указать ряд опций. [Источник 4]
Опция
Назначение
HELP
Выдает информацию об использовании и синтаксисе вызова команды sqlplus и завершает работу
VERSION
Выдает информацию о версии утилиты SQL*Plus и завершает работу
MARKUP
Опция MARKUP позволяет генерировать полную HTML-страницу по результатам запроса или сценария
RESTRICT
Позволяет отключить ряд команд, взаимодействующих с операционной системой. Если указан уровень ограничения 3, сценарий login.sql в текущем каталоге не читается. Стандартный сценарий запуска glogin.sql читается, но ограниченные команды в нем не работают
SILENT
Подавляет выдачу всех информационных сообщений и приглашений SQL*Plus, включая приглашение командной строки, эхо команд и начальную информацию, обычно выдаваемую при входе в SQL*Plus. Используется при вызове SQL*Plus из другой программы, когда его желательно скрыть, и совместно с опцией MARKUP при создании Web- отчетов.
Типы команд
После запуска утилита SQL*Plus выдает приглашение командной строки (обычно, это строка «SQL> «), после которого можно вводить команды. Команды состоят из одного или нескольких слов, разделенных произвольным количеством пробелов и/или символов табуляции. Регистр символов в командах обычно не имеет значения. При нажатии клавиши Enter команда (при выполнении определенных условий по ее завершению) выполняется. После выполнения команды снова выдается приглашение.
Некоторые команды, например, блоки PL/SQL, могут занимать несколько строк. Тогда SQL*Plus выдает дополнительные приглашения с номером строки, например, «2> «.
В командной строке SQL*Plus можно вводить команды трех видов [Источник 5] :
Сценарии могут включать в себя все эти компоненты.
Выполнение команд
Буфер SQL
Последнюю введенную команду (SQL-оператор или блок PL/SQL) SQL*Plus хранит в области, которая называется буфер SQL. Оператор или блок остается в этом буфере пока не будет введен новый. В буфер SQL не попадает точка с запятой или косая, завершающая оператор или блок, соответственно. Содержимое буфера SQL можно редактировать и выполнять повторно.
Для повторного выполнения содержимого буфера SQL используется команда RUN или косая черта (/). Команда RUN выдает содержимое буфера в стандартный выходной поток, а затем выполняет. Косая черта вызывает просто выполнение команды из буфера. [Источник 5]
Выполнение операторов SQL
Оператор SQL можно завершить тремя способами:
Точка с запятой в конце строки означает выполнение оператора. Утилита SQL*Plus выполняет оператор и запоминает его в буфере SQL. Этот символ является признаком завершения команды и после него нельзя вводить комментарий.
Косая черта в качестве первого и единственного символа очередной строки означает, что оператор надо выполнить. Утилита SQL*Plus выполняет оператор и запоминает его в буфере SQL.
Пустая строка в операторе SQL или сценарии SQL*Plus обычно означает, что ввод команды завершен, но выполнять ее пока не надо (это поведение можно изменить с помощью команды SQL*Plus SET SQLBLANKLINES ). Команда помещается в буфер SQL и остается там, пока не будет введена другая команда.
Выполнение команд SQL*Plus
Команды SQL*Plus выполняются сразу и в буфер SQL не попадают. Завершать команды SQL*Plus точкой с запятой можно, но не обязательно. Большинство команд SQL*Plus можно сокращать до одной или нескольких первых букв, достаточных для однозначного определения команды.
Если необходимо ввести длинную команду SQL*Plus, ее можно перенести на следующую строку. Для этого в конце строки необходимо ввести дефис (-) и только затем нажать клавишу Enter. Будет выдано приглашение, и ввод команды можно будет продолжить.
Прекращение команды по ходу работы
Для прекращения долго выполняющейся команды по ходу работы, необходимо ввести символ прерывания, обычно, Ctrl+C. При этом SQL*Plus прекратит выдачу результатов команды и выдаст приглашение.
Архитектура командной строки SQL * Plus и Windows GUI
Компоненты Oracle Database Net обеспечивают связь между клиентом SQL * Plus и базой данных Oracle. Два уровня могут быть на одной машине.
Предварительные условия для SQL * Plus
Прежде чем вы сможете запустить SQL * Plus или iSQL * Plus необходимо [Источник 6] :
Запуск командной строки SQL * Plus
Пример использования идентификатора Easy Connection для подключения к схеме HR в базе данных MYDB, работающей на mymachine [Источник 7] : sqlplus hr@//mymachine.mydomain:port/MYDB
Пример использования сетевого имени службы:
Если вы хотите использовать сетевые имена служб, настроенные в локальном файле Oracle Net tnsnames.ora, то установите переменную среды TNS_ADMIN в каталог, содержащий файл tnsnames.ora. Например, в UNIX, если ваш файл tnsnames.ora находится в / home / user1 и он определяет имя сетевого сервиса MYDB2:
Запуск SQL * Plus и подключение к базе данных по умолчанию
Вам будет предложено ввести пароль.
Теперь можно вводить и выполнять операторы и команды SQL, PL / SQL и SQL * Plus в командной строке SQL>.
Запуск SQL * Plus и подключение к базе данных, отличной от стандартной
Откройте терминал UNIX или Windows и введите команду SQL * Plus:
Вам будет предложено ввести пароль. [Источник 7] Теперь можно вводить и выполнять операторы и команды SQL, PL / SQL и SQL * Plus в командной строке SQL>.
Запуск SQL * Plus Windows GUI
Для запуска SQL * Plus Windows GUI и подключения к базе данных [Источник 8] :
Теперь можно вводить и выполнять операторы и команды SQL, PL / SQL и SQL * Plus в командной строке SQL>.
Выход из SQL * Plus
Рекомендуется всегда использовать значок Logout, чтобы выйти из iSQL*Plus и освободить системные и серверные ресурсы.
Для выхода из графического интерфейса Windows введите EXIT или выберите Exit в меню File.
В iSQL*Plus команда EXIT или QUIT останавливает работающий в данный момент скрипт, но не прерывает сеанс. [Источник 9]
