Хранимые процедуры помогают оптимизировать базу данных и повысить возможность повторного использования. Вместо выполнения одного и того же запроса вы можете создать задачу в виде хранимой процедуры, которую вы будете вызывать при необходимости. К концу этого поста вы узнаете все о хранимых процедурах.
Работа с хранимыми процедурами в PostgreSQL
Как пользователь PostgreSQL, вы могли заметить, что функции PostgreSQL не выполняют транзакции. Хотя создать транзакцию возможно, зафиксировать ее или откатить до предыдущего состояния невозможно. Однако эти ограничения можно обойти с помощью хранимых процедур.
Вот основной синтаксис для создания хранимой процедуры в PostgreSQL:
СОЗДАТЬ ИЛИ ЗАМЕНИТЬ ПРОЦЕДУРУ имя_процедуры(
параметр[ы] тип_данных
)
ЯЗЫК
КАК $$
ОБЪЯВЛЯТЬ
переменные_если_любой_тип_данных
НАЧИНАТЬ
логика
КОНЕЦ;
$$
Ключевые моменты, на которые следует обратить внимание в данном синтаксисе, — это «имя_процедуры», то есть имя, которое вы будете использовать для хранимой процедуры, параметры, которые вы хотите включить, и их типы данных, а также логика, которая в основном представляет собой операторы SQL.
Давайте приведем три примера, которые помогут вам понять, как создавать хранимые процедуры в PostgreSQL.
Пример 1. Хранимая процедура для вычисления квадрата числа
В нашем первом примере мы создаем хранимую процедуру, которая использует оператор RAISE NOTICE как способ вывода вывода на терминал. Хранимая процедура принимает целочисленное значение, которое вы ей даете при вызове, и рассчитывает его квадрат.
Вот как мы создаем хранимую процедуру:
Мы назовем наш параметр «num1», и это целое число. Что касается логической части, мы определяем, как она получает квадрат числа «num1» и сохраняет его как квадратную переменную. Когда мы выполняем команду, мы получаем вывод «CREATE PROCEDURE», который подтверждает, что нам удалось успешно создать хранимую процедуру.
Следующая задача — вызвать процедуру и передать ей ожидаемый аргумент.
ВЫЗОВ имя_процедуры(аргументы);Вы получите вывод CALL, показывающий, что хранимая процедура была выполнена, и мы получаем ожидаемый результат, который в данном случае представляет собой квадрат добавленного нами аргумента.
Пример 2. Хранимая процедура для вставки значений в запись таблицы
Следующие два примера показывают, как создать хранимую процедуру, работающую с таблицей базы данных. Давайте быстро создадим таблицу «студенты», с которой мы будем работать.
В этом примере мы создаем хранимую процедуру, которая позволяет пользователю вставлять значения во вновь созданную таблицу. Обратите внимание, как мы указываем параметры, которые, как мы ожидаем, будут добавлены в качестве аргументов при вызове хранимой процедуры. Более того, мы определяем логику, которая принимает добавленные аргументы и выполняет оператор INSERT SQL для таблицы «студенты».
Мы можем проверить доступные хранимые процедуры, выполнив следующую команду:
\dfПервая хранимая процедура, которую мы видим в следующем выводе, — это «add_student», которую мы создали ранее.
Теперь давайте вызовем хранимую процедуру для ее выполнения. На следующем изображении показано, что у нас есть пустая таблица, но мы вызвали хранимую процедуру, чтобы добавить первого студента:
Если мы перечислим значения в нашей таблице, обратите внимание, что аргументы, которые мы добавили с помощью команды вызова процедуры, являются значениями для нашего первого студента в нашей таблице. Вот как вы создаете хранимую процедуру для вставки значений в таблицу.
Обратите внимание, что при создании хранимой процедуры указанные вами параметры должны соответствовать ожидаемым в вашей таблице, чтобы избежать ошибок. При этом тип данных должен совпадать.
Пример 3. Хранимая процедура для обновления записи таблицы
Двигаясь дальше, давайте создадим еще одну хранимую процедуру, которая обновляет запись таблицы. Если вы хотите иметь быстрый способ обновления значений в нашей таблице, вы можете создать хранимую процедуру обновления следующим образом:
Укажите, какой столбец вы хотите обновить, используя ключевое слово WHERE, а новое значение — с помощью ключевого слова SET. Затем вам необходимо добавить ключевое слово COMMIT, чтобы сохранить изменения.
Давайте вызовем хранимую процедуру обновления и добавим ожидаемые аргументы: «student_id» и новый курс.
Если мы перечислим записи в нашей таблице, мы сможем убедиться, что у нас есть обновленный курс для конкретного студента, на которого мы ориентировались. Именно так работает хранимая процедура обновления.
Заключение
В PostgreSQL вы можете создать любую хранимую процедуру. Вам нужно только понять синтаксис, которому нужно следовать, а затем определить логику для хранимой процедуры. Отсюда вызовите хранимую процедуру и убедитесь, что она выполнена должным образом. В этом посте рассказывается о хранимых процедурах в PostgreSQL и приводятся примеры их создания.