SQL
Процедуры
16
Авторские права
© Postgres Professional, 2017–2024
Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Игорь Гнатюк
Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал)
Использование материалов курса
Некоммерческое использование материалов курса (презентации,
демонстрации) разрешается без ограничений. Коммерческое
использование возможно только с письменного разрешения компании
Postgres Professional. Запрещается внесение изменений в материалы
курса.
Обратная связь
Отзывы, замечания и предложения направляйте по адресу:
Отказ от ответственности
Компания Postgres Professional не несет никакой ответственности за
любые повреждения и убытки, включая потерю дохода, нанесенные
прямым или непрямым, специальным или случайным использованием
материалов курса. Компания Postgres Professional не предоставляет
каких-либо гарантий на материалы курса. Материалы курса
предоставляются на основе принципа «как есть» и компания Postgres
Professional не обязана предоставлять сопровождение, поддержку,
обновления, расширения и изменения.
2
Темы
Процедуры и их отличие от функций
Входные и выходные параметры
Перегрузка и полиморфизм
3
Подпрограммы
Функции
вызываются в контексте выражения
не могут управлять транзакциями
возвращают результат
Процедуры
вызываются оператором CALL
могут управлять транзакциями
могут возвращать результат
Процедуры были введены в PostgreSQL 11. Основная причина
их появления состоит в том, что функции не могут управлять
транзакциями. Функции вызываются в контексте какого-либо
выражения, которое вычисляется как часть уже начатого оператора
(например, SELECT) в уже начатой транзакции. Нельзя завершить
транзакцию и начать новую «посередине» выполнения оператора.
Процедуры всегда вызываются специальным оператором CALL.
Если этот оператор сам начинает новую транзакцию (а не вызывается
из уже начатой), то в процедуре можно использовать команды
управления транзакциями.
К сожалению, процедуры, написанные на языке SQL, лишены сейчас
возможности использовать команды COMMIT и ROLLBACK, хотя те
из них, что оформлены в новом стиле стандарта SQL, вероятно смогут
это делать в будущем. Поэтому пример процедуры, управляющей
транзакциями, придется отложить до темы «PL/pgSQL. Выполнение
запросов».
Иногда можно услышать, что процедура отличается от функции тем,
что не возвращает результат. Но это не так — процедуры тоже могут
возвращать результат, если необходимо.
Функции и процедуры имеют общее пространство имен и вместе
называются подпрограммами (routine).
5
Перегрузка
Несколько подпрограмм с одним и тем же именем
подпрограмма однозначно определяется сигнатурой —
именем и входными параметрами
тип возвращаемого значения и выходные параметры игнорируются
подходящая подпрограмма выбирается во время выполнения
в зависимости от фактических параметров
Команда CREATE OR REPLACE
при несовпадении типов входных параметров создаст новую
перегруженную подпрограмму
при совпадении — изменит существующую подпрограмму,
но нельзя поменять тип подпрограммы, тип возвращаемого значения,
типы OUT-параметров
Перегрузка — это возможность использования одного и того же имени
для нескольких подпрограмм, отличающихся типами параметров IN
и INOUT.
Сигнатура подпрограммы — ее имя и типы входных параметров.
При вызове PostgreSQL находит подпрограмму, соответствующую
сигнатуре. Возможны ситуации, когда подходящую подпрограмму
невозможно определить однозначно; в таком случае во время
выполнения возникнет ошибка.
Однако в сигнатуру, в частности, не входят:
●
тип подпрограммы (процедура или функция);
●
типы параметров OUT;
●
тип возвращаемого значения.
Перегрузку надо учитывать, выполняя команду CREATE OR REPLACE
(FUNCTION или PROCEDURE). Если сигнатура не совпадает
ни с одной из существующих, будет создана новая перегруженная
подпрограмма, а при совпадении — изменена существующая.
В последнем случае не разрешается изменять тип подпрограммы, тип
OUT-параметров или тип возвращаемого значения, однако можно
изменить язык и другие свойства. Поэтому иногда приходится удалять
подпрограмму и создавать ее заново, в этом случае потребуется также
удалить зависящие от нее объекты: другие подпрограммы,
представления, триггеры и т. п. (DROP ROUTINE ... CASCADE).
6
Полиморфизм
Подпрограмма, принимающая параметры разных типов
формальные параметры используют полиморфные псевдотипы
(например, anyelement или anycompatible)
конкретный тип данных выбирается во время выполнения
по типу фактических параметров
В некоторых случаях удобно не создавать несколько перегруженных
подпрограмм для разных типов, а написать одну, принимающую
параметры любого (или почти любого) типа.
Для этого в качестве типа формального параметра указывается
специальный полиморфный псевдотип. Пока мы рассмотрим два
из них — anyelement и anycompatible — но позже встретимся
и с другими.
Полиморфные псевдотипы в определении позволяют использовать
в качестве параметров любые типы, при этом конкретный тип,
с которым будет работать подпрограмма, выбирается во время
выполнения по типу фактического параметра.
Если в определении указано несколько полиморфных параметров
типа anyelement, то все фактические параметры неявно приводятся
к типу первого из них. Если же указано несколько параметров типа
anycompatible, то типы фактических параметров приводятся
к некоторому общему типу.
Если подпрограмма объявлена с возвращаемым значением
полиморфного типа, то она должна иметь по крайней мере один
входной полиморфный параметр. Конкретный тип возвращаемого
значения также определяется исходя из типа фактического входного
параметра. Для подпрограмм в стиле стандарта SQL возможности
использовать полиморфные типы данных для аргументов нет.
8
Итоги
Можно создавать и использовать собственные процедуры
В отличие от функций, процедуры вызываются оператором
CALL и могут управлять транзакциями
Для процедур и функций поддерживаются перегрузка
и полиморфизм
9
Практика
1. В таблице authors имена, фамилии и отчества авторов
по смыслу должны быть уникальны, но это условие никак не
проверяется. Напишите процедуру, удаляющую возможные
дубликаты авторов.
2. Чтобы необходимость в подобной процедуре не возникала,
создайте ограничение целостности, которое не позволит
появляться дубликатам в будущем.
1. В приложении возможность добавлять авторов появится в теме
«PL/pgSQL. Выполнение запросов». А пока для проверки можно
добавить дубликаты в таблицу вручную.
10
Практика
1. Получится ли создать в одной и той же схеме и имеющие
одно и то же имя: а) процедуру с одним входным параметром,
б) функцию с одним входным параметром того же типа,
возвращающую некоторое значение? в) процедуру с одним
входным и одним выходным параметром? Проверьте.
2. В таблице хранятся вещественные числа (например,
результаты каких-либо измерений). Напишите процедуру
нормализации данных, которая умножает все числа на
определенный коэффициент так, чтобы все значения попали
в интервал от −1 до 1.
Процедура должна возвращать выбранный коэффициент.
2. В качестве коэффициента возьмите максимальное абсолютное
значение из таблицы.
