Создание функций в oracle

Обновлено: 04.07.2024

Функция PL/SQL представляет собой модуль, который возвращает значение командой RETURN (вместо аргументов OUT или IN OUT ). В отличие от вызова процедуры, который представляет собой отдельный оператор, вызов функции всегда является частью исполняемого оператора, то есть включается в выражение или служит в качестве значения по умолчанию, присваиваемого переменной при объявлении.

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

Функции играют важную роль в реализации модульного подхода. К примеру, реализацию отдельного бизнес-правила или формулы в приложении рекомендуется оформить в виде функции. Вместо того чтобы писать один и тот же запрос снова и снова («Получить имя работника по идентификатору», «Получить последнюю строку заказа из таблицы order для заданного идентификатора компании» и т. д.), поместите его в функцию и вызовите эту функцию в нужных местах. Такой код создает меньше проблем с отладкой, оптимизацией и сопровождением.

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

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

Структура функции

Функция (рис. 1) имеет почти такую же структуру, как и процедура, не считая того, что ключевое слово RETURN в ней играет совершенно другую роль:

Основные элементы этой структуры:

схема — имя схемы, которой будет принадлежать функция (необязательный аргумент). По умолчанию применяется имя схемы текущего пользователя. Если значение схемы отлично от имени схемы текущего пользователя, то этот пользователь должен обладать привилегиями для создания функции в другой схеме.
имя — имя функции.
параметр — необязательный список параметров, которые применяются для передачи данных в функцию и возврата информации из нее в вызывающую программу.
возвращаемый_тип — задает тип значения, возвращаемого функцией. Возвращаемый тип должен быть указан в заголовке функции; он более подробно рассматривается в следующем разделе.
AUTHID — определяет, с какими разрешениями будет вызываться функция: создателя (владельца) или текущего пользователя. В первом случае (используется по умолчанию) применяется модель прав создателя, во втором — модель прав вызывающего.
DETERMINISTIC — определяет функцию как детерминированную, то есть возвращаемое значение полностью определяется значениями ее аргументов. Если включить эту секцию, ядро SQL сможет оптимизировать выполнение функции при ее вызове в запросах.
PARALLEL_ENABLE — используется для оптимизации и позволяет функции выполняться параллельно в случае, когда она вызывается из команды SELECT .
PIPELINED — указывает, что результат табличной функции должен возвращаться в итеративном режиме с помощью команды PIPE ROW .
RESULT_CACHE — указывает, что входные значения и результат вызова функции должен быть сохранен в кэше результатов. Эта возможность, появившаяся в Orade11g.
ACCESSIBLE BY (Oracle Database 12c) — ограничивает доступ к функции программными модулями, перечисленными в круглых скобках.
AGGREGATE — используется при определении агрегатных функций.
EXTERNAL — определяет функцию с «внешней реализацией» — то есть написанную на языке C.
объявления — объявления локальных идентификаторов этой функции. Если объявления отсутствуют, между ключевыми словами IS и BEGIN не будет никаких выражений.
исполняемые команды — команды, выполняемые функцией при вызове. Между ключевыми словами BEGIN и END или EXCEPTION должна находиться по крайней мере одна исполняемая команда.

Рис. 1. Код функции

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

На рис. 1 изображено строение функции PL/SQL и ее различных разделов. Обратите внимание: функция total_sales не имеет раздела исключений.

Возвращаемый тип

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

Несколько примеров использования RETURN :

Возвращение строки:

Возвращение числа функцией-членом объектного типа:

Возвращение записи, имеющей ту же структуру, что и у таблицы books:

Возвращение курсорной переменной с заданным типом REF CURSOR (базирующемся на типе записи):

Метка END

Вы можете указать имя функции за завершающим ключевым словом END :

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

Вызов функции

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

Присваивание переменной значения по умолчанию вызовом функции:

Использование функции-члена для объектного типа в условии:

Вставка в запись строки с информацией о книге:

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

Вызов написанной вами функции из команды CREATE VIEW с использованием выражения CURSOR для передачи результирующего набора в аргументе функции:

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

будет выдана ошибка PLS-00221: ‘FAVORITE_NICKNAME’ is not a procedure or is undefined .

Функцию нельзя использовать так, как если бы она была процедурой.

Функции без параметров

Если функция не имеет параметров, ее вызов может записываться с круглыми скобками или без них. Следующий код демонстрирует эту возможность на примере вызова метода age объектного типа pet_t:

Заголовок функции

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

Имя функции.
Модификаторы определения и поведения функции (детерминированность, возможность параллельного выполнения и т. д.).
Список параметров (если имеется).
Тип возвращаемого значения.

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

Заголовок упоминавшейся ранее функции total_sales выглядит так:

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

Тело функции

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

Как и в случае с процедурами, разделы исключений и объявлений не являются обязательными. Если обработчики исключений отсутствуют, опустите ключевое слово EXCEPTION и завершите функцию командой END . Если объявления отсутствуют, команда BEGIN просто следует непосредственно за ключевым словом IS.

Исполняемый раздел функции должен содержать команду RETURN . Функция откомпилируется и без него, но если выполнение функции завершится без выполнения команды RETURN, Oracle выдаст ошибку: ORA-06503: PL/SQL: Function returned without value .

Эта ошибка не выдается, если функция передает наружу свое необработанное исключение.

Команда RETURN

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

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

Любое допустимое выражение

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

Вы также можете возвращать сложные типы данных — экземпляры объектных типов, коллекции и записи.

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

множественные команды RETURN

В функции total_sales на рис. 2 я использую две разные команды RETURN для обработки разных ситуаций в функции: если из курсора не удалось получить информацию, возвращается NULL (не нуль). Если же от курсора было получено значение, оно возвращается вызывающей программе. В обоих случаях команда RETURN возвращает значение: в одном случае NULL , в другом — переменную return_value.

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

RETURN как последняя исполняемая команда

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

Переработанная версия логики на рис. 2, в которой решена проблема множественных команд RETURN , выглядит так:

Остерегайтесь исключений! Помните, что инициированное исключение может «перепрыгнуть» через последнюю команду прямо в обработчик. Если обработчик исключения не содержит команды RETURN , то будет выдана ошибка ORA-06503: Function returned without value независимо от того, как было обработано исходное исключение.

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