Использование Object Browser для работы с хранимыми процедурами

Панель Object Browser содержит папку Stored Procedures для каждой базы данных, включая главную. Каждая хранимая процедура, содержащаяся в списке, имеет папку Parameters. В этой папке в определенном порядке размещаются параметры хранимой процедуры, поэтому вы можете воспользоваться ею для проверки имен параметров и их позиций.

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

CREATE PROCEDURE имя_процедуры

[список параметров]

AS

операторы_процедуры

Имя_процедуры должно отвечать правилам, принятым для идентификаторов.

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

Операторы_процедуры, следующие после ключевого слова AS в операторе CREATE, определяют действия, которые будут выполняться при вызове хранимой процедуры. Хранимые процедуры могут вызвать другие хранимые процедуры, т. е. реализовывать вложенность. Фактическая глубина вложенности хранимых процедур составляет 32.

Имена_параметров должны начинаться с символа @, подобно локальным переменным.

@имя_параметра тип_данных [= значение_по_умолчанию] [OUTPUT]

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

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

 

Использование аргументов

Хранимые процедуры не были бы так полезны, если бы они не могли принимать аргументы. В этом хранимые процедуры подобны функциям: они принимают один или более аргументов и возвращают вызывающему коду одно или более значений. Кроме того, хранимые процедуры могут возвращать один или более курсоров. Кур­сор — это эквивалент объекта ADO Recordset (но не совсем то же самое, что объект DataSet из ADO. NET). Данные курсора выводятся на панели Results в окне Query Analyzer, когда вы выполняете инструкцию SELECT.

Аргументы, передаваемые в хранимую процедуру и из нее, объявляются после имени процедуры в инструкции CREATE PROCEDURE. Список разделенных запятыми аргументов следует после имени процедуры, перед ключевым словом AS:

CREATE PROCEDURE procedure_name

@argument1 type1, @argument2 type2, …

AS

 

Возврат значений из хранимой процедуры

Возврат значений реализуется с помощью оператора RETURN, который имеет следующую форму: RETURN (int)

В операторе RETURN int - это целочисленное значение. Возврат значений чаще всего используется для определения статуса выполнения хранимой процедуры. Ошибки могут быть проанализированы с помощью глобальной переменной @@ERROR, которая возвращает статус выполнения последней команды Transact-SQL: 0 указывает на успешное выполнение, а ненулевое значение указывает, что имела место ошибка.

Триггеры

 

Триггер — это особый вид хранимой процедуры, которую Microsoft SQL Server выполняет автоматически, когда вы модифицируете строку с помощью команд INSERT, UPDATE или DELETE. Мы рассмотрим различные виды триггеров, в том числе обладающий богатыми возможностями триггер INSTEAD OF, который является новинкой для SQL Server 2000.

Преимущество триггерной процедуры состоит в том, что SQL Server автоматически вызывает ее при обращении к команде, для которой она определена. Это повышает устойчивость базы данных к внешним воздействиям (робастность).

 

Триггеры AFTER

SQL Server поддерживает два различных типа триггеров: триггеры AFTER и триггеры INSTEAD OF. Триггеры AFTER вызываются после выполнения команды, которой они назначены, а триггеры INSTEAD OF вызываются вместо команды.

Триггеры AFTER вы можете использовать для команд INSERT, UPDATE и DELETE. Триггеры AFTER можно создавать только для таблиц, но не для представлений. Для каждой из этих трех команд может быть уста­новлено несколько триггеров. С другой стороны, один триггер может быть применен для любой комбинации этих трех команд.

Триггер AFTER вызывается после того, как выполнены все операции по обработке ограничений низкого уровня, и не будут вызваны в случае на­рушения ограничения. Например, если осуществляется попытка вставить строку, которая нарушает ограничение PRIMARY KEY для таблицы, опе­ратор INSERT не будет выполнен до того, как произойдет вызов триггера.

 

Триггеры INSTEAD OF

Триггеры INSTEAD OF заменяют команду, для которой они объявлены. Подобно триггерам AFTER, вы можете определять триггеры INSTEAD OF для команд INSERT, UPDATE или DELETE. Один триггер может быть применен к нескольким командам.

Однако, в отличие от триггеров AFTER, вы можете создавать триггеры INSTEAD OF как для таблиц, так и для представлений, но для каждого действия над этой таблицей или представлением может быть создан толь­ко один триггер INSTEAD OF.

Триггеры INSTEAD OF несовместимы с каскадными изменениями свя­занных данных. Вы не можете объявить триггер INSTEAD OF DELETE или INSTEAD OF UPDATE для таблицы, внешний ключ которой затра­гивается действиями удаления (DELETE) или модификации (UPDATE).

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

 

Триггеры BEFORE

Триггера BEFORE не существует, но триггер INSTEAD OF может порож­дать команду, для которой он объявлен, и эта команда будет выдаваться, как если бы триггера INSTEAD OF не было.

Например, если вы хотите проверить некое условие до выполнения ко­манды INSERT, вы можете объявить триггер INSTEAD OF INSERT. Триг­гер INSTEAD OF будет выполнять проверку, а затем выполнять команду INSERT для таблицы. Оператор INSERT будет выполняться обычным образом, не порождая рекурсивных вызовов триггера INSTEAD OF.

 

Создание триггеров

SQL Server накладывает ряд ограничений на действия, выполняемые триггерами. Вы не можете создавать (командой CREATE), изменять (ко­мандой ALTER) или удалять (командой DROP) базу данных с использо­ванием триггера; вы не можете восстанавливать базу данных или файл журнала; вы не можете выполнять определенные операции, которые из­меняют конфигурацию SQL Server. (Если вас интересует полный пере­чень «запрещенных» команд, обратитесь к документации SQL Server Books Online.)

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

Базовый синтаксис оператора создания триггера следующий:

CREATE TRIGGER имя_триггера

ON таблица_или_представление

тип_триггера, список_команд

AS

onepaтopы_SQL

Имя_триггера должно удовлетворять правилам, принятым для иденти­фикаторов. Таблица_или_представление может быть именем представ­ления, только если тип_триггера есть INSTEAD OF, поскольку только этот тип триггера вы можете определять для представлений.

Триггеры не могут быть созданы для временных или системных таблиц; но они мо­гут ссылаться на временные таблицы.

Тип_триггера должен быть выражен одним из ключевых слов AFTER, FOR или INSTEAD OF, в то время как список_команд может быть любой комбинацией команд INSERT, UPDATE или DELETE. Если вы указывае­те более одной команды, их следует отделять запятыми.

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

 

Задания для проведения семинарских занятий