Процедуры и функции в Oracle

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

Процедура. Программа, которая осуществляет одно или несколько действий и вызывается как исполняемый оператор PL/SQL. Передавать данные процедуре и получать их можно с помощью списка параметров.
Функция. Программа, которая возвращает одно значение и используется как выражение PL/SQL. Для передачи информации функции используется ее список параметров. Параметры также могут использоваться для возврата информации из функции, но обычно это считается проявлением плохого стиля программирования.
Триггер базы данных. Набор команд, который вызывается при выполнении некоторого события (подключение к базе данных, модификация строки таблицы или DDL-операция).
Пакет. Именованный набор процедур, функций, типов и переменных. Пакет не является модулем (скорее, это мета-модуль), но он тесно связан с реализацией модульного подхода.
Объектный тип или экземпляр объектного типа. Эмуляция объектно-ориентированного класса в Oracle. Объектный тип инкапсулирует состояние и поведение данных, комбинируя их (как реляционная таблица) с правилами (процедурами и функциями, которые манипулируют этими данными).

Процедуры

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

PROCEDURE [схема.]имя[( параметр[, параметр...] ) ]
 [AUTHID DEFINER | CURRENT_USER]
 [ACCESSIBLE BY (список)]
IS
 [объявления]
BEGIN
 исполняемые команды
[ EXCEPTION
 обработчики исключений]
 END [имя];

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

схема — имя схемы, которой будет принадлежать процедура (необязательный аргумент). По умолчанию применяется имя схемы текущего пользователя. Если значение схемы отлично от имени схемы текущего пользователя, то этот пользователь должен обладать привилегиями для создания процедуры в другой схеме.
имя — имя процедуры.
параметр — необязательный список параметров, которые применяются для передачи данных в процедуру и возврата информации из процедуры в вызывающую программу.
AUTHID — определяет, с какими разрешениями будет вызываться процедура: создателя (владельца) или текущего пользователя. В первом случае процедура выполняется с правами создателя, во втором — с правами вызывающего.
объявления — объявления локальных идентификаторов этой процедуры. Если объявления отсутствуют, между ключевыми словами IS и BEGIN не будет никаких выражений.
ACCESSIBLE BY (Oracle Database 12c) — ограничивает доступ к процедуре программными модулями, перечисленными в круглых скобках.
исполняемые команды — команды, выполняемые процедурой при вызове. Между ключевыми словами BEGIN и END или EXCEPTION должна находиться по крайней мере одна исполняемая команда.
обработчики исключений — необязательные обработчики исключений для процедуры. Если процедура не обрабатывает никаких исключений, слово EXCEPTION можно опустить и завершить исполняемый раздел ключевым словом END.

Вызов процедуры

Процедура вызывается как исполняемая команда PL/SQL. Другими словами, ее вызов должен заканчиваться точкой с запятой (;) и может предшествовать другим командам SQL либо PL/SQL (если таковые имеются) в исполняемом разделе блока PL/SQL или следовать за ними:

BEGIN
 apply_discount( new_company_id, 0.15 );
END;

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

display_store_summary;
display_store_summary();

Заголовок процедуры

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

Имя процедуры.
Условие AUTHID (если имеется).
Список параметров (если имеется).
Список ACCESSIBLE BY (если имеется — новая возможность Oracle Database 12c).

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

PROCEDURE apply_discount (
 company_id_in IN company.company_id%TYPE
 , discount_in IN NUMBER)

Он состоит из типа модуля, имени и списка из двух параметров.

Тело процедуры

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

Метка END

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

PROCEDURE display_stores (region_in IN VARCHAR2) IS
BEGIN
 ...
END display_stores;

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

Функции

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

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

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

Структура функции Функция имеет почти такую же структуру, как и процедура, не считая того, что ключевое слово 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 — указывает, что входные значения и результат вызова функции должен быть сохранен в кэше результатов.
ACCESSIBLE BY (Oracle Database 12c) — ограничивает доступ к функции программными модулями, перечисленными в круглых скобках.
AGGREGATE — используется при определении агрегатных функций.
EXTERNAL — определяет функцию с «внешней реализацией» — то есть написанную на языке C.
объявления — объявления локальных идентификаторов этой функции. Если объявления отсутствуют, между ключевыми словами IS и BEGIN не будет никаких выражений.
исполняемые команды — команды, выполняемые функцией при вызове. Между ключевыми словами BEGIN и END или EXCEPTION должна находиться по крайней мере одна исполняемая команда.
обработчики исключений — необязательные обработчики исключений для функции. Если процедура не обрабатывает никаких исключений, слово EXCEPTION можно опустить и завершить исполняемый раздел ключевым словом END.

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

Функция 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;

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

Функция может вызываться из любой части исполняемой команды 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');

### Функции без параметров Если функция не имеет параметров, ее вызов может записываться с круглыми скобками или без них. Следующий код демонстрирует эту возможность на примере вызова метода 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.

Вопросы для самопроверки

Что такое процедура?
Что такое функция?
Чем отличается функция от процедуры?
Какова структура функции?
Как осуществляется вызов процедуры?

Задания

Напишите процедуру, котороая выполняет арифметические операции (+, -, *, /)
Напишите функцию, котрая возвращает строку “Hello World!”
Напишите процедуру, а в ней объявите функцию

Предыдущий урок Следующий урок

IgorSikors.github.io

My lessons Oracle