Технология Microsoft ADO.NET


Создание хранимых процедур в SQL Query Analyzer - часть 3


/p>

На практике часто бывает нужно получить результаты запроса для определенного значения (параметра). Такие запросы называются параметризированными, а соответствующие процедуры создаются с параметрами. Например, для получения записи в таблице "Туристы" по заданной фамилии создаем следующую процедуру:

create proc proc_p1 @Фамилия nvarchar(50) as select * from Туристы where Фамилия=@Фамилия

После знака @ указывается название параметра и его тип. Мы выбрали nvarchar c количеством символов 50, поскольку в самой таблице для поля "Фамилия" установлен этот тип. Попытаемся запустить процедуру:

exec proc_p1

Появляется диагностическое сообщение (рис. 5.5):

Сообщение при запуске процедуры exec proc_p1

Рис. 5.5.  Сообщение при запуске процедуры exec proc_p1

Перевод этого сообщения: "Процедура 'proc_p1' ожидает параметр '@Фамилия', который не указан".

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

exec proc_p1 'Андреева'

В результате выводится запись, соответствующая фамилии "Андреева" (рис. 5.6):

Запуск процедуры proc_p1

Рис. 5.6.  Запуск процедуры proc_p1

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

exec proc_p1 'Сидоров'

Запуск процедуры proc_p1. Фамилия не найдена

Рис. 5.7.  Запуск процедуры proc_p1. Фамилия не найдена

В таблице 5.2 приводятся примеры хранимых процедур с параметрами.

Таблица 5.2. Хранимые процедуры с параметрами

№SQL-конструкция для созданияКоманда для извлечения№SQL-конструкция для созданияКоманда для извлечения№SQL-конструкция для созданияКоманда для извлечения№SQL-конструкция для созданияКоманда для извлечения№SQL-конструкция для созданияКоманда для извлечения№SQL-конструкция для созданияКоманда для извлечения
1

create proc proc_p1 @Фамилия nvarchar(50) as select * from Туристы where Фамилия=@Фамилия

exec proc_p1 'Андреева'

Описание
Извлечение записи из таблицы "Туристы" с заданной фамилией
Результат запуска
2

create proc proc_p2 @nameTour nvarchar(50) as select * from Туры where Название=@nameTour

exec proc_p2 'Франция'

Описание
Извлечение записи из таблицы "Туры" с заданным названием тура. Обратите внимание на название параметра "nameTour " - он может быть произвольным, не обязательно, чтобы он совпадал с заголовком столбца извлекаемой таблицы
Результат запуска
3

create procedure proc_p3 @Фамилия nvarchar(50) as select * from Туристы inner join Информацияотуристах on Туристы.КодТуриста = Информацияотуристах.КодТуриста where Туристы.Фамилия = @Фамилия

exec proc_p3 'Андреева'

Описание
Вывод родительской и дочерней записей с заданной фамилией из таблиц "Туристы" и "Информацияотуристах"
Результат запуска
4

create procedure proc_p4 @nameTour nvarchar(50) as select * from Туры inner join Сезоны on Туры.Кодтура=Сезоны.Кодтура where Туры.Название = @nameTour

exec proc_p4 'Франция'

Описание
Вывод родительской и дочерней записей с заданной названием тура из таблиц "Туры" и "Сезоны"
Результат запуска (изображение разрезано)
5

create proc proc_p5 @nameTour nvarchar(50), @Курс float as update Туры set Цена=Цена/(@Курс) where Название=@nameTour

exec proc_p5 'Франция', 26

или exec proc_p5 @nameTour = 'Франция', @Курс= 26

Просматриваем изменения простым SQL - запросом: select * from Туры

Описание
Процедура с двумя входными параметрами - названием тура и курсом валюты. При извлечении процедуры они последовательно указываются. Поскольку в самом запросе используется оператор update, не возвращающий данных, то для просмотра результата следует извлечь измененную таблицу оператором select
Результат запуска
(1 row(s) affected) После запуска оператора select:
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 'Таиланд'

Для просмотра используем оператор select:


Запускаем программу SQL Server Enterprise Manager, восстанавливаем значение поля "Цена" для тура "Таиланд" и запускаем процедуру с двумя входными параметрами:


exec proc_p6 'Таиланд', 28

Теперь используется введенное значение второго параметра:

<


Начало  Назад  Вперед



Книжный магазин