Создание процедуры, возвращающей набор данных

Лабораторная работа № 6. Объекты SQL Server.

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

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

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

По сравнению с программами Transact-SQL, которые хранятся локально на клиентских компьютерах, хранимые процедуры SQL Server имеют следующие преимущества.

· Хранимые процедуры регистрируются на сервере.

· Хранимые процедуры могут иметь атрибуты безопасности.
Пользователи могут обладать разрешениями на выполнение хранимых процедур вместо прямых разрешений для работы с объектами, на которые ссылаются эти процедуры.

· Хранимые процедуры позволяют сделать защиту приложений более надежной.
Параметризованные хранимые процедуры могут защитить приложения от атак, осуществляемых путем инжекции кода SQL.

· Хранимые процедуры поддерживают модульное программирование.
Процедуру можно создать один раз и потом по мере необходимости вызывать ее в программе любое число раз. Это делает обслуживание приложения более удобным и позволяет унифицировать доступ приложений к базе данных.

· Хранимые процедуры представляют собой именованный код, дающий возможность отсроченного связывания.
Это обеспечивает уровень косвенности, упрощающий развитие программного кода.

· Хранимые процедуры позволяют уменьшить сетевой трафик.
Операцию, занимающую сотни строк программного кода Transact-SQL, можно выполнить в одной инструкции, которая обрабатывает процедуру, а не отправляет этот код по сети.

Хранимые процедуры делятся на несколько типов:

1. Пользовательские: Transact-SQL

2. Расширенные хранимые процедуры

3. Системные хранимые процедуры

Создание пользовательских хранимых процедур Transact-SQL.

Все хранимые процедуры в БД находятся в ветке: «Программирование»

Для создания хранимой процедуры используется следующий синтаксис:

CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>

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

<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,

<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>

AS

BEGIN

-- здесь располагаются операторы языка Transact-SQL

SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>

END

Для модификации хранимой процедуры необходимо в полном ее коде заменить слово CREATE на ALTER. При этом можно полностью изменить поведение хранимой процедуры, вплоть до списка параметров.

Создание процедуры, возвращающей набор данных

Создадим процедуру, которая возвращает список товаров указанного заказа по его коду:

CREATE PROCEDURE [dbo].[spOrderInfo]

@OrderID INT – параметр – код заказа

AS

-- собственно тело процедуры

SELECT * FROM [OrderDet]

WHERE OrderID = @OrderID

Для того, что бы выполнить хранимую процедуру необходимо воспользоваться командами EXEC или EXECUTE. Параметры передаются хранимой процедуре после ее названия. Первый параметр передается через пробел, остальные - через запятую.

EXECUTE spOrderInfo 5

Создадим еще одну процедуру, которая возвращает список заказов за заданный промежуток времени. Эта хранимая процедура позволяет не задавать последний аргумент. Если он не определен, то возвращаются все заказы до текущей даты:

CREATE PROCEDURE spOrdersByPeriod

@DataN DATETIME,

@DataK DATETIME = NULL

AS

BEGIN

IF (@DataK IS NULL)

SET @DataK = GETDATE()

SELECT * FROM [Order] WHERE OrderDate BETWEEN @DataN AND @DataK

END

Запустим процедуру с двумя параметрами:

EXECUTE spOrdersByPeriod '01.03.2011', '01.04.2011'

Запустим эту хранимую процедуру с одним параметром:

EXECUTE spOrdersByPeriod '01.03.2011'

Задание:

1. Создать хранимую процедуру, добавляющую и модифицирующую записи в таблице Grup. Процедура принимает два параметра: код группы и название группы. Если код группы не определен (null) или равен 0, то происходит добавление новой записи в таблицу, то есть добавляется новая группа с названием, указанным вторым параметром. Если параметр код группы задан, то происходит изменение названия группы с соответствующим кодом на новое.

2. Создать хранимую процедуру, удаляющую группу с заданным кодом.