Хранимые процедуры в Microsoft SQL Server

Хранимые процедуры

Часто при работе с БД используют одни и те же запросы, либо набор последовательных запросов. Хранимые процедуры позволяют объединить последовательность запросов и сохранить их на сервере. Это очень удобный инструмент.

Синтаксис:

CREATE PROCEDURE имя_процедуры (параметры) begin

операторы

end

Параметры - это те данные, которые будут передаваться процедуре при ее вызове, а операторы - это собственно запросы. Вместо ключевого слова PROCEDURE можно использовать PROC

Пример 1:

Стандартный запрос на добавление данных имеет вид:

INSERT INTO customers (name, email) VALUE ('Иванов Сергей', 'sergo@mail.ru');

Данный запрос используется каждый раз, когда будет добавляться новый покупателя, то вполне уместно оформить его в виде процедуры:

CREATE PROCEDURE ins_cust(n CHAR(50), e CHAR(50)) begin

insert into customers (name, email) value (n, e);

end

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

Пример2:

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

• 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;

Stored Procedures : " width="640"

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

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

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

команда  EXEC  или  EXECUTE:

• EXEC ProductSummary

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

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

PROCEDURE :

1. DROP PROCEDURE ProductSummary

Параметры в процедурах

Процедуры могут принимать параметры. Параметры

бывают входными - с их помощью в процедуру можно

передать некоторые значения. И также параметры бывают

выходными - они позволяют возвратить из процедуры

некоторое значение.

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

• USE productsdb;
• 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 AddProduct
•      @name VARCHAR(20),
•      @manufacturer VARCHAR(20),
•      @count INT,
•      @price MONEY
• AS
• INSERT INTO Products(ProductName, Manufacturer, ProductCount, Price)
• VALUES (@name, @manufacturer, @count, @price)

После названия процедуры идет список входных

параметров, которые определяются также как и

переменные - название начинается с символа  @ , а после

названия идет тип переменной.

С помощью команды INSERT значения этих параметров

будут передаваться в таблицу Products.

Используем эту процедуру:

• USE productsdb;
• DECLARE @prodName VARCHAR(20), @company VARCHAR(20);
• DECLARE @prodCount INT, @price MONEY
• SET @prodName = 'Galaxy C7'
• SET @company = 'Samsung'
• SET @price = 22000
• SET @prodCount = 5 
• EXEC AddProduct @prodName, @company, @prodCount, @price 
• SELECT * FROM Products

Здесь передаваемые в процедуру значения определяются через переменные. При вызове процедуры ей через запятую передаются значения. При этом значения передаются параметрам процедуры  по позиции . Так как первым определен параметр @name, то ему будет передаваться первое значение - значение переменной @prodName. Второму параметру - @manufacturer передается второе значение - значение переменной @company и так далее. Главное, чтобы между передаваемыми значениями и параметрами процедуры было соответствие по типу данных.

Также можно было бы передать непосредственно

значения:

• EXEC AddProduct 'Galaxy C7', 'Samsung', 5, 22000

Также значения параметрам процедуры можно передавать по имени:

• USE productsdb;
• DECLARE @prodName VARCHAR(20), @company VARCHAR(20);
• SET @prodName = 'Honor 9'
• SET @company = 'Huawei' 
• EXEC AddProduct @name = @prodName,
•                  @manufacturer=@company,
•                  @count = 3,
•                  @price = 18000

Необязательные параметры

Параметры можно отмечать как необязательные,

присваивая им некоторое значение по умолчанию.

Например, в случае выше мы можем автоматически

устанавливать для количества товара значение 1, если

соответствующее значение не передано в процедуру:

• USE productsdb;
• GO
• CREATE PROCEDURE AddProductWithOptionalCount
•      @name NVARCHAR(20),
•      @manufacturer NVARCHAR(20),
•      @price MONEY,
•      @count INT = 1
• AS
• INSERT INTO Products(ProductName, Manufacturer, ProductCount, Price)
• VALUES(@name, @manufacturer, @count, @price)

При этом необязательные параметры лучше помещать в

конце списка параметров процедуры.

DECLARE @prodName VARCHAR(20), @company

VARCHAR(20), @price MONEY

SET @prodName = 'Redmi Note 5A‘

SET @company = 'Xiaomi‘

SET @price = 22000 

EXEC AddProductWithOptionalCount @prodName, @company, @price 

SELECT * FROM Products

В этом случае для параметра @count в процедуру можно

не передавать значение.

Выходные параметры и возвращение результата

Выходные параметры позволяют возвратить из процедуры

некоторый результат. Выходные параметры определяются

с помощью ключевого слова  OUTPUT . Например,

определим процедуру :

• USE productsdb;
• GO
• CREATE PROCEDURE GetPriceStats
•      @minPrice MONEY OUTPUT,
•      @maxPrice MONEY OUTPUT
• AS
• SELECT @minPrice = MIN(Price),  @maxPrice = MAX(Price)
• FROM Products

При вызове процедуры для выходных параметров

передаются переменные с ключевым словом OUTPUT :

• USE productsdb;
• DECLARE @minPrice MONEY, @maxPrice MONEY 
• EXEC GetPriceStats @minPrice OUTPUT, @maxPrice OUTPUT 
• PRINT 'Минимальная цена ' + CONVERT(VARCHAR, @minPrice)
• PRINT 'Максимальная цена ' + CONVERT(VARCHAR, @maxPrice)

Также можно сочетать входные и выходные параметры.

Например, определим процедуру, которая добавляет

новую строку в таблицу и возвращает ее id:

• USE productsdb;
• GO
•  
• CREATE PROCEDURE CreateProduct
•      @name NVARCHAR(20),
•      @manufacturer NVARCHAR(20),
•      @count INT,
•      @price MONEY,
•      @id INT OUTPUT
• AS
•       INSERT INTO Products(ProductName, Manufacturer, ProductCount, Price)
•      VALUES (@name, @manufacturer, @count, @price)
•       SET @id = @@IDENTITY

С помощью глобальной переменной  @@IDENTITY  можно

получить идентификатор добавленной записи.

При вызове этой процедуры ей также по позиции

передаются все входные и выходные параметры:

• USE productsdb;
• DECLARE @id INT
• EXEC CreateProduct 'LG V30', 'LG', 3, 28000, @id OUTPUT 
• PRINT @id

Возвращение значения

Кроме передачи результата выполнения через выходные

параметры хранимая процедура также может возвращать

какое-либо значение типа  INT  с помощью

оператора  RETURN .

Например, возвратим среднюю цену на товары:

• USE productsdb;
• GO
• CREATE PROCEDURE GetAvgPrice AS
• DECLARE @avgPrice MONEY
• SELECT @avgPrice = AVG(Price)
• FROM Products
• RETURN @avgPrice;

После оператора RETURN указывается возвращаемое

значение. В данном случае это значение переменной

@avgPrice.

Вызов данной процедуры:

• USE productsdb; 
• DECLARE @result MONEY
• EXEC @result = GetAvgPrice
• PRINT @result

Для получения результата процедуры ее значение

сохраняется в переменную (в данном случае в @result )

RETURN  возвращает только целочисленные значения.