Как устроены функции и хранимые процедуры и как их применять для повторного использования запросов.
SQL, как и любой другой язык программирования, предоставляет функции и хранимые процедуры. В этой статье мы рассмотрим функции и хранимые процедуры в PostgreSQL, а также будут освещены следующие моменты:
- что такое функции и хранимые процедуры;
- разница между функциями и хранимыми процедурами;
- создание функций и хранимых процедур;
- применение функций и хранимых процедур в реальных примерах.
Функции и хранимые процедуры в SQL, как и в любом другом языке программирования, обеспечивают возможность повторного использования и гибкость. Функции и хранимые процедуры представляют собой блок кода или запросов, хранящихся в базе данных, которые можно использовать снова и снова. Вместо того чтобы писать одни и те же запросы, удобнее сгруппировать все запросы и сохранить их, чтобы можно было использовать их много раз. Что касается гибкости, то всякий раз, когда происходит изменение логики запросов, можно передавать новый параметр функциям и хранимым процедурам.
Между функциями и хранимыми процедурами в PostgreSQL есть несколько различий. Они показаны в таблице ниже.
| Функции |
Хранимые процедуры |
| Функция имеет возвращаемый тип и возвращает значение |
Хранимая процедура не имеет возвращаемого типа, но имеет выходные аргументы |
| Использование DML (insert, update, delete) запросов внутри функции невозможно. В функциях разрешены только SELECT-запросы |
Использование DML-запросов (insert, update, delete) возможно в хранимой процедуре. |
| Функция не имеет выходных аргументов |
Хранимая процедура имеет и входные, и выходные аргументы |
| Вызов хранимой процедуры из функции невозможно |
Использование или же управление транзакциями возможно в хранимой процедуре |
Вызов функции внутри SELECT запросов возможен |
Вызов хранимой процедуры из SELECT запросов невозможно |
Давайте рассмотрим создание функции в 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.
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, указанный ниже.
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
Заключение
В этой статье мы рассмотрели:
- что такое функции и хранимые процедуры, а также их разницу;
- создание функции и процедуры;
- применение функций и процедур на реальных примерах.