Prompt oracle что это
Обновлено: 05.07.2024
Утилита SQL*Plus позволяет создавать командные файлы, запрашивающие у пользователя параметры и подставляющие их значение в команды по ходу выполнения. Для этого используются т.н. пользовательские переменные . Такие переменные определяются в командном файле, в частности, с помощью команды DEFINE . На пользовательские переменные можно ссылаться в командах, предваряя их имя символами & или && . Конструкцию & <имя переменной> называют подставляемой переменной .
Команда DEFINE позволяет определить пользовательскую переменную строкового типа и задать ей значение типа CHAR , либо получить значение определенной или всех пользовательских переменных. Эта команда имеет следующий синтаксис:
Если текст, присваиваемый переменной, содержит пробелы или символы пунктуации, его надо брать в апострофы.
При вызове без значения, команда DEFINE выдает значение переменной. Команда DEFINE без параметров выдает значение всех пользовательских переменных SQL*Plus, включая системные. Рассмотрим пример:
Как видите, утилита SQL*Plus автоматически определяет ряд системных пользовательских переменных.
Для удаления как явно определенной пользовательской переменной, так и параметра командной строки сценария (см. описание команды START выше) используется команда UNDEFINE . После применения этой команды к переменной значение переменной теряется, и она становится неопределенной. Команда UNDEFINE имеет следующий простой синтаксис:
Продолжая предыдущий пример:
Подставляемую переменную можно использовать в любом месте команды SQL*Plus или SQL-оператора, кроме первого слова в командной строке (по первому слову утилита SQL*Plus определяет тип команды). Когда SQL*Plus встречает в командной строке подставляемую переменную, она подставляет значение этой переменной (запрашивая его, если переменная не определена). Используются подставляемые переменные для получения более гибких, интерактивных сценариев SQL*Plus. Рассмотрим простой пример:
Как видите, если в команде встречается не определенная явно ранее подставляемая переменная, SQL*Plus запрашивает ее значение. Затем на экран выдается вид команды до и после подстановки всех значений (это можно отключить с помощью команды SET VERIFY OFF ), и команда выполняется. В нашем примере мы подставили в команду конкретную функцию агрегирования, имя столбца, по которому выполняется агрегирование, и имя таблицы.
Обратите внимание, что если необходимо вставить значение подставляемой переменной перед не пробельным символом, необходимо указать точку ( . ) после имени переменной.
В ответ на запрос значения можно ввести любую строку, в том числе с пробелами. Если значение должно быть взято в апострофы и эти апострофы не указаны явно в команде с подставляемой переменной, необходимо будет ввести значение в апострофах.
SQL*Plus читает данные с клавиатуры, даже если входной и выходной потоки терминала перенаправлены в файлы. Если же сценарий запущен в пакетном режиме, данные читаются из соответствующего файла.
Если использовать одну и ту же подставляемую переменную с символом & в одной команде несколько раз, значение будет запрашиваться каждый раз заново:
Чтобы значение переменной запрашивалось только один раз, используется подстановка с двумя амперсантами ( && ):
Подстановка переменных выполняется и для позиционных параметров, переданных при вызове сценария. На эти параметры можно ссылаться как на &1 , &2 и т.д. Если значение для них в командной строке не передано, SQL*Plus запрашивает значения при вызове сценария. Подстановка позиционных параметров выполняется только при вызове сценария командами START (или ее сокращенными формами @ , @@ ).
Подставляемые переменные нельзя использовать в командах редактирования буфера SQL ( APPEND , CHANGE , DEL , INPUT ) и в других командах, где эта подстановка "не имеет смысла", в частности, в комментариях. Команды редактирования буфера считают символ & обычным и используют его буквально (см. пример выше).
В табл. 13 представлены системные установки, влияющие на подстановку пользовательских переменных.
Таблица 13. Системные установки, влияющие на подстановку переменных.
| Установка | Описание |
| SET DEFINE | Задает символ подстановки (вместо стандартного - & ) и позволяет включать и отключать подстановку. |
| SET ESCAPE | Задает символ маскировки, позволяющий маскировать символ подстановки. Стандартным символом маскировки является обратная косая ( \ ). |
| SET VERIFY | Позволяет включать и отключать выдачу каждой строки команды до и после подстановки пользовательских переменных. |
| SET CONCAT | Задает символ, отделяющий имя подставляемой переменной от следующей непосредственно за ним строки. По умолчанию используется точка ( . ). |
Для выдачи на экран произвольного текста используется команда PROMPT со следующим синтаксисом:
<команда PROMPT> ::= PRO [ MPT ] [<текст>]
Она выдает указанный текст или пустую строку (при вызове без параметров). Если необходимо выдать несколько строк, для каждой строки выполняется отдельная команда PROMPT .
Считать строку и запомнить ее в указанной пользовательской переменной определенного типа (выдавая, при необходимости, приглашение) позволяет команда ACCEPT со следующим синтаксисом:
<команда ACCEPT> ::= ACC [ EPT ] <имя переменной> [<тип переменной>]
[ FOR [ MAT ] <формат>] [ DEF [ AULT ] <стандартное значение>]
[<приглашение>] [ HIDE ] <тип переменной> ::= NUM [ BER ] / CHAR / DATE <приглашение> ::= PROMPT <текст> / NOPR [ OMPT ]
Если указанная в команде ACCEPT пользовательская переменная не существует, SQL*Plus создает ее. Опции команды ACCEPT описаны в табл. 14.
Таблица 14. Опции команды ACCEPT.
Рассмотрим простой пример совместного использования команд PROMPT и ACCEPT . Пусть имеется командный файл splus1.sql со следующим содержимым:
Вот что происходит при его выполнении:
Команда PAUSE имеет следующий синтаксис:
<команда PAUSE> ::= PAU [ SE ] [<текст>]
Эта команда выдает пустую строку, затем строку текста, если он указан, или еще одну пустую строку, и ждет подтверждения от пользователя. Ввод эта команда ожидает с терминала (при интерактивном запуске), даже если входной и выходной потоки перенаправлены. При работе в пакетном режиме для продолжения необходимо наличие новой строки в файле, откуда берется входной поток.
Рассмотрим простой пример. Изменим файл splus1.sql следующим образом:
Вот что будет выдано при его выполнении:
Для получения результатов пришлось нажать клавишу Enter после вывода соответствующего приглашения.
Связываемые переменные - это создаваемые в SQL*Plus переменные, на которые можно ссылаться (как на хост-переменные) в блоках PL/SQL. Таким переменным можно, например, присваивать значения в блоках PL/SQL или использовать их значения во включенных в блоки SQL-операторах. Значения связываемых переменных можно затем выдавать в SQL*Plus.
Для создания связываемой переменной используется команда VARIABLE со следующим синтаксисом:
<команда VARIABLE> ::= VAR [ IABLE ] [<имя переменной> [<тип данных>]] <тип данных> ::= NUMBER
/ CHAR [ ( <количество> [<единица измерения>] ) ]
/ NCHAR [ ( <количество> ) ]
/ VARCHAR2 ( <количество> [<единица измерения>] )
/ NVARCHAR2 ( <количество> ) / CLOB
/ NCLOB
/ REFCURSOR <единица измерения> ::= CHAR / BYTE
При вызове без параметров команда VARIABLE выдает список всех переменных, созданных в сеансе. Если указать только имя переменной, выдается информация только об этой переменной.
Связываемые переменные можно использовать как параметры хранимых процедур или непосредственно, в анонимных PL/SQL-блоках. Их нельзя использовать в команде COPY или присвоить им значение в SQL-операторах, не входящих в PL/SQL-блоки. Вместо связанной переменной, не получившей явно значения, при необходимости подставляется значение NULL .
Рассмотрим простой пример использования связываемых переменных:
Мы использовали связываемую переменную для передачи значения из одного оператора SQL в другой, так и не выдав его на экран. Чтобы значения используемых в команде связываемых переменных выдавались автоматически, необходимо выполнить команду SET AUTOPRINT ON .
Типы данных для связываемых переменных аналогичны соответствующим типам данных PL/SQL. Переменные типа REFCURSOR позволяют работать с курсорными переменными PL/SQL. Утилита SQL*Plus обрабатывает связываемые переменные такого типа особым образом. Курсор, соответствующий курсорной переменной, открывается явно, а закрывается после выдачи значения или при завершении сеанса. При выдаче значения на экран выдается результирующее множество соответствующего запроса. Рассмотрим пример:
Результаты, выдаваемые при показе значений связываемой переменной типа REFCURSOR можно форматировать так же, как и результаты выполнения SQL-оператора SELECT . Значение такой переменной выдается только один раз, - затем результирующее множество надо выбирать повторно.
Для явной выдачи на экран значения связываемой переменной используется команда PRINT .
Команда PRINT имеет следующий синтаксис:
<команда PRINT> ::= PRI [ NT ]
Эта команда выдает текущее значение перечисленных связываемых переменных. При вызове без параметров выдаются значения всех связываемых переменных.
Продолжая предыдущий пример:
Утилита SQL*Plus позволяет автоматически получать отчет о способе выполнения оператора, выбранном оптимизатором SQL, а также статистическую информацию о выполнении. Этот отчет выдается после успешного выполнения операторов SELECT , INSERT , UPDATE и DELETE . Такой отчет полезен для контроля и настройки производительности этих операторов.
Для управления данным отчетом используется команда SET AUTOTRACE . Эта команда имеет пять опций:
| OFF | Отчет не выдается. Это стандартный режим работы SQL*Plus. |
| ON EXPLAIN | Выдается только выбранный оптимизатором план выполнения оператора. |
| ON STATISTICS | Выдается только статистическая информация о выполнении оператора. |
| ON | Выдается план выполнения запроса и статистическая информация о выполнении оператора. |
| TRACEONLY | Выдается отчет о статистике, но не выдаются результаты выполнения оператора. Используется для трассировки запросов, возвращающих большие объемы данных. |
Для использования этой возможности SQL*Plus необходимо создать в схеме пользователя таблицу PLAN_TABLE и получить роль PLUSTRACE (предоставить ее может только DBA). Выполним следующие действия:
Как видите, по умолчанию эта возможность не поддерживается. Создадим таблицу с помощью сценария $ORACLE_HOME/rdbs/admin/utlxplan.sql :
Затем создадим роль PLUSTRACE, дадим ей необходимые привилегии, а затем предоставим ее роли DBA (с помощью сценария $ORACLE_HOME/sqlplus/admin/plustrce.sql ):
Теперь предоставим роль PLUSTRACE пользователю, который будет использовать трассировку:
Проверяем, что трассировочный отчет теперь выдается:
Подробнее использование возможностей трассировки в SQL*Plus рассмотрено в отдельном модуле, посвященном настройке производительности.
Среда SQL*Plus - очень гибкая и имеет широкие возможности настройки по требованиям пользователя. Эти настройки выполняются, в основном, с помощью команды SET . Мы неоднократно использовали команду SET в примерах данного модуля. В следующем разделе представлено ее формальное описание.
Команда SET позволяет установить системную переменную, изменяющую свойства среды SQL*Plus для текущего сеанса. Она имеет следующий синтаксис:
<команда SET> ::= SET <системная переменная> <значение>
Имена, описания и возможные значения основных системных переменных для команды SET представлены в табл. 15.
Здесь собраны примеры, демонстрирующие некоторые из полезных возможностей Oracle SQL*Plus. Все приведенные скрипты выполнены в SQL*Plus Release 11.2.0.1.0 в консоли Windows.
Подборка носит отчасти ностальгический характер, поскольку в последние годы всю повседневную оперативную работу с СУБД Oracle я делаю в PL/SQL Developer. Однако, SQL*Plus остается незаменимым для работы в консоли Unix/Linux и из shell-скриптов. Пример в конце статьи - только для Unix/Linux.
Для упрощения демонстрации написаны несколько маленьких скриптов-"подпрограмм", которые в дальнейшем вызываются из других скриптов. Вот они:
Чтобы избежать этого "спама", будем запускать SQL*Plus в молчаливом (silent) режиме. Следующий скрипт использует приведенные выше скрипты-подпрограммы и демонстрирует работу в silent режиме:
Запускаю скрипт в silent режиме:
Переходим собственно к демонстрации полезных возможностей SQL*Plus.
Скрипт define.sql показывает, как определить, использовать и очистить переменные (substitution variables) в SQL*Plus, а также выводит значения встроенных переменных:
Следующий скрипт показывает, что делать, если необходимо использовать символ & буквально (как символ):
Как видим, в первом случае SQL*Plus запросил значение для переменной good , интерпретировав символ & как префикс имени переменной, значение которой необходимо подставить. А во втором случае, после выполнения директивы set define off , символ & был использован буквально.
Следующий скрипт вычисляет значение выражения, введенного пользователем, и выводит результат.
Пользователь может ввести любое допустимое выражение SQL, в том числе, использовать функции SQL. Узнаем, например, последнюю дату текущего месяца:
Вычисляемые или извлекаемые из базы данных значения SQL*Plus позволяет присвоить переменным, чтобы использовать эти значения далее по ходу скрипта. Как это делается, демонстрирует следующий скрипт:
Следующий скрипт демонстрирует вывод результата запроса к БД в файл. Принимаются меры, чтобы в файл не попало ничего лишнего: ни заголовки, ни счетчик извлеченных строк.
Только что выполненный скрипт - простейший прототип отчета, выводимого в текстовый файл. Другое применение данной техники - динамически сформировать скрипт и выполнить его. Например, следующий скрипт выдает привилегии на чтение всех таблиц текущей схемы указанному пользователю ( grantee ):
Выполню скрипт, сделав таблицы текущей схемы публично доступными:
По аналогии с данным скриптом легко создать скрипт, создающий синонимы для чужих таблиц, права на которые имеет текущий пользователь, или скрипт, противоположный данному, - отбирающий привилегии на все таблицы схемы у указанного пользователя.
А следующий пример демонстрирует передачу параметров в вызываемый скрипт:
Следующий скрипт демонстрирует прерывание работы SQL*Plus при возникновении ошибки 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>.
1- Введение
PL/SQL (Procedural Language/Structured Query Language) это процедурно-ориентированный язык программирования использующийся для Oracle SQL. Является расширением Oracle SQL.
PL/SQL включает компоненты процедурно-ориентированного языка включая условие и цикл. Он позволяет объявлять константы и переменные, процедуры и функции, виды данных и переменные видов данных, и trigger. Он может обрабатывать исключения (ошибки времени запуска) Массив так же поддерживается для использования коллекций в PL/SQL. От версии 8 и далее он включает объектно-ориентированные функции. Может создать такие единицы PL/SQL как процедуры, функции, пакеты, виды данных, triggers, которые хранятся в базе данных для переиспользования приложением, чтобы взаимодействовать с приложениями Oracle.
Примечание: В следующих изображениях я использую инструмент PL/SQL Developer версии 8.x, но нет отличия если вы используете PL/SQL Developer версии 10.x или другую версию.
2- Что нужно чтобы начать с PL/SQL?
Чтобы иметь быстрый доступ к PL/SQL вам нужен инструмент программирования. По моему опыту работы, вы можете использовать PL/SQL Developer, это визуальный инструмент для работы с Oracle и программирования PL/SQL.
Вы можете посмотреть инструкцию установки и конфигурации PL/SQL по ссылке:
3- Обзор PL/SQL
Есть некоторые определения, которые вы должные четко знать при программировании с PL/SQL:
- Каждая команда SQL заканчивается точкой с запятой (;)
- Команды "языка определения данных" (Data Definition Language - DDL) не используются в PL/SQL
- Команда SELECT.. INTO возврщает много строк создающих exception ( > 1 строки).
- Команда SELECT .. INTO не возвращает строки создающие exception
- Команды "языка манипулирования данными" (Data Manipulation Language - DML) может подействовать на многие строки данных.
- Использовать оператор := чтобы дать значение переменной.
PL/SQL организован по блокам команд. Один блок команды может содержать подблок команд внутри.
4- Базовые команды PL/SQL
Здесь я представляю обзор команд PL/SQL. Вы поймете больше через примеры в следующих частях.
4.1- Команда If-elsif-else
4.2- Не предопределенный цикл (LOOP)
4.3- Предопределенный цикл (FOR LOOP)
4.4- Цикл while (WHILE)
5- Начать с PL/SQL используя PL/SQL Developer
Для начала вам нужно открыть PL/SQL Developer, и войти как пользователь learningsql:
В PL/SQL Developer создать новое окно SQL:
Читайте также: