Как вызвать процедуру oracle

Обновлено: 03.07.2024

Хранимая процедура или функция есть объект реляционной базы данных, который является поименнованным набором операторов SQL и, в случае СУБД Oracle, набором операторов PL/SQL , который может быть скомпилирован и необязательно сохранен в базе данных. Если процедура сохраняется в базе данных, то она называется хранимой процедурой или функцией. Описание хранимых процедур и функций хранится в словаре данных реляционной базы данных.

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

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

Хранимые процедуры и функции, как объекты базы данных, создаются командой CREATE и уничтожаются командой DROP . Команда создания хранимой процедуры имеет следующий синтаксис:

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

Исполнение созданной процедуры может быть выполнено оператором EXEC PL/SQL , как показано ниже:

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

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

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

Вызвать созданную хранимую функцию можно следующим образом:

Для уничтожения хранимой процедуры или функции в базе данных используется команда DROP в формате DROP [имя схемы].имя процедуры;

Или DROP [имя схемы].имя функции;

Особенности использования процедур и функций в СУБД Oracle

В этом подразделе рассмотрим некоторые особенности использования процедур и функций в PL/SQL :

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

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

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

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

При передаче параметра по имени стрелка, называемая оператором связывания ( association operator ), связывает формальный параметр слева от стрелки с фактическим параметром справа от стрелки, причем порядок следования таких пар не имеет значения.

Можно комбинировать передачу параметра по позиции и по имени. Но при этом следует соблюдать требование того, чтобы передача параметра по позиции предшествовала передаче параметра по имени.

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

Пример. Для нашей учебной базы данных разработаем процедуру создания нового отдела таблице DEPARTAMENT.

Если при вызове процедуры фактический параметр не передается, то используется значение по умолчанию. Рассмотрим следующие вызовы этой процедуры:

При первом вызове не передается никаких параметров, так что используются значения параметра по умолчанию - Новый отдел в г. Москве. Во втором вызове передается один параметр - первый. Для второго используется значение по умолчанию. В третьем случае передаются оба параметра и значения по умолчанию не используются.

Для оптимизации выполнения процедур и функций компилятор PL/SQL может выбирать различные методы передачи параметров (по значению или по ссылке) для различных параметров в одном и том же вызове процедуры или функции. Когда компилятор выбирает метод передачи параметра по значению, то его значение копируется в процедуре или функции. Когда компилятор выбирает метод передачи параметра по ссылке, то адрес фактического параметра передается в процедуру или функцию. Может создаться ситуация когда переменная в процедуре или функции будет иметь два имени - так называемая проблема алиасных имен. В этом случае результат будет неопределенным. Это происходит, когда глобальная переменная передается по адресу, как в примере ниже.

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

Пример. Рассмотрим процедуру реверсирования строки.

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

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

Таким образом, основными преимуществами использования процедур и функций в базах данных являются:

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

Создание хранимой процедуры PL/SQL

Для того чтобы написать собственную программу на PL/SQL, нужно воспользоваться одной из инструкций SQL CREATE . Например, если вы хотите создать хранимую функцию именем wordcount для подсчета количества слов в строке, выполните инструкцию CREATE FUNCTION ::

Как и в случае с простыми блоками BEGIN-END, приводившимися ранее, код этой инструкции в SQL*Plus должен завершаться символом косой черты, который размещается в отдельной строке.

По этой причине Oracle поддерживает инструкцию CREATE OR REPLACE FUNCTION — вероятно, вы будете использовать ее в 99 случаях из 100:

Связка OR REPLACE позволяет избежать побочных эффектов, вызванных удалением и повторным созданием программ; она сохраняет все привилегии на объект, предоставленные другим пользователям или ролям. При этом она заменяет только объекты одного типа и не станет автоматически удалять таблицу с именем wordcount только потому, что вы решили создать функцию с таким же именем.

Программисты обычно сохраняют подобные команды (равно как и анонимные блоки, предназначенные для повторного использования) в файлах операционной системы. Например, для хранения рассматриваемой функции можно было бы создать файл wordcount.fun , а для его запуска применить команду SQL*Plus @ :

Как упоминалось ранее, SQL*Plus по умолчанию не выводит содержимое сценария на экран. Для того чтобы исходный код сценария, включая присвоенные Oracle номера строк, отображался на экране, воспользуемся командой SET ECHO ON . Особенно полезна эта команда в ходе диагностики. Давайте намеренно допустим в программе ошибку, закомментировав объявление переменной:

Предупреждение сообщает нам о том, что функция была создана, но из-за ошибок компиляции ее выполнение невозможно. Нам удалось сохранить исходный код в базе данных; теперь нужно извлечь подробную информацию об ошибке из базы данных. Проще всего это сделать с помощью команды SQL*Plus SHOW ERRORS, которую можно сократить до SHO ERR :

Вывод других ошибок

Многие программисты Oracle знают только одну форму команды SQL*Plus:

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

Например, чтобы просмотреть информацию о последних ошибках в процедуре wordcount , выполните такую команду:

Оно выводится в трех случаях: (1) когда код объекта откомпилирован успешно; (2) вы задали неверную категорию (скажем, функцию вместо процедуры); и (3) объект с заданным именем не существует.

Полный список категорий, поддерживаемых этой командой, зависит от версии СУБД, но в него как минимум входят следующие категории:

Компилятор обнаружил оба вхождения переменной и сообщил точные номера строк и столбцов. Более подробную информацию об ошибке можно найти по идентификатору (в данном случае PLS-00201) в документации Oracle Database Error Messages.

Во внутренней реализации команда SHOW ERRORS обращается с запросом к представлению Oracle USER_ERRORS из словаря данных. В принципе вы можете обращаться к этому представлению и самостоятельно, но обычно это просто не нужно (см. врезку «Вывод других ошибок»).

Команда SHOW ERRORS часто добавляется послед каждой инструкции CREATE , создающей хранимую программу PL/SQL. Поэтому типичный шаблон для построения хранимых процедур в SQL*Plus может начинаться так:

(Обычно я не включаю команду SET ECHO ON в сценарий, а просто ввожу ее в командной строке, когда это потребуется.)

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

Выполнение хранимой процедуры PL/SQL

Мы рассмотрели два способа вызова хранимой программы: заключение ее в простом блоке PL/SQL и использование команды EXECUTE среды SQL*Plus. Одни хранимые процедуры также можно использовать в других. Например, функция wordcount может использоваться в любом месте, где может использоваться целочисленное выражение. Короткий пример тестирования функции wordcount с входным значением CHR(9) , которое является ASCII-кодом символа табуляции:

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

Многие функции PL/SQL можно вызывать и из SQL-инструкций. Несколько примеров использования функции wordcount :

Включение в список выборки для вычисления количества слов в столбце таблицы:

Использование в ANSI-совместимой инструкции CALL для привязки выходных данных функции к переменной SQL*Plus и вывода результата:

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

Вывод хранимых процедур PL/SQL

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

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

Представление содержит сведения о каждом объекте: его имя, тип, время создания, время последней компиляции, состояние работоспособности и другую полезную информацию.

Если вам нужно получить данные об интерфейсе программы в SQL*Plus, проще всего воспользоваться командой DESCRIBE :

Команда DESCRIBE также работает с таблицами, объектными типами, процедурами и пакетами. Чтобы просмотреть полный исходный код хранимых процедур, обратитесь с запросом к представлению USER_SOURCE или TRIGGER_SOURCE .

Управление привилегиями и создание синонимов хранимых процедур

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

Инструкция REVOKE лишает пользователя этой привилегии:

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

а также всем пользователям Oracle:

Если привилегия EXECUTE представляется отдельному пользователю (например, с идентификатором scott ), затем — роли, в которую входит этот пользователь (например, all_mis ), и наконец, — всем пользователям, Oracle запомнит все три варианта ее предоставления. Любой из них позволит пользователю scott выполнять программу. Но если вы захотите лишить данного пользователя этой возможности, то сначала следует отменить привилегию пользователя с идентификатором scott , а затем аннулировать привилегию на выполнение функции для всех пользователей ( PUBLIC ) и роли (или же исключить пользователя из этой роли).

Для просмотра списка привилегий, предоставленных другим пользователям и ролям, можно запросить информацию представления USER_TAB_PRIVS_MADE . Имена программ в этом представлении почему-то выводятся в столбце table_name :

Если пользователь scott имеет привилегию EXECUTE на выполнение программы wordcount , он, возможно, захочет создать для нее синоним, чтобы ему не приходилось указывать перед именем программы префикс с именем схемы:

Теперь пользователь может выполнять программу, ссылаясь на ее синоним:

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

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

Созданный синоним удаляется простой командой:

Удаление хранимой программы (процедуры) PL/SQL

Если вы твердо уверены в том, что какая-либо хранимая программа вам уже не понадобится, удалите ее с помощью команды SQL DROP . Например, следующая команда удаляет хранимую функцию wordcount :

Полное удаление пакета, который может состоять из двух элементов (спецификации и тела):

Также можно удалить только тело пакета без отмены соответствующей спецификации:

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

Сокрытие исходного кода хранимой программы (процедуры) PL/SQL

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

Oracle предлагает приложение командной строки wrap , которое преобразует серию команд CREATE в комбинацию обычного текста и шестнадцатеричных кодов. Это действие не является шифрованием в прямом смысле слова, но все же направлено на сокрытие кода. Приведем несколько фрагментов преобразованного кода:

Но если вам понадобится полноценное шифрование (скажем, для передачи такой секретной информации, как пароль), полагаться на возможности wrap не следует.

На уровне схемы
Внутри упаковки
Внутри блока 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 .

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

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

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

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

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, используя временные таблицы.

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