Как создать функцию (FUNCTION) в PL/SQL на примере

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

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

Оглавление статьи[Показать]

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

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

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

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

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

FUNCTION [схема.]имя[( параметр[, параметр...] ) ]
   RETURN возвращаемый_тип 
   [AUTHID DEFINER | CURRENT_USER]
   [DETERMINISTIC]
   [PARALLEL_ENABLE ...]
   [PIPELINED]
   [RESULT_CACHE ...]
   [ACCESSIBLE BY (program_unit_list)
   [AGGREGATE ...]
   [EXTERNAL ...]
IS
   [объявления]
BEGIN
   исполняемые команды 
[EXCEPTION
   обработчики исключений]
END [имя];

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

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

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

FUNCTION favorite_nickname (
   name_in IN VARCHAR2) RETURN VARCHAR2 
IS
BEGIN
   ...
END;

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

TYPE pet_t IS OBJECT (
   tag_no	INTEGER,
   NAME	VARCHAR2 (60),
   breed	VARCHAR2(100),
   dob DATE,
   MEMBER FUNCTION age RETURN NUMBER
)

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

PACKAGE book_info IS
   FUNCTION onerow (isbn_in IN books.isbn%TYPE)
      RETURN books%ROWTYPE;

...

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

PACKAGE book_info 
IS
   TYPE overdue_rt IS RECORD (
      isbn books.isbn%TYPE, 
      days_overdue PLS_INTEGER);

   TYPE overdue_rct IS REF CURSOR RETURN overdue_rt;
   
   FUNCTION overdue_info (username_in IN lib_users.username%TYPE)
      RETURN overdue_rct;

...

Метка END

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

FUNCTION total_sales (company_in IN INTEGER) RETURN NUMBER 
IS
BEGIN
   ...
END total_sales;

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

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

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

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

DECLARE
   v_nickname VARCHAR2(100) :=
      favorite_nickname ('Steven');

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

DECLARE
   my_parrot pet_t :=
      pet_t (1001, 'Mercury', 'African Grey',
         TO_DATE ('09/23/1996', 'MM/DD/YYYY'));
BEGIN
   IF my_parrot.age () < INTERVAL '50' YEAR 
    THEN
      DBMS_OUTPUT.PUT_LINE ('Still a youngster!');
   END IF;

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

DECLARE
   my_first_book books%ROWTYPE;
BEGIN
   my_first_book := book_info.onerow ('1-56592-335-9');

...

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

DECLARE
   l_name employees.last_name%TYPE;
BEGIN
   SELECT last_name INTO l_name 
   FROM employees
   WHERE employee_id = hr_info_pkg.employee_of_the_month ('FEBRUARY');
   ...

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

VIEW young_managers 
AS
   SELECT managers.employee_id AS manager_employee_id 
     FROM employees managers 
     WHERE most_reports_before_manager 
      (
         CURSOR ( SELECT reports.hire_date 
                  FROM employees reports
                  WHERE reports.manager_id = managers.employee_id
                 ),
         managers.hire_date 
) = 1;

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

BEGIN
   favorite_nickname('Steven');
END;

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

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

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

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

IF my_parrot.age < INTERVAL '50' YEAR -- 9i INTERVAL type 
IF my_parrot.age() < INTERVAL '50' YEAR

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

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

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

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

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

FUNCTION total_sales
   (company_id_in IN company.company_id%TYPE, 
    status_in IN order.status_code%TYPE := NULL)
RETURN NUMBER

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

DECLARE
   v_sales NUMBER;
BEGIN
   v_sales := total_sales (1505, 'ACTIVE');
END;

Тело функции

В теле функции содержится код, необходимый для реализации этой функции; тело состоит из объявления, исполняемого раздела и раздела исключений этой функции. Все, что следует за ключевым словом 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 'buy me lunch';
RETURN POWER (max_salary, 5);
RETURN (100 - pct_of_total_salary (employee_id));
RETURN TO_DATE ('01' || earliest_month || initial_year, 'DDMMYY');

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

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

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

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

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

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

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

FUNCTION do_it_all (parameter_list) RETURN NUMBER IS
   return_value NUMBER;
BEGIN
   ... множество исполняемых команд ...
   RETURN return_value;
END;

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

OPEN sales_cur;
IF sales_cur%NOTFOUND 
THEN
   return_value:= NULL;
END IF;
CLOSE sales_cur;
RETURN return_value;

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