Создание хранимых процедур

Если нужно сделать выбор между выполнением оператора SQL, хранящегося в клиентской подсистеме (задаваемого непосредственно или хранящегося в MDB-файле запроса Access) и выполнением того же кода, хранящегося на сервере в виде хранимой процедуры, всегда лучше выбрать хранимую процедуру. На это есть несколько причин.

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

• Использование хранимых процедур позволяет централизовать логику приложения, сократить количество избыточного кода, тем самым упростить сопровождение проекта.

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

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

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

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

При использовании текстового редактора хранимая процедура SQL Server создается с помощью оператора SQL CREATE PROCEDURE.

CREATE PROCEDURE имя_ процедуры

AS

Операторы SQL

Пример:

С помощью оператора UPDATE стоимость блюд в меню увеличивается на 10%

CREATE PROCEDURE procMenuPricesIncrease

AS

UPDATE tblMenu

SET Price = Price*1.1

Параметры и переменные

Хранимые процедуры SQL Server могут иметь параметры и переменные, а также возвращать значения.

CREATE PROCEDURE имя_процедуры

@параметр1 тип_данных [=значение по умолчанию] [OUTPUT]

@параметр2 тип_данных [=значение по умолчанию] [OUTPUT]

…

AS

Операторы_SQL

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

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

Из таблицы Заказчики выбрать строку, соответствующую заказчику с заданным номером:

CREATE PROCEDURE procGetCustomer

@custid INT

As

SELECT * FROM Заказчики

WHERE код_заказчика = @custid

При выполнении хранимой процедуры из окна базы данных Access система сама запрашивает значение каждого параметра, подобно тому, как она это делает при выполнении обычного запроса с параметрами.

Бывает необходимо, чтобы хранимая процедура возвратила информацию другой процедуре - вызывающей процедуре. Для этой цели используются выходные параметры.

Для присваивания значения выходному параметру используется оператор SET:

SET @параметр = значение

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

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

CREATE Procedure procInsertOrder

@orderdate datetime,

@customerid int,

@orderid int OUTPUT

As

set nocount on

INSERT INTO Заказы

(Дата_заказа, код_заказчика)

VALUES

(@orderdate, @customerid)

-- Возвращаем вызывающей программе

-- новое значение Orderid в выходном параметре

SET @orderid = @@IDENTITY

В этой процедуре параметру @orderid присваивается значение специальной переменной T-SQL @@IDENTITY, которая возвращает значение последнего идентификатора записи.

В дополнение к параметрам хранимая процедура может иметь и локальные переменные. Для их объявления предназначен оператор DECLARE:

DECLARE @переменная тип_данных

В качестве вызывающей процедуры можно воспользоваться такой процедурой:

CREATE Procedure procInsertOrderTest

As

DECLARE @intOrderId INT

EXECUTE procInsertOrder

'9/1/2004', 1, @intOrderId OUTPUT

-- возвращаем код нового заказа

SELECT @intOrderId AS Код_нового_заказа

В данном примере процедура procInsertOrderTest выполняет процедуру procInsertOrder с помощью оператора T-SQL Execute.

После этого она возвращает вызывающей программе единственную запись, содержащую последнее присвоенное значение поля идентификатора записи.