🐘 Функции и хранимые процедуры в SQL: зачем нужны и как применять в реальных примерах

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

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

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

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

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

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

CREATE [or REPLACE] FUNCTION function_name(param_list)
   RETURNS return_type 
   LANGUAGE plpgsql
  as
$$
DECLARE 
-- 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;
  
• RETURN 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);

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

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

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

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;

Чтобы в полной мере воспользоваться функциями, их можно использовать во многих случаях, например, во внутренних запросах 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);

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

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

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

Заключение

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

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

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

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