Создание и выполнение процедур в SQL

Нередко операция с данными представляет набор инструкций, которые необходимо выполнить в определенной последовательности. Например, при добавлении покупке товара необходимо внести данные в таблицу заказов. Однако перед этим надо проверить, а есть ли покупаемый товар в наличии. Возможно, при этом понадобится проверить еще ряд дополнительных условий. То есть фактически процесс покупки товара охватывает несколько действий, которые должны выполняться в определенной последовательности. И в этом случае более оптимально будет инкапсулировать все эти действия в один объект - хранимую процедуру (stored procedure).

То есть по сути хранимые процедуры представляет набор инструкций, которые выполняются как единое целое. Тем самым хранимые процедуры позволяют упростить комплексные операции и вынести их в единый объект. Изменится процесс покупки товара, соответственно достаточно будет изменить код процедуры. То есть процедура также упрощает управление кодом.

Также хранимые процедуры позволяют ограничить доступ к данным в таблицах и тем самым уменьшить вероятность преднамеренных или неосознанных нежелательных действий в отношении этих данных.

И еще один важный аспект - производительность. Хранимые процедуры обычно выполняются быстрее, чем обычные SQL-инструкции. Все потому что код процедур компилируется один раз при первом ее запуске, а затем сохраняется в скомпилированной форме.

Для создания хранимой процедуры применяется команда CREATE PROCEDURE или CREATE PROC.

Таким образом, хранимая процедура имеет три ключевых особенности: упрощение кода, безопасность и производительность.

Например, пусть в базе данных есть таблица, которая хранит данные о товарах:

CREATE TABLE Products
(

Id INT IDENTITY PRIMARY KEY,

ProductName NVARCHAR(30) NOT NULL,

Manufacturer NVARCHAR(20) NOT NULL,
ProductCount INT DEFAULT 0,

);

Price MONEY NOT NULL

Создадим хранимую процедуру для извлечения данных из этой таблицы:

USE productsdb;
GO

CREATE PROCEDURE ProductSummary AS

SELECT ProductName AS Product, Manufacturer, Price

FROM Products

Поскольку команда CREATE PROCEDURE должна вызываться в отдельном пакете, то после команды USE, которая устанавливает текущую базу данных, используется команда GO для определения нового пакета.

После имени процедуры должно идти ключевое слово AS.

Для отделения тела процедуры от остальной части скрипта код процедуры нередко помещается в блок BEGIN...END:

USE productsdb;
GO

CREATE PROCEDURE ProductSummary AS
BEGIN

SELECT ProductName AS Product, Manufacturer, Price
FROM Products

END;

После добавления процедуры мы ее можем увидеть в узле базы данных в SQL Server Management Studio в подузле Programmability -> Stored Procedures:

И мы сможем управлять процедурой также и через визуальный интерфейс.

Выполнение процедуры

Для выполнения хранимой процедуры вызывается команда EXEC или EXECUTE:

EXEC ProductSummary

Удаление процедуры

Для удаления процедуры применяется команда DROP PROCEDURE:

DROP PROCEDURE ProductSummary