Хранимая процедура в 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; |
Здесь мы удаляем хранимую процедуру, которую создали ранее.
Оценить статью:
Загрузка...