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

Создание хранимых процедур в SQL Server Enterprise Manager

Программа SQL Server Enterprise Manager предоставляет графический интерфейс для работы с хранимыми процедурами, равно как и для других объектов базы данных. Для просмотра определенной процедуры базы данных BDTur_firm2 переходим на соответствующий узел, щелкаем правой кнопкой (или дважды левой) и в появившемся меню выбираем пункт "Свойства" (рис. 5.10, А). В появившемся окне "Stored Procedure Properties" выводится SQL-конструкция, для проверки синтаксиса которой нажимаем на "Check Syntax" (рис. 5.10, Б и В).

увеличить изображение
Рис. 5.10. Узел "Stored Procedures" в SQL Server Enterprise Manager. А - список хранимых процедур. Б - свойства выбранной процедуры. В - проверка синтаксиса

Для создания новой процедуры выбираем пункт меню "New Stored Procedure", появляется окно "Stored Procedure Properties" где можно вводить SQL-конструкцию.

Для быстрой разработки удобно применять мастер. На панели инструментов нажимаем на кнопку "Run a Wizard", в появившемся окне раскрываем узел "DataBase", переходим к заголовку "Create Stored Procedure Wizard" (рис. 5.11).

Рис. 5.11. Запуск мастера

В первом шаге мастера, в окне приветствия, нажимаем кнопку "Далее". Во втором шаге выбираем базу BDTur_firm2. Затем выбираем таблицу "Туристы" и отмечаем галочками три команды - insert, update, delete (рис. 5.12) - мастер создаст сразу три хранимых процедуры для вставки, обновления и удаления записей.

Рис. 5.12. Выбор таблицы и команд модификации

В последнем шаге мастера можно отредактировать создаваемые процедуры, нажав кнопку "Edit_" (рис. 5.13, А). В окне "Edit Stored Procedure Properties" в поле "Name" задается название текущей процедуры. Нажимая на кнопку "Edit SQL_", открываем SQL-конструкцию, сгенерированную мастером (рис. 5.13, Б и В).

увеличить изображение
Рис. 5.13. Настройка хранимой процедуры. А - переход от последнего шага мастера к режиму редактирования, Б - окно "Edit Stored Procedure Properties", В - SQL-конструкция создаваемой процедуры

Оставим все названия как есть, нажимаем кнопку "Готово". В результате в списке появляется три новых объекта (рис. 5.14).

Рис. 5.14. Появившиеся в списке "Stored Procedures" объекты

Мастер сгенерировал три SQL-конструкции, для insert_Туристы_1:

CREATE PROCEDURE [insert_Туристы_1]
	(@Кодтуриста_1 	[int],
	 @Фамилия_2 	[nvarchar](50),
	 @Имя_3 	[nvarchar](50),
	 @Отчество_4 	[nvarchar](50))

AS INSERT INTO [BDTur_firm2].[dbo].[Туристы] 
	 ( [Кодтуриста],
	 [Фамилия],
	 [Имя],
	 [Отчество]) 
 VALUES 
	( @Кодтуриста_1,
	 @Фамилия_2,
	 @Имя_3,
	 @Отчество_4)
GO

Для update_Туристы_1:

CREATE PROCEDURE [update_Туристы_1]
	(@Кодтуриста_1 	[int],
	 @Фамилия_2 	[nvarchar],
	 @Имя_3 	[nvarchar],
	 @Отчество_4 	[nvarchar],
	 @Кодтуриста_5 	[int],
	 @Фамилия_6 	[nvarchar](50),
	 @Имя_7 	[nvarchar](50),
	 @Отчество_8 	[nvarchar](50))

AS UPDATE [BDTur_firm2].[dbo].[Туристы] 

SET [Кодтуриста]	 = @Кодтуриста_5,
	 [Фамилия]	 = @Фамилия_6,
	 [Имя]	 = @Имя_7,
	 [Отчество]	 = @Отчество_8 

WHERE 
	( [Кодтуриста]	 = @Кодтуриста_1 AND
	 [Фамилия]	 = @Фамилия_2 AND
	 [Имя]	 = @Имя_3 AND
	 [Отчество]	 = @Отчество_4)
GO

Для delete_Туристы_1:

CREATE PROCEDURE [delete_Туристы_1]
	(@Кодтуриста_1 	[int],
	 @Фамилия_2 	[nvarchar],
	 @Имя_3 	[nvarchar],
	 @Отчество_4 	[nvarchar])

AS DELETE [BDTur_firm2].[dbo].[Туристы] 

WHERE 
	( [Кодтуриста]	 = @Кодтуриста_1 AND
	 [Фамилия]	 = @Фамилия_2 AND
	 [Имя]	 = @Имя_3 AND
	 [Отчество]	 = @Отчество_4)
GO

Мы получили три хранимые процедуры для вставки, изменения и удаления записей в таблице "Туристы". Для процедур update_Туристы_1 и delete_Туристы_1 в условии WHERE (где) мастер добавил оператор AND (и) для объединения параметров запроса. Изменим его на оператор OR (или) для получения более гибкого запроса. В окне SQL Server Enterprise Manager дважды щелкаем на процедуре update_Туристы_1 и в появившемся окне свойств изменяем SQL-конструкцию:

...
WHERE 
	( [Кодтуриста]	 = @Кодтуриста_1 OR 
	 [Фамилия]	 = @Фамилия_2 OR 
	 [Имя]	 = @Имя_3 OR 
	 [Отчество]	 = @Отчество_4)
GO

Точно так же этот фрагмент будет выглядеть и для delete_Туристы_1. Прежде чем мы начнем проверять работу созданных процедур, сделаем поле "Код туриста" в таблице "Туристы" ключевым. Открываем таблицу в режиме дизайна, выделяем это поле, щелкаем правой кнопкой и выбираем пункт меню "Set Primary Key". Теперь в SQL Query Analyzer запускаем процедуру insert_Туристы_1 с параметрами, передающими значения полей новой записи:

exec insert_Туристы_1
@Кодтуриста_1 = 6,
@Фамилия_2 = 'Смирнов',
@Имя_3 = 'Валерий',
@Отчество_4 = 'Константинович'

Появляется сообщение - одна запись добавлена^{4Affected - перев. с англ., здесь - "изменена".}:

(1 row(s) affected)

Попытаемся добавить еще раз эту же запись - нажимаем F5. Поскольку мы задали ключевое поле, не допускающее дублирование значений, появляется сообщение об ошибке:

Server: Msg 2627, Level 14, State 1, Procedure insert_Туристы_1, Line 7
Violation of PRIMARY KEY constraint 'PK_Туристы'. 
Cannot insert duplicate key in object 'Туристы'.
The statement has been terminated.

Изменим значение параметра "@Кодтуриста":

exec insert_Туристы_1
@Кодтуриста_1 = 7,
@Фамилия_2 = 'Смирнов',
@Имя_3 = 'Валерий',
@Отчество_4 = 'Константинович'

Еще одна запись будет добавлена:

(1 row(s) affected)

Выведем все записи:

select * from Туристы

В таблице появились две новые записи (рис. 5.15):

Рис. 5.15. Таблица "Туристы". Добавление записей

Теперь изменим в последней записи фамилию "Смирнов" на "Тихонов". Для этого запускаем процедуру update_Туристы_1 следующим образом:

exec update_Туристы_1
@Кодтуриста_1 = 7,
@Фамилия_2 = 'Смирнов',
@Имя_3 = 'Валерий',
@Отчество_4 = 'Константинович',
@Кодтуриста_5 = 7,
@Фамилия_6 = 'Тихонов',
@Имя_7 = 'Валерий',
@Отчество_8 = 'Константинович'

Снова появляется сообщение о изменении записи:

(1 row(s) affected)

Здесь первые четыре параметра задают текущие значения, а следующие четыре указывают новые. В результате получаем следующие записи в таблице (рис. 5.16):

Рис. 5.16. Таблица "Туристы". Изменение записей

Для удаления записей, поскольку мы задали оператор OR, можно вызвать процедуру delete_Туристы_ 1, передавая параметры следующим образом:

exec delete_Туристы_1
@Кодтуриста_1 = 7,
@Фамилия_2 = 'Тихонов',
@Имя_3 = 'Валерий',
@Отчество_4 = 'Константинович'

(1 row(s) affected)

exec delete_Туристы_1
@Кодтуриста_1 = 6,
@Фамилия_2 ='',
@Имя_3='',
@Отчество_4=''
(1 row(s) affected)

Мы получаем прежнее число записей (рис. 5.17):

Рис. 5.17. Таблица "Туристы". Удаление записей

В программном обеспечении к курсу вы найдете файлы^{5После присоединения базы к Microsoft SQL Server все созданные хранимые процедуры будут находиться в узле "Stored Procedures".} BDTur_ firm2.mdf, BDTur_firm2.ldf, а также исходный файл Microsoft Access BDTur_firm2.mdb (Code\Glava3\).