Создание хранимых процедур

Внимание! Для работы с лекциями 5, 6 необходимы учебные файлы, которые Вы можете загрузить здесь.

Создание хранимых процедур

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

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

Сами базы данных используют огромное количество встроенных хранимых процедур для функционирования. Запустим программу SQL Query Analyzer^{1Вводные сведения об этой программе см. в "Элементы работы с базами данных" .}, входящую в пакет 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 из "Элементы работы с базами данных" , назовите ее "BDTur_firm2.mdb". Открываем ее в Microsoft Access и в названиях таблиц и полей удаляем все пробелы. Например, таблица "Информация о туристах" будет теперь называться так: "Информацияотуристах", а поле "Код туриста" станет полем "Кодтуриста". Затем конвертируем базу в формат Microsoft SQL и присоединяем ее к локальному серверу^{2См. "Элементы работы с базами данных" .}. Запускаем SQL Query Analyzer, открываем чистый бланк и вводим запрос^{3Названия операторов принято писать прописными буквами, вот так: CREATE PROCEDURE. Однако если вам неудобно постоянно переключать регистр, вы можете писать операторы строчными буквами: create procedure. Это не совсем строго, и, возможно, далее придется отказаться от этой привычки, но на первых порах это экономит много времени - SQL Query Analyzer понимает любой регистр и сохраняет процедуру в нужном формате.}:

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.
	Результат запуска