Збережена процедура в SQL є набором команд, які виконують певні дії. Збережені процедури схожі на функції в програмуванні і створюються для того, щоб можна було повторно використовувати набір команд. Вони можуть приймати параметри та виконувати операції, коли ми їх викликаємо.
Створення збереженої процедури
Збережена процедура створюється за допомогою оператора CREATE PROCEDURE, за яким слідує необхідний набір SQL-команд. Наприклад:
SQL Server
|
1 2 3 4 |
CREATE PROCEDURE us_customers AS SELECT customer_id, first_name FROM Customers WHERE Country = 'USA'; |
PostgreSQL
|
1 2 3 4 5 6 7 |
CREATE PROCEDURE us_customers () LANGUAGE SQL AS $$ SELECT customer_id, first_name FROM Customers WHERE Country = 'USA'; $$; |
MySQL
|
1 2 3 4 5 6 7 8 |
DELIMITER // CREATE PROCEDURE us_customers () BEGIN SELECT customer_id, first_name FROM Customers WHERE Country = 'USA'; END // DELIMITER ; |
Oracle
|
1 2 3 4 5 6 7 8 9 |
CREATE PROCEDURE us_customers AS res SYS_REFCURSOR; BEGIN open res for SELECT customer_id, first_name FROM Customers WHERE country = 'USA'; DBMS_SQL.RETURN_RESULT(res); END; |
Виконання збереженої процедури
Тепер, якщо нам потрібно отримати всіх клієнтів, які живуть у США, ми можемо просто викликати збережену процедуру, яку написали раніше. Наприклад:
SQL Server, Oracle
|
1 |
EXEC us_customers; |
PostgreSQL, MySQL
|
1 |
CALL us_customers(); |
Параметризована збережена процедура
Ми можемо передавати власні дані в збережені процедури так, щоб один і той же набір SQL-команд працював по-різному для різних даних.
Припустимо, ми хочемо отримати рядки, в яких стовпці country є значення USA. Наш запит виглядатиме так:
|
1 2 3 |
SELECT * FROM Customers WHERE country = 'USA'; |
І знову, якщо ми хочемо отримати рядки, в яких у стовпці country є значення UK, ми виконаємо наступне:
|
1 2 3 |
SELECT * FROM Customers WHERE country = 'UK'; |
Зверніть увагу, що в цих двох прикладах все те саме, за винятком значення для пошуку в стовпці country.
Таким чином, замість повторного написання одного і того ж коду, ми можемо створити збережену процедуру і просто викликати її з різними значеннями. Наприклад:
SQL Server
|
1 2 3 4 |
CREATE PROCEDURE ctr_customers @ctr VARCHAR(50) AS SELECT customer_id, first_name FROM Customers WHERE Country = @ctr; |
PostgreSQL
|
1 2 3 4 5 6 7 |
CREATE PROCEDURE ctr_customers (ctr VARCHAR(50)) LANGUAGE SQL AS $$ SELECT customer_id, first_name FROM Customers WHERE Country = ctr; $$; |
MySQL
|
1 2 3 4 5 6 7 8 |
DELIMITER // CREATE PROCEDURE ctr_customers (ctr VARCHAR(50)) BEGIN SELECT customer_id, first_name FROM Customers WHERE Country = ctr; END // DELIMITER ; |
Тут ctr — це параметр, який нам потрібно вказати при виклику збереженої процедури. Наприклад:
SQL Server
|
1 2 3 4 5 |
-- Викликаємо збережену процедуру, вказавши аргумент 'USA' EXEC ctr_customers 'USA'; -- Викликаємо ту ж збережену процедуру, але тепер з аргументом 'UK' EXEC ctr_customers 'UK'; |
PostgreSQL, MySQL
|
1 2 3 4 5 |
-- Викликаємо збережену процедуру, вказавши аргумент 'USA' CALL ctr_customers ('USA'); -- Викликаємо ту ж збережену процедуру, але тепер з аргументом 'UK' CALL ctr_customers ('UK'); |
Декілька параметризованих процедур
Збережена процедура також може приймати декілька параметрів. Наприклад:
SQL Server
|
1 2 3 4 5 6 7 8 |
-- Створюємо збережену процедуру, яка приймає 2 параметри: cus_id та max_amount CREATE PROCEDURE order_details @cus_id INT, @max_amount INT AS SELECT Customers.customer_id, Customers.first_name, Orders.amount FROM Customers JOIN Orders ON Customers.customer_id = Orders.customer_id where Customers.customer_id = @cus_id AND Orders.amount < @max_amount; |
PostgreSQL
|
1 2 3 4 5 6 7 8 9 10 11 |
-- Створюємо збережену процедуру, яка приймає 2 параметри: cus_id та max_amount CREATE PROCEDURE order_details (cus_id INT, max_amount INT) LANGUAGE SQL AS $$ SELECT Customers.customer_id, Customers.first_name, Orders.amount FROM Customers JOIN Orders ON Customers.customer_id = Orders.customer_id where Customers.customer_id = cus_id AND Orders.amount < max_amount; $$; |
MySQL
|
1 2 3 4 5 6 7 8 9 10 11 12 |
-- Створюємо збережену процедуру, яка приймає 2 параметри: cus_id та max_amount DELIMITER // CREATE PROCEDURE order_details (cus_id INT, max_amount INT) BEGIN SELECT Customers.customer_id, Customers.first_name, Orders.amount FROM Customers JOIN Orders ON Customers.customer_id = Orders.customer_id where Customers.customer_id = cus_id AND Orders.amount < max_amount; END // DELIMITER ; |
Тепер, щоб викликати збережену процедуру, нам потрібно всього лише написати:
SQL Server
|
1 |
EXEC order_details 4, 400; |
PostgreSQL
|
1 |
CALL order_details (4, 400); |
MySQL
|
1 |
CALL order_details (4, 400); |
Тут ми передаємо два аргументи в процедуру.
Примітка: Дізнатися про різницю між параметром та аргументом функції можна на відповідному уроці.
Видалення збереженої процедури
Ми можемо видалити збережену процедуру за допомогою команди DROP PROCEDURE. Наприклад:
|
1 |
DROP PROCEDURE order_details; |
Тут ми видаляємо збережену процедуру, яку створили раніше.
Оцінити статтю:
(14 оцінок, середня: 5,00 з 5)
Завантаження...