Oracle spool как работать
SQL*Plus - программа для работы с СУБД Oracle посредством командной строки. Используя SQL*Plus можно выполнять команды SQL и PL/SQL в интерактивном режиме или используя сценарий.
Основное преимущество SLQ*Plus - доступность, т.к. инструмент доступен практически всегда, когда в системе установлен Oracle. При этом это достаточно мощный инструмент, используя который можно решать различные задачи на удаленных машинах.
К написанию этой статьи меня подтолкнула книга "Oracle SQL*Plus: The Definitive Guide, 2nd Edition", написанная Jonathan Gennick. Как обычно, появилось желание систематизировать информацию и представить ее в удобном виде.
При этом сразу считаю нужным оговориться, что я использую SQLplus для написания и выполнения скриптов на удаленных машинах, в этой статье описываю именно используемые для этого команды.
Область возможного использования SQLplus при этом гораздо шире, например - построение отчетов, в том числе в формате HTML.
Параметры, подключение к базе, запуск скриптов
Выполнить несколько строк кода (не передавая отдельный файл со скриптом), unix:
Выполнение SQL запросов
Запрос может состоять из нескольких строк, содержать комментарии, но не может содержать внутри пустые строки.
Запрос может быть выполнен тремя способами:
- точка с запятой в конце запроса
- строка с слешем "/" после запроса
- пустая строка после запроса (будет помещен в буфер, но не выполнен немедленно)
Выполнение PL/SQL блоков
Пример PL/SQL блока:
Правила выполнения PL/SQL блоков:
- Первое слово в PL/SQL блоке должно быть из списка: BEGIN, DECLARE, CREATE PROCEDURE,
CREATE FUNCTION, CREATE TRIGGER, CREATE PACKAGE, CREATE TYPE, CREATE TYPE BODY. Регистр не важен. - Блок может состоять из нескольких строк
- Можно вставлять /* комментарии */, они также могут быть на несколько строк
- Пустые строки не разрешены внутри блока
Сигнал к выполнению блока может быть подан двумя путями:
- Строка, содержащая только слеш "/" после блока - выполнить сразу
- Строка, содержащая точку "." после блока - поместить в буфер. Содержимое буфера может быть выведено командой LIST и выполнено, путем указания одиночного слеша "/" или команды RUN.
Одиночное выражение PL/SQL может быть выполнено, используя:
В случае ошибок, при выполнении процедуры, можно отобразить их выполнив команду:
Работа с переменными
Переменные могут быть заданы двумя способами:
Если уже была определена &&variable, то значение будет подставлено во все дальнейшие переменные как &variable так и &&variable.
Если была определена &&variable, и скрипт запущен повтороно в ходе той же сессии работы с SQLplus - будет использовано старое значение переменной. Чтобы этого избежать - можно запрашивать интерактивный ввод в скрипте принудительно, испольтзуя команду:
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.
Запомнить настройки перед выполнением скрипта и вернуть обратно по завершении выполнения:
Работа с внешними файлами из Oracle SQL*Plus, команды GET, START, символы @ и @@, команды SAVE и SPOOL
В принципе, можно и не вызывать текстовый редактор операционной системы из SQL*Plus , а заранее написать код во внешнем редакторе и сохранить его в файле операционной системы. SQL*Plus позволяет работать с такими файлами: считывать их в буфер, запускать на выполнение, сохранять набранные в SQL*Plus команды в файле, а также записывать в файл результаты выполнения запросов.
Отметим, что во всех случаях SQL*Plus подразумевает, что для скрипта используется расширение sql . Если используется именно это расширение, его можно опускать. Если же расширение другое, потребуется указать имя файла вместе с расширением.
Для того, чтобы просмотреть из SQL*Plus код, который находится в файле на диске, можно использовать команду GET , например:
GET C :\ OracleScript . sql
Информация из файла C :\ OracleScript . sql будет загружена в буфер SQL*Plus (и выведена на экран).
Запустить на выполнение код из внешнего файла можно при помощи команды START . Можно также просто дописать символ @ к имени файла:
START C :\ OracleScript . sql
@ C :\ OracleScript . sql
Для запуска внешнего файла можно также использовать формат @@имя_файла. Отличие между применением одного символа At (@) и двумя символами At (@@) — в их поведении, когда полный путь к файлу скрипта не указан. Обычно при использовании двух символов @@ в ситуации, когда этой командой запускается один скрипт из другого скрипта, поиск второго скрипта будет производиться SQL*Plus в том же каталоге, в котором находится первый скрипт. Если же использовать один символ @, то поиск будет производиться в каталоге скриптов по умолчанию (зависит от операционной системы и установленных экземпляров Oracle ).
Сохранить содержимое буфера SQL*Plus в файле можно при помощи команды SAVE :
Для команды SAVE можно указать дополнительные параметры:
- CREATE (по умолчанию) — создать новый файл;
- REP ( REPLACE ) — заменить имеющийся на диске файл новым (если такого файла еще нет, он будет просто создан);
- APP ( APPEND ) — дописать содержимое буфера SQL*Plus к файлу на диске, например:
SAVE c:\MyScript.sql APP
Часто возникает необходимость записать результаты выполнения запросов SQL*Plus в файл. Для этой цели используется команда SPOOL :
SPOOL C :\ ScriptResults . rpt
select * from emp
Для отключения вывода информации в файл используется команда SPOOL OFF .
Отметим некоторые моменты, связанные с перенаправлением вывода SPLPlus в файл:
· если расширение для файла не указано, по умолчанию SQL*Plus добавит расширение lst ;
· перенаправлять вывод можно не только в файл, но и на служебные устройства операционной системы (принтер, NULL ) и т.п.;
· при выводе информации в файл производится буферизация вывода. Поэтому не удивляйтесь, если непосредственно после выполнения запроса обнаружится, что его результаты в файл не добавились. Они будут добавлены полностью только после выполнения команды SPOOL OFF , закрытия SQL*Plus или других действий по очистке кэша результатов;
· команду SPOOL можно использовать для формирования результатов выполнения запросов в формате HTML ;
· отключить/включить вывод результатов на экран SQL*Plus можно при помощи команд SET TERMOUT ON / SET TERMOUT OFF .
Отметим, что запуск внешнего скрипта на выполнение и запись результатов во внешний файл можно осуществить и при помощи параметров командной строки SQL*Plus (см. раздел 1.2.2).
SQL * Plus , инструмент командной строки базы данных Oracle , предоставляет команду SPOOL для «сохранения результатов запроса в файле». На следующем снимке экрана показан SPOOL, используемый в SQL * Plus для буферизации списка пользовательских таблиц в файл с именем C:\pdf\output.txt .
И выполненный запрос, и результаты запроса были output.txt в файл output.txt как показано в следующем листинге этого файла.
SPOOL вывод файла Oracle * Plus в SPOOL SPOOL
Инструмент командной строки PostgreSQL , psql , обеспечивает функциональность, аналогичную SPOOL в SQL * Plus, с мета-командой \ o ( \ out ). Следующий снимок экрана показывает это в действии в psql .
Только сгенерированные в psql результаты запроса содержатся в сгенерированном файле output.txt . Сам запрос, даже более длинный запрос, созданный с помощью \set ECHO_HIDDEN on , не содержится в выводе.
Основным недостатком использования \qecho является то, что он должен использоваться перед \qecho каждого оператора в выходной файл.
Переменная psql ECHO может быть настроена на queries чтобы «все команды SQL отправлялись на сервер [отправлялись] также на стандартный вывод». Это продемонстрировано на следующем снимке экрана.
К сожалению, хотя установка переменной psql ECHO для queries приводит к выводу запроса вместе с результатами в окне psql , запрос не записывается в файл с помощью \o . Вместо этого, когда \o используется с ECHO установленным на queries , сам запрос снова распечатывается в окне, а результаты записываются только в указанный файл. Это связано с тем, что, как указано в документации (я добавил акцент ), \o записывает « вывод запроса… в стандартный вывод». Это продемонстрировано на следующем снимке экрана.
Я не смог найти способ легко использовать команду метаданных \o и записать запрос и результаты в файл, не используя \qecho . Тем не менее, другой подход, который не требует \qecho состоит в том, чтобы запустить не пытаться \qecho вывод файла изнутри psql интерактивном режиме, а вместо этого выполнить внешний входной файл сценария SQL.
Для работы в SQL*Plus, как для выполнения простых команд, так и для разработки сценариев по сбору информации из базы данных, необходимо уметь пользоваться командами SQL*Plus двух основных видов. К первому относятся команды, которые на самом деле что-то делают и потому могут называться рабочими (как, например, RECOVER , которая выполняет восстановление базы данных), а ко второму — команды, которые позволяют получать удобный для прочтения вывод от запросов и потому называются командами форматирования.
В настоящей статье вы ознакомитесь с важнейшими представителями обоих этих видов команд. В частности, в этом разделе рассматриваются команды, которые что-то делают, а в следующем — те, что позволяют форматировать вывод и получать отчеты.
Команда SQLPROMPT
Администратору баз данных обычно приходится иметь дело с несколькими базами данных. Из-за этого при выполнении множества задач на протяжении дня очень легко забыть, к какой базе данных подключен тот или иной сеанс SQL*Plus. Поэтому во избежание допущения грубых ошибок (вроде удаления производственных таблиц вместо разрабатываемых или тестируемых), следует всегда настраивать среду так, чтобы имя экземпляра базы данных постоянно отображалось в приглашении, напоминая о том, с какой базой данных происходит взаимодействие.
Для настройки приглашения SQL*Plus так, чтобы в нем отображалось имя базы данных, служит приведенная ниже команда, в которой используется специальная предопределенная переменная _CONNECT_IDENTIFIER (предопределенные переменные подробно рассматриваются в разделе “Предопределенные переменные SQL*Plus” далее в главе):
Обратите внимание, что команда SET приводит к немедленному изменению приглашения в интерфейсе SQL*Plus. После выдачи этой команды приглашение приобретает более значимый вид, ясно указывающий на то, с какой базой данных происходит взаимодействие, и избавляет от необходимости делать по этому поводу какие-либо предположения. В данном примере оно указывает на то, что в текущий момент подключение установлено с базой данных nick .
Для настройки приглашения SQL*Plus можно также использовать и другие специальные предопределенные переменные. Например, с помощью переменной _USER в приглашении отображается имя пользователя:
Применив одновременно переменные _USER и _CONNECT_IDENTIFIER , можно сделать так, чтобы в приглашении отображалось и имя базы данных, и имя текущего пользователя:
Используя переменные _USER и _PRIVILEGE , в приглашении можно отображать не только имя текущего пользователя, но и привилегии, которыми он обладает:
С помощью переменных _USER , _DATE и _CONNECT_IDENTIFIER в приглашении можно отобразить имя пользователя, текущую дату и имя базы данных:
При желании строку, вроде показанной выше, легко добавить в файл login.sql ,чтобы желаемые значения устанавливались автоматически при каждом входе в систему, и их не приходилось настраивать вручную.
Команда DESCRIBE
Команда DESCRIBE описывает или выводит список столбцов таблицы вместе с их спецификациями. Кроме того, она позволяет получать описание процедур или пакетов Oracle. Эта команда чрезвычайно полезна при выполнении рутинных обязанностей администратора баз данных. Если, например, нет уверенности в том, какой столбец нужно выбрать в определенной таблице, но точно известно, к какой именно таблице следует выполнить запрос, с помощью команды DESCRIBE можно получить список всех столбцов, которые имеются в данной таблице. Поскольку команда DESCRIBE может применяться даже в отношении метаданных (словаря данных), с ее помощью можно также очень легко знакомиться с информацией о таблицах и столбцах и их применении, которая является критически важной для работы с базой данных.
В листинге ниже показано, как команда DESCRIBE позволяет отображать столбцы и типы столбцов таблицы.
Команда HOST
Команда HOST позволяет выполнять в SQL*Plus команды операционной системы.Например, может возникнуть необходимость посмотреть, существует ли некий файл в определенном каталоге, или выдать команды cp или tar на уровне UNIX , а затем вернуться в сеанс SQL*Plus и возобновить взаимодействие с базой данных Oracle.
Ниже приведен пример применения команды HOST :
В этом примере команда HOST помогает скопировать файл new.sql из указанного каталога в каталог tmp .
С помощью команды HOST можно выполнять практически все те же команды, которые доступны на уровне операционной системы. Слово HOST можно заменять восклицательным знаком (!):
На заметку! В случае ввода команды HOST без параметров вы попадаете в каталог операционной системы, из которого изначально запускали сеанс SQL*Plus.
По завершении работы с операционной системой достаточно ввести в командной строке exit и на экране снова появится приглашение покинутого ранее сеанса SQL*Plus.
Команда SPOOL
Команда SPOOL позволяет сохранять вывод одного и более SQL-операторов в файлах операционной системы, как в UNIX, так и в Windows:
По умолчанию создаваемые этой командой буферные (spooled) текстовые файлы сохраняются как имя_файла.lst . Хотя по умолчанию принято сохранять вывод в файле,его также можно отправлять и на принтер. Буферизация файлов является очень полезным приемом при использовании SQL для оказания помощи в написании SQL-сценариев, и некоторые примеры его применения можно найти в приложении.
С помощью команды SPOOL можно добавлять данные в существующий буферный файл, а также полностью заменять его (по умолчанию происходит замена). Ниже приведен весь синтаксис этой команды:
Ниже описаны параметры команды SPOOL .
У команды SPOOL имеет множество способов применения. Например, она легко экспортируется для перехвата результатов оператора SELECT . Перед этим, однако, должен обязательно задаваться формат вывода путем определения значений для переменных HEADING, FEEDBACK и LINESIZE . Ниже приведен пример:
В этом примере файл employees.txt служит для перехвата всех данных из таблицы HR.EMPLOYEES . Далее его можно будет использовать для загрузки данных в другую таблицу с помощью утилиты SQL*Loader.
Команды ACCEPT и PROMPT
Команда ACCEPT применяется для считывания вводимых пользователем данных с экрана и сохранения их в какой-то переменной. Эту переменную можно либо указать самостоятельно, либо позволить создавать ее SQL*Plus. Обычно команда ACCEPT применяется для считывания данных, вводимых пользователем в ответ на приглашения в интерфейсе SQL*Plus.
Команды ACCEPT и PROMPT в SQL-сценарии обычно используются вместе и служат,как правило, для запроса у пользователя входных данных и сохранения этих данных в переменных для дальнейшей работы с ними в программе. Ниже приведен пример, иллюстрирующий использование этих команд:
Команда EXECUTE
При использовании сценариев, которые вызывают PL/SQL-код в форме процедур и пакетов, для фактического запуска отдельных процедур в пакете необходимо применять команду EXECUTE . Ниже приведен пример:
Обратите внимание, что для выполнения процедуры или функции можно специфицировать как ключевое слово EXECUTE , так и ключевое слово EXEC .
Команда PAUSE
Часто бывает так, что сценарии при выполнении генерируют вывод, который не умещается на экране. Этот вывод быстро пролетает перед глазами на экране и исчезает до того, как его удается прочитать. Разумеется, можно воспользоваться командой SPOOL для перехвата всего вывода целиком, но делать так постоянно не выгодно, поскольку тогда придется тратить на создание и прочтение файлов с выводом сценариев целый день. Поэтому лучше использовать другую предлагаемую в SQL*Plus команду, а именно — PAUSE , которая позволяет приостанавливаться после отображения каждого целого экрана вывода. Переход к следующему экрану вывода осуществляется просто нажатием клавиши <Enter>.
Приведенный ниже пример демонстрирует использование команды PAUSE для замедления отображения вывода на экране терминала:
После настройки команды PAUSE вывод больше не будет быстро проскакивать на экране при каждом выполнении SQL-команды. Вместо этого SQL*Plus будет отображать один экран вывода, и ожидать нажатия клавиши <Enter>. При запуске запросов с командой PAUSE для просмотра первого экрана вывода тоже необходимо нажимать клавишу <Enter>.
Читайте также: