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

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

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

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

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

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

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

Примеры хранимых процедур
№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.
Результат запуска

На практике часто бывает нужно получить результаты запроса для определенного значения (параметра). Такие запросы называются параметризированными, а соответствующие процедуры создаются с параметрами. Например, для получения записи в таблице "Туристы" по заданной фамилии создаем следующую процедуру:

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 приводятся примеры хранимых процедур с параметрами.

Хранимые процедуры с параметрами
№SQL-конструкция для созданияКоманда для извлечения
1 
create proc proc_p1
  @Фамилия nvarchar(50)
as
select *
from Туристы 
where Фамилия=@Фамилия
 
exec proc_p1 'Андреева'
Описание
Извлечение записи из таблицы "Туристы" с заданной фамилией
Результат запуска
№SQL-конструкция для созданияКоманда для извлечения
2 
create proc proc_p2
  @nameTour nvarchar(50)
as
select *
from Туры 
where Название=@nameTour
 
exec proc_p2 'Франция'
Описание
Извлечение записи из таблицы "Туры" с заданным названием тура. Обратите внимание на название параметра "nameTour " - он может быть произвольным, не обязательно, чтобы он совпадал с заголовком столбца извлекаемой таблицы
Результат запуска
№SQL-конструкция для созданияКоманда для извлечения
3 
create procedure proc_p3 
@Фамилия nvarchar(50)
as
select * 
from Туристы inner
 join Информацияотуристах 
on Туристы.КодТуриста =
 Информацияотуристах.КодТуриста  
where Туристы.Фамилия =
 @Фамилия
 
exec proc_p3 'Андреева'
Описание
Вывод родительской и дочерней записей с заданной фамилией из таблиц "Туристы" и "Информацияотуристах"
Результат запуска
№SQL-конструкция для созданияКоманда для извлечения
4 
create procedure proc_p4 
@nameTour nvarchar(50)
as
select * 
from Туры inner join Сезоны 
on Туры.Кодтура=Сезоны.Кодтура  
where Туры.Название = @nameTour
 
exec proc_p4 'Франция'
Описание
Вывод родительской и дочерней записей с заданной названием тура из таблиц "Туры" и "Сезоны"
Результат запуска (изображение разрезано)
№SQL-конструкция для созданияКоманда для извлечения
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:
№SQL-конструкция для созданияКоманда для извлечения
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 'Таиланд'
Для просмотра используем оператор Запускаем программу 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 приводятся примеры хранимых процедур с входными и выходными параметрами.

Хранимые процедуры с входными и выходными параметрами
№SQL-конструкция для созданияКоманда для извлечения
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 'Фамилия туриста'
Описание
Извлечение фамилии туриста по заданному коду
Результат запуска
№SQL-конструкция для созданияКоманда для извлечения
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  'Количество туристов, 
 проживающех в городах 
 %рг%'
Описание
Подсчет количества туристов из городов, имеющих в своем названии сочетание букв "рг". Следует ожидать число три (Екатеринбург, Оренбург, Санкт-Петербург)
Результат запуска
№SQL-конструкция для созданияКоманда для извлечения
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 'Количество туров, 
 которые турист посетил'
Описание
Подсчет количества туров, которых посетил турист с заданным значением поля "Кодтуриста"
Результат запуска
№SQL-конструкция для созданияКоманда для извлечения
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
Результат запуска
№SQL-конструкция для созданияКоманда для извлечения
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:

drop proc proc1

Здесь proc1 - название процедуры (см. табл. 5.1).

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

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

(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) Таблица "Туристы". Удаление записей

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

Вызов простых хранимых процедур при помощи объекта DataAdapter

Мы разобрались с созданием и запуском хранимых процедур, приступим теперь к их использованию в Windows-приложениях, связанных с базами данных. Создайте новый Windows-проект и назовите его "VisualDataAdapterSP". Перетаскиваем на форму элемент управления DataGrid, его свойству Dock устанавливаем значение "Fill". В окне Toolbox переходим на вкладку Data и перетаскиваем на форму элемент SqlDataAdapter. В появившемся мастере в поле имени сервера вводим ".", выбираем тип входа "учетные сведения Windows NT", а из выпадающего списка баз данных выбираемДалее мы будем работать с этой базой данных. Вполне возможно, что у вас ее нет - вы начали читать с этого места книгу, не выполняли упражнения или потеряли диск. В этом случае вам нужно будет сделать следующее: а) Прочитать первую лекцию, создать по описаниям базу данных BDTur_firm.mdb в Microsoft Access. б) Как описывается в начале уже этой, пятой лекции, изменить названия таблиц и полей базы. в) Преобразовать файл BDTur_firm.mdb в формат Microsoft SQL Server 2000, заодно присоединив его к своему локальному серверу. "BDTur_firm2" (рис. 5.18):

(рис 5.18) Подключение к базе данных BDTur_firm2

Проверив подключение, закрываем окно "Свойства связи с данными". В шаге "Choose a Query Type" мастера выбираем пункт "Use existing stored procedures" (рис. 5.19):

(рис 5.19) Шаг "Choose a Query Type" мастера настройки объекта DataAdapter

Далее выбираем процедуру "proc1" - как вы помните, она извлекала все записи из таблицы "Туристы". Выводимые поля отображаются в окне "Set Select procedure parameters" (рис. 5.20):

(рис 5.20) Выбор хранимой процедуры

Нажимаем кнопку "Next", а в следующем, заключительном шаге - "Finish". Просмотрим данные, которые будут извлечены объектом DataAdapter. Выделяем sqlDataAdapter1, переходим в окно Properties и щелкаем по ссылке "Preview Data_". В появившемся окне "Data Adapter Preview" нажимаем кнопку Fill для просмотра данных (рис. 5.21).

(рис 5.21) Просмотр данных, извлекаемых объектом DataAdapter

Закрываем окно "Data Adapter Preview", снова выделяем объект sqlDataAdapter1, в его окне Properties нажимаем на ссылку "Generate Dataset_" (см. рис. 5.21). В появившемся окне "Generate Dataset" предлагается создать новый объект "DataSet1". Нажимаем кнопку "OK". Выделяем элемент DataGrid, из выпадающего списка свойства "DataSource" выбираем "dataSet11.proc1" (рис. 5.22).

(рис 5.22) Свойство DataSource элемента DataGrid

Вид формы изменился - на нем появились названия полей. В конструкторе формы вызываем метод Fill объекта DataAdapter для заполнения DataSet:

public Form1()
	{
		InitializeComponent();
		sqlDataAdapter1.Fill(dataSet11);
	}

Запускаем приложение. На форму выводятся данные, полученные в результате выполнения хранимой процедуры proc1 (рис. 5.23).

(рис 5.23) Приложение "VisualDataAdapterSP". Данные хранимой процедуры "proc1"

Изменим настройку объекта DataAdapter. Выделяем sqlDataAdapter1, в окне Properties щелкаем по ссылке "Configure DataAdapter_" (см. рис. 5.21). Появляется уже знакомый мастер "Data Adapter Configuration Wizard", нажимаем кнопку "Next". В шаге "Choose Your Data Connection" оставляем имеющееся подключение - мы будем работать с той же самой базой данных. В шаге "Binds Commands to Existing Stored Procedures" на этот раз выбираем процедуру proc9 - она извлекала данные из таблиц "Туристы" и "Информацияотуристах" (см. таблицу 5.1). Завершаем работу мастера. Изменим свойство DataSource объекта DataGrid - установим теперь значение "dataSet11" (рис. 5.24):

(рис 5.24) Изменение свойства DataSource объекта DataGrid

Запускаем приложение. Теперь мы видим две ссылки - "proc1" и "proc9". Переходя по последней, мы видим данные хранимой процедуры (рис. 5.25, Б).

(рис 5.25) Приложение "VisualDataAdapterSP". А - данные хранимой процедуры "proc1". Б - данные хранимой процедуры "proc9"

Если мы перейдем по ссылке "proc1", мы обнаружим, что данных в ней нет, однако названия полей сохранились (рис. 5.25, А). Дело в том, что в структуре объекта DataSet остался "след" первой хранимой процедуры. В восьмой лекции мы научимся работать со структурой DataSet, а пока, если нам не нужна такая пустая ссылка "proc1", можно удалить объект DataSetКроме удаления самого объекта DataSet с панели компонент формы, потребуется также удаление его схемы. Переходим в окно "Server Explorer" и удаляем файл схемы, имеющий расширение XSD. Например, dataSet1.xsd., а затем сгенерировать его заново по ссылке объекта DataAdapter окна Properties.

Создадим теперь хранимую процедуру при помощи мастера настройки объекта DataAdapter. Выделяем sqlDataAdapter1 и в окне Properties снова нажимаем на ссылку " Configure DataAdapter_". В шаге "Choose a Query Type" (см. рис. 5.19) выбираем "Create new stored procedures". В следующем шаге "Generate the stored procedures" нажимаем кнопку "Query Builder" (Построитель запроса). Добавляем таблицу "Туристы". Создадим еще раз запрос, выводящий всех туристов, фамилия которых содержит букву "и" (см. табл. 5.1, процедура proc8 ). Ставим галочку в поле *(All Columns), затем просто вводим условие отбора WHEREЗдесь я привожу названия операторов прописными буквами. Построитель выражений генерирует запросы именно в этом регистре.:

SELECT   *
FROM     Туристы
WHERE   (Фамилия LIKE '%и%')

Обратите внимание на небольшое отличие синтаксиса - здесь условие находится в круглых скобках. Внешний вид построителя выражения также изменился: в таблице "Туристы" появился значок фильтра, в поле "Column" - заголовок "Фамилия", в поле "Criteria" (Условие) - выражение "LIKE '%и%'". Щелкнув правой кнопкой в любой части построителя, выбираем пункт меню "Run" - в нижней таблице появляются данные, извлеченные запросом (рис. 5.26):

(рис 5.26) Создание запроса в Query Builder

Работа с Query Builder очень похожа на создание запросов в режиме конструктора в Microsoft Access. Читатель, с этим знакомый, без труда разберется во всех полях и свойствах построителя выраженияЕсли наличие большого количества полей и свойств кажется запутанным - лучше отложить Visual Studio .NET, запустить Access и как следует разобраться с созданием запросов. Достаточно одного учебника или даже справочной системы, чтобы научиться создавать запросы среднего уровня сложности.. Завершив настройку, закрываем построитель, нажимая кнопку "ОК". Нажимаем кнопку "Next", в шаге "Create the Stored Procedures" задаем название созданной процедуре - "proc_da1" (см. рис. 5.27).

(рис 5.27) Окно "Preview SQL Script" и шаг мастера "Create the Stored Procedures"

По умолчанию мастер также генерирует процедуры типа insert, update и delete. В построители выражения мы создали саму SQL-конструкцию, без указания команд создания хранимой процедуры. Нажав кнопку "Preview SQL Script_", можно просмотреть команды, которые были сгенерированы автоматически. В окне "Create the Stored Procedures" также по умолчанию отмечено автоматическое создание хранимых процедур в самой базе данных. Завершаем работу, нажимая кнопку "Finish". Запускаем приложение - на форму выводятся данные хранимой процедуры proc_da1 (рис. 5.28).

(рис 5.28) Приложение "VisualDataAdapterSP". Данные хранимой процедуры "proc_da1"

В программном обеспечении к курсу вы найдете приложение VisualData AdapterSP (Code\Glava3\ VisualDataAdapterSP).

Создание хранимых процедур в Visual Studio .NET

Среда Visual Studio .NET предоставляет интерфейс для создания хранимых процедур в базе данных при наличии подключения к ней. Это удобно - если вы работаете с базой данных по сети, встроенные средства администрирования Microsoft SQL Server могут оказаться недоступными. Запускаем Visual Studio (нам даже не нужно создавать какой-либо проект), переходим на вкладку "Server Explorer", раскрываем подключение к базе данных BDTur_firm2, затем на узле "Stored Procedures" щелкаем правой кнопкой и выбираем пункт "New Stored Procedure" (рис. 5.29):

(рис 5.29) Создание новой процедуры в окне "Server Explorer"

Появляется шаблон структуры, сгенерированный мастером:

CREATE PROCEDURE dbo.StoredProcedure1
/*
	(
		@parameter1 datatype = default value,
		@parameter2 datatype OUTPUT
	)
*/
AS
	/* SET NOCOUNT ON */
	RETURN

Для того чтобы приступить к редактированию, достаточно убрать знаки комментариев "/*". Команда NOCOUNT со значением ON отключает выдачу сообщений о количестве строк таблицы, получающейся в качестве запроса. Дело в том, что при использовании более чем одного оператора ( SELECT, INSERT, UPDATE или DELETE ) в начале запроса надо поставить команду "SET NOCOUNT ON", а перед последним оператором SELECT - "SET NOCOUNT OFF". С другими частями шаблона мы уже сталкивались. Например, хранимую процедуру proc_po1 (см. таблицу 5.3) можно переписать так:

CREATE PROCEDURE dbo.proc_vs1

	(
		@TouristID int,
		@LastName nvarchar(60) OUTPUT
	)

AS
	SET NOCOUNT ON 
	SELECT   @LastName = Фамилия
	FROM     Туристы
	WHERE   Кодтуриста = @TouristID

	RETURN

После завершения редактирования SQL-конструкция будет обведена синей рамкой. Щелкнув правой кнопкой в этой области и выбрав пункт меню "Design SQL Block", можно перейти к построителю выражения ("Query Builder") (рис. 5.30, А, Б). При выборе в этом же меню пункта "Run Stored Procedure" появляется одноименное окно, где отслеживаются передаваемые параметры (рис. 5.30, В).

(рис 5.30) Редактирование хранимой процедуры в Visual Studio .NET. А - контекстное меню, Б - построитель выражений ( режим "Design SQL Block"), В - окно "Run stored procedure", Г - окно "Output".

В данном случае необходимо указывать значение параметров (см. таблицу 5.3), поэтому после нажатия кнопки "ОК" в окне "Run stored procedure" процедура выполнена не будет, в окне "Output" появляется следующее сообщение (рис. 5.30, Г).

Running dbo."proc_vs1" ( @TouristID = <DEFAULT>, @LastName = <DEFAULT> ).

Procedure 'proc_vs1' expects parameter '@TouristID', which was not supplied.

Для сохранения процедуры в базе данных выбираем "File \ Save proc_vs1" (или нажимаем Ctrl+S), теперь можно закрывать студию - хранимая процедура создана. Впрочем, для продолжения работы выбираем пункт "Refresh" контекстного меню в окне "Server Explorer". Происходит синхронизация с базой данных, и процедура "proc_vs1" появляется в списке. Двойной щелчок открывает ее для редактирования, причем заголовок имеет следующий вид:

ALTER PROCEDURE dbo.proc_vs1

Оператор ALTER позволяет производить действия (редактирование) с уже имеющимся объектом базы данных.

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

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

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

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

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

Примеры хранимых процедур
№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.
Результат запуска

На практике часто бывает нужно получить результаты запроса для определенного значения (параметра). Такие запросы называются параметризированными, а соответствующие процедуры создаются с параметрами. Например, для получения записи в таблице "Туристы" по заданной фамилии создаем следующую процедуру:

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 приводятся примеры хранимых процедур с параметрами.

Хранимые процедуры с параметрами
№SQL-конструкция для созданияКоманда для извлечения
1 
create proc proc_p1
  @Фамилия nvarchar(50)
as
select *
from Туристы 
where Фамилия=@Фамилия
 
exec proc_p1 'Андреева'
Описание
Извлечение записи из таблицы "Туристы" с заданной фамилией
Результат запуска
№SQL-конструкция для созданияКоманда для извлечения
2 
create proc proc_p2
  @nameTour nvarchar(50)
as
select *
from Туры 
where Название=@nameTour
 
exec proc_p2 'Франция'
Описание
Извлечение записи из таблицы "Туры" с заданным названием тура. Обратите внимание на название параметра "nameTour " - он может быть произвольным, не обязательно, чтобы он совпадал с заголовком столбца извлекаемой таблицы
Результат запуска
№SQL-конструкция для созданияКоманда для извлечения
3 
create procedure proc_p3 
@Фамилия nvarchar(50)
as
select * 
from Туристы inner
 join Информацияотуристах 
on Туристы.КодТуриста =
 Информацияотуристах.КодТуриста  
where Туристы.Фамилия =
 @Фамилия
 
exec proc_p3 'Андреева'
Описание
Вывод родительской и дочерней записей с заданной фамилией из таблиц "Туристы" и "Информацияотуристах"
Результат запуска
№SQL-конструкция для созданияКоманда для извлечения
4 
create procedure proc_p4 
@nameTour nvarchar(50)
as
select * 
from Туры inner join Сезоны 
on Туры.Кодтура=Сезоны.Кодтура  
where Туры.Название = @nameTour
 
exec proc_p4 'Франция'
Описание
Вывод родительской и дочерней записей с заданной названием тура из таблиц "Туры" и "Сезоны"
Результат запуска (изображение разрезано)
№SQL-конструкция для созданияКоманда для извлечения
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:
№SQL-конструкция для созданияКоманда для извлечения
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 'Таиланд'
Для просмотра используем оператор Запускаем программу 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 приводятся примеры хранимых процедур с входными и выходными параметрами.

Хранимые процедуры с входными и выходными параметрами
№SQL-конструкция для созданияКоманда для извлечения
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 'Фамилия туриста'
Описание
Извлечение фамилии туриста по заданному коду
Результат запуска
№SQL-конструкция для созданияКоманда для извлечения
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  'Количество туристов, 
 проживающех в городах 
 %рг%'
Описание
Подсчет количества туристов из городов, имеющих в своем названии сочетание букв "рг". Следует ожидать число три (Екатеринбург, Оренбург, Санкт-Петербург)
Результат запуска
№SQL-конструкция для созданияКоманда для извлечения
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 'Количество туров, 
 которые турист посетил'
Описание
Подсчет количества туров, которых посетил турист с заданным значением поля "Кодтуриста"
Результат запуска
№SQL-конструкция для созданияКоманда для извлечения
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
Результат запуска
№SQL-конструкция для созданияКоманда для извлечения
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:

drop proc proc1

Здесь proc1 - название процедуры (см. табл. 5.1).

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

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

(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) Таблица "Туристы". Удаление записей

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

Вызов простых хранимых процедур при помощи объекта DataAdapter

Мы разобрались с созданием и запуском хранимых процедур, приступим теперь к их использованию в Windows-приложениях, связанных с базами данных. Создайте новый Windows-проект и назовите его "VisualDataAdapterSP". Перетаскиваем на форму элемент управления DataGrid, его свойству Dock устанавливаем значение "Fill". В окне Toolbox переходим на вкладку Data и перетаскиваем на форму элемент SqlDataAdapter. В появившемся мастере в поле имени сервера вводим ".", выбираем тип входа "учетные сведения Windows NT", а из выпадающего списка баз данных выбираемДалее мы будем работать с этой базой данных. Вполне возможно, что у вас ее нет - вы начали читать с этого места книгу, не выполняли упражнения или потеряли диск. В этом случае вам нужно будет сделать следующее: а) Прочитать первую лекцию, создать по описаниям базу данных BDTur_firm.mdb в Microsoft Access. б) Как описывается в начале уже этой, пятой лекции, изменить названия таблиц и полей базы. в) Преобразовать файл BDTur_firm.mdb в формат Microsoft SQL Server 2000, заодно присоединив его к своему локальному серверу. "BDTur_firm2" (рис. 5.18):

(рис 5.18) Подключение к базе данных BDTur_firm2

Проверив подключение, закрываем окно "Свойства связи с данными". В шаге "Choose a Query Type" мастера выбираем пункт "Use existing stored procedures" (рис. 5.19):

(рис 5.19) Шаг "Choose a Query Type" мастера настройки объекта DataAdapter

Далее выбираем процедуру "proc1" - как вы помните, она извлекала все записи из таблицы "Туристы". Выводимые поля отображаются в окне "Set Select procedure parameters" (рис. 5.20):

(рис 5.20) Выбор хранимой процедуры

Нажимаем кнопку "Next", а в следующем, заключительном шаге - "Finish". Просмотрим данные, которые будут извлечены объектом DataAdapter. Выделяем sqlDataAdapter1, переходим в окно Properties и щелкаем по ссылке "Preview Data_". В появившемся окне "Data Adapter Preview" нажимаем кнопку Fill для просмотра данных (рис. 5.21).

(рис 5.21) Просмотр данных, извлекаемых объектом DataAdapter

Закрываем окно "Data Adapter Preview", снова выделяем объект sqlDataAdapter1, в его окне Properties нажимаем на ссылку "Generate Dataset_" (см. рис. 5.21). В появившемся окне "Generate Dataset" предлагается создать новый объект "DataSet1". Нажимаем кнопку "OK". Выделяем элемент DataGrid, из выпадающего списка свойства "DataSource" выбираем "dataSet11.proc1" (рис. 5.22).

(рис 5.22) Свойство DataSource элемента DataGrid

Вид формы изменился - на нем появились названия полей. В конструкторе формы вызываем метод Fill объекта DataAdapter для заполнения DataSet:

public Form1()
	{
		InitializeComponent();
		sqlDataAdapter1.Fill(dataSet11);
	}

Запускаем приложение. На форму выводятся данные, полученные в результате выполнения хранимой процедуры proc1 (рис. 5.23).

(рис 5.23) Приложение "VisualDataAdapterSP". Данные хранимой процедуры "proc1"

Изменим настройку объекта DataAdapter. Выделяем sqlDataAdapter1, в окне Properties щелкаем по ссылке "Configure DataAdapter_" (см. рис. 5.21). Появляется уже знакомый мастер "Data Adapter Configuration Wizard", нажимаем кнопку "Next". В шаге "Choose Your Data Connection" оставляем имеющееся подключение - мы будем работать с той же самой базой данных. В шаге "Binds Commands to Existing Stored Procedures" на этот раз выбираем процедуру proc9 - она извлекала данные из таблиц "Туристы" и "Информацияотуристах" (см. таблицу 5.1). Завершаем работу мастера. Изменим свойство DataSource объекта DataGrid - установим теперь значение "dataSet11" (рис. 5.24):

(рис 5.24) Изменение свойства DataSource объекта DataGrid

Запускаем приложение. Теперь мы видим две ссылки - "proc1" и "proc9". Переходя по последней, мы видим данные хранимой процедуры (рис. 5.25, Б).

(рис 5.25) Приложение "VisualDataAdapterSP". А - данные хранимой процедуры "proc1". Б - данные хранимой процедуры "proc9"

Если мы перейдем по ссылке "proc1", мы обнаружим, что данных в ней нет, однако названия полей сохранились (рис. 5.25, А). Дело в том, что в структуре объекта DataSet остался "след" первой хранимой процедуры. В восьмой лекции мы научимся работать со структурой DataSet, а пока, если нам не нужна такая пустая ссылка "proc1", можно удалить объект DataSetКроме удаления самого объекта DataSet с панели компонент формы, потребуется также удаление его схемы. Переходим в окно "Server Explorer" и удаляем файл схемы, имеющий расширение XSD. Например, dataSet1.xsd., а затем сгенерировать его заново по ссылке объекта DataAdapter окна Properties.

Создадим теперь хранимую процедуру при помощи мастера настройки объекта DataAdapter. Выделяем sqlDataAdapter1 и в окне Properties снова нажимаем на ссылку " Configure DataAdapter_". В шаге "Choose a Query Type" (см. рис. 5.19) выбираем "Create new stored procedures". В следующем шаге "Generate the stored procedures" нажимаем кнопку "Query Builder" (Построитель запроса). Добавляем таблицу "Туристы". Создадим еще раз запрос, выводящий всех туристов, фамилия которых содержит букву "и" (см. табл. 5.1, процедура proc8 ). Ставим галочку в поле *(All Columns), затем просто вводим условие отбора WHEREЗдесь я привожу названия операторов прописными буквами. Построитель выражений генерирует запросы именно в этом регистре.:

SELECT   *
FROM     Туристы
WHERE   (Фамилия LIKE '%и%')

Обратите внимание на небольшое отличие синтаксиса - здесь условие находится в круглых скобках. Внешний вид построителя выражения также изменился: в таблице "Туристы" появился значок фильтра, в поле "Column" - заголовок "Фамилия", в поле "Criteria" (Условие) - выражение "LIKE '%и%'". Щелкнув правой кнопкой в любой части построителя, выбираем пункт меню "Run" - в нижней таблице появляются данные, извлеченные запросом (рис. 5.26):

(рис 5.26) Создание запроса в Query Builder

Работа с Query Builder очень похожа на создание запросов в режиме конструктора в Microsoft Access. Читатель, с этим знакомый, без труда разберется во всех полях и свойствах построителя выраженияЕсли наличие большого количества полей и свойств кажется запутанным - лучше отложить Visual Studio .NET, запустить Access и как следует разобраться с созданием запросов. Достаточно одного учебника или даже справочной системы, чтобы научиться создавать запросы среднего уровня сложности.. Завершив настройку, закрываем построитель, нажимая кнопку "ОК". Нажимаем кнопку "Next", в шаге "Create the Stored Procedures" задаем название созданной процедуре - "proc_da1" (см. рис. 5.27).

(рис 5.27) Окно "Preview SQL Script" и шаг мастера "Create the Stored Procedures"

По умолчанию мастер также генерирует процедуры типа insert, update и delete. В построители выражения мы создали саму SQL-конструкцию, без указания команд создания хранимой процедуры. Нажав кнопку "Preview SQL Script_", можно просмотреть команды, которые были сгенерированы автоматически. В окне "Create the Stored Procedures" также по умолчанию отмечено автоматическое создание хранимых процедур в самой базе данных. Завершаем работу, нажимая кнопку "Finish". Запускаем приложение - на форму выводятся данные хранимой процедуры proc_da1 (рис. 5.28).

(рис 5.28) Приложение "VisualDataAdapterSP". Данные хранимой процедуры "proc_da1"

В программном обеспечении к курсу вы найдете приложение VisualData AdapterSP (Code\Glava3\ VisualDataAdapterSP).

Создание хранимых процедур в Visual Studio .NET

Среда Visual Studio .NET предоставляет интерфейс для создания хранимых процедур в базе данных при наличии подключения к ней. Это удобно - если вы работаете с базой данных по сети, встроенные средства администрирования Microsoft SQL Server могут оказаться недоступными. Запускаем Visual Studio (нам даже не нужно создавать какой-либо проект), переходим на вкладку "Server Explorer", раскрываем подключение к базе данных BDTur_firm2, затем на узле "Stored Procedures" щелкаем правой кнопкой и выбираем пункт "New Stored Procedure" (рис. 5.29):

(рис 5.29) Создание новой процедуры в окне "Server Explorer"

Появляется шаблон структуры, сгенерированный мастером:

CREATE PROCEDURE dbo.StoredProcedure1
/*
	(
		@parameter1 datatype = default value,
		@parameter2 datatype OUTPUT
	)
*/
AS
	/* SET NOCOUNT ON */
	RETURN

Для того чтобы приступить к редактированию, достаточно убрать знаки комментариев "/*". Команда NOCOUNT со значением ON отключает выдачу сообщений о количестве строк таблицы, получающейся в качестве запроса. Дело в том, что при использовании более чем одного оператора ( SELECT, INSERT, UPDATE или DELETE ) в начале запроса надо поставить команду "SET NOCOUNT ON", а перед последним оператором SELECT - "SET NOCOUNT OFF". С другими частями шаблона мы уже сталкивались. Например, хранимую процедуру proc_po1 (см. таблицу 5.3) можно переписать так:

CREATE PROCEDURE dbo.proc_vs1

	(
		@TouristID int,
		@LastName nvarchar(60) OUTPUT
	)

AS
	SET NOCOUNT ON 
	SELECT   @LastName = Фамилия
	FROM     Туристы
	WHERE   Кодтуриста = @TouristID

	RETURN

После завершения редактирования SQL-конструкция будет обведена синей рамкой. Щелкнув правой кнопкой в этой области и выбрав пункт меню "Design SQL Block", можно перейти к построителю выражения ("Query Builder") (рис. 5.30, А, Б). При выборе в этом же меню пункта "Run Stored Procedure" появляется одноименное окно, где отслеживаются передаваемые параметры (рис. 5.30, В).

(рис 5.30) Редактирование хранимой процедуры в Visual Studio .NET. А - контекстное меню, Б - построитель выражений ( режим "Design SQL Block"), В - окно "Run stored procedure", Г - окно "Output".

В данном случае необходимо указывать значение параметров (см. таблицу 5.3), поэтому после нажатия кнопки "ОК" в окне "Run stored procedure" процедура выполнена не будет, в окне "Output" появляется следующее сообщение (рис. 5.30, Г).

Running dbo."proc_vs1" ( @TouristID = <DEFAULT>, @LastName = <DEFAULT> ).

Procedure 'proc_vs1' expects parameter '@TouristID', which was not supplied.

Для сохранения процедуры в базе данных выбираем "File \ Save proc_vs1" (или нажимаем Ctrl+S), теперь можно закрывать студию - хранимая процедура создана. Впрочем, для продолжения работы выбираем пункт "Refresh" контекстного меню в окне "Server Explorer". Происходит синхронизация с базой данных, и процедура "proc_vs1" появляется в списке. Двойной щелчок открывает ее для редактирования, причем заголовок имеет следующий вид:

ALTER PROCEDURE dbo.proc_vs1

Оператор ALTER позволяет производить действия (редактирование) с уже имеющимся объектом базы данных.

Предыдущее занятие Следующее занятие
Вернуться к учебному плану
^