Set define off oracle для чего

Обновлено: 26.01.2025

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.

Запомнить настройки перед выполнением скрипта и вернуть обратно по завершении выполнения:

Утилита 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 или ошибки операционной системы:

У меня есть проблема в настройке Set Define OFF через JDBC, так как мы не можем запускать команды SqlPlus напрямую из JDBC. Есть ли какой-нибудь способ запустить этот набор Define off в моей базе данных/схеме постоянно, чтобы я мог игнорировать переменную подстановки '&'? Пожалуйста.

В чем разница между переменной define в классе с или без него? Например, если я объявлю переменную следующим образом public string VariableName < get; set; >и public string VariableName; В чем разница и если нет никакой разницы, то почему и когда использовать get set?

По умолчанию SQL Plus рассматривает '&' как специальный символ, который начинает строку подстановки. Это может вызвать проблемы при запуске скриптов, которые включают '&' по другим причинам:

Если вы знаете, что ваш скрипт включает (или может включать) данные, содержащие символы '&', и вам не нужно поведение подстановки, как описано выше, то используйте set define off , чтобы отключить это поведение во время выполнения скрипта:

Возможно, вы захотите добавить set define on в конце скрипта, чтобы восстановить поведение по умолчанию.

С помощью set define off он взял строку со значением &var , предложил пользователю ввести для нее значение и заменил &var введенным значением (в данном случае X ).