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

18 сентября 2018 Просмотров: 22040

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

На рис. 1 показан код процедуры apply_discount, который содержит все четыре раздела, характерных для именованных блоков PL/SQL.

Рис. 1. Код процедуры

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

Процедура вызывается как исполняемая команда 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

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

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