Oracle вызов процедуры с параметром out

Обновлено: 04.07.2024

На уровне схемы
Внутри упаковки
Внутри блока PL / SQL

На уровне схемы подпрограмма является отдельной подпрограммой . Он создается с помощью CREATE PROCEDURE или оператора CREATE FUNCTION. Он хранится в базе данных и может быть удален с помощью оператора DROP PROCEDURE или DROP FUNCTION.

В этой главе будут рассмотрены важные аспекты процедуры PL / SQL . Мы обсудим функцию PL / SQL в следующей главе.

Части подпрограммы PL / SQL

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

Декларативная часть

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

Исполняемая часть

Это обязательная часть и содержит операторы, которые выполняют назначенное действие.

Обработка исключений

Это опять необязательная часть. Он содержит код, который обрабатывает ошибки во время выполнения.

Декларативная часть

Исполняемая часть

Это обязательная часть и содержит операторы, которые выполняют назначенное действие.

Обработка исключений

Это опять необязательная часть. Он содержит код, который обрабатывает ошибки во время выполнения.

Создание процедуры

Процедура создается с помощью оператора CREATE OR REPLACE PROCEDURE . Упрощенный синтаксис для оператора CREATE OR REPLACE PROCEDURE выглядит следующим образом:

имя-процедуры определяет имя процедуры.

Опция [ИЛИ ЗАМЕНА] позволяет модифицировать существующую процедуру.

Список необязательных параметров содержит имя, режим и типы параметров. IN представляет значение, которое будет передано извне, а OUT представляет параметр, который будет использоваться для возврата значения вне процедуры.

Тело процедуры содержит исполняемую часть.

Ключевое слово AS используется вместо ключевого слова IS для создания отдельной процедуры.

имя-процедуры определяет имя процедуры.

Опция [ИЛИ ЗАМЕНА] позволяет модифицировать существующую процедуру.

Тело процедуры содержит исполняемую часть.

CREATE OR REPLACE PROCEDURE greetings AS BEGIN dbms_output . put_line ( 'Hello World!' ); END ; /

Когда приведенный выше код выполняется с использованием подсказки SQL, он даст следующий результат:

Выполнение отдельной процедуры

Автономная процедура может быть вызвана двумя способами:

Использование ключевого слова EXECUTE

Вызов имени процедуры из блока PL / SQL

Использование ключевого слова EXECUTE

Вызов имени процедуры из блока PL / SQL

Удаление автономной процедуры

Режимы параметров в подпрограммах PL / SQL

Параметр IN позволяет передавать значение в подпрограмму. Это параметр только для чтения . Внутри подпрограммы параметр IN действует как константа. Ему нельзя присвоить значение. Вы можете передать константу, литерал, инициализированную переменную или выражение в качестве параметра IN. Вы также можете инициализировать его значением по умолчанию; тем не менее, в этом случае он исключается из вызова подпрограммы. Это режим передачи параметров по умолчанию. Параметры передаются по ссылке .

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

Параметр IN OUT передает начальное значение подпрограмме и возвращает обновленное значение вызывающей стороне. Ему может быть присвоено значение, и значение может быть прочитано.

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

Пример режима IN & OUT 1

Эта программа находит минимум двух значений. Здесь процедура берет два числа, используя режим IN, и возвращает их минимум, используя параметры OUT.

Пример 2 в режиме IN & OUT

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

Методы для передачи параметров

Фактические параметры могут быть переданы тремя способами:

Позиционная запись
Именное обозначение
Смешанная запись

Позиционная запись

В позиционной нотации первый фактический параметр заменяется первым формальным параметром; второй фактический параметр заменяется вторым формальным параметром и так далее. Таким образом, a заменяет x, b заменяет y, c заменяет z и d заменяет m .

Именное обозначение

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

Смешанная запись

В смешанной нотации вы можете смешивать обе нотации в вызове процедуры; однако позиционная нотация должна предшествовать именованной нотации.

Идем дальше по пути познания процедур. Итак, мы с вами рассмотрели тип передаваемых параметров - IN и OUT. Существует еще один тип параметров процедур, а именно как вы уже, наверное, догадываетесь это тип IN OUT - то есть при объявлении это выглядит примерно вот так:

Вот с этим параметром давайте разберемся подробнее. Давайте сформулируем правила, для такого тип параметров.

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

Теперь давайте создадим следующую процедуру примерно вот такого вида:

Не торопитесь ее компилировать, давайте порасcуждаем. Итак, теперь NUM параметр IN OUT - как видно второй SELECT принимает его как параметр и возвращает через него значение. То есть принимает условие и вернет результат. Удобно, не так ли? Компилируем:

Вот теперь если сделать вот такой вызов:

Не трудно заметить, что первое значение фактического параметра FRNM равно 2103, а вот вернул он значение 105 - т.е. один параметр выполнил двойную работу. Вполне не плохо. К слову, если вы попытаетесь сделать что-то вроде:

То получите в ответ следующее:

В литерале 2103 нельзя сохранить возвращаемый результат, так как литерал не хранится в памяти после использования. Ну, что я думаю и так очевидно. Надеюсь, с данным типом параметра теперь вам все стало ясно! :)

Давайте теперь остановимся на понятии тела (body) процедуры. Тело процедуры содержит собственно исполняемый код и располагается между ключевыми словами BEGIN и EXCEPTION. Далее идет блок EXCEPTION и END, в котором располагается собственно обработчик исключительной ситуации. Раздел объявлений располагается между операторами CREATE и IS или AS (вот и снова язык Ada!) - а выглядит это все примерно вот так:

Такой скелет имеет процедура и все что к ней прилагается. Запомните это получше! :)

Для передачи информации между модулем и вызывающим блоком 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 присваивается текущая дата и время. В третьем вызове параметры вообще не указаны, поэтому круглые скобки отсутствуют (то же относится и к последнему вызову, в который включены пустые круглые скобки). Оба значения по умолчанию используются в теле процедуры.

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

Programming Language for SQL (PL/SQL) – решение Oracle, предоставляющее средства, которые позволяют выполнять сложную обработку информации (plsql).

Программные единицы PL/SQL

Структура блока PL/SQL

Блок PL/SQL может содержать до четырех разделов, однако только один из них является обязательным.

Заголовок. Используется только в именованных блоках, определяет способ вызова именованного блока или программы. Не обязателен.
Раздел объявлений. Содержит описания переменных, курсоров и вложенных блоков, на которые имеются ссылки в исполняемом разделе и разделе исключений. Не обязателен.
Исполняемый раздел. Команды, выполняемые ядром PL/SQL во время работы приложения. Обязателен.
Раздел исключений. Обрабатывает исключения (предупреждения и ошибки). Не обязателен.

Хранимая процедура

Хранимая процедура — это определенный набор инструкций, написанных на языке PL/SQL. Вызов процедуры приводит к выполнению содержащихся в ней инструкций. Процедура хранится в базе данных, поэтому и называется хранимой. Процедура состоит из двух основных частей:спецификации и тела.
Спецификация процедуры (procedure specification) включает в себя имя процедуры и описание ее входных и выходных данных. Эти входные и выходные данные называются формальными параметрами (formal parameters) или формальными аргументами (formal arguments). Если при вызове процедуры указываются параметры командной строки или другие входные данные, эти значения называются фактическими (actual) параметрами или фактическими аргументами.
Пример спецификации

increase_salary_find_tax ( increase_percent IN NUMBER : = 7 , sal IN OUT NUMBER , tax OUT NUMBER )

Здесь мы видим процедуру с тремя формальными параметрами. Слово IN после имени параметра означает, что при вызове процедура может считать из этого параметра входное значение. Слово OUT означает, что процедура может использовать данный параметр для возврата значения в ту программу, из которой она была вызвана. Комбинация IN OUT после имени параметра говорит о том, что параметр может использоваться как для передачи значения процедуре, так и для возврата значения.
Тело процедуры (procedure body) — это блок PL/SQL-кода.

Хранимые функции

Функция PL/SQL похожа на процедуру PL/SQL: она также имеет спецификацию и тело. Главное различие между процедурой и функцией в том, что функция предназначена для возврата значения, которое может использоваться в более крупном SQL-Операторе.

Триггеры

Триггер — это процедура PL/SQL, которая выполняется автоматически, когда происходит некоторое заданное событие, называемое триггерным событием (triggering event).
Например, можно писать триггеры, срабатывающие при выполнении над таблицей операций INSERT, UPDATE или DELETE; при выдаче команд DDL; при входе пользователя в систему или его выходе из системы; при запуске или останове базы данных; при возникновении ошибок.
Между триггерами и процедурами PL/SQL есть три различия:

Триггеры нельзя вызывать из кода программы. Oracle вызывает их автоматически в ответ на определенное событие.
Триггеры не имеют списка параметров.
Спецификация триггера немного отличается от спецификации процедуры.

Структура блока PL/SQL

Базовый блок PL/SQL состоит из четырех секций:

секции заголовка (header section);
необязательной секции объявлений (declaration section);
выполняемой секции (execution section);
необязательной секции исключений (exception section).

Анонимный блок (anonumous block) — это блок PL/SQL без секции заголовка, иначе говоря, секции имени, поэтому он и называется анонимным. Анонимные блоки могут выполняться из SQL*Plus и использоваться в функциях, процедурах и триггерах PL/SQL. Вспомните, что сами процедуры, функции и триггеры также состоят из базовых блоков. Это означает, что базовый блок можно помещать в другой базовый блок.

Секция объявлений

Секция объявлений не является обязательной. В случае использования она начинается после секции заголовка и оканчивается перед ключевым, словом BEGIN. Эта секция содержит объявления переменных, констант, курсоров, исключений, функций и процедур PL/SQL, которые будут использоваться в выполняемой секции и секции исключений. Все объявления переменных и констант должны размещаться до объявлений функций или процедур. О переменных и константах PL/SQL будет подробно рассказано в следующем разделе. Объявление сообщает PL/SQL о том, что нужно создать переменную, константу, курсор, функцию или процедуру согласно приведенной спецификации. Когда выполнение базового блока завершается, все элементы, объявленные в секции объявлений, перестают существовать. Элементы, объявленные в секции объявлений базового блока, могут использоваться только в пределах этого блока. Одним словом, все, что находится в секции объявлений, при надлежит блоку и может использоваться только внутри него, а следовательно, существует только на протяжении его времени жизни. Часть кода, в которой может использоваться переменная, называется областью видимости (scope).

Выполняемая секция

Выполняемая секция начинается с ключевого слова BEGIN и заканчивается либо ключевым словом EXCEPTION, если присутствует секция исключений, либо ключевым словом END, за которым следуют необязательное имя функции или процедуры и точка с запятой. Выполняемая секция содержит один и более PL/SQL-операторов, выполняемых при передаче управления данному блоку. Структура выполняемой секции показана ниже.

В выполняемом коде PL/SQL чаще всего встречается оператор присваивания (:=). Он указывает, что нужно вычислить выражение справа и поместить результат в переменную слева.

Секция исключений

действия , предпринимаемые при возникновении исключения действия , предпринимаемые при возникновении исключения

Секция исключений начинается с ключевого слова EXCEPTION и продолжается до конца блока. Каждому исключению соответствует оператор WHEN имя_исключения, указывающий, что должно быть сделано при возникновении данного исключения. Все операторы, находящиеся между оператором, вызвавшим ошибку, и секцией исключений, игнорируются. Выполнение оператора, указанного в секции исключений, называется обработкой исключения (exception handling). Процесс, включающий в себя обнаружение ошибки, определение, какое исключение описывает ее наилучшим образом, и передачу PL/SQL информации, позволяющей найти соответствующий код в секции исключений, называется возбуждением исключения (raising exception).

Переменные

Переменные — это именованные контейнеры. Они могут содержать информацию (данные) различных видов. В зависимости от того, какую информацию в них можно помещать, они имеют различные типы данных, а чтобы отличать их друг от друга, им присваиваются имена. PL/SQL хранит числа в переменных типа NUMBER, а текст — в переменных типа CHAR или VARCHAR2. Синтаксис объявления переменной в PL/SQL может иметь любую из следующих форм:

имя _ переменной тип _ данных [ [ NOTNULL ] : = выражение _ по _ умолчанию ] ; имя _ переменной тип _ данных [ [ NOT NULL ] DEFAULT выражение _ по _ умолчанию ] ;

Имя_переменной — это любой правильный идентификатор PL/SQL. Правильный идентификатор PL/SQL должен:

Тип_данных — это любой допустимый тип данных SQL или PL/SQL. Модификатор NOT NULL требует, чтобы переменная имелазначение. Если он указан, переменной должно быть присвоено значение по умолчанию.

Объявление констант PL/SQL

Синтаксис объявления константы имеет следующий вид:

имя _ переменной тип _ данных CONSTANT : = выражение ;

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

Оператор IF

Оператор IF имеет следующий синтаксис:

Циклы

PL/SQL предоставляет три различные конструкции для итеративной обработки. Каждая из них позволяет циклически выполнять набор операторов PL/SQL. Выход из цикла осуществляется в зависимости от некоторого условия.

Конструкция LOOP имеет следующий синтаксис:

Цикл WHILE

Еще одной разновидностью цикла является цикл WHILE. Он хорошо подходит в ситуациях, когда количество итераций заранее неизвестно, и определяется некоторым внешним фактором. Цикл WHILE имеет следующий синтаксис:

Цикл FOR

В цикле FOR для подсчета итераций используется переменная-счетчик, называемая также индексом цикла (loop index). По завершении каждой итерации счетчик увеличивается, начиная с нижнего предела, или уменьшается, начиная с верхнего предела. Как только его значение выйдет за указанный диапазон, цикл завершается. Синтаксис цикла FOR выглядит следующим бразом:

FOR счетчик IN [ REVERSE ] нижняя _ граница . . верхняя _ граница LOOP

Курсоры

Объявление курсора и атрибуты курсора

Курсор объявляется в процедуре PL/SQL следующим образом:

оператор _select [ FOR UPDATE [ OF таблица _ или _ столбец _1 [ , таблица _ или _ столбец _2 . . . ] ] ]

Параметры курсора похожи на параметры процедуры, за тем исключением, что они всегда являются входными (IN). Использование параметров OUT или IN OUT невозможно, поскольку курсор не может их модифицировать. Параметры используются в конструкции WHERE курсорного оператора SELECT. Спецификация возврата показывает, записи какого типа будут выбираться оператором SELECT. Таблица_или_столбец — это имя столбца, который предстоит обновлять, или имя таблицы, в которой предстоит удалять или обновлять строки. Оно должно входить в число имен таблиц и столбцов, указанных в операторе SELECT курсора, и предназначено для документирования, показывая, какие элементы могут быть потенциально модифицированы кодом, использующим данный курсор. Команда FOR UPDATE блокирует строки, выбранные оператором SELECT при открытии курсора. Строки остаются заблокированными до тех пор, пока вы не закроете курсор рассмотренными выше способами. Атрибуты курсора:

Записи PL/SQL

Запись PL/SQL— это набор данных базовых типов. К ней можно обращаться, как к единому целому. Для доступа к отдельным полям записи применяется нотация имя_записи_имя_поля, которую вы уже использовали для столбцов таблицы. Записи могут иметь один из трех типов, перечисленных ниже; вы можете объявлять переменные, имеющие тип записи.

Основанные на таблице (table-based) Эти записи имеют поля, совпадающие по имени и типу со столбцами таблицы. Если курсор выбирает всю строку — например, оператором SELECT * FROM некоторая_таблица — то возвращаемые им записи можно непосредственно копировать в переменную, имеющую тип записи, основанной на таблице некоторая_таблица.
Основанные на курсоре (cursor-based) Поля этих записей совпадают по имени, типу и порядку с заключительным списком столбцов в курсорном операторе SELECT.
Определенные программистом (programmer-defined) Это записи, тип которых определяете вы сами.

Использование команд OPEN, FETCH и CLOSE

Команды открытия курсора, выборки из курсора и закрытия курсора имеют следующий синтаксис:

FETCH имя _ курсора INTO переменная _ или _ список _ переменных ;

После открытия курсор содержит набор записей, если в результате успешного выполнения оператора SELECT из базы данных были выбраны заданные строки. Каждая команда FETCH удаляет запись из открытого курсора и перемещает ее содержимое либо в переменную PL/SQL, тип записи которой совпадает с типом записи курсора, либо в группу переменных PL/SQL, где каждая переменная в списке совпадает по типу с соответствующим полем в записи курсора. Перед тем как пытаться выбрать из курсора очередную запись, следует проверить с помощью атрибутов FOUND и NOTFOUND, есть ли в нем еще записи. Выборки из пустого курсора будут все время давать последнюю запись, не приводя к ошибке. Не забывайте проверять атрибуты FOUND и NOTFOUND при использовании FETCH. Фактическая обработка записей из курсора обычно выполняется внутри цикла. При написании такого цикла неплохо начать с проверки, была ли найдена запись в курсоре. Если да, можно продолжать необходимую обработку; в противном случае следует выйти из цикла. То же самое можно сделать более коротким путем, использовав курсорный цикл FOR. При этом PL/SQL будет осуществлять открытие, выборку и закрытие без вашего участия.

Курсорный цикл FOR

Синтаксис курсорного цикла FOR имеет следующий вид:

Конструкция WHERE CURRENT OF

Когда курсор открывается для обновления или удаления выбранных записей, можно использовать конструкцию WHERE CURRENT OF имя_курсора для доступа к таблице и строке, которые соответствуют последней записи, выбранной в конструкции WHERE оператора UPDATE или DELETE.

Обработка ошибок

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

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

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

Существует два типа исключений:

Системное исключение определяется в Oracle и обычно инициируется исполняемым ядром PL/SQL, обнаружившим ошибку. Одним системным исключениям присваиваются имена (например, NO_DATA_FOUND), другие ограничиваются номерами и описаниями.
Исключение, определяемое программистом, актуально только для конкретного приложения. Имя исключения можно связать с конкретной ошибкой Oracle с помощью директивы компилятора EXCEPTION_INIT или же назначить ошибке номер и описание процедурой RAISE_APPLICATION_ERROR.

Исключения

Системные исключения

Исключения, определяемые программистом

Одной из удобных возможностей PL/SQL является то, что он позволяет вам определять свои собственные исключения. При возбуждении и обработке они должны именоваться и объявляться аналогично любым другим элементам PL/SQL. Исключение объявляется в секции объявлений. Аналогично любой другой объявленной там переменной, исключение действительно только для данного блока. Вы можете использовать свои собственные исключения для обработки ошибок, которые система не обнаруживает или не считает за ошибки.

Схема сложного запроса PL/SQL с использованием временных таблиц

Ниже рассмотрена схема получения результирующей таблицы в Oracle Database, используя временные таблицы.

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