Хранимые процедуры в среде 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]}

Случайные записи:

Триггеры и хранимые процедуры в MS SQL Server (Triggers and stored procedures)


Похожие статьи:

Добавьте постоянную ссылку в закладки. Вы можете следить за комментариями через RSS-ленту этой статьи.
Комментарии и трекбеки сейчас закрыты.