Съхранените процедури помагат при оптимизирането на базата данни и при подобряването на повторното използване. Вместо да се налага да изпълнявате същата заявка, можете да създадете задачата като съхранена процедура, която ще извиквате, когато е необходимо. Ще научите всичко за съхранените процедури до края на тази публикация.
Работа със съхранени процедури в PostgreSQL
Като потребител на PostgreSQL може би сте забелязали, че функциите на PostgreSQL не изпълняват транзакции. Въпреки че е възможно да се създаде транзакция, нейното ангажиране или връщане към предишното състояние не е възможно. Тези ограничения обаче се заобикалят с помощта на съхранени процедури.
Ето основния синтаксис за създаване на съхранена процедура в PostgreSQL:
СЪЗДАВАЙТЕ ИЛИ ЗАМЕНЕТЕ ПРОЦЕДУРА име_на_процедура(
параметр[и] тип_данни
)
ЕЗИК plpsql;
КАТО $$
ДЕКЛАРИРАЙТЕ
променливи_ако_всеки тип_данни
НАЧАЛО
логика
КРАЙ;
$$
Ключовите неща, които трябва да се отбележат от дадения синтаксис, са „име_на_процедура“, което е името, което ще използвате за съхранената процедура, параметрите, които искате да включите и техните типове данни, и логиката, която е главно SQL операторите.
Нека дадем три примера, за да ви помогнем да разберете как да създавате съхранени процедури в PostgreSQL.
Пример 1: Съхранена процедура за изчисляване на квадрат на число
За нашия първи пример създаваме съхранена процедура, която използва израза „RAISE NOTICE“ като начин за отпечатване на изхода към терминала. Съхранената процедура приема целочислената стойност, която й давате, когато я извиквате, и изчислява нейния квадрат.
Ето как създаваме съхранената процедура:
Наричаме нашия параметър „num1“ и той е цяло число. За логическата част ние дефинираме как получава квадрата на „num1“ и го съхранява като квадратна променлива. Когато изпълним командата, получаваме изхода „CREATE PROCEDURE“, който потвърждава, че сме успели да създадем съхранената процедура успешно.
Следващата задача е да извикате процедурата и да й дадете очаквания аргумент.
CALL име_на_процедура(аргументи);Ще получите изхода CALL, показващ, че съхранената процедура е изпълнена и ние получаваме очаквания изход, който в този случай е квадратът на аргумента, който добавихме.
Пример 2: Съхранена процедура за вмъкване на стойностите в запис в таблица
Следващите два примера показват как да създадете съхранена процедура, която работи с таблица на база данни. Нека бързо създадем таблицата „ученици“, с която ще работим.
За този пример създаваме съхранена процедура, която позволява на потребителя да вмъкне стойностите в новосъздадената таблица. Забележете как посочваме параметрите, които очакваме да бъдат добавени като аргументи, когато извикваме съхранената процедура. Нещо повече, ние дефинираме логиката, която приема добавените аргументи и изпълнява оператор INSERT SQL към таблицата „students“.
Можем да проверим наличните запаметени процедури, като изпълним следната команда:
\dfПървата съхранена процедура, която можем да видим от следния изход, е „add_student“, който създадохме преди това.
Сега нека извикаме съхранената процедура, за да я изпълним. Следното изображение показва как имаме празна таблица, но извикахме съхранената процедура, за да добавим първия ученик:
Ако изброим стойностите в нашата таблица, забележете как аргументите, които добавихме с командата call procedure, са стойностите за нашия първи ученик в нашата таблица. Ето как създавате съхранена процедура за вмъкване на стойностите в таблица.
Имайте предвид, че когато създавате запомнената процедура, параметрите, които задавате, трябва да съответстват на това, което се очаква във вашата таблица, за да избегнете грешки. Освен това типът данни трябва да съвпада.
Пример 3: Съхранена процедура за актуализиране на запис в таблица
Продължавайки, нека създадем друга съхранена процедура, която актуализира запис в таблица. Ако искате да имате бърз начин за актуализиране на стойностите в нашата таблица, можете да създадете съхранена процедура за актуализиране, както следва:
Посочете коя колона искате да актуализирате, като използвате ключовата дума WHERE и новата стойност, като използвате ключовата дума SET. След това трябва да добавите ключовата дума COMMIT, за да запазите промените.
Нека извикаме съхранената процедура за актуализиране и добавим очакваните аргументи: „student_id“ и новия курс.
Ако изброим записите в нашата таблица, можем да проверим дали имаме актуализирания курс за конкретния ученик, към който сме се насочили. Ето как работи съхранената процедура за актуализиране.
Заключение
Можете да създадете всяка съхранена процедура в PostgreSQL. Трябва само да разберете синтаксиса, който да следвате, и след това да дефинирате вашата логика за съхранената процедура. Оттам извикайте съхранената процедура и проверете дали се е изпълнила според очакванията. Тази публикация обяснява запаметените процедури в PostgreSQL и предоставя примери как да ги създадете.