Задание 8

Использование хранимых процедур

Содержание работы.

1. Создание хранимой процедуры без параметров, которая возвращает простой набор данных. Эта процедура в работе будет использоваться как базовая.

2. Использование входных параметров.

3. Использование выходных параметров.

4. Создание (самостоятельно) хранимой процедуры для БД Sales с параметрами, подсчитывающую количество товаров во всех заказах, сделанных таким-то покупателем за такой-то период. Предварительно добавьте (если нужно) в БД три записи, две из которых относятся к заказам, сделанным указанным во входном параметре покупателем в указанный период времени. Протестируйте созданную процедуру.

Описание работы

Хранимая процедура - это инструмент, изначально предназначенный для оптимизации процесса извлечения данных.

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

Хранимая процедура представляет собой запрос, хранящийся в базе данных SQL Server, а не в интерфейсном коде на клиентской машине. Зачем хранить запросы в базе данных на сервере? Причины здесь три:

производительность,

компилирование,

управление.

Как хранимые процедуры повышают производительность? Рассмотрим следующий запрос, который выводит все заказы из таблицы dbo.Orders базы данных Sales, сделанные на даты, начиная с 1 января 2008 года:

USE Sales

SELECT ProdID, Qty, OrdDate

FROM dbo.Orders

WHERE OrdDate > '1/1/2008'

ORDER BY OrdDate, ProdID

Хотя этот запрос небольшой (всего пять строк текста), представьте, что будет, если 5000 пользователей в сети будут весь день выполнять его, пересылая со своих машин на сервер через сеть.

Чтобы не допускать перегрузок и поддерживать максимальную скорость работы сети, нужно уменьшить объем кода, пересылаемого с клиентских машин на сервер через сеть, и, таким образом, уменьшить трафик, генерируемый в сети. Для этого нужно всего лишь сохранить код на сервере, а не на клиентских машинах, поместив запрос в хранимую процедуру. После создания хранимой процедуры пользователям потребуется пересылать по сети всего лишь следующий код:

EXEC имя_хранимой_процедуры

Еще одно преимущество хранимых процедур касается компилирования запросов. Когда программа SQL Server компилирует запрос, она считыва­ет его, выискивает такие выражения, как JOIN и WHERE, и определяет, при исполь­зовании какого доступного индекса (если таковой есть) данные будут возвращаться быстрее всего. После определения самых быстрых индексов SQL Server создает план выполнения (представляющий собой набор инструкций запуска запроса) и сохраняет его в памяти. Нерегламентированные запросы практически всегда должны компилировать­ся при запуске, а хранимые процедуры — лишь раз, предварительно. Это означает, что хранимые процедуры практически всегда уже имеют свой план выполнения в памяти. Соответственно, они выполняются быстрее нерегламентированных запросов.

Есть еще одно преимущество хранимых процедур, касающееся управления базой данных. Например, если вам нужно модифицировать существующий запрос, который хранится на пользовательских компьютерах, все эти изменения следует выполнять на всех машинах пользователей. Если вы храните запрос на сервере как хранимую процедуру, то изменения нужно сделать лишь один раз — на сервере. Это может суще­ственно сэкономить время и усилия.

Если запрос запускается редко (нерегла­ментированный запрос), нет реальной необходимости создавать хранимую процедура на сервере. Если же пользователи запускают некоторый запрос регулярно, то имеет смысл создать для него хранимую процедуру.

Самая простая хранимая процедура возвращает результаты, не требуя никаких па­раметров. В этом плане она похожа на запрос.

Данная хранимая процедура будет предназначена для извлечения информации обо всех заказах из таблицы dbo.Orders базы данных Sales, сделанные на даты, начиная с 1 января 2008 года.

1. Откройте SQL Server Management Studio. В окне Object Explorer разверните папки сервера Databases=> Sales=> Programmability.

2. Щелкните правой кнопкой на папке Stored Procedures и выберите команду New Stored Procedure. Откроется редактор запросов SQL Server с шаблоном запроса на создание хранимой процедуры.

1. В предложении CREATE PROCEDURE введите имя процедуры: Show_Orders.

2. В секции Add the parameters for the stored procedure here закомментируйте (или удалите) шаблоны параметров.

1. В секции Insert statements for procedure here измените код, чтобы он выглядел следующим образом (рис.1).

SELECT ProdID, Qty, OrdDate

FROM dbo.Orders

WHERE OrdDate > '1/1/2008'

ORDER BY OrdDate, ProdID

6. Выполните сформированный запрос на создание хранимой процедуры, нажав на кнопку Execute. Имя процедуры должно появиться в списке хранимых процедур в окне SQL explorer.

Рис.1. Создание хранимой процедуры

1. Чтобы сохранить процедуру, щелкните на кнопке Save панели инструментов и в раскрывшемся окне введите имя процедуры: Show_Orders.

2. Чтобы протестировать новую процедуру, откройте новый запрос SQL Server и
   выполните следующий код (рис.2).

USE Sales

EXEC dbo.Show_Orders

1. Закройте окно запроса.

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

Теперь для получения информации пользователи будут пересылать по сети всего лишь одну строку кода (EXEC Show_Orders) вместо пяти, как было перед созданием процедуры.

Единственная проблема, связанная с хранимыми процедурами, состоит в статич­ности всех значений. Если пользователям потребуются данные о продажах с 1 января 1998 года, то эта процедура им не поможет — им придется создавать нерегламентированный запрос.

Использование входных параметров

С технической точки зрения параметры являются временны­ми переменными, поскольку они хранятся в памяти, а их содержимое может изме­няться. Например, в предыдущей хранимой процедуре вместо статичного значения '1/1/2008' следовало использовать входной параметр. Тогда пользователи могли бы задавать дату по своему усмотрению. Чтобы продемонстрировать принцип дей­ствия входных параметров, мы модифицируем ранее созданную хранимую процеду­ру dbo.Show_Orders, чтобы она принимала параметры от пользователей (этим мы продемонстрируем не только принцип действия параметров, но и метод модификации существующей хранимой процедуры).

1. Откройте SQL Server Management Studio. В окне Object Explorer раскрой­те папки сервера Databases=> Sales=> Prograramability=> Stored Procedures.

2. Щелкните правой кнопкой на хранимой процедуре dbo.Show_Sales и выберите команду Modify. Откроется редактор запросов SQL Server с шаблоном запроса на изменение хранимой процедуры.

3. В секции Add the parameters for the stored procedure here вместо шаблона параметра введите имя (@Date), тип (Datatime) и значение по умолчанию (‘1/1/2000’).

4. Чтобы использовать новый параметр, модифицируйте запрос в секции Insert statements for procedure (рис.3):

SELECT ProdID, Qty, OrdDate

FROM dbo.Orders

WHERE OrdDate > @Date

ORDER BY OrdDate, ProdID

5. Чтобы сохранить изменения, щелкните на кнопке Save панели инструментов.

Итак, у нас имеется модифицированная хранимая процедура dbo.Show_ Orders, принимающая параметр пользователя. Осталось протестировать ее.

1. Чтобы протестировать процедуру, откройте новый запрос SQL Server и выпол­ните следующий код:

USE Sales

EXEC dbo.Show_Sales '5/7/2008'

2. Теперь попробуйте ввести другую дату.

USE Sales

EXEC dbo.Show_Sales '5/13/2008'

1. Закройте окно запроса.

Рис. 3. Добавляем в хранимую процедуру параметры

Подумайте хорошенько о том, что вы только что сделали. Вместо того, чтобы вынуждать пользователей извлекать данные лишь для 1 января 2008 года, вы предо­ставили им возможность делать это для любой даты. А потребовалось-то всего доба­вить в начало хранимой процедуры переменную @Date. В нашем случае переменной @Date сначала была присвоена дата 7 мая 2008 года, а затем 13 мая 2008 года. Если пользователь не введет дату, то на этот случай для входного параметра предусмотрено значение по умолчанию (1января 2000 года). Мы это сделали путем добавления оператора @Date datetime = '1/1/2000'.

Однако это далеко не все возможности хранимых процедур. Предположим, что пользователи не хотят видеть результат запроса, а лишь результат выполнения математических операций. Чтобы удовлетворить такие потреб­ности, можно создать хранимую процедуру, использующую как входные, так и выход­ные параметры.

Использование выходных параметров

Выходной параметр противоположен входному. С помощью входного параметра вы задаете хранимой процедуре значение для работы; с помощью выходного параме­тра хранимая процедура возвращает значение, используемое в дальнейших запросах. Выходной параметр создается в том же месте, где и входной — между именем про­цедуры и ключевым словом AS. Единственное отличие состоит в том, что выходной параметр определяется с помощью ключевого слова OUTPUT. В следующем примере мы создадим простую хранимую процедуру калькулятора, чтобы увидеть возможно­сти выходного параметра.

1. Откройте SQL Server Management Studio. В окне Object Explorer разверните
   папки сервера Databases=> Sales=> Programmability.

2. Щелкните правой кнопкой на папке Stored Procedures и выберите команду
   New Stored Procedure. Откроется редактор запросов SQL Server с шаблоном запроса на создание хранимой процедуры.

3. В предложении CREATE PROCEDURE введите имя процедуры: Calc.

4. В секции Add the parameters for the stored procedure here для создания первого входного параметра используйте следующие данные:

• Parameter Name: @ first

• Datatype: int

• Value_Default:: 0

1. Для создания второго входного параметра используйте следующие данные:

• Parameter Name: @sec

• Datatype: int

• Value_Default:: 0

1. Для создания выходного параметра используйте следующие данные:

• Parameter Name: @ret

• Datatype: int

• Value_Default:: 0

12. В блок синтаксиса Transact-SQL введите следующий код (рис. 4):

SET @ret = @first + @sec

13. Чтобы сохранить процедуру, щелкните на кнопке Save панели инструментов.

Рис.4. Формирование хранимой процедуры-операции

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

1. Чтобы протестировать изменения, откройте новый запрос SQL Server и выполните следующий код (обратите внимание на то, что переменная @answer специально предназначена для хранения результата, возвращаемого выходным параметром @ret хранимой процедуры) (рис. 5):