НОУ ИНТУИТ | Технология Microsoft ADO .NET. Лекция 5: Создание хранимых процедур

Учитесь и получайте официальные документы БЕСПЛАТНО. Вы можете поддержать наш проект.

Регистрация Вход

Твой путь к знаниям!

Опубликован: 05.08.2007 | Уровень: специалист | Доступ: платный

|

Вам нравится? Нравится 17 студентам

| Поделиться |

Поддержать программу

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

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

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

exec proc_p1

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

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

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

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

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

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

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

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

exec proc_p1 'Сидоров'

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

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

Таблица 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 Просматриваем изменения простым SQL - запросом: 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 'Таиланд' Для просмотра используем оператор select: Запускаем программу SQL Server Enterprise Manager, восстанавливаем значение поля "Цена" для тура "Таиланд" и запускаем процедуру с двумя входными параметрами: 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)

Рис. 5.8. Результат запуска процедуры proc_po1

Для задания названия столбца можно применить псевдоним:

declare @LastName nvarchar(60)
exec proc_po1 '4',
@LastName output
select @LastName as 'Фамилия туриста'

Теперь столбец имеет заголовок (рис. 5.9):

Рис. 5.9. Результат запуска процедуры proc_po1. Применение псевдонима

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

Таблица 5.3. Хранимые процедуры с входными и выходными параметрами
№	SQL-конструкция для создания	Команда для извлечения
1	create proc proc_po1 @TouristID int, @LastName nvarchar(60) output as select @LastName = Фамилия from Туристы where Кодтуриста = @TouristID	declare @LastName nvarchar(60) exec proc_po1 '4', @LastName output select @LastName as 'Фамилия туриста'
	Описание
	Извлечение фамилии туриста по заданному коду
	Результат запуска

№	SQL-конструкция для создания	Команда для извлечения
2	create proc proc_po2 @CountCity int output as select @CountCity = count(Кодтуриста) from Информацияотуристах where Город like '%рг%'	declare @CountCity int exec proc_po2 @CountCity output select @CountCity as 'Количество туристов, проживающех в городах %рг%'
	Описание
	Подсчет количества туристов из городов, имеющих в своем названии сочетание букв "рг". Следует ожидать число три (Екатеринбург, Оренбург, Санкт-Петербург)
	Результат запуска

№	SQL-конструкция для создания	Команда для извлечения
3	create proc proc_po3 @TouristID int, @CountTour int output as select @CountTour = count(Туры.Кодтура) from Путевки inner join Сезоны on Путевки.Кодсезона = Сезоны.Кодсезона inner join Туры on Туры.Кодтура = Сезоны.Кодтура inner join Туристы on Путевки.Кодтуриста = Туристы.Кодтуриста where Туристы.Кодтуриста = @TouristID	exec proc_po3 '1', @CountTour output select @CountTour AS 'Количество туров, которые турист посетил'
	Описание
	Подсчет количества туров, которых посетил турист с заданным значением поля "Кодтуриста"
	Результат запуска

№	SQL-конструкция для создания	Команда для извлечения
4	create proc proc_po4 @TouristID int, @BeginDate smalldatetime, @EndDate smalldatetime, @SumMoney money output as select @SumMoney = sum(Сумма) from Оплата inner join Путевки on Оплата.Кодпутевки = Путевки.Кодпутевки inner join Туристы on Путевки.Кодтуриста = Туристы.Кодтуриста where Датаоплаты between(@BeginDate) and (@EndDate) and Туристы.Кодтуриста = @TouristID	declare @TouristID int, @BeginDate smalldatetime, @EndDate smalldatetime, @SumMoney money exec proc_po4 '1', '1/20/2007', '1/20/2008', @SumMoney output select @SumMoney as 'Общая сумма за период'
	Описание
	Подсчет общей суммы, которую заплатил данный турист за определенный период. Турист со значением "1" поля "Кодтуриста" внес оплату 4/13/2007
	Результат запуска

№	SQL-конструкция для создания	Команда для извлечения
5	create proc proc_po5 @CodeTour int, @ChisloPutevok int output as select @ChisloPutevok = count(Путевки.Кодсезона) from Путевки inner join Сезоны on Путевки.Кодсезона = Сезоны.Кодсезона inner join Туры on Туры.Кодтура = Сезоны.Кодтура where Сезоны.Кодтура = @CodeTour	declare @ChisloPutevok int exec proc_po5 '1', @ChisloPutevok output select @ChisloPutevok AS 'Число путевок, проданных в этом туре'
	Описание
	Подсчет количества путевок, проданных по заданному туру
	Результат запуска