Опубликован: 05.08.2007 | Доступ: свободный | Студентов: 2199 / 83 | Оценка: 4.47 / 4.09 | Длительность: 20:11:00
ISBN: 978-5-9556-0097-0
Лекция 5:

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

< Лекция 4 || Лекция 5: 12345 || Лекция 6 >
Аннотация: Понятие хранимой процедуры. Примеры хранимых процедур. Процедуры с параметрами. Создание процедур в SQL Query Analyzer, SQL Server Enterprise Manager, Visual Studio .NET

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

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

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

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

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

exec sp_databases

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

Программа SQL Query Analyzer. Выполнение запроса. Выделена процедура "sp_databases"

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

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

exec sp_monitor

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

Статистика Microsoft SQL-Server

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

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

USE Northwind
exec sp_stored_procedures

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

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

Рис. 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):

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

Рис. 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.
Результат запуска
< Лекция 4 || Лекция 5: 12345 || Лекция 6 >