Prompt oracle что это
Get full access to Oracle SQL*Plus: The Definitive Guide and 60K+ other titles, with free 10-day trial of O'Reilly.
There's also live online events, interactive content, certification prep materials, and more.
The most reliable and robust method for getting input from the user is to explicitly prompt for values using the ACCEPT and PROMPT commands. The ACCEPT command takes input from the user and stores it in a user variable, and also allows you some level of control over what the user enters. The PROMPT command may be used to display messages to the user, perhaps supplying a short summary of what your script is going to accomplish.
There are several potential problems that arise when you simply place substitution variables in your scripts and rely on SQL*Plus’s default prompting mechanisms. All of these problems can be avoided through the use of the ACCEPT command. Table 4.1 provides a list of these problems together with a description of how the ACCEPT and PROMPT commands can be used to overcome them.
Table 4-1. Potential Problems with SQL*Plus’s Default Prompting
Using double ampersands to define a variable in a script results in your not being prompted for a value the second time you run the script.
Use the ACCEPT command to prompt for a value. This works regardless of whether the variable has previously been defined.
Setting terminal output off, such as when spooling a report to a file, prevents you from seeing the prompts for substitution variables used in the query.
Use the ACCEPT command to prompt for these values earlier in the script, before the SET TERMOUT OFF command is executed.
The default prompt provided by SQL*Plus consists of little more than the variable name.
Use the ACCEPT command to specify your own prompt. For longer explanations, the PROMPT command may be used.
This section shows how to enhance the LIST_INDEXES script with the PROMPT and ACCEPT commands. The PROMPT command will be used to better explain what the script is doing, while the ACCEPT command will be used to reliably prompt the user for the table name.
Записи в PL/SQL
Записи (records) в PL/SQL позволяют воспринимать взаимосвязанные данные как одно целое. Они могут содержать поля, каждое из которых может представлять отдельный элемент. Можно использовать атрибут ROW%TYPE и с его помощью объявлять записью столбцы определенной таблицы, что подразумевает применение таблицы в качестве шаблона курсора, а можно создавать и свои собственные записи. Ниже приведен простой пример записи:
Для ссылки на отдельное поле внутри записи применяется точечное обозначение, как показано ниже:
Курсорный цикл FOR
Обычно при использовании явных курсоров требуется открывать курсор, извлекать данные и по завершении закрывать курсор. Курсорный цикл FOR позволяет выполнять эти процедуры по открытию, извлечению и закрытию автоматически, чем очень сильно упрощает дело. В листинге А.5 показан пример применения конструкции курсорного цикла FOR.
Команда SPOOL
Команда SPOOL позволяет сохранять вывод одного и более SQL-операторов в файлах операционной системы, как в UNIX, так и в Windows:
По умолчанию создаваемые этой командой буферные (spooled) текстовые файлы сохраняются как имя_файла.lst . Хотя по умолчанию принято сохранять вывод в файле,его также можно отправлять и на принтер. Буферизация файлов является очень полезным приемом при использовании SQL для оказания помощи в написании SQL-сценариев, и некоторые примеры его применения можно найти в приложении.
С помощью команды SPOOL можно добавлять данные в существующий буферный файл, а также полностью заменять его (по умолчанию происходит замена). Ниже приведен весь синтаксис этой команды:
Ниже описаны параметры команды SPOOL .
У команды SPOOL имеет множество способов применения. Например, она легко экспортируется для перехвата результатов оператора SELECT . Перед этим, однако, должен обязательно задаваться формат вывода путем определения значений для переменных HEADING, FEEDBACK и LINESIZE . Ниже приведен пример:
В этом примере файл employees.txt служит для перехвата всех данных из таблицы HR.EMPLOYEES . Далее его можно будет использовать для загрузки данных в другую таблицу с помощью утилиты SQL*Loader.
Использование курсоров
Курсором (cursor) в Oracle называется указатель на область в памяти, в которой содержится результирующий набор SQL-запроса, позволяющий индивидуально обрабатывать содержащиеся в результирующем наборе строки. Курсоры, которые используются Oracle при выполнении DML-операторов, называются неявными, а курсоры, которые создают и используют разработчики приложений — явными.
Работа с переменными
Переменные могут быть заданы двумя способами:
Если уже была определена &&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 возможностью задать дефолтные значения:
Использование DML-операторов в PL/SQL
Любые операторы INSERT, DELETE или UPDATE работают в PL/SQL точно так же, как в обычном SQL. Однако в PL/SQL после каждого из них можно также применять оператор COMMIT, как показано ниже:
Команда EXECUTE
При использовании сценариев, которые вызывают PL/SQL-код в форме процедур и пакетов, для фактического запуска отдельных процедур в пакете необходимо применять команду EXECUTE . Ниже приведен пример:
Обратите внимание, что для выполнения процедуры или функции можно специфицировать как ключевое слово EXECUTE , так и ключевое слово EXEC .
Using ACCEPT to get the table name
You can make the LIST_INDEXES script more reliable by using ACCEPT to get the table name from the user. This ensures that the user is prompted for a table name each time the script is run. The following ACCEPT command should do the trick:
A good place to add the command would be just prior to the COLUMN commands, so the resulting script would look like this:
It doesn’t really matter now whether the script uses &table_name or &&table_name for the substitution variable. Either will work just as well, and the script just shown uses both. When you run the script, here’s how the prompt will look:
Now you can run this script many times in succession, and you will be prompted for a different table name each time. In addition, this prompt is a bit more user-friendly than the default prompt generated by SQL*Plus.
Выполнение 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 может быть выполнено, используя:
В случае ошибок, при выполнении процедуры, можно отобразить их выполнив команду:
Конструкции циклов в PL/SQL
Конструкции циклов в PL/SQL позволяют обеспечивать итеративное выполнение кода либо заданное количество раз, либо до тех пор, пока определенное условие не станет истинным или ложным. В следующих подразделах описываются основные виды этих конструкций.
Простой цикл
Конструкция простого цикла подразумевает помещение набора SQL-операторов между ключевыми словами LOOP и END LOOP. Оператор EXIT завершает цикл. Конструкция простого цикла применяется тогда, когда точно неизвестно, сколько раз должен выполняться цикл. В случае ее применения решение о том, когда цикл должен завершаться, принимается на основании содержащейся между операторами LOOP и END LOOP логики.
В следующем примере цикл будет выполняться до тех пор, пока значение quality_grade не достигнет 6:
Еще один простой вид цикла позволяет выполнять конструкция LOOP. EXIT. WHEN, в которой длительность цикла регулируется оператором WHEN. Внутри WHEN указывается условие, и когда это условие становится истинным, цикл завершается. Ниже показан простой пример:
Цикл WHILE
Цикл WHILE указывает, что определенный оператор должен выполняться до тех пор, пока определенное условие остается истинным. Обратите внимание на то, что условие вычисляется за пределами цикла, и вычисляется оно всякий раз, когда выполняются операторы, указанные между операторами LOOP и END LOOP. Когда условие перестает быть истинным, происходит выход из цикла. Ниже приведен пример цикла WHILE:
Цикл FOR
Цикл FOR применяется тогда, когда требуется, чтобы оператор выполнялся определенное количество раз. Он имитирует классический цикл do, который существует в большинстве языков программирования. Ниже приведен пример цикла FOR:
Выполнение SQL запросов
Запрос может состоять из нескольких строк, содержать комментарии, но не может содержать внутри пустые строки.
Запрос может быть выполнен тремя способами:
- точка с запятой в конце запроса
- строка с слешем "/" после запроса
- пустая строка после запроса (будет помещен в буфер, но не выполнен немедленно)
Команда HOST
Команда HOST позволяет выполнять в SQL*Plus команды операционной системы.Например, может возникнуть необходимость посмотреть, существует ли некий файл в определенном каталоге, или выдать команды cp или tar на уровне UNIX , а затем вернуться в сеанс SQL*Plus и возобновить взаимодействие с базой данных Oracle.
Ниже приведен пример применения команды HOST :
В этом примере команда HOST помогает скопировать файл new.sql из указанного каталога в каталог tmp .
С помощью команды HOST можно выполнять практически все те же команды, которые доступны на уровне операционной системы. Слово HOST можно заменять восклицательным знаком (!):
На заметку! В случае ввода команды HOST без параметров вы попадаете в каталог операционной системы, из которого изначально запускали сеанс SQL*Plus.
По завершении работы с операционной системой достаточно ввести в командной строке exit и на экране снова появится приглашение покинутого ранее сеанса SQL*Plus.
Команда 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 ,чтобы желаемые значения устанавливались автоматически при каждом входе в систему, и их не приходилось настраивать вручную.
Using PROMPT to explain the output
The PROMPT command can also be used to better explain the output of a script. In the LIST_INDEXES example, messages could be added prior to the DESCRIBE command, and prior to the SELECT statement, in order to explain the output. The resulting script would look like this:
Here is the result of executing the script with all the PROMPT commands added. The messages not only make the output more clear, but space it out better as well.
Get Oracle SQL*Plus: The Definitive Guide now with the O’Reilly learning platform.
O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.
Хотя язык SQL и является легким в изучении и обладает массой мощных функциональных возможностей, он не позволяет создавать такие процедурные конструкции, которые возможны в языках третьего поколения вроде C. Язык PL/SQL является собственным расширением языка SQL от Oracle и предлагает функциональность серьезного языка программирования. Одно из главных его преимуществ состоит в том, что он позволяет использовать в базе данных такие программные единицы, как процедуры и пакеты, и тем самым увеличивать возможность повторного использования кода и его производительность.
The PROMPT Command
The PROMPT command is used to print text on the display for the user to read. It allows you to provide informative descriptions of what a script is about to do. It can be used to provide very long and detailed prompts for information, and it can be used simply to add blank lines to the output in order to space things out a bit better.
Настройки выполнения скриптов
Действуют на протяжении сессии в SQLplus.
Запомнить настройки перед выполнением скрипта и вернуть обратно по завершении выполнения:
Использование оператора SELECT в PL/SQL
При использовании оператора SELECT в PL/SQL нужно сохранять извлекаемые значения в переменных, как показано ниже:
Syntax for the ACCEPT command
Here is the syntax for the ACCEPT command:
Tells SQL*Plus that you want to prompt the user for a value, and that you want the value stored in the specified user variable. The command may be abbreviated to ACC.
Is the variable you want to define. Do not include leading ampersands. If your script uses a “&table_name” for a substitution variable, you should used “table_name” here.
Is the type of data you are after. The default is CHAR, which allows the user to type in anything as a response. Use NUMBER to force the user to enter a number and DATE when you want a date.
This is an optional format specification, which may optionally be enclosed in quotes. If this is specified, ACCEPT will reject any input that does not conform to the specification. An error message will be displayed, and the prompt reissued. Specifying a format makes the most sense when dealing with numeric and date data, and SQL*Plus is actually somewhat loose in enforcing the format. Chapter 7, delves into this aspect of the ACCEPT command in detail.
Specifies a default value to assign to the variable. This is used if the user bypasses the prompt by pressing ENTER without actually entering a response. The default value should usually be enclosed within single quotes.
This is the prompt text displayed to the user before waiting for input.
Indicates that you do not want the user to see a visible prompt.
Causes SQL*Plus not to echo the user’s response back to the display. This is useful if you are prompting for a password.
The syntax for the ACCEPT command has evolved significantly with the past few releases of SQL*Plus. The syntax shown here is valid for version 8.1. Not all of the clauses are available when using prior versions. Be sensitive to this, and check your documentation if you are writing scripts that need to work under earlier versions of SQL*Plus.
Курсорные переменные
Курсорные переменные указывают на текущую строку в многострочном результирующем наборе. В отличие от обычного курсора, однако, курсорная переменная является динамической, что позволяет присваивать ей новые значения и передавать ее другим процедурами и функциям. Создаются курсорные переменные в PL/SQL следующим образом.
Сначала определяется тип REF CURSOR, как показано ниже:
Затем объявляются сами курсорные переменные типа EmpCurType в анонимном блоке кода PL/SQL либо в процедуре (или функции):
Объявление переменных в PL/SQL
В операторе DECLARE можно объявлять как переменные, так и константы. Прежде чем использовать какую-либо переменную ее нужно обязательно объявить. Переменная в PL/SQL может представлять собой как переменную встроенного типа, такого как DATE, NUMBER, VARCHAR2 или CHAR, так и составного вроде VARRAY. Помимо этого, в PL/SQL еще применяются такие типы данных, как BINARY_INTEGER и BOOLEAN.
Ниже приведены некоторые типичные примеры объявления переменной в PL/SQL:
Помимо переменных также можно объявлять и константы, как показано в следующем примере:
Еще можно использовать атрибут %TYPE и с его помощью указывать при объявлении переменной, что ее тип данных должен совпадать с типом данных определенного столбца таблицы:
Посредством атрибута %ROWTYPE можно указывать, что тип данных записи (строки) должен совпадать с типом данных определенной таблицы базы данных. Например, в следующем коде указано, что запись DeptRecord должна содержать все те же столбцы, что и таблица department, а типы данных и длина этих столбцов в ней должны выглядеть абсолютно идентично:
Команда PAUSE
Часто бывает так, что сценарии при выполнении генерируют вывод, который не умещается на экране. Этот вывод быстро пролетает перед глазами на экране и исчезает до того, как его удается прочитать. Разумеется, можно воспользоваться командой SPOOL для перехвата всего вывода целиком, но делать так постоянно не выгодно, поскольку тогда придется тратить на создание и прочтение файлов с выводом сценариев целый день. Поэтому лучше использовать другую предлагаемую в SQL*Plus команду, а именно — PAUSE , которая позволяет приостанавливаться после отображения каждого целого экрана вывода. Переход к следующему экрану вывода осуществляется просто нажатием клавиши .
Приведенный ниже пример демонстрирует использование команды PAUSE для замедления отображения вывода на экране терминала:
После настройки команды PAUSE вывод больше не будет быстро проскакивать на экране при каждом выполнении SQL-команды. Вместо этого SQL*Plus будет отображать один экран вывода, и ожидать нажатия клавиши . При запуске запросов с командой PAUSE для просмотра первого экрана вывода тоже необходимо нажимать клавишу .
Утилита 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 .
Считать строку и запомнить ее в указанной пользовательской переменной определенного типа (выдавая, при необходимости, приглашение) позволяет команда ACCEPT со следующим синтаксисом:
Если указанная в команде ACCEPT пользовательская переменная не существует, SQL*Plus создает ее. Опции команды ACCEPT описаны в табл. 14.
Таблица 14. Опции команды ACCEPT.
Рассмотрим простой пример совместного использования команд PROMPT и ACCEPT . Пусть имеется командный файл splus1.sql со следующим содержимым:
Вот что происходит при его выполнении:
Команда PAUSE имеет следующий синтаксис:
Эта команда выдает пустую строку, затем строку текста, если он указан, или еще одну пустую строку, и ждет подтверждения от пользователя. Ввод эта команда ожидает с терминала (при интерактивном запуске), даже если входной и выходной потоки перенаправлены. При работе в пакетном режиме для продолжения необходимо наличие новой строки в файле, откуда берется входной поток.
Рассмотрим простой пример. Изменим файл splus1.sql следующим образом:
Вот что будет выдано при его выполнении:
Для получения результатов пришлось нажать клавишу Enter после вывода соответствующего приглашения.
Связываемые переменные - это создаваемые в SQL*Plus переменные, на которые можно ссылаться (как на хост-переменные) в блоках PL/SQL. Таким переменным можно, например, присваивать значения в блоках PL/SQL или использовать их значения во включенных в блоки SQL-операторах. Значения связываемых переменных можно затем выдавать в SQL*Plus.
Для создания связываемой переменной используется команда VARIABLE со следующим синтаксисом:
При вызове без параметров команда VARIABLE выдает список всех переменных, созданных в сеансе. Если указать только имя переменной, выдается информация только об этой переменной.
Связываемые переменные можно использовать как параметры хранимых процедур или непосредственно, в анонимных PL/SQL-блоках. Их нельзя использовать в команде COPY или присвоить им значение в SQL-операторах, не входящих в PL/SQL-блоки. Вместо связанной переменной, не получившей явно значения, при необходимости подставляется значение NULL .
Рассмотрим простой пример использования связываемых переменных:
Мы использовали связываемую переменную для передачи значения из одного оператора SQL в другой, так и не выдав его на экран. Чтобы значения используемых в команде связываемых переменных выдавались автоматически, необходимо выполнить команду SET AUTOPRINT ON .
Типы данных для связываемых переменных аналогичны соответствующим типам данных PL/SQL. Переменные типа REFCURSOR позволяют работать с курсорными переменными PL/SQL. Утилита SQL*Plus обрабатывает связываемые переменные такого типа особым образом. Курсор, соответствующий курсорной переменной, открывается явно, а закрывается после выдачи значения или при завершении сеанса. При выдаче значения на экран выдается результирующее множество соответствующего запроса. Рассмотрим пример:
Результаты, выдаваемые при показе значений связываемой переменной типа REFCURSOR можно форматировать так же, как и результаты выполнения SQL-оператора SELECT . Значение такой переменной выдается только один раз, - затем результирующее множество надо выбирать повторно.
Для явной выдачи на экран значения связываемой переменной используется команда PRINT .
Команда PRINT имеет следующий синтаксис:
Эта команда выдает текущее значение перечисленных связываемых переменных. При вызове без параметров выдаются значения всех связываемых переменных.
Продолжая предыдущий пример:
Утилита 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 представлены в табл. 15.
SQL*Plus - программа для работы с СУБД Oracle посредством командной строки. Используя SQL*Plus можно выполнять команды SQL и PL/SQL в интерактивном режиме или используя сценарий.
Основное преимущество SLQ*Plus - доступность, т.к. инструмент доступен практически всегда, когда в системе установлен Oracle. При этом это достаточно мощный инструмент, используя который можно решать различные задачи на удаленных машинах.
К написанию этой статьи меня подтолкнула книга "Oracle SQL*Plus: The Definitive Guide, 2nd Edition", написанная Jonathan Gennick. Как обычно, появилось желание систематизировать информацию и представить ее в удобном виде.
При этом сразу считаю нужным оговориться, что я использую SQLplus для написания и выполнения скриптов на удаленных машинах, в этой статье описываю именно используемые для этого команды.
Область возможного использования SQLplus при этом гораздо шире, например - построение отчетов, в том числе в формате HTML.
Syntax for the PROMPT command
PROMPT is a very simple command. The syntax looks like this:
Is the command, which may be abbreviated to PRO.
Is whatever text you want displayed for the user to see. This should not be a quoted string. If you include quotes, they will appear in the output.
If you are spooling output to a file when a PROMPT command is executed, the prompt text will also be written to the file. Any substitution variables in the prompt text will be replaced by their respective values before the text is displayed.
Команды ACCEPT и PROMPT
Команда ACCEPT применяется для считывания вводимых пользователем данных с экрана и сохранения их в какой-то переменной. Эту переменную можно либо указать самостоятельно, либо позволить создавать ее SQL*Plus. Обычно команда ACCEPT применяется для считывания данных, вводимых пользователем в ответ на приглашения в интерфейсе SQL*Plus.
Команды ACCEPT и PROMPT в SQL-сценарии обычно используются вместе и служат,как правило, для запроса у пользователя входных данных и сохранения этих данных в переменных для дальнейшей работы с ними в программе. Ниже приведен пример, иллюстрирующий использование этих команд:
Неявные курсоры
В приведенном ниже блоке кода PL/SQL оператор SELECT, например, предусматривает применение неявного курсора:
Процедуры, функции и пакеты
Процедуры в PL/SQL могут применяться для выполнения различных DML-операций. Ниже приведен пример простой процедуры Oracle:
В отличие от процедур, функции в PL/SQL возвращают значение, как показано в следующем примере:
Пакеты (packages) в Oracle представляют собой объекты, которые обычно состоят из нескольких взаимосвязанных процедур и функций и, как правило, применяются для выполнения какой-нибудь функции приложения путем вызова всех находящихся внутри пакета взаимосвязанных процедур и функций. Пакеты являются чрезвычайно мощным средством, поскольку могут содержать большие объемы функционального кода и многократно выполняться несколькими пользователями.
Каждый пакет обычно состоит из двух частей: спецификации и тела. В спецификации пакета объявляются все входящие в его состав переменные, курсоры и подпрограммы (процедуры и функции), а в теле пакета содержится фактический код этих курсоров и подпрограмм.
В листинге А.6 приведен пример простого пакета Oracle.
При желании использовать пакет emp_pkg для награждения какого-то сотрудника надбавкой к зарплате, все, что потребуется сделать — выполнить следующую команду:
Для работы в SQL*Plus, как для выполнения простых команд, так и для разработки сценариев по сбору информации из базы данных, необходимо уметь пользоваться командами SQL*Plus двух основных видов. К первому относятся команды, которые на самом деле что-то делают и потому могут называться рабочими (как, например, RECOVER , которая выполняет восстановление базы данных), а ко второму — команды, которые позволяют получать удобный для прочтения вывод от запросов и потому называются командами форматирования.
В настоящей статье вы ознакомитесь с важнейшими представителями обоих этих видов команд. В частности, в этом разделе рассматриваются команды, которые что-то делают, а в следующем — те, что позволяют форматировать вывод и получать отчеты.
Условное управление
Главной разновидностью условной управляющей структуры в PL/SQL является оператор IF, который обеспечивает условное выполнение операторов. Он может применяться в одной из трех следующих форм: IF-THEN, IF-THEN-ELSE и IF-THEN-ELSEIF. Ниже приведен пример простого оператора IF-THEN-ELSEIF:
Обработка ошибок
В PL/SQL любая ошибка или предупреждение называется исключением (exception). В PL/SQL есть кое-какие определенные внутренне ошибки, но также допускается определять и свои собственные. При возникновении любой ошибки инициируется исключение, и управление переходит в отвечающий за обработку исключений раздел программы PL/SQL. В случае определения своих собственных ошибочных ситуаций необходимо обеспечивать инициирование исключений за счет применения специального оператора RAISE.
Ниже приведен пример использования оператора RAISE для обработки исключений:
Атрибуты курсоров
В примере, приведенном в листинге А.4, для указания того, когда цикл должен завершаться, используется специальный атрибут курсора %NOTFOUND. Атрибуты курсоров очень полезны при работе с явными курсорами. Наиболее важные из них перечислены ниже.
- %ISOPEN. Булевский атрибут, который после завершения выполнения SQL-оператора возвращает false. До тех пор, пока курсор остается открытым, он возвращает true.
- %FOUND. Булевский атрибут, который выполняет проверку на предмет наличия подходящих для SQL-оператора строк, т.е. остались ли у курсора еще какие-то строки для извлечения.
- %NOTFOUND. Булевский атрибут, который сообщает о том, что не удалось обнаружить ни одной подходящей для SQL-оператора строки, т.е. у курсора больше не осталось никаких строк для извлечения.
- %ROWCOUNT. Атрибут, который возвращает информацию о том, сколько курсору удалось извлечь строк на текущий момент.
Явные курсоры
Явные курсоры создаются разработчиком приложения и облегчают операции с набором строк, которые могут обрабатываться друг за другом. Они применяются всегда, когда известно, что SQL-оператор будет возвращать более одной строки. Обратите внимание, что явный курсор необходимо всегда объявлять в начале блока PL/SQL внутри раздела DECLARE, в отличие от неявного курсора, на который никогда не нужно ссылаться в коде.
После объявления явного курсора он будет проходить через следующие этапы обработки.
- Конструкция OPEN будет определять строки, которые находятся в курсоре, и делать их доступными для программы PL/SQL.
- Команда FETCH будет извлекать данные из курсора в указанную переменную.
- По завершении процесса обработки курсор должен всегда закрываться явным образом.
В листинге А.4 показан пример создания курсора и затем его использования внутри цикла.
Управляющие структуры в PL/SQL
В PL/SQL предлагается несколько видов управляющих структур (control structures), которые позволяют обеспечивать итерацию кода или условное выполнение определенных операторов. Все они кратко описаны в последующих разделах моего блога.
Using PROMPT to summarize the script
It would be nice to add some messages to the LIST_INDEXES script to make it more self-explanatory to the user. You can do that by adding the following PROMPT commands to the beginning of the script:
The first and last PROMPT commands simply space the output a bit better by adding a blank line above and below the description.
Базовый блок PL/SQL
Блоком в PL/SQL называется исполняемая программа. Блок кода PL/SQL, независимо от того, инкапсулируется он внутри какой-то программной единицы наподобие процедуры или задается в виде анонимного блока в свободной форме, состоит из следующих структур, которые представляют собой четыре ключевых оператора, только два из которых являются обязательными.
- DECLARE . Этот оператор является необязательным и представляет собой то место, в котором при желании объявляются переменные и курсоры программы.
- BEGIN . Этот оператор является обязательным и указывает, что далее будут идти операторы SQL и PL/SQL, т.е. обозначает начало блока кода PL/SQL.
- EXCEPTION . Этот оператор является необязательным и описывает методы обработки ошибок.
- END . Этот оператор является обязательными и обозначает конец блока кода PL/SQL.
Ниже приведен пример простого блока кода PL/SQL:
Параметры, подключение к базе, запуск скриптов
Выполнить несколько строк кода (не передавая отдельный файл со скриптом), unix:
Написание исполняемых операторов PL/SQL
После оператора BEGIN можно начинать вводить все свои желаемые SQL-операторы. Выглядеть эти операторы должны точно так же, как обычные операторы SQL. При использовании операторов SELECT и INSERT в PL/SQL, правда, необходимо помнить об особенностях, о которых более подробно речь пойдет в следующих разделах.
Команда DESCRIBE
Команда DESCRIBE описывает или выводит список столбцов таблицы вместе с их спецификациями. Кроме того, она позволяет получать описание процедур или пакетов Oracle. Эта команда чрезвычайно полезна при выполнении рутинных обязанностей администратора баз данных. Если, например, нет уверенности в том, какой столбец нужно выбрать в определенной таблице, но точно известно, к какой именно таблице следует выполнить запрос, с помощью команды DESCRIBE можно получить список всех столбцов, которые имеются в данной таблице. Поскольку команда DESCRIBE может применяться даже в отношении метаданных (словаря данных), с ее помощью можно также очень легко знакомиться с информацией о таблицах и столбцах и их применении, которая является критически важной для работы с базой данных.
В листинге ниже показано, как команда DESCRIBE позволяет отображать столбцы и типы столбцов таблицы.
The ACCEPT Command
The ACCEPT command is used to obtain input from the user. With it, you specify a user variable and text for a prompt. The ACCEPT command displays the prompt for the user, waits for the user to respond, and assigns the user’s response to the variable.
Читайте также: