Хранимые процедуры в среде MS SQL Server

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

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

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

В SQL Server имеется несколько типов хранимых процедур.

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

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

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

Пользовательские хранимые процедуры реализуют те или иные дей­ствия. Хранимые процедуры - полноценный объект базы данных. Они располагаются в кон­кретной базе данных, где и выполняются.

Временные хранимые процедуры существуют лишь некоторое вре­мя, после чего автоматически уничтожаются сервером. Они делятся на две категории:

1. локальные;

2. глобальные.

Локальные временные хранимые про­цедуры могут быть вызваны только из того соединения, в котором созданы.

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

Как и все временные объекты, хранимые процедуры этого типа автоматически удаляются при отклю­чении пользователя, перезапуске или остановке сервера.

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

Для ее определения достаточно дать ей имя, начинающееся с символов ##.

Удаляются эти процедуры при перезапуске или остановке сервера, а также при закры­тии соединения, в котором которого они были созданы.

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

Создание хранимой процедуры предполагает решение следующих

задач:

1. определение типа создаваемой хранимой процедуры: пользовательская или временная. Можно создать свою собственную системную хранимую процедуру, назначив ей имя с префиксом sp_ и поместив ее в системную базу данных. Такая процедура будет дос­тупна в контексте любой базы данных локального сервера;

2. планирование прав доступа. При создании хранимой процедуры следует учитывать, что она будет иметь те же права доступа к объек­там базы данных, что и создавший ее пользователь;

3. определение параметров хранимой процедуры. Подобно процеду­рам, входящим в состав большинства языков программирования, хранимые процедуры могут обладать входными и выходными пара­метрами;

4. разработка кода хранимой процедуры. Код процедуры может содер­жать последовательность любых команд SQL, включая вызов других хранимых процедур.

Создание новой и изменение имеющейся хранимой процедуры осу­ществляется с помощью следующей команды:

<определение_процедуры>::=

{CREATE| ALTER } PROC[EDURE] имя_процедуры [; номер]

[(@имя_параметра тип_данных ] [VARYING ] [= default]

[OUTPUT] ] [,...n]

[ READONLY]

[WITH { RECOMPILE | ENCRYPTION }]

[FOR REPLICATION]

AS

SQL_onepaтоpы [ ...n]

Рассмотрим параметры данной команды.

Используя префиксы sp_, #, ##, создаваемую процедуру можно оп­ределить в качестве системной или временной.

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

Таким образом, чтобы разместить создаваемую хранимую процедуру в конкретной базе данных, необходимо выполнить команду

CREATE PROCEDURE при использовании этой базы данных.

При обраще­нии из тела хранимой процедуры к объектам той же базы данных можно использовать укороченные имена, т. е. без указания имени базы данных. Когда же требуется обратиться к объектам, расположенным в других базах данных, указание имени базы данных обязательно.

Для удобства уп­равления процедурами логически однотипные хранимые процедуры можно группировать, присваивая им одинаковые имена, но разные иден­тификационные номера, то есть создавать группу процедур.

Номер в имени - это идентификационный номер хранимой проце­дуры, однозначно определяющий ее в группе процедур (если создается группа).

Для передачи входных и выходных данных в создаваемой хранимой процедуре могут использоваться параметры, имена которых, как и имена локальных переменных, должны начинаться с символа @.

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

В теле процедуры нельзя применять локальные переменные, чьи имена совпадают с именами параметров этой процедуры.

Для определения типа данных, который будет иметь соответствую­щий параметр хранимой процедуры, годятся любые типы данных SQL, включая определенные пользователем. Однако тип данных CURSOR может быть использован только как выходной параметр хранимой процедуры, т.е. с указанием ключевого слова OUTPUT.

Наличие ключевого слова OUTPUTозначает, что соответствующий параметр предназначен для возвращения данных из хранимой процеду­ры. Однако это вовсе не означает, что этот параметр не подходит и для передачи значений в хранимую процедуру.

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

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

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

Ключевое слово VARYING применяется совместно с параметром

OUTPUT, имеющим тип CURSOR. Оно определяет, что выходным парамет­ром будет некоторое результирующее множество.

Ключевое слово DEFAULT представляет собой значение, которое бу­дет принимать соответствующий параметр по умолчанию. В этом случае при вызове процедуры можно не указывать явно значение соответствую­щего параметра.

Так как сервер кэширует план исполнения запроса и откомпилирован­ный код, при последующем вызове процедуры будут использоваться уже готовые значения. Однако в некоторых случаях все же требуется выпол­нять перекомпиляцию кода процедуры. Указание ключевого слова RECOMPILEпредписывает системе создавать новый план выполнения хранимой процедуры при каждом ее вызове.

Параметр FOR REPLICATION востребован при репликации данных и включении создаваемой хранимой процедуры в качестве статьи в публи­кацию,

Ключевое слово encryption предписывает серверу выполнить шиф­рование кода хранимой процедуры, что может обеспечить защиту от исполь­зования авторских алгоритмов, реализующих работу хранимой процедуры.

Ключевое слово AS размещается в начале собственно тела хранимой процедуры, т.е. набора команд SQL, с помощью которых и будет реализо­вываться то или иное действие. В теле процедуры могут применяться пра­ктически все команды SQL, объявляться транзакции, устанавливаться блокировки и вызываться другие хранимые процедуры.

Выход из хранимой процедуры можно осуществить посредством команды RETURN.

Удаление хранимой процедуры осуществляется командой:

DROP PROCEDURE {имя_процедуры> [,...n]}