Хранимые процедуры

Хранимая процедура ещё один объект базы данных, которая представляет собой набор откомпилированных операторов SQL. Хранимая процедура не содержит информации из базы данных, но содержит ссылки на базовые таблицы, где хранятся нужные данные. Хранимые процедуры позволяют выделять какие-либо правила в отдельную структуру, которые затем могут использоваться многими приложениями.

В хранимые процедуры вводятся аргументы, возвращаются результирующие наборы данных. Если хранимая процедура не является триггером, то она вызывается приложением явно.

При создании процедур необходимо придерживаться следующих правил: во время выполнения хранимой процедуры все объекты, на которые она ссылается, должны присутствовать в базе данных. В хранимых процедурах нельзя применять операторы создания объектов: CREATE PROCEDURE, CREATE TRIGGER, CREATE VIEW.

Прежде чем выполнить хранимую процедуру, SQL Server 2000 генерирует для нее план исполнения, выполняет оптимизацию хранимой процедуры и компилирует ее. В дальнейшем этот план и откомпилированный код кэшируются. При повторном вызове процедуры, сервер уже использует готовый план и откомпилированный код процедуры, экономя за счет этого ресурсы (см. Рисунок 4). Недостатком данного метода является то, что процедура может быть оптимизирована «не на тех данных», и план запуска оптимальный на начальном наборе данных, может быть не оптимален на всех последующих. В этих случаях используется принудительная перекомпиляция при каждом запуске процедуры.

Существует несколько типов хранимых процедур, реализуемых на SQL Server 2000

1. Системные хранимые процедуры

2. Пользовательские хранимые процедуры

3. Локальные временные хранимые процедуры

4. Глобальные временные хранимые процедуры.

Системные хранимые процедуры в своем имени имеют префикс sp_, хранятся в системной базе данных master и являются интерфейсом, обеспечивающим работу с системными базами данных, то есть обеспечивает администрирование базы данных.

Пользовательские хранимые процедуры – процедуры, обслуживающие конкретную базу данных и являющиеся ее объектом, как правило, их функции связаны с поддержанием бизнес правил.

Локальные и глобальные временные хранимые процедуры хранятся в базе данных tempdb. Имя локальных процедур начинается с #, имя глобальных процедур - ##. Локальные временные хранимые процедуры могут быть вызваны только из того соединения, в котором они созданы, глобальные – из любого соединения сервера, на котором была создана эта процедура. Так как эти процедуры временные, то они удаляются

Рисунок 4

при перезапуске или остановке сервера, при закрытии соединения, в контексте которого они были созданы.

Созданию хранимой процедуры должны предшествовать работы, связанные с определением типа создаваемой процедуры, планированием прав доступа и определением параметров.

Синтаксис команды:

CREATE PROC[EDURE ] имя_процедуры [; число ]

[ { @параметр тип_данных }

[ VARYING ] [ = значение_по_умолчанию ] [ OUTPUT ] ] [,...n ]

[ WITH { RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION } ]

AS оператор_SQL [...n ]

OUTPUT – означает, что соответствующий параметр может быть использован для возвращения данных из хранимой процедуры.

VARYING – определяет, что выходным параметром будет результирующее множество (используется совместно с параметром OUTPUT).

RECOMPILE – предписывает выполнять перекомпиляцию кода процедуры при ее запуске.

ENCRUPTION – предписывает серверу выполнить кодирование процедуры, сделав ее содержимое недоступным для пользователя. Автору процедуры при этом надо позаботиться о наличии у него исходного текста процедуры.

AS – индикатор начала собственно кода процедуры.

Вызов процедуры (исключение составляют триггеры) осуществляется по команде EXEC.

Синтаксис команды EXEC

EXEC <Имя процедуры> [<список параметров>]

Однако, прежде чем приступить к созданию хранимых процедур, познакомимся с некоторыми операторами Transact-SQL и основными понятиями, связанными с его реализацией

Во-первых, познакомимся с понятием сценарий (SCRIPT), представляющим собой последовательность действий, выполняемых целиком и сохраненных в файле. Примером сценария может быть любая команда языка DDL или DML

Во-вторых, с понятием пакет (BATH), который есть совокупность процедур, функций, переменных и операторов языка SQL, которые сгруппированы вместе и хранятся в виде единого программного блока. Все операторы внутри пакета комбинируются в единый план исполнения (execution plan), и пока все операторы не будут успешно проанализированы синтаксическим анализатором, ни один из операторов пакета не будет выполнен. Если некоторый оператор в пакете вызвал ошибку на этапе выполнения программы, то выполняются все операторы до ошибочного. Пакеты отделяются друг от друга с помощью оператора GO, который не является командой T-SQL, это директива, и на сервер не пересылается. Оператор GO должен писаться в отдельной строке и ничего кроме комментариев не должно следовать за ним. Все операторы от начала сценария до GO компилируются в один пакет и пересылаются на сервер отдельно от других пакетов.