Oracle запрос с параметрами

Обновлено: 02.07.2024

И вот тут начинаются приколы. В моей локальной таблице всего 3 записи, в удаленной таблице записей много, но по полю ID есть индекс, он VALID-ный, я проверял. Казалось бы, оракл должен был вытянуть 3 ид-шника из коллекции, передать список из 3-х ид-шников в запрос к удаленной таблице, и поскольку то поле проиндексировано, то запрос должен был выполниться очень быстро.
Но в реале запрос выполняется больше минуты. Посмотреть план запроса к удаленной БД не могу, т.к. дблинк - это отдельная транзакция и в плане запроса я увижу только REMOTE, а к удаленной базе у меня нет админского доступа.
Когда я делаю вот так:

т.е. пишу ид-шники напрямую, запрос выполняется мгновенно.
Сделал временно так:

Казалось бы все хорошо, но на удаленной базе в кеше запросов будет куча моих однотипных запросов с разными вариациями IN(. ), что есть очень плохо. Пытался сделать через параметризированный запрос, определив мой id_list_str как параметр:

- не получилось. Видимо, оракл не может объявить параметр типа список, только конкретные значения, а у меня постоянно меняется их количество.
У меня остался еще один резервный вариант: в цикле по коллекции выполнять селект к удаленной БД, извлекая по одной записи через параметр, но это решение не очень эффективное, т.к. постоянно будет идти переключение контекста между PL/SQL и SQL, что тоже не очень хорошо. Хотелось бы все-таки обойтись одним запросом к удаленной базе с передачей списка ид-шников, но чтобы при этом не забить library cache этой удаленной базы.
Заранее спасибо за помощь.

это remote SQL ибо у нас одна удаленная таблицa. A вот:

это distributed SQL ибо имеем локальную и удаленную таблицы. Смотри план. Скорее всего driving site локальная база. Посему ВСЕ данные удаленной таблицы закачиваются в локальную базу и только потом фильтруются. Помоги оптимазеру вставь хинт /*+ DRIVING_SITE(R) */. Может уговоришь .

решается без хинтов, заменой этого блока на:

А возможно и больше чем нужно

И не факт что "не будет тащить всю удалённую таблицу". Оптимайзер понятия не имеет о размере T, так-что совершенно не факт что WITH сделает R as driving site.

И на каком заборе это написано? Это отимайзер решает материализовать WITH (вернее каждое CTE по отдельности) или нет. Но даже если и материализует то что? Получим соединение temporary table с удаленной таблицей что ничем это отличается от исходной ситуации. Оптимайзер понятия не имеет о размере temporary table и считает cardinality (если не ошибаюсь) по рaзмеру блока БД а посему как ни крути а либо CARDINALITY либо DRIVING_SITE.

c коллекцией тоже можно, только я забыл что тут еще одну хитрость надо сделать:

Ну где " материализация сте на локале идёт по умолчанию и получается "выдёргивание" по индексируемому полю нужных значений"?

SY.: Workaround от Льюиса, конечно, рабочий, и в большинстве случаев он будет хорошо подходить, но у него есть и минус - remote statement будет вызываться столько же раз сколько к нему лукапов будет, а это, естественно, будет медленнее при большом кол-ве обращений (и само кол-во выполнений, и кол-во сетевых раундтрипов. )
- да, бесспорно, но это в 100 раз лучше, чем мое временное решение, где я в цикле PL/SQL вызывал Select, выдирая из ремоут таблицы по одной записи, т.е. еще и переключение контекста шло)

Для передачи информации между модулем и вызывающим блоком PL/SQL используются параметры.

Параметры модуля, являющиеся частью его заголовка (или сигнатуры), являются не менее важными компонентами модуля, чем находящиеся в нем исполняемые команды. Заголовок программы иногда называется контрактом между автором и пользователями. Конечно, автор должен позаботиться о том, чтобы модуль выполнял свою задачу. Но ведь модули создаются именно для того, чтобы их можно было вызывать повторно — в идеале более чем из одного модуля. Если список параметров плохо составлен, то другим программистам будет сложно применять такой модуль. В таком случае уже будет неважно, насколько хорошо он написан.

Многие разработчики не уделяют должного внимания наборам параметров своих модулей. Следующие рекомендации помогут вам сделать правильный выбор:

Количество параметров. Если процедура или функция имеет слишком мало параметров, это снижает ее универсальность; с другой стороны, слишком большое количество параметров усложняет ее повторное использование. Конечно, количество параметров в основном определяется требованиями программы, но существуют разные варианты их определения (например, несколько параметров можно объединить в одну запись).
Типы параметров. При выборе типа необходимо учитывать, для каких целей будут использоваться параметры: только для чтения, только для записи, для чтения и записи.
Имена параметров. Параметрам следует присваивать простые имена, отражающие их назначение в модуле.
Значения по умолчанию. Когда параметру следует задать значение по умолчанию, а когда нужно заставить программиста ввести определенное значение?

PL/SQL предоставляет много средств эффективного планирования. В этом разделе представлены все элементы определения параметров.

Определение параметров

Формальные параметры определяются в списке параметров программы. Синтаксис определения параметров очень близок к синтаксису объявления переменных в разделе объявлений блока PL/SQL . Впрочем, существуют два важных различия : во-первых, для параметров определяется режим передачи, которого нет у объявлений переменных; во-вторых, объявление параметра должно быть неограниченным.

Ограниченное объявление устанавливает некоторые ограничения для значений, которые могут присваиваться переменной, объявленной с этим типом. Например, следующее объявление переменной company_name ограничивает переменную 60 символами:

При объявлении параметра ограничивающая часть опускается:

Формальные и фактические параметры

Очень важно понимать различия между формальными и фактическими параметрами. Формальные параметры представляют собой имена, объявленные в списке параметров заголовка модуля, тогда как фактические параметры — это значения и выражения, которые помещаются в список параметров при вызове модуля.

Различия между формальными и фактическими параметрами нам также поможет понять функция total_sales . Ее заголовок выглядит так:

Формальные параметры total_sales :

company_id_in — первичный ключ (идентификатор компании).

status_in — статус заказов, включаемых в вычисление.

Эти формальные параметры не существуют за пределами модуля. Их можно рассматривать как своего рода «заместителей» реальных значений, передаваемых модулю при его использовании в программе (то есть фактических параметров).

При вызове total_sales необходимо предоставить два аргумента, которыми могут быть переменные, константы или литералы (для параметров в режимах OUT и IN OUT это должны быть переменные). В следующем примере переменная company_id содержит первичный ключ, указывающий на запись компании. В первых трех вызовах total_sales функции передаются жестко запрограммированные значения статуса заказов, а в последнем вызове статус не указан; в этом случае функция присваивает параметру status_in значение по умолчанию, указанное в заголовке:

При вызове total_sales вычисляются значения всех фактических параметров. Результаты вычислений присваиваются соответствующим формальным параметрам внутри функции (обратите внимание: это относится только к параметрам IN и IN OUT; параметры режима OUT не копируются).

Формальные параметры и соответствующие им фактические параметры (указанные при вызове) должны относиться к одинаковым или совместимым типам данных. Во

многих ситуациях PL/SQL выполняет преобразования данных автоматически, однако лучше по возможности обходиться без неявных преобразований. Используйте такие функции, как TO_CHAR и TO_DATE , чтобы точно знать, какие данные получает модуль.

Режимы передачи параметров

При определении параметра также указывается допустимый способ его применения. Он задается с помощью одного из трех указанных в таблице режимов.

Режим	Предназначение	Использование параметров
IN	Только для чтения	Значение параметра может применяться, но не может быть изменено в модуле. Если режим параметра не задан, используется режим IN
OUT	Только для записи	В модуле можно присвоить значение параметру, но нельзя использовать его. Впрочем, это «официальное» определение — на самом деле Oracle позволяет читать значение параметра OUT в подпрограмме
IN OUT	Для чтения и записи	В модуле можно использовать и изменять значение параметра

Режим параметра указывается непосредственно после его имени, но перед типом данных и необязательным значением по умолчанию. В следующем заголовке процедуры задействованы все три режима передачи параметров:

Процедура predict_activity принимает дату последнего действия ( last_date ) и описание этого действия ( task_desc_inout ). Возвращает она два значения: описание действия (возможно, модифицированное) и дату следующего действия (next_date_out). Поскольку параметр task_desk_inout передается в режиме IN OUT , программа может читать и изменять его значение.

Режим IN

Режим IN используется по умолчанию; если режим параметра не задан, параметр автоматически считается определенным в режиме IN. Тем не менее я рекомендую всегда указывать режим параметра, чтобы предполагаемое использование было явно указано в коде.

В заголовке программы параметрам IN могут присваиваться значения по умолчанию (см. раздел «Значения по умолчанию»).

Фактическим значением параметра IN может быть переменная, именованная константа, литерал или сложное выражение. Все следующие вызовы display_title допустимы:

А если вам потребуется передать данные из своей программы? В таком случае используйте параметр OUT или IN OUT — или рассмотрите возможность преобразования процедуры в функцию.

Режим OUT

Как вы, вероятно, уже поняли, параметр OUT по смыслу противоположен параметру IN. Параметры OUT могут использоваться для возвращения значений из программы вызывающему блоку PL/SQL. Параметр OUT сходен с возвращаемым значением функции, но он включается в список параметров, и количество таких параметров не ограничено (строго говоря, PL/SQL разрешает использовать до 64 000 параметров, но на практике это вряд ли можно считать ограничением).

В программе параметр OUT работает как неинициализированная переменная. Собственно, параметр OUT вообще не содержит никакого значения до успешного завершения вызванной программы (если только вы не использовали ключевое слово NOCOPY . Во время выполнения программы все операции присваивания параметру OUT в действительности выполняются с внутренней копией параметра. Когда программа успешно завершается и возвращает управление вызывающему блоку, значение локальной копии перемещается в параметр OUT. После этого значение становится доступным в вызывающем блоке PL/SQL.

У правил, относящихся к параметрам OUT , есть несколько практических следствий:

Параметрам OUT нельзя задавать значения по умолчанию. Значение параметра OUT может задаваться только в теле модуля.
Все операции присваивания параметрам OUT отменяются при инициировании исключения в программе. Так как значение параметра OUT присваивается только в случае успешного завершения программы, все промежуточные присваивания игнорируются. Если обработчик не перехватит исключение и не присвоит значение параметру OUT, параметр останется неизменным. Переменная сохранит значение, которое она имела до вызова программы.
Фактический параметр, соответствующий формальному параметру OUT, не может быть константой, литералом или выражением. Иначе говоря, он должен поддерживать присваивание.

Как упоминается выше в таблице, Oracle позволяет прочитать значение параметра OUT в подпрограмме. Это значение изначально всегда равно NULL , но после его присваивания в подпрограмме оно становится «видимым», как показывает следующий сценарий:

Режим IN OUT

В параметре IN OUT можно передавать значения программе и возвращать их на сторону вызова (либо исходное, неизменное значение, либо новое значение, заданное в программе). На параметры IN OUT распространяются два ограничения параметров OUT:

Параметр IN OUT не может быть константой, литералом или выражением.
Фактический параметр IN OUT должен быть переменной. Он не может быть константой, литералом или выражением, потому что эти форматы не могут использоваться PL/SQL в качестве приемника для размещения исходящих значений.

Других ограничений для параметров IN OUT нет.

Параметры IN OUT могут использоваться в обеих сторонах присваивания, потому что они работают как инициализированные переменные. PL/SQL не теряет значение параметра IN OUT в начале выполнения программы. Это значение может использоваться в программе там, где это необходимо.

Процедура combine_and_format_names объединяет имя и фамилию в заданном формате (« LAST, FIRST » или « FIRST LAST »). Процедура получает имя и фамилию, которые преобразуются к верхнему регистру. В ней продемонстрированы все три режима параметров:

Параметры имени и фамилии должны задаваться в режиме IN OUT . Параметр full_name_ out должен быть параметром OUT , потому что процедура возвращает результат объединения имени и фамилии. Наконец, параметр name_format_in, содержащий форматную строку, объявляется в режиме IN, потому что он описывает способ форматирования, но никак не изменяется в процедуре.

Каждый режим параметров имеет свои характеристики и предназначение. Тщательно выбирайте режим, назначаемый вашим параметрам, чтобы они правильно использовались в модулях.

Определяйте формальные параметры в режимах OUT и IN OUT только в процедурах. Функции должны возвращать всю свою информацию исключительно командой RETURN . Выполнение этой рекомендации упростит понимание и использование ваших подпрограмм. Кроме того, функции с параметрами OUT и IN OUT не могут вызываться из команд SQL .

Связывание формальных и фактических параметров в PL/SQL

Каким образом при выполнении программы определяется, какому формальному параметру должен соответствовать фактический параметр? PL/SQL предлагает два метода установления такого соответствия:

по позиции (неявное связывание);
по имени (явное связывание с указанием имени формального параметра и обозначения = >).

Позиционное связывание

Во всех приводившихся ранее примерах применялось связывание параметров в соответствии с их позицией. При использовании этого способа PL/SQL принимает во внимание относительные позиции параметров, то есть N-й фактический параметр в вызове программы связывается с N-м формальным параметром в заголовке программы. В следующем примере PL/SQL связывает первый фактический параметр : order. company_id с первым формальным параметром company_id_in , а второй фактический параметр 'N' — со вторым формальным параметром status_in :

Метод позиционного связывания параметров, безусловно, является более наглядным и понятным (рис. 3).

Рис. 3. Позиционное связывание параметров

связывание по имени

Чтобы установить соответствие параметров по имени, следует при вызове подпрограммы явно связать формальный параметр с фактическим. Для этого используется комбинация символов =>:

Поскольку имя формального параметра указано явно, PL/SQL уже не нужно учитывать порядок параметров. Поэтому в данном случае при вызове модуля не обязательно перечислять параметры в порядке их следования в заголовке. Функцию total_sales можно вызывать любым из двух способов:

В одном вызове можно комбинировать оба метода связывания фактических и формальных параметров:

При этом все «позиционные» параметры должны быть перечислены перед «именованными», как в приведенном примере. Позиционному методу необходима начальная точка для отсчета позиций, которой может быть только первый параметр. Если разместить «именованные» параметры перед «позиционными», PL/SQL собьется со счета. Оба вызова total_sales , приведенные ниже, недопустимы. В первой команде «именованные» параметры указываются до «позиционных», а во второй используется позиционная запись, но параметры перечислены в неверном порядке. В этом случае PL/SQL пытается преобразовать 'N' к типу NUMBER (для параметра company_id ):

Преимущества связывания по имени

Возникает резонный вопрос: зачем использовать связывание по имени? У него есть два основных преимущества:

Повышение информативности. При использовании связывания по имени вызов программы содержит сведения о формальных параметрах, с которыми ассоциируются фактические значения. Если вы не знаете, что делают модули, вызываемые приложением, наличие имен формальных параметров помогает понять назначение конкретного программного вызова. В некоторых средах разработки именно по этой причине связывание по имени считается стандартным. Эта причина особенно актуальна в том случае, когда имена формальных параметров строятся по схеме с присоединением суффикса режима. Только взглянув на вызов процедуры или функции, разработчик сразу видит, в каком направлении передаются те или иные данные.
Гибкость при описании параметров. Вы имеете возможность размещать параметры в удобном для работы порядке (конечно, это не значит, что при вызове аргументы можно перечислять так, как вам заблагорассудится!) и включать в список только те из них, которые действительно необходимы. В сложных приложениях иногда создаются процедуры с десятками параметров. Но как вы знаете, любой параметр, значение которого определяется по умолчанию, при вызове модуля может быть опущен. Разработчик может передать процедуре только те значения, которые необходимы для решения текущей задачи.

Давайте посмотрим, как реализовать все эти преимущества в программе. Возьмем для примера следующий заголовок:

Анализ списка параметров приводит нас к следующим выводам:

Минимальное количество аргументов, которые должны передаваться business_as_ usual , равно 3. Чтобы определить его, сложите количество параметров IN, не имеющих значения по умолчанию, с количеством параметров OUT и IN OUT.
При использовании связывания по позиции программа может вызываться с четырьмя или пятью аргументами, потому что последний параметр объявлен в режиме IN со значением по умолчанию.
Для хранения значений, возвращаемых параметрами OUT и IN OUT, понадобятся как минимум две переменные.

С таким списком параметров программа может вызываться несколькими способами:

Связывание только по позиции: задаются все фактические параметры. Обратите внимание, как трудно вспомнить, какой формальный параметр связывается с каждым из этих значений (и каков его смысл):

Связывание только по позиции: минимальное количество фактических параметров. Понять смысл вызова все еще нелегко:

Связывание только по имени с сохранением исходного порядка. Обратите внимание: смысл вызова business_as_usual вполне понятен и не требует пояснений:

Все параметры IN пропускаются, и для них используются значения по умолчанию (другая важная возможность связывания по имени):

Связывание по имени с изменением порядка следования фактических параметров и передачей неполного списка:

Объединение связывания по имени и по позиции. Список параметров начинается со связывания по позиции, а после перехода на связывание по имени вернуться к позиционному связыванию уже не удастся:

Как видите, механизм передачи аргументов в PL/SQL весьма гибок. Как правило, связывание по имени способствует написанию кода, который лучше читается и создает меньше проблем с сопровождением. От вас потребуется совсем немного: найти и записать имена параметров.

Квалификатор режима параметра NOCOPY

PL/SQL предоставляет возможность изменить определение параметра при помощи конструкции NOCOPY . NOCOPY требует, чтобы компилятор PL/SQL не создавал копии аргументов OUT и IN — и в большинстве случаев компилятор это требование удовлетворяет. NOCOPY используется прежде всего для повышения эффективности передачи больших конструкций (например, коллекций) в аргументах IN OUT .

Значения по умолчанию

Как было показано в предыдущих примерах, параметрам IN можно задать значения по умолчанию. Если параметр IN имеет значение по умолчанию, включать этот параметр в вызов программы не обязательно. Значение по умолчанию параметра вычисляется и используется программой только в том случае, если параметр не был включен в список при вызове. Конечно, для всех параметров IN OUT фактические параметры должны включаться в список. Значение по умолчанию определяется для параметра так же, как для объявляемой переменной. Предусмотрены два способа задания значений по умолчанию — с ключевым словом DEFAULT и с оператором присваивания ( := ):

Значения по умолчанию позволяют вызывать программы с разным количеством фактических параметров. Программа использует значения по умолчанию для всех незаданных параметров, а для параметров в списке значения по умолчанию заменяются указанными значениями. Несколько примеров разных вариантов использования связывания по позиции:

В первом вызове оба параметра задаются явно. Во втором вызове задается только первый фактический параметр, поэтому born_at_in присваивается текущая дата и время. В третьем вызове параметры вообще не указаны, поэтому круглые скобки отсутствуют (то же относится и к последнему вызову, в который включены пустые круглые скобки). Оба значения по умолчанию используются в теле процедуры.

Чтобы пропустить начальные параметры, имеющие значения по умолчанию, необходимо использовать связывание по имени. Включая имена формальных параметров, можно указать только те параметры, для которых необходимо передать значения:

Мы рассмотрели несколько примеров использования параметров с NDS . Давайте познакомимся с различными правилами и специальными ситуациями, которые могут вам встретиться при передаче параметров.

SQL -запросу могут передаваться только выражения (литералы, переменные, сложные выражения), заменяющие формальные параметры в строке значениями данных. Не допускается передача имен элементов схемы (таблиц, столбцов и т. д.) или целых фрагментов кода SQL (например, условий WHERE ), которые должны строиться посредством конкатенации.

Допустим, вы хотите создать процедуру для очистки заданного представления или таблицы. Первая версия может выглядеть примерно так:

При упрощении процедуры до следующего вида:

Почему же в NDS (как, впрочем, и в пакете DBMS_SQL ) имеется такое ограничение? При передаче строки команде EXECUTE IMMEDIATE исполняющее ядро должно прежде всего выполнить синтаксический анализ команды, чтобы убедиться в правильности ее определения. PL/SQL может определить, что следующая команда определена правильно, даже не зная значения параметра :xyz:

Но корректность следующий команды PL/SQL проверить не сможет:

По этой причине в данной ситуации необходимо использовать конкатенацию:

Режимы передачи параметров в SQL

При передаче значений параметров команды SQL можно использовать один из трех режимов: IN ( только чтение, действует по умолчанию), OUT (только запись) или IN OUT (чтение и запись). При выполнении динамического запроса все параметры команды SQL , за исключением параметра в секции RETURNING , должны передаваться в режиме IN :

Параметры подстановки команды SQL , передаваемые в режимах OUT и IN OUT , используются прежде всего при выполнении динамического PL/SQL . В этом случае режимы передачи параметров соответствуют аналогичным режимам обыкновенных программ PL/SQL , а также использованию переменных в динамических блоках PL/SQL . Несколько общих рекомендаций, касающихся использования секции USING при выполнении динамического PL/SQL :

В качестве параметра подстановки, передаваемого в режиме IN , может быть задан любой элемент соответствующего типа: литеральное значение, именованная константа, переменная или сложное выражение. Такой элемент сначала вычисляется, а затем передается в динамический блок PL/SQL .
Для значения параметра динамической команды в режиме OUT или IN OUT следует объявить переменную.
Значения можно подставлять только вместо тех параметров динамического блока PL/SQL , тип которых поддерживается SQL . Например, если параметр процедуры имеет тип BOOLEAN, его значение нельзя задать или считать с помощью секции USING .

Это ограничение частично снято в версии 12.1 и выше. Теперь разрешается подстановка многих типов PL/SQL , включая типы записей и коллекций, но подстановка BOOLEAN по-прежнему запрещена.

Давайте рассмотрим механизм передачи параметров на примерах. Вот заголовок процедуры с параметрами IN, OUT и IN OUT :

Пример блока с динамическим вызовом этой процедуры:

Поскольку процедура имеет четыре параметра, в секции USING также должно быть указано четыре элемента. Для первых двух параметров, передаваемых в режиме IN , следует задать литеральные значения или выражения, а следующие два элемента должны быть именами переменных, так как для них заданы режимы OUT и IN OUT .

Но что, если два и более формальных параметра имеют одинаковые имена?

Дублирование формальных параметров

При выполнении динамической команды SQL связь между формальными и фактическими параметрами устанавливается в соответствии с их позициями. Однако интерпретация одноименных параметров зависит от того, какой код используется — SQL или PL/SQL .

При выполнении динамической команды SQL (DML - или DDL-строки, не оканчивающейся точкой с запятой) параметр подстановки нужно задать для каждого формального параметра, даже если их имена повторяются.
При выполнении динамического блока PL/SQL (строки, оканчивающейся точкой с запятой) нужно указать параметр подстановки для каждого уникального формального параметра.

Далее приведен пример динамической команды SQL с повторяющимися формальными параметрами. Особое внимание обратите на повторяющийся параметр подстановки val_in в секции USING :

А вот динамический блок PL/SQL с повторяющимися формальными параметрами — для него в секции USING параметр val_in задан только один раз:

Передача значений NULL

При передаче NULL в качестве параметра подстановки — например, как в команде:

Что же делать, если вам потребуется передать в динамический код значение NULL ? Это можно сделать двумя способами.

Во-первых, значение можно скрыть в переменной, для чего проще всего использовать неинициализированную переменную подходящего типа:

Во-вторых, с помощью функции преобразования типа можно явно преобразовать NULL в типизированное значение:

Oracle имеет два собственных типа переменных. Переменные связи (bind variables) предназначены для хранения отдельных значений присваиваемых при исполнении команды.
Переменные подстановки (Substitution variables) позволяют хранить вводимые вручную перед исполнением команды значения.
Переменные подстановки обозначаются префиксом & . Распознав такую переменную в процессе исполнения Oracle просит ввести ее значение и после ввода продолжает исполнение запроса пользователя. Если символьная переменная в тексте программы записана в одинарных кавычках (например, ‘YEAR_SAL’), при вводе кавычки не ставятся.
Переменная подстановки с двумя амперсендами (например, &&NUM1) запрашивается один раз за сеанс.
Поскольку значения переменных подстановки запрашиваются перед исполнением запроса, то с их помощью может вводиться и часть текста команды.
Например, команда SELECT &STOLBETS . позволяет при каждом исполнении менять имя выводимого столбца.

Команда SQL*Plus DEF[INE] позволяет задать значение переменной на сеанс или до выполнения команды UNDEF[INE] отменяющей определение.
Пример:

DEFINE REM=SAL*12
SELECT ENAME, JOB, REM FROM EMP;

Замечание 1: Команда DEF выдает все определенные переменные, команда DEF <имя> выдает значение указанной переменной, если же она не определена, выдается undefinite.
Команда ACCEPT позволяет определять переменные и присваивать им значения в интерактивном режиме.
Формат команды:

ACC[EPT] имя_переменной [ NUMBER|CHAR ] [ PROMPT|NOPROMPT ‘текст_подсказки’] [ HIDE ]

NUMBER|CHAR -- тип переменной;
PROMPT -- высвечивание подсказки;
HIDE -- скрывает вводимый текст; удобен при вводе паролей.

Для командных файлов, содержащих переменные подстановки используют переменные специального вида. Их девять, имена от 1 до 9. Команда START в этом случае используется в формате:

причем первый фактический параметр заменяет &1, второй &2 и т.д.

Упражнения

1. Напишем команду, использующую приглашение во время выполнения. Назначение -- перечислить всех сотрудников. принятых на работу в период между двумя произвольными датами.

SELЕСТ ENAME, JOB, MGR, DEPTNO FRОМ EMP WHERE JOB = '&JOB' ;

В появившемся запросе "Enter value for job:" введите MANAGER и в окне SQL*Plus Вы должны получить следующие значения:

2. Определим переменную, представляюшую выражение для вычисления полных годовых начислений сотрудникам. Используем эту переменную в команде, которая находит всех сотрудников, чьи годовые начисления не меньше $30000.

В появившемся запросе "Enter value for job:" введите MANAGER и в окне SQL*Plus Вы должны получить следующие значения:

Термином «динамический SQL» обозначаются команды SQL, которые конструируются и вызываются непосредственно во время выполнения программы. Статическими называются жестко закодированные команды SQL, которые не изменяются с момента компиляции программы. «Динамическим PL/SQL» называют целые блоки кода PL/SQL, которые строятся динамически, а затем компилируются и выполняются.

Пожалуй, написание динамических команд SQL и динамических программ PL/SQL было самым интересным делом из всего, что я когда-либо делал на языке PL/SQL. Конструирование и динамическое выполнение обеспечивает невероятную гибкость. У разработчика появляется возможность создавать обобщенный код с широким спектром применения. Несмотря на это, динамический SQL следует применять лишь там, где это необходимо; решения со статическим SQL всегда являются предпочтительными, потому что динамические решения более сложны, создают больше проблем с отладкой и тестированием, обычно медленнее работают и усложняют сопровождение. Что же можно делать с динамическими конструкциями SQL и PL/SQL ? Лишь несколько идей:

Выполнение команд DDL. Со статическим кодом SQL в PL/SQL могут выполняться только запросы и команды DML. А если вы захотите создать таблицу или удалить индекс? Используйте динамический SQL!
Поддержка специализированных запросов и требований к обновлению веб-приложений. К интернет-приложениям часто предъявляется одно стандартное требование: пользователь должен иметь возможность выбрать столбцы, которые он желает видеть, и изменить порядок просмотра данных (конечно, пользователь может и не понимать, что именно при этом происходит).
Оперативное изменение бизнес-правил и формул. Вместо того чтобы жестко фиксировать бизнес-правила в коде, можно разместить соответствующую логику в таблицах. Во время выполнения программа генерирует и выполняет код PL/SQL, необходимый для применения правил.

Начиная с Oracle7, поддержка динамического SQL осуществлялась в виде встроенного пакета DMBS_SQL . В Oracle8i для этого появилась еще одна возможность — встроенный динамический SQL (Native Dynamic SQL, NDS). NDS интегрируется в язык PL/SQL; пользоваться им намного удобнее, чем DBMS_SQL . Впрочем, для некоторых ситуаций

лучше подходит DBMS_SQL . На практике в подавляющем большинстве случаев NDS является более предпочтительным решением.

Команды NDS в PL/SQL

Главным достоинством NDS является его простота. В отличие от пакета DBMS_SQL, для работы с которым требуется знание десятка программ и множества правил их использования, NDS представлен в PL/SQL единственной новой командой EXECUTE IMMEDIATE , которая немедленно выполняет заданную команду SQL, а также расширением существующей команды OPEN FOR , позволяющей выполнять многострочные динамические запросы.

Команды EXECUTE IMMEDIATE и OPEN FOR не будут напрямую доступны в Oracle Forms Builder и Oracle Reports Builder до тех пор, пока версия PL/SQL этих инструментов не будет обновлена до Oracle8i и выше. Для более ранних версий придется создавать хранимые программы, скрывающие вызовы этих конструкций; эти хранимые программы могут выполняться в клиентском коде PL/SQL.

Команда EXECUTE IMMEDIATE

Команда EXECUTE IMMEDIATE используется для немедленного выполнения заданной команды SQL. Она имеет следующий синтаксис:

Здесь строка_SQL — строковое выражение, содержащее команду SQL или блок PL/SQL; переменная — переменная, которой присваивается содержимое поля, возвращаемого запросом; запись — запись, основанная на пользовательском типе или типе %ROWTYPE , принимающая всю возвращаемую запросом строку; аргумент — либо выражение, значение которого передается команде SQL или блоку PL/SQL, либо идентификатор, являющийся входной и/или выходной переменной для функции или процедуры, вызываемой из блока PL/SQL. Секция INTO используется для однострочных запросов. Для каждого значения столбца, возвращаемого запросом, необходимо указать переменную или поле записи совместимого типа. Если INTO предшествует конструкция BULK COLLECT , появляется возможность выборки множественных строк в одну или несколько коллекций. Секция USING предназначена для передачи аргументов строке SQL. Она используется с динамическим SQL и PL/SQL, что и позволяет задать режим параметра. Этот режим актуален только для PL/SQL и секции RETURNING . По умолчанию для параметров используется режим IN (для команд SQL допустима только эта разновидность аргументов).

Команда execute immediate может использоваться для выполнения любой команды SQL или блока PL/SQL. Строка может содержать формальные параметры, но они не могут связываться с именами объектов схемы (например, именами таблиц или столбцов).

При выполнении команды DDL в программе также происходит закрепление операции. Если вы не хотите, чтобы закрепление, обусловленное DDL, отражалось на текущих изменениях в других частях приложения, поместите динамическую команду DDL в процедуру автономной транзакции. Пример такого рода приведен в файле auton_ddl.sql.

При выполнении команды исполняющее ядро заменяет в SQL-строке формальные параметры (идентификаторы, начинающиеся с двоеточия — например, :salary_value )

фактическими значениями параметров подстановки в секции USING . Не допускается передача литерала NULL — вместо него следует указывать выражение соответствующего типа, результат вычисления которого может быть равен NULL .

NDS поддерживает все типы данных SQL. Переменные и параметры команды могут быть коллекциями, большими объектами (LOB), экземплярами объектных типов, документами XML и т. д. Однако NDS не поддерживает типы данных, специфические для PL/SQL, такие как BOOLEAN , ассоциативные массивы и пользовательские типы записей. С другой стороны, секция INTO может содержать запись PL/SQL, количество и типы полей которой соответствуют значениям, выбранным динамическим запросом. Рассмотрим несколько примеров.

Проще не бывает, верно?

О Создание хранимой процедуры, выполняющей любую команду DDL:

При наличии процедуры exec_ddl тот же индекс может быть создан следующим образом:

Получение количества строк в произвольной таблице для заданного предложения WHERE :

Таким образом, нам больше не понадобится писать команду SELECT COUNT (*) ни в SQI*Plus , ни в программах PL/SQL . Она заменяется следующим блоком кода:

Изменение числового значения в любом столбце таблицы employees:

Безусловно, для такой гибкости объем кода получился совсем небольшим! В этом примере показано, как используется подстановка: после разбора команды UPDATE ядро PL/SQL заменяет в ней формальные параметры (:the_value, :lo и :hi) значениями переменных. Также обратите внимание, что в этом случае атрибут курсора SQL%ROWCOUNT используется точно так же, как при выполнении статических команд DML. Выполнение разных блоков кода в одно время в разные дни. Имя каждой программы строится по схеме ДЕНЬ_set_sd^edule . Все процедуры получают четыре аргумента: при вызове передается код работника employee_id и час первой встречи, а процедура возвращает имя работника и количество встреч в заданный день. Задача решается с использованием динамического PL/SQL:

Привязка значения BOOLEAN , специфического для PL/SQL , командой EXECUTE IMMEDIATE (новая возможность 12c):

Как видите, команда EXECUTE IMMEDIATE позволяет исключительно легко выполнять динамические команды SQL и блоки PL/SQL с удобным синтаксисом.

Команда OPEN FOR

Команда OPEN FOR изначально не была включена в PL/SQL для NDS ; она появилась в Oracle7 и предназначалась для работы с курсорными переменными. Затем ее синтаксис был расширен для реализации многострочных динамических запросов. При использовании пакета DBMS_SQL реализация многострочных запросов получается очень сложной: приходится производить разбор и подстановку, отдельно определять каждый столбец, выполнять команду, выбирать сначала строки, а затем — последовательно значения каждого столбца. Код получается весьма громоздким.

Для динамического SQL разработчики Oracle сохранили существующий синтаксис OPEN, но расширили его вполне естественным образом:

Здесь курсорная переменная — слаботипизированная курсорная переменная; хост_ переменная — курсорная переменная, объявленная в хост-среде PL/SQL, например в программе OCI (Oracle Call Interface); cmpoка SQL — команда SELECT , подлежащая динамическому выполнению.

Курсорные переменные рассматривались в этом блоге. Здесь мы подробно расскажем об их использовании с NDS .

В следующем примере объявляется тип REF CURSOR и основанная на нем переменная- курсор, а затем с помощью команды OPEN FOR открывается динамический запрос:

После того как запрос будет открыт командой OPEN FOR , синтаксис выборки записи, закрытия курсорной переменной и проверки атрибутов курсора ничем не отличается от синтаксиса статических курсорных переменных и явных курсоров.

Давайте поближе познакомимся с командой OPEN FOR . При выполнении OPEN FOR ядро PL/SQL :

связывает курсорную переменную с командой SQL, заданной в строке запроса;
вычисляет значения параметров и заменяет ими формальные параметры в строке запроса;
выполняет запрос;
идентифицирует результирующий набор;
устанавливает курсор на первую строку результирующего набора;
обнуляет счетчик обработанных строк, возвращаемый атрибутом %rowcount . Обратите внимание: параметры подстановки, заданные в секции USING , вычисляются только при открытии курсора. Это означает, что для передачи тому же динамическому запросу другого набора параметров нужно выполнить новую команду OPEN FOR .

Для выполнения многострочного запроса (то есть запроса, возвращающего набор строк) необходимо:

объявить тип ref cursor (или использовать встроенный тип sys_refcursor );
объявить на его основе курсорную переменную;
открыть курсорную переменную командой OPEN FOR ;
с помощью команды fetch по одной извлечь записи результирующего набора;
при необходимости проверить значения атрибутов ( %found, %notfound, %rowcount, %isopen) ;
закрыть курсорную переменную обычной командой Как правило, после завершения работы с курсорной переменной следует явно закрыть ее.

Следующая простая программа выводит значения поля заданной таблицы в строках, отбираемых с помощью секции WHERE (столбец может содержать числа, даты или строки, файл showcol.sp):

Примерный результат выполнения этой процедуры выглядит так:

Столбцы даже можно комбинировать:

Выборка в переменные или записи

Команда FETCH в процедуре showcol из предыдущего раздела осуществляет выборку в отдельную переменную. Также возможна выборка в серию переменных:

Работа с длинным списком переменных в списке FETCH может быть громоздкой и недостаточно гибкой; вы должны объявить переменные, поддерживать синхронизацию этого набора значений в команде FETCH и т. д. Чтобы упростить жизнь разработчика, NDS позволяет осуществить выборку в запись, как показано в следующем примере:

Конечно, во многих ситуациях выполнение команды SELECT * нежелательно; если ваша таблица содержит сотни столбцов, из которых вам нужны два-три, эта команда крайне неэффективна. Лучше создать тип записи, соответствующий разным требованиям. Эти структуры лучше всего разместить в спецификации пакета, чтобы их можно было использовать во всем приложении. Вот один из таких пакетов:

С таким пакетом приведенный выше код можно переписать следующим образом:

Секция USING в OPEN FOR

Как и в случае с командой EXECUTE IMMEDIATE , при открытии курсора можно передать аргументы. Для запроса можно передать только аргументы IN. Аргументы также повышают эффективность SQL, упрощая написание и сопровождение кода. Кроме того, они могут радикально сократить количество разобранных команд, хранящихся в общей памяти SGA, а это повышает вероятность того, что уже разобранная команда будет находиться в SGA в следующий раз, когда она вам потребуется.

Вернемся к процедуре showcol. Эта процедура получает полностью обобщенную секцию WHERE . Допустим, действуют более специализированные требования: я хочу вывести (или иным образом обработать) всю информацию столбцов для строк, содержащих столбец даты со значением из некоторого диапазона. Другими словами, требуется обеспечить поддержку запроса:

а также запроса:

Также нужно проследить за тем, чтобы компонент времени столбца даты не учитывался в условии WHERE .

Заголовок процедуры выглядит так:

Теперь команда OPEN FOR содержит два формальных параметра и соответствующую секцию USING :

Команда построена таким образом, что при отсутствии конечной даты секция WHERE возвращает строки, у которых значение в столбце даты совпадает с заданным значением dt1 . Остальной код процедуры showcol остается неизменным, не считая косметических изменений в выводе заголовка.

Следующий вызов новой версии showcol запрашивает имена всех работников, принятых на работу в 1982 году:

О четырех категориях динамического SQL

Итак, мы рассмотрели две основные команды, используемые для реализации динамического SQL в PL/SQL . Теперь пришло время сделать шаг назад и рассмотреть четыре разновидности (категории) динамического SQL , а также команды NDS , необходимые для реализации этих категорий. Категории и соответствующие команды NDS перечислены в табл. 1.

Таблица 1. Четыре категории динамического SQL

тип	описание	Команды NDS
Категория 1	Без запросов; только команды DDL и команды UPDATE, INSERT, MERGE и DELETE без параметров	EXECUTE IMMEDIATE без секций USING и INTO
Категория 2	Без запросов; только команды DDL и команды UPDATE, INSERT, MERGE и DELETE с фиксированным количеством параметров	EXECUTE IMMEDIATE с секцией USING
Категория 3 (одна строка)	Запросы (SELECT) с фиксированным количеством столбцов и параметров, с выборкой одной строки данных	EXECUTE IMMEDIATE с секциями USING и INTO
Категория 3 (несколько строк)	Запросы (SELECT) с фиксированным количеством столбцов и параметров, с выборкой одной или нескольких строк данных	EXECUTE IMMEDIATE с секциями USING и BULK COLLECT INTO или OPEN FOR с динамической строкой
Категория 4	Команда, в которой количество выбранных столб- цов (для запроса) или количество параметров неизвестно до стадии выполнения	Для категории 4 необходим пакет DBMS_SQL

Категория 1

Следующая команда DDL является примером динамического SQL категории 1:

Команда UPDATE также относится к динамическому SQL категории 1, потому что единственным изменяемым аспектом является имя таблицы — параметры отсутствуют:

Категория 2

Если заменить оба жестко фиксированных значения в предыдущей команде DML формальными параметрами (двоеточие, за которым следует идентификатор), появляется динамический SQL категории 2:

Секция USING содержит значения, которые будут подставлены в строку SQL после разбора и перед выполнением.

Категория 3

Команда динамического SQL категории 3 представляет собой запрос с фиксированным количеством параметров (или вообще без них). Вероятно, чаще всего вы будете создавать команды динамического SQL именно этого типа. Пример:

Здесь я запрашиваю всего два столбца из таблицы employees и сохраняю их значения в двух локальных переменных из секции INTO . Также используется один параметр. Так как значения этих компонентов являются статическими на стадии компиляции, я использую динамический SQL категории 3.

Категория 4

Наконец, рассмотрим самый сложный случай: динамический SQL категории 4. Возьмем предельно обобщенный запрос:

На момент компиляции кода я понятия не имею, сколько столбцов будет запрашиваться из таблицы employees . Возникает проблема: как написать команду FETCH INTO , которая будет обеспечивать подобную изменчивость? Есть два варианта: либо вернуться к DBMS_SQL для написания относительно тривиального (хотя и объемистого) кода, либо переключиться на исполнение динамических блоков PL/SQL .

К счастью, ситуации, требующие применения категории 4, встречаются редко.

Читайте также: