Создание хранимых процедур
На практике часто бывает нужно получить результаты запроса для определенного значения (параметра). Такие запросы называются параметризированными, а соответствующие процедуры создаются с параметрами. Например, для получения записи в таблице "Туристы" по заданной фамилии создаем следующую процедуру:
create proc proc_p1 @Фамилия nvarchar(50) as select * from Туристы where Фамилия=@Фамилия
После знака @ указывается название параметра и его тип. Мы выбрали nvarchar c количеством символов 50, поскольку в самой таблице для поля "Фамилия" установлен этот тип. Попытаемся запустить процедуру:
exec proc_p1
Появляется диагностическое сообщение (рис. 5.5):
Перевод этого сообщения: "Процедура 'proc_p1' ожидает параметр '@Фамилия', который не указан".
Запустим процедуру так:
exec proc_p1 'Андреева'
В результате выводится запись, соответствующая фамилии "Андреева" (рис. 5.6):
Если мы укажем фамилию, которая не содержится в таблице, появится пустая запись (рис. 5.7):
exec proc_p1 'Сидоров'
В таблице 5.2 приводятся примеры хранимых процедур с параметрами.
№ | SQL-конструкция для создания | Команда для извлечения |
---|---|---|
1 |
create proc proc_p1 @Фамилия nvarchar(50) as select * from Туристы where Фамилия=@Фамилия |
exec proc_p1 'Андреева' |
Описание | ||
Извлечение записи из таблицы "Туристы" с заданной фамилией | ||
Результат запуска | ||
![]() | ||
№ | SQL-конструкция для создания | Команда для извлечения |
2 |
create proc proc_p2 @nameTour nvarchar(50) as select * from Туры where Название=@nameTour |
exec proc_p2 'Франция' |
Описание | ||
Извлечение записи из таблицы "Туры" с заданным названием тура. Обратите внимание на название параметра "nameTour " - он может быть произвольным, не обязательно, чтобы он совпадал с заголовком столбца извлекаемой таблицы | ||
Результат запуска | ||
![]() | ||
№ | SQL-конструкция для создания | Команда для извлечения |
3 |
create procedure proc_p3 @Фамилия nvarchar(50) as select * from Туристы inner join Информацияотуристах on Туристы.КодТуриста = Информацияотуристах.КодТуриста where Туристы.Фамилия = @Фамилия |
exec proc_p3 'Андреева' |
Описание | ||
Вывод родительской и дочерней записей с заданной фамилией из таблиц "Туристы" и "Информацияотуристах" | ||
Результат запуска | ||
![]() | ||
№ | SQL-конструкция для создания | Команда для извлечения |
4 |
create procedure proc_p4 @nameTour nvarchar(50) as select * from Туры inner join Сезоны on Туры.Кодтура=Сезоны.Кодтура where Туры.Название = @nameTour |
exec proc_p4 'Франция' |
Описание | ||
Вывод родительской и дочерней записей с заданной названием тура из таблиц "Туры" и "Сезоны" | ||
Результат запуска (изображение разрезано) | ||
![]() | ||
№ | SQL-конструкция для создания | Команда для извлечения |
5 |
create proc proc_p5 @nameTour nvarchar(50), @Курс float as update Туры set Цена=Цена/(@Курс) where Название=@nameTour |
exec proc_p5 'Франция', 26 exec proc_p5 @nameTour = 'Франция', @Курс= 26 select * from Туры |
Описание | ||
Процедура с двумя входными параметрами - названием тура и курсом валюты. При извлечении процедуры они последовательно указываются. Поскольку в самом запросе используется оператор update, не возвращающий данных, то для просмотра результата следует извлечь измененную таблицу оператором select | ||
Результат запуска | ||
(1 row(s) affected)
После запуска оператора select:![]() | ||
№ | SQL-конструкция для создания | Команда для извлечения |
6 |
create proc proc_p6 @nameTour nvarchar(50), @Курс float = 26 as update Туры set Цена=Цена/(@Курс) where Название=@nameTour |
exec proc_p6 'Таиланд' exec proc_p6 'Таиланд', 28 |
Описание | ||
Процедура с двумя входными параметрами, причем один их них - @Курс имеет значение по умолчанию. При запуске процедуры достаточно указать значение первого параметра - для второго параметра будет использоваться его значение по умолчанию. При указании значений двух параметров будет использоваться введенное значение | ||
Результат запуска | ||
Запускаем процедуру с одним входным параметром:exec proc_p6 'Таиланд' exec proc_p6 'Таиланд', 28 |
Процедуры с выходными параметрами позволяют возвращать значения, получаемые в результате обработки SQL-конструкции при подаче определенного параметра. Представим, что нам нужно получать фамилию туриста по его коду (полю "Кодтуриста"). Создадим следующую процедуру:
create proc proc_po1 @TouristID int, @LastName nvarchar(60) output as select @LastName = Фамилия from Туристы where Кодтуриста = @TouristID
Оператор output указывает на то, что выходным параметром здесь будет @LastName. Запустим эту процедуру, извлекая фамилию туриста, значение поля "Кодтуриста" которого равно "4":
declare @LastName nvarchar(60) exec proc_po1 '4', @LastName output select @LastName
Оператор declare нужен для объявления поля, в которое будет выводиться значение. Получаем фамилию туриста (рис. 5.8)
Для задания названия столбца можно применить псевдоним:
declare @LastName nvarchar(60) exec proc_po1 '4', @LastName output select @LastName as 'Фамилия туриста'
Теперь столбец имеет заголовок (рис. 5.9):
В таблице 5.3 приводятся примеры хранимых процедур с входными и выходными параметрами.
Для удаления хранимой процедуры используется оператор drop:
drop proc proc1
Здесь proc1 - название процедуры (см. табл. 5.1).