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

Создание хранимых процедур в SQL Query Analyzer


Хранимая процедура - это одна или несколько SQL-конструкций, которые записаны в базе данных. Задача администрирования базы данных включает в себя в первую очередь распределение уровней доступа к ней. Разрешение выполнения обычных SQL-запросов большому числу пользователей может стать причиной неисправностей из-за неверного запроса или их группы. Чтобы их избежать, разработчики базы данных могут создать ряд хранимых процедур для работы с данными и полностью запретить доступ для обычных запросов. Такой подход при прочих равных условиях обеспечивает большую стабильность и надежность работы. Это одна из главных причин создания собственных хранимых процедур. Другие причины - быстрое выполнение, разбиение больших задач на малые модули, уменьшение нагрузки на сеть - значительно облегчают процесс разработки и обслуживания архитектуры "клиент-сервер".

Сами базы данных используют огромное количество встроенных хранимых процедур для функционирования. Запустим программу SQL Query Analyzer1), входящую в пакет Microsoft SQL Server 2000. Создадим новый бланк (Ctrl +N) и введем в нем следующее:

exec sp_databases

В результате выполнения выводится список всех баз, созданных на данном локальном сервере (рис. 5.1):


увеличить изображение
Рис. 5.1.  Программа SQL Query Analyzer. Выполнение запроса. Выделена процедура "sp_databases"

Мы запустили одну из системных хранимых процедур, которая находится в базе master. Ее можно найти в списке "Stored Procedures" базы - все системные хранимые процедуры имеют приставку "sp". Обратите внимание, что системные процедуры выделяются бордовым цветом и для многих из них не нужно указывать в выпадающем списке конкретную базу. Запустим еще одну процедуру:

exec sp_monitor

В результате ее выполнения выводится статистика текущего SQL-сервера (рис. 5.2).


Рис. 5.2.  Статистика Microsoft SQL-Server

Для вывода списка хранимых процедур в учебной базе Northwind используем следующую процедуру:


USE Northwind exec sp_stored_procedures

Можно было, конечно, указать название и в выпадающем списке
. База Northwind содержит 38 хранимых процедур (рис. 5.3), большая часть из которых - системные. Для просмотра списка в других базах следует вызвать для них название этой же процедуры.


Рис. 5.3.  Вывод списка хранимых процедур базы данных Northwind

Перейдем к созданию своих собственных процедур. Скопируйте базу BDTur_firm.mdb из лекции 1, назовите ее "BDTur_firm2.mdb". Открываем ее в Microsoft Access и в названиях таблиц и полей удаляем все пробелы. Например, таблица "Информация о туристах" будет теперь называться так: "Информацияотуристах", а поле "Код туриста" станет полем "Кодтуриста". Затем конвертируем базу в формат Microsoft SQL и присоединяем ее к локальному серверу2). Запускаем SQL Query Analyzer, открываем чистый бланк и вводим запрос3):

create procedure proc1 as select Кодтуриста, Фамилия, Имя, Отчество from Туристы

Здесь create procedure - оператор, указывающий на создание хранимой процедуры, proc1 - ее название, далее после оператора as следует обычный SQL-запрос. Запускаем его - появляется сообщение:

The COMMAND(s) completed successfully.

Это означает, что мы все сделали правильно и команда создала процедуру proc1. Для просмотра результата вызываем ее:

exec proc1

Появляется уже знакомое нам извлечение всех записей таблицы "Туристы" со всеми записями (рис. 5.4):


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

Как видите, создание содержимого хранимой процедуры не отличается ничем от создания обычного SQL-запроса. В таблице 5.1 приведены примеры хранимых процедур:

Таблица 5.1. Примеры хранимых процедур№SQL-конструкция для созданияКоманда для извлеченияОписание
1

create procedure proc1 as select Кодтуриста, Фамилия, Имя, Отчество from Туристы

exec proc1Вывод всех записей таблицы Туристы
Результат запуска
2



create procedure proc2 as select top 3 Фамилия from туристы

exec proc2

Вывод первых трех значений поля Фамилия таблицы Туристы
Результат запуска
3

create procedure proc3 as select * from туристы where Фамилия = 'Андреева'

exec proc3

Вывод всех полей таблицы Туристы, содержащих в поле Фамилия значение " Андреева "
Результат запуска
4

create procedure proc4 as select count (*) from Туристы

exec proc4

Подсчет числа записей таблицы Туристы
Результат запуска
5

create procedure proc5 as select sum(Сумма) from Оплата

exec proc5

Подсчет значений поля Сумма таблицы Оплата
Результат запуска
6

create procedure proc6 as select max(Цена) from Туры

exec proc6

Вывод максимального значения поля Цена таблицы Туры
Результат запуска
7

create procedure proc7 as select min(Цена) from Туры

exec proc7

Вывод минимального значения поля Цена таблицы Туры
Результат запуска
8

create procedure proc8 as select * from Туристы where Фамилия like '%и%'

exec proc8 Вывод всех записей таблицы Туристы, содержащих в значении поля Фамилия букву "и" (в любой части слова)
Результат запуска
9

create procedure proc9 as select * from Туристы inner join Информацияотуристах on Туристы.КодТуриста= Информацияотуристах.КодТуриста

exec proc9

Операция inner join объединяет записи из двух таблиц, если поле (поля), по которому связаны эти таблицы, содержат одинаковые значения. Общий синтаксис выглядит следующим образом: from таблица1 inner join таблица2 on таблица1.поле1 оператор_сравнения таблица2.поле2
Результат запуска
10

create procedure proc10 as select * from Туристы left join Информацияотуристах on Туристы.КодТуриста= Информацияотуристах.КодТуриста
exec proc10

Прежде чем создать эту процедуру и затем ее извлечь, запускаем программу SQL Server Enterprise Manager, выделяем таблицу "Туристы" базы данных " BDTur_firm2". Щелкаем на ней правой кнопкой и в появившемся меню выбираем Open Table - Return all rows. Теперь добавляем запись - "Корнеев Глеб Алексеевич". В результате в таблице "Туристы" у нас получилось 6 записей, а в связанной с ней таблице "Информацияотуристах" - 5. В SQL Query Analyzer создаем хранимую процедуру и запускаем ее. Операция left join используется для создания так называемого левого внешнего соединения. С помощью объединения выбираются все записи первой (левой) таблицы, даже если они не соответствуют записям во второй (правой) таблице. Общий синтаксис имеет вид: from таблица1 left join таблица2 on таблица1.поле1 оператор_сравнения таблица2.поле2.

Здесь в таблице "Информацияотуристах" нет связанной записи для туриста "Корнеев Глеб Алексеевич", поэтому соответствующие поля заполняются значениями null
Результат запуска
11

create procedure proc11 as select * from Туристы right join Информацияотуристах on Туристы.КодТуриста= Информацияотуристах.КодТуриста

exec proc11

Перед созданием этого запроса нам снова придется изменить таблицы. В SQL Server Enterprise Manager удаляем шестую запись в таблице "Туристы", добавляем шестую запись в таблицу " Информацияотуристах"(значения полей - см. на рисунке). Операция right join используется для создания правого внешнего соединения. С его помощью выбираются все записи второй (правой) таблицы, даже если они не соответствуют записям в первой (левой) таблице. Общий синтаксис имеет вид: from таблица1 right join таблица2 on таблица1.поле1 оператор_сравнения таблица2.поле2.
Результат запуска
<


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

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-конструкция для созданияКоманда для извлечения№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

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


Процедуры с выходными параметрами позволяют возвращать значения, получаемые в результате обработки 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-конструкция для созданияКоманда для извлечения№SQL-конструкция для созданияКоманда для извлечения№SQL-конструкция для созданияКоманда для извлечения№SQL-конструкция для созданияКоманда для извлечения№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 'Фамилия туриста'
Описание
Извлечение фамилии туриста по заданному коду
Результат запуска
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 'Количество туристов, проживающех в городах %рг%'
Описание
Подсчет количества туристов из городов, имеющих в своем названии сочетание букв "рг". Следует ожидать число три (Екатеринбург, Оренбург, Санкт-Петербург)
Результат запуска
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 'Количество туров, которые турист посетил'
Описание
Подсчет количества туров, которых посетил турист с заданным значением поля "Кодтуриста"
Результат запуска
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
Результат запуска
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 'Число путевок, проданных в этом туре'
Описание
Подсчет количества путевок, проданных по заданному туру
Результат запуска
Для удаления хранимой процедуры используется оператор drop:

drop proc proc1

Здесь proc1 - название процедуры (см. табл. 5.1).


Содержание раздела