3.1. Общие понятия хранимых процедур
Хранимые процедуры - это модули или подпрограммы, в которых содержится повторно используемый код. Они хранятся на сервере.
Хранимая процедура принимает входные параметры, выполняет инструкции языка описания данных (DDL) и языка обработки данных (DML), возвращает клиенту табличные или скалярные результаты, а также выходные параметры.
Организация взаимодействия между клиентом и сервером с помощью хранимых процедур предлагает следующее: клиент осуществляет вызов блока команд, хранящегося на сервере базы данных, по его имени; сервер выполняет этот блок команд и возвращает клиенту результат. Таким образом, использование хранимых процедур снижает сетевой трафик и сокращает число запросов клиентов, т.к. вместо пересылки по сети нескольких операторов передается лишь вызов необходимой процедуры.
В SQL Server 2005 хранимая процедура может относиться к одному из двух типов по способу создания: Transact-SQL или CLR.
Хранимой процедурой Transact-SQL называется сохраненная запись инструкций языка Transact-SQL, которая может принимать и возвращать параметры. Например, хранимая процедура может содержать инструкции, производящие вставку новой строки в одну или несколько таблиц на основе значений, полученных от клиентского приложения, или же возвращать приложению данные из базы данных.
Хранимая процедура CLR представляет собой ссылку на метод общеязыковой среды выполнения (CRL) платформы Microsoft .NET Framework, который может принимать и возвращать пользователю параметры.
В Microsoft SQL Server 2005 существует несколько типов хранимых процедур по назначению:
1) Пользовательские хранимые процедуры. Создаются, хранятся и выполняются пользователями в контексте только той базы данных, для которой были созданы
2) Расширенные хранимые процедуры. Позволяют создавать собственные внешние подпрограммы на языках программирования (например на С). В будущей версии Microsoft SQL Server эта возможность будет удалена. Поэтому лучше избегайть использования этой возможности. Вместо этого рекомендуется использовать интеграцию со средой CLR.
3) Системные хранимые процедуры. Многие административные действия в SQL Server 2005 осуществляются при помощи этих процедур. Физически системные хранимые процедуры хранятся в базе данных ресурсов и имеют префикс sp_. SQL Server поддерживает системные хранимые процедуры, обеспечивающие интерфейс между SQL Server и внешними программами для выполнения различных действий по обслуживанию системы. Эти расширенные хранимые процедуры имеют префикс xp_.
4) Временные хранимые процедуры. Такие процедуры будут удалены при завершении работы SQL Server. При создании локальных и глобальных временных хранимых процедур, по аналогии с временными таблицами, используются префиксы # и ##. Префикс # обозначает локальную временную процедуру, а ## - глобальную. Хранимые процедуры среды CLR не могут быть временными.
Для работы с хранимыми процедурами можно использовать такие хранимые процедуры:
sp_helptext ИмяПроцедуры – выводит код указанной хранимой процедуры;
sp_stored_procedures – возвращает список сохраненных процедур текущей базы данных.
Для работы с конкретной БД ее имя можно выбрать на панели инструментов в SQL Server Management Studio:
Задание 1. Определите количество хранимых процедур базы данных AdventureWorksDW.
3.2. Создание процедуры средствами Transact-SQL
Создание хранимой процедуры:
CREATE PROC[EDURE] Имя_процедуры
[ @параметр Тип_данных [=Значение_по_умолчанию] [OUTPUT] ] [,...n]
[ WITH <Дополнительные_опции_процедуры> [,...n] ]
AS <Инструкции_SQL> ;
Имя процедуры должно удовлетворять правилам именования объектов SQL-Server, @параметр определяет имя параметра, который будет использоваться для передачи входных или выходных данных (при указании ключевого слова OUTPUT), Тип_данных указывает, к какому типу данных должны относиться значения параметра, Значение_по_умолчанию – позволяет определить значение по умолчанию, если при вызове процедуры параметр был не указан. После ключевого слова AS следуют команды Transact-SQL, которые и составляют тело процедуры.
3.3. Выполнение процедуры
Хранимая процедура может быть выполнена с помощью оператора EXECUTE:
EXEC[UTE] Имя_процедуры
[ [@параметр=] {Значение | Выражение} [OUTPUT] ] [,…n];
При использовании выходного параметра, его необходимо описать с использование ключевого слова OUTPUT при создании процедуры, а также использовать это слово и при указании соответствующего параметра при вызове процедуры. В противном случае процедура не передает выходное значение.
3.4. Управление хранимыми процедурами
Для изменения существующей процедуры используется оператор ALTER PROC, параметры этой команды аналогичны параметрам команды создания процедуры.
Для переименования необходимо использовать специальную системную хранимую процедуру:
sp_rename ‘Имя_объекта’, ‘Новое_имя_объекта’
Для удаления хранимой процедуры используется команда Transact-SQL:
DROP PROC Имя_процедуры
3.5. Работа с процедурами средствами SQL Server Management Studio
Хранимые процедуры базы данных можно просмотреть в SQL Server Management Studio, открыв на панели Обозреватель объектов для соответствующей базы данных последовательно папки Программирование → Хранимые процедуры.
Создать хранимые процедуры средствами Transact-SQL можно в окне создания запроса.
Задание 2
Прежде всего, создайте базу данных DB1 для помещения в нее хранимых процедур. Если не определять подробно все параметры, сделать это можно командой:
CREATE DATABASE Имя_БД
Оформите решение следующих задач в виде хранимых процедур в БД DB1:
1. По введенной дате рождения пользователя вывести вывод о том, исполнилось или нет ему полных 16 лет.
2. Установить является ли введенная строка символов адресом электронной почты.
Примечание: в строке должен присутствовать символ @, перед которым должны идти символы без точки, а после которого обязательно наличие хотя бы одной точки через некоторую последовательность символов.
Не знаете как заработать на блоге , почитайте блог, предлагающий варианты заработка в интернет - только эффективные способы
Блог расскажет все об юзабилити. Рекомендуется к прочтению!
Есть ли лучший отдых, чем рыбалка под Астраханью, ночевка в палатке и ужин под луной?
