Создание хранимых процедур в 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
Можно было, конечно, указать название и в выпадающем списке
Рис. 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 приведены примеры хранимых процедур:
| 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 приводятся примеры хранимых процедур с параметрами.
| 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 приводятся примеры хранимых процедур с входными и выходными параметрами.
| 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 proc proc1
Здесь proc1 - название процедуры (см. табл. 5.1).
