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

       

Создание хранимых процедур в 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 = 'Константинович'

Появляется сообщение - одна запись добавлена4):

(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) BDTur_ firm2.mdf, BDTur_firm2.ldf, а также исходный файл Microsoft Access BDTur_firm2.mdb (Code\Glava3\).


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