Процедуры

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

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

create procedure Имя_процедуры;Номер

@Имя_переменной1 Тип = Первоначальное_значение [OUTPUT],

@Имя_переменной2 Тип = Первоначальное_значение [OUTPUT],

...

as

[тело процедуры]

Имя_процедуры – название данной процедуры в соответствии с общими правилами именования объектов базы данных. Если в названии процедуры используются недопустимые знаки, имя процедуры помещается в квадратные скобки [].

Номер – номер группы процедур.

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

Тип – тип входного параметра.

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

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

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

На SQL данная процедура выглядит следующим образом:

create procedure ostscl;1

@kod_tov int = NULL, {код товара}

@name_tov varchar(30) = NULL, {наименование товара}

@Kol decimal(7,2) = NULL output, {остаток на складе, изменяется}

@Kol_vo decimal(7,2) = NULL, {запрашиваемое количество}

@otv varchar(40) = null OUTPUT, {ответ, изменяется}

@nuzh decimal(7,2) = null OUTPUT {необходимое количество (дополнительно)}

as

if @kod_tov IS NOT NULL {если дан код товара}

begin

select @Kol=sum(ost_post) from postavka where tovar_id=@kod_tov

{считает суммарный остаток товара на складе, складывая остатки каждой поставки этого товара}

if (@kol_vo<=@Kol) {если запрашиваемое количество меньше остатка на сладе}

begin

set @otv='требуемое количество есть на складе'

set @nuzh=null

end

else

begin

set @otv='для выполнения заказа требуется еще '

set @nuzh=@kol_vo-@Kol

end

end

else {делает то же самое, но если задано наименование товара}

begin

select @Kol=sum(ost_post)

from postavka join tovar on (tovar.id_tovar=postavka.tovar_id)

where tovar.t_name=@name_tov

if @kol_vo<=@Kol

begin

set @otv='требуемое количество есть на складе'

set @nuzh=null

end

else

begin

set @otv='для выполнения заказа требуется еще'

set @nuzh=@kol_vo-@Kol

end

end

После написания нажимаем кнопку Execute.

Процедура успешно сохранилась в нашей базе данных.

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

declare @kod_tov int, @name_tov varchar(30), @Kol decimal(7,2), @Kol_vo decimal(7,2), @otv varchar(40), @nuzh decimal(7,2)

set @kod_tov=1

set @kol_vo=20

Обращение к процедуре выполняется с помощью команды exec.

exec ostscl;1 @kod_tov, @name_tov, @Kol output, @Kol_vo, @otv output, @nuzh output

select @otv,@nuzh

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

Выполним команду:

Если задать @kol_vo=200, то ответ будет другой.

Также можно задать не код товара, а его наименование.

declare @kod int, @name varchar(30), @Kol decimal(7,2), @Kol_vo decimal(7,2), @otvet varchar(40), @treb decimal(7,2)

set @name='Радужная форель'

set @kol_vo=200

exec ostscl;1 @kod, @name, @Kol output, @Kol_vo, @otvet output, @treb output

select @otvet, @treb

Получим:

Дата добавления: 2015-07-08