Как устроены функции и хранимые процедуры и как их применять для повторного использования запросов.

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

• что такое функции и хранимые процедуры;
• разница между функциями и хранимыми процедурами;
• создание функций и хранимых процедур;
• применение функций и хранимых процедур в реальных примерах.

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

Между функциями и хранимыми процедурами в PostgreSQL есть несколько различий. Они показаны в таблице ниже.

Давайте рассмотрим создание функции в PostgreSQL. Следующий блок кода иллюстрирует, как создавать функцию.

         CREATE [or REPLACE] FUNCTION function_name(param_list)    RETURNS return_type     LANGUAGE plpgsql   as $$ DECLATE  -- variable declaration BEGIN  -- logic END; $$     

Оператор:

• create [or replace] function имя_функции — создает или заменяет функцию, если она существует, с заданным именем и параметрами;
• returns return_type — тип данных, который возвращает функция;
• язык plpgsql — указывает на процедурное расширение PostgreSQL;
• внутри знака $ является телом функции;
• declare — показывает, как объявляются или инициализируются переменные;
• блок кода [begin — end] — содержит всю логику функции;
• begin — указывает на начало запросов;
• end — указывает конец функции.

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

         CREATE OR REPLACE FUNCTION findMostExpensivePurchase(customer_id int)     RETURNS numeric(10, 2)     LANGUAGE plpgsql AS $$ DECLARE     itemCost numeric(10, 2); begin     SELECT MAX(cost)     INTO itemCost     FROM purchases     WHERE user_id = customer_id;     RETURN itemCost; end; $$;      

Важно отметить:

• Declare itemCost integer — объявляем локальную переменную;
• SELECT max(cost) INTO itemCost — как мы инициализируем переменную itemCost;
• Возвращает itemCost — возвращает значение функции.

Создание хранимой процедуры, как показано в блоке кода ниже, почти такое же, как создание функции с небольшим отличием — в ней нет return. Остальное почти идентично.

         CREATE [OR REPLACE] PROCEDURE procedure_name(parameter_list) LANGUAGE language_name AS $     stored_procedure_body; $;     

В приведенном ниже блоке кода показано создание процедуры — transfer(), которая принимает три параметра. Сразу после имени процедуры передаются аргументы с соответствующими типами данных — sourceAccountId, destinationAccountId, сумма. Процедура вычитает переданную сумму из одного account и добавляет ее к другому account.

         CREATE OR REPLACE PROCEDURE transfer(sourceAccountId bigInt, destinationAccountId bigInt, amount Integer) language plpgsql as $$ begin     update accounts     set balance = accounts.balance - amount     where id = sourceAccountId;      update accounts     set balance = balance + amount     where id = destinationAccountId;      commit; end; $$;     

Наконец, давайте применим все это на реальных примерах. Чтобы запустить весь код, который будет показан ниже, потребуется установить PostgreSQL (версия 13.2) на локальный компьютер или запустить PostgreSQL с помощью Docker-контейнера. Чтобы запустить PostgreSQL в Docker контейнере, необходимо запустить файл docker-compose.yaml, указанный ниже.

         version: "3.8"  services:    postgresqldb:     container_name: database     image: postgres:13.2-alpine     ports:       - "5432:5432"     environment:       - POSTGRES_DB=customer-service       - POSTGRES_USER=customer-dev       - POSTGRES_PASSWORD=1awer321!qwQ     volumes:     - pgdata:/var/lib/postgresql/data  volumes:   pgdata:     

         > docker-compose up     

Давайте создадим таблицы users и purchases и заполним их. Для простоты в таблице users есть три столбца — id, name и profession; таблица profession состоит из четырех столбцов — id, name, cost и user_id.

         CREATE table users (     id         serial primary key,     name       varchar(255),     profession varchar(255) );  insert into users(name, profession) values ('Bob', 'QA'), ('Camilo', 'Front End developer'), ('Billy', 'Backend Developer'), ('Alice', 'Mobile Developer'), ('Kate', 'QA'), ('Wayne', 'DevOps'), ('Tim', 'Mobile Developer'), ('Amigos', 'QA');  CREATE TABLE purchases (     id      serial primary key ,     name    varchar(255),     cost    numeric(10, 2),     user_id int,     foreign key (user_id)         references users (id) );  insert into purchases(name, cost, user_id) values ('M1 MacBook Air', 1300.99, 1), ('Iphone 14', 1200.00 , 2), ('Iphon 10', 700.00, 3), ('Iphone 13', 800.00, 1), ('Intel Core i5', 500.00, 4), ('M1 MacBook Pro', 1500, 5), ('IMAC',2500 , 7), ('ASUS VIVOBOOK', 899.99, 6), ('Lenovo', 1232.99, 1), ('Galaxy S21', 999.99, 2), ('XIAMI REDMIBOOK 14', 742.99, 4), ('M1 MacBook Air', 1299.99 , 8), ('ACER', 799.99, 7);     

После выполнения запросов мы можем проверить нашу базу данных

Получение записей из таблицы users

Получение записей из таблицы purchases

Допустим, мы хотим запросить имя пользователя и его самые дорогие покупки. Можно написать несколько сложных запросов, которые вернут желаемый результат, однако мы можем использовать нашу функцию, определенную выше.

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

         CREATE OR REPLACE FUNCTION findMostExpensivePurchase(customer_id int)     RETURNS numeric(10, 2)     LANGUAGE plpgsql AS $$ DECLARE     itemCost numeric(10, 2); begin     SELECT MAX(cost)     INTO itemCost     FROM purchases     WHERE user_id = customer_id;     RETURN itemCost; end; $$;     

Чтобы вызвать функцию — выполните следующую команду:

         SELECT findMostExpensivePurchase(1) as mostExpensivePurchase;     

Получение самой дорогой покупки пользователя по id = 1

Чтобы в полной мере воспользоваться функциями, их можно использовать во многих случаях, например, во внутренних запросах select. С функциями запросы становятся намного короче и точнее, это уменьшает шаблонный код и делает запросы лаконичными и простыми.

Получение списка имён пользователей и их самых дорогих покупок

Как мы обсуждали ранее, хранимые процедуры немного отличаются от функций. Функции позволяют выполнять только Select-запросы, а хранимые процедуры позволяют выполнять Insert, Update, Delete операции. Хранимые процедуры очень удобны при работе со случаями, когда необходимы операции insert, update или delete.

Рассмотрим банковскую операцию — перевод. При выполнении какой-либо банковской операции деньги переводятся с одного счета на другой. Чтобы реализовать эту хранимую процедуру — transfer(), давайте создадим таблицу accounts и заполним ее.

         create table accounts (     id      SERIAL primary key,     balance  BIGINT,     user_id INT unique ,     FOREIGN KEY (user_id)         references users (id) );  INSERT INTO accounts(balance, user_id) values (1500, 1), (1100, 2), (2300, 3), (7500, 5), (6500, 4);     

После выполнения приведенных выше запросов мы получим следующий вывод.

Получение записей из таблицы accounts

Для вызова хранимой процедуры используется — call procedure_name().

Сделаем условно перевод денег – 500 единиц с account.id = 3 на account.id = 4 и проверим результат.

Вызов функции transfer() и с последующим выводом записей из таблицы accounts

Заключение

В этой статье мы рассмотрели:

• что такое функции и хранимые процедуры, а также их разницу;
• создание функции и процедуры;
• применение функций и процедур на реальных примерах;

***

Материалы по теме

• 🐘 Руководство по SQL для начинающих. Часть 1: создание базы данных, таблиц и установка связей между таблицами
• 📜 Основные SQL-команды и запросы с примерами, которые должен знать каждый разработчик
• 🗄️ Лучшие стратегии по работе с РСУБД: индексы, транзакции и уровни изоляции

Больше полезных материалов вы найдете на нашем телеграм-канале «Библиотека программиста» Интересно, перейти к каналу