Использование хранимых процедур
Для вызова пользовательских и системных хранимых процедур используется оператор EXECUTE. Если хранимая процедура не требует параметров, или если она не возвращает результат, синтаксис ее будет очень простым:
EXECUTE имя_процедуры
Совет. Ключевое слово EXECUTE можно не указывать, если вызов хранимой процедуры является первым оператором в пакете. Однако, как и в других подобных случаях, лучше лишний раз подстраховаться. Поэтому заведите привычку всегда использовать ключевое слово EXECUTE или его аббревиатуру EXEC при использовании хранимой процедуры.
Выполните простую хранимую процедуру
Нажмите кнопку New Query (Новый запрос)
увеличить изображение
Внимание! Поскольку процедура sp_help отображает все базы данных, имеющиеся на текущем сервере, ваша структура может не совпадать с той, которая представлена на рисунке. В вашей панели сетки Grid Pane могут содержаться другие базы данных.
Если хранимая процедура принимает входные параметры, вы можете предоставить их, указав позицию или имя. Чтобы предоставит параметры по позиции, вам нужно просто указать их после имени хранимой процедуры, отделяя запятыми:
EXECUTE имя_процедуры параметр [ , параметр ...]
Использование Object Browser для работы с хранимыми процедурами
Панель Object Browser содержит папку Stored Procedures для каждой базы данных, включая главную. Каждая хранимая процедура, содержащаяся в списке, имеет папку Parameters. В этой папке в определенном порядке размещаются параметры хранимой процедуры, поэтому вы можете воспользоваться ею для проверки имен параметров и их позиций.
Для создания сценария EXECUTE для хранимой процедуры вы также можете воспользоваться командами скриптования из контекстного меню. Сценарий EXECUTE в Object Browser создает включения объявлений локальных переменных для возвращаемых значений и выходных параметров.
Выполните хранимую процедуру с входными параметрами
Выберите панель редактирования Editor Pane в окне Query (Запрос) и нажмите кнопку Clear Window (Очистить окно)
EXECUTE sp_dboption 'Aromatherapy', 'read only' Нажмите кнопку Execute Query (Выполнить запрос)
Параметры также могут быть переданы хранимой процедуре путем явного указания их имен. При этом от вас потребуется больше усилий при вводе, но зато вы сможете задать параметры в любом порядке. Синтаксис для вызова хранимой процедуры с указанием именованных параметров следующий:
EXECUTE хранимая_процедура @имя_парам = значение [, @имя_парам = значение ...]
Выполните хранимую процедуру с именованными параметрами
Выберите панель редактирования Editor Pane в окне Query (Запрос) и нажмите кнопку Clear Window (Очистить окно)
EXECUTE sp_dboption @optname = 'read only', @dbname = 'Aromatherapy'
Нажмите кнопку Execute Query (Выполнить запрос)
увеличить изображение
Некоторые хранимые процедуры предоставляют для своих параметров значения по умолчанию. Подобно значениям по умолчанию для столбцов таблицы, параметры по умолчанию используются хранимой процедурой, если пользователь явно не задал значение. Использовать умолчание проще для именованных параметров – вам достаточно не указывать значение для параметра.
Если вы передаете параметры по позициям, обращение к умолчанию параметра зависит от его положения в списке. Если параметр находится в конце списка, или если этот параметр единственный, вы можете просто опустить его (не указывать). Если параметр не является последним параметром в списке, вы можете воспользоваться ключевым словом DEFAULT, чтобы указать хранимой процедуре использовать значение по умолчанию.
Выполните хранимую процедуру с использованием ключевого слова DEFAULT
Выберите панель редактирования Editor Pane в окне Query (Запрос) и нажмите кнопку Clear Window (Очистить окно)
EXECUTE sp_dboption DEFAULT; 'read only' Нажмите кнопку Execute Query (Выполнить запрос)
Кроме доступа к переданным им данным, хранимые процедуры могут также возвращать данные обратно через выходные параметры. Выходные параметры должны быть локальными переменными. Они могут быть заданы либо путем указания их имен, либо путем указания их позиций, но при этом после выходного параметра должно следовать ключевое слово OUTPUT.
Выполните хранимую процедуру с выходными параметрами
Выберите панель редактирования Editor Pane в окне Query (Запрос) и нажмите кнопку Clear Window (Очистить окно)
Перейдите к папке SQL 2000 Step by Step в корневой директории, выделите сценарий TableValidation и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.
Нажмите кнопку Execute Query (Выполнить запрос)
Синтаксис для хранимой процедуры, возвращающей значения, является неким гибридом оператора EXECUTE и оператора SET:
EXECUTE @имя_переменной = хранимая_процедура [, парам [, парам ...] ]
Большинство системных процедур имеют возвращаемые значения, но поскольку они являются параметрами по умолчанию, их можно игнорировать в описании вызова процедуры. Если вы не указали локальную переменную для приема результатов, SQL Server просто отбросит значение.
Выполните хранимую процедуру с возвращаемым значением
Нажмите кнопку Load Script (Загрузить сценарий)
Выделите сценарий ReturnValue и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.
Нажмите кнопку Execute Query (Выполнить запрос)
Выберите вкладку Message (Сообщение). Query Analyzer отобразит результаты выполнения оператора PRINT, который выводит возвращаемое значение.
Использование и создание хранимых процедур
Как и для всех других объектов базы данных, вы должны сначала создать пользовательскую хранимую процедуру, прежде чем сможете ее использовать. Чтобы несколько облегчить понимание, мы рассмотрим эти действия в обратном порядке: сначала мы познакомимся с использованием хранимых процедур, а затем вы создадите свою собственную.
Обмен данными с хранимыми процедурами
SQL-сценарии, с которыми мы работали, выполнялись независимо – у нас не было никакой возможности передать им какую-либо информацию, а единственная информация, которую они возвращали, отображалась в панелях сетки Grid или в панели сообщений Message Pane окна Query (Запрос). Хранимые процедуры предоставляют два метода взаимодействия с внешними процессами: через параметры и через возвращаемые значения.
Параметры представляют собой специальный тип локальных переменных, объявляемых как часть хранимой процедуры. Вы можете использовать параметры для передачи информации хранимым процедурам (входные параметры) или получения данных обратно из хранимой процедуры (выходные параметры).
Возвращаемое значение схоже с результатом выполнения функции и аналогичным образом может быть присвоено локальной переменной. Возвращаемые значения всегда являются целыми числами. Теоретически они могут быть использованы для возврата любого результата, но в соответствии с соглашением они применяются для возврата статуса выполнения хранимой процедуры. Например, хранимая процедура может возвращать 0, если все идет нормально, или -1, если возникла ошибка. Более сложные хранимые процедуры могут возвращать различные значения для указания типа ошибки.
Важно не путать параметры и возвращаемые коды с какими-либо результирующими множествами, которые может возвращать хранимая процедура. Хранимая процедура может содержать любое количество операторов SELECT, которые будут возвращать результирующие множества. Для их получения вам не нужно использовать параметр; они будут возвращены в программу приложения независимо.
Пользовательские хранимые процедуры
Подобно таблицам базы данных, пользовательские хранимые процедуры представляют собой объект, являющийся частью базы данных, в которой он создан, и будет перемещаться вместе с ней. Различие заключается в том, что объекты хранимых процедур содержат не данные, а код Transact-SQL.
Примечание. SQL Server создает в каждой базе данных несколько хранимых процедур. Все имена этих хранимых процедур начинаются с dt_. Они используются для управления исходными данными, а также при преобразовании данных.
Выполнение хранимой процедуры обычно занимает меньше времени, чем запуск пакета команд из приложения. Дело в том, что SQL Server может выполнять некоторые действия из выполняемой процедуры с опережением, а затем сохранять результаты выполнения этих действий в хранимой процедуре.
Понятие о хранимых процедурах
Хранимые процедуры – не единственное средство выполнения операторов Transact-SQL. Мы уже сталкивались с SQL-сценариями и с возможностью передавать команды непосредственно из приложения. Однако хранимые процедуры обладают рядом преимуществ:
хранимые процедуры являются объектами базы данных; они размещаются в файле базы данных и перемещаются вместе с файлом в случае отключения или репликации базы данных;хранимые процедуры позволяют вам передавать данные процедуре для их обработки и принимать обратно от процедуры как данные, так и сформированный процедурой итоговый код;хранимые процедуры представляются в оптимизированной форме, что дает возможность ускорить их выполнение.
Системные процедуры
Хранимые процедуры делятся на две группы: системные процедуры, создаваемые SQL Server, и пользовательские хранимые процедуры, которые вы создаете самостоятельно. Системные хранимые процедуры хранятся в главной базе данных. Все они начинаются с символов sp_.
Совет. Вы можете создать хранимую процедуру, использовав sp_ в качестве начальной части имени, но лучше этого не делать.
Поскольку SQL Server всегда ищет хранимые процедуры прежде всего в главной базе данных, если существует системная процедура с таким же именем, ваша хранимая процедура никогда не будет выполнена.
В главной базе данных около сотни системных процедур. Многие из них предоставляют средства для программного выполнения задач администрирования, рассмотренных нами в части 1. Например, процедура sp_addlogin позволяет вам добавлять идентификатор учетной записи, а процедура sp_add_jobschedule дает возможность составлять расписание заданий, таких как резервное копирование базы данных.
Примечание. Детальная информация обо всех системных хранимых процедурах содержится в документации SQL Server Books Online.
Другие системные процедуры помогают вам управлять объектами базы данных. Например, процедура sp_rename дает возможность переименовывать объекты базы данных, а процедура sp_renamedb предоставляет средства для переименования базы данных.
Совет. Единственным способом переименования базы данных является использование системной процедуры sp_renamedb. Это действие не может быть выполнено в Enterprise Manager.
Важная группа системных процедур предоставляет информацию о текущем статусе системы: процедура sp_who предоставляет информацию о текущих пользователях и процессах; процедура sp_cursor_list предоставляет список текущих курсоров для данного соединения; процедура sp_helpdb предоставляет список всех текущих баз данных, обслуживаемых сервером, а также сообщает вам физическое местоположение файла данных и журнала транзакций для любой заданной базы данных. Вы также можете воспользоваться процедурой sp_help для получения информации об объектах базы данных. В эту информацию входят: имя, владелец и тип каждого объекта базы данных, сведения о системных и пользовательских типах данных, а также имена и параметры хранимых процедур.
Создание хранимых процедур
Как вы можете догадаться, хранимые процедуры создаются с использованием одной из разновидностей оператора CREATE – на этот раз, CREATE PROCEDURE. Синтаксис оператора CREATE PROCEDURE следующий:
CREATE PROCEDURE имя_процедуры [список_параметров] AS операторы_процедуры
Имя_процедуры должно отвечать правилам, принятым для идентификаторов.
Совет. Вы можете создать временную локальную или глобальную хранимую процедуру, указав перед именем процедуры # или ## соответственно.
Операторы_процедуры, следующие после ключевого слова AS в операторе CREATE, определяют действия, которые будут выполняться при вызове хранимой процедуры. Они по своему функциональному назначению полностью аналогичны сценариям. Фактически можно считать все, что находится перед ключевым словом AS, заголовком SQL-сценария.
Хранимые процедуры могут вызвать другие хранимые процедуры, т. е. реализовывать вложенность. Фактическая глубина вложенности хранимых процедур составляет 32.
Создайте простую хранимую процедуру
Нажмите кнопку Load Script (Загрузить сценарий)
Выделите сценарий SimpleSP и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.
Нажмите кнопку Execute Query (Выполнить запрос)
Нажмите кнопку New Query (Новый запрос)
EXECUTE SimpleSP Нажмите кнопку Execute Query (Выполнить запрос)
Закройте окно Query (Запрос), не сохраняя изменения при появлении соответствующего окна-запроса.
Каждый из параметров в списке_параметров имеет следующую структуру:
@имя_параметра тип_данных [= значение_по_умолчанию] [OUTPUT]
Имя_параметра должно удовлетворять правилам, принятым для идентификаторов. Имена параметров должны начинаться с символа @, подобно локальным переменным. Параметры являются локальными переменными; они видимы только в пределах хранимой процедуры. В одной хранимой процедуре может быть использовано максимально 2100 параметров.
Значение_по_умолчанию представляет собой значение, которое будет использоваться хранимой процедурой в случае, если пользователь не укажет значение для входного параметра в вызове хранимой процедуры. Ключевое слово OUTPUT, которое также не является обязательным, определяет параметры, которые будут возвращены в вызвавший процедуру сценарий.
Создайте хранимую процедуру с входным параметром
Перейдите к окну, содержащему сценарий SimpleSP.Нажмите кнопку Load Script (Загрузить сценарий)
Выделите сценарий InputSP и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.
Нажмите кнопку Execute Query (Выполнить запрос)
Нажмите кнопку New Query (Новый запрос)
EXECUTE InputSP 'Basil' Нажмите кнопку Execute Query (Выполнить запрос)
Закройте окно Query (Запрос), отклонив сохранение изменений в появившемся окне-запросе.
Создайте хранимую процедуру со значением по умолчанию
Перейдите к окну, содержащему сценарий InputSP.Нажмите кнопку Load Script (Загрузить сценарий)
Выделите сценарий DefaultSP и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.
Нажмите кнопку Execute Query (Выполнить запрос)
Нажмите кнопку New Query (Новый запрос)
EXECUTE DefaultSPНажмите кнопку Execute Query (Выполнить запрос)
Закройте окно Query (Запрос), отклонив сохранение изменений в появившемся окне-запросе.
Создайте хранимую процедуру с выходным параметром
Перейдите к окну, содержащему сценарий DefaultSP.Нажмите кнопку Load Script (Загрузить сценарий)
Выделите сценарий OutputSP и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.
Нажмите кнопку Execute Query (Выполнить запрос)
Нажмите кнопку New Query (Новый запрос)
DECLARE @myOutput char(6) EXECUTE OutputSP @myOutput OUTPUT SELECT @myOutput Нажмите кнопку Execute Query (Выполнить запрос)
Закройте окно Query (Запрос), отклонив сохранение изменений в появившемся окне-запросе.
Возврат значений реализуется с помощью оператора RETURN, который имеет следующую форму:
RETURN(int)
В операторе RETURN int – это целочисленное значение. Как мы видели раньше, возврат значений чаще всего используется для определения статуса выполнения хранимой процедуры. При этом 0 указывает на успешное завершение выполнения, а любое другое число указывает на ошибку. Ошибки могут быть проанализированы с помощью глобальной переменной @@ERROR, которая возвращает статус выполнения последней команды Transact-SQL: 0 указывает на успешное выполнение, а ненулевое значение указывает, что имела место ошибка.
Совет. Строка сообщения об ошибках SQL Server хранится в главной базе данных в таблице sysmessage. Вы можете добавить свои собственные сообщения об ошибках в эту таблицу, воспользовавшись системной процедурой sp_addmessage, а затем применить функцию RAISERROR для генерирования характерных для базы данных, или даже для приложения, ошибок.
Создайте хранимую процедуру с возвращаемым значением
Перейдите к окну, содержащему сценарий OutputSP.Нажмите кнопку Load Script (Загрузить сценарий)
Выделите сценарий ErrorSP и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.
Нажмите кнопку Execute Query (Выполнить запрос)
Нажмите кнопку New Query (Новый запрос)
DECLARE @theError int EXECUTE @theError = ErrorSP SELECT @theError AS 'Return Value' Нажмите кнопку Execute Query (Выполнить запрос)
Закройте окно Query (Запрос), без сохранения изменений.
Использование функции UPDATE
SQL Server предоставляет специальную функцию, UPDATE, которая может быть использована в триггере для определения, подвергся ли изменению определенный столбец в строке. Функция UPDATE имеет следующий синтаксис:
UPDATE (имя_столбца)
Функция UPDATE будет возвращать TRUE, если значения данных для указанного столбца были изменены командой INSERT или командой UPDATE.
Совет. Другая функция Transact-SQL, COLUMNS_UPDATED, возвращает битовую маску, биты которой устанавливаются в единицу для тех столбцов, которые были модифицированы. Функция COLUMNS_UPDATED может оказаться более эффективной, чем функция UPDATE, если вам необходимо проверить статус нескольких столбцов.
Используйте функцию UPDATE
Перейдите к окну Query (Запрос), содержащему сценарий insteadOf.Нажмите кнопку Load Script (Загрузить сценарий)
Выделите сценарий UpdateFunc и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
Нажмите кнопку Execute Query (Выполнить запрос)
Использование команды CREATE TRIGGER
Как и любые другие объекты базы данных, триггер определяется с помощью соответствующей формы оператора CREATE. Базовый синтаксис оператора создания триггера следующий:
CREATE TRIGGER имя_триггера ON таблица_или_представление тип_триггера, список_команд AS операторы_SQL
Имя_триггера должно удовлетворять правилам, принятым для идентификаторов. Таблица_или_представление может быть именем представления, только если тип_триггера есть INSTEAD OF, поскольку только этот тип триггера вы можете определять для представлений. Триггеры не могут быть созданы для временных таблиц или системных таблиц, но они могут ссылаться на временные таблицы.
Тип_триггера должен быть выражен одним из ключевых слов AFTER, FOR или INSTEAD OF, в то время как список_команд может быть любой комбинацией команд INSERT, UPDATE или DELETE. Если вы указываете более одной команды, их следует отделять запятыми.
Примечание. Ранние версии SQL Server поддерживали только триггеры AFTER и использовали ключевое слово FOR в качестве типа_триггера. Этот синтаксис по-прежнему поддерживается SQL Server 2000, но это будет означать то же самое, что триггер AFTER.
Операторы_SQL, следующие за ключевым словом AS, определяют действия, выполняемые триггером. Здесь имеется аналогия с хранимыми процедурами, за исключением того, что триггеры не имеют параметров.
Создайте триггер AFTER
Нажмите кнопку New Query (Новый запрос)
Перейдите к папке SQL 2000 Step by Step в корневой директории, выделите сценарий afterUpdate и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
Нажмите кнопку Execute Query (Выполнить запрос)
Выделите сценарий TestAfterUpdate и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.
Нажмите кнопку Execute Query (Выполнить запрос)
Выделите сценарий TestAfterUpdate и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.
Нажмите кнопку Execute Query (Выполнить запрос)
Создайте триггер INSTEAD OF
Перейдите к окну запроса Query, содержащему сценарий afterUpdate.Нажмите кнопку Load Script (Загрузить сценарий)
Выделите сценарий insteadOf и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
Нажмите кнопку Execute Query (Выполнить запрос)
Использование таблиц вставки и удаления
SQL Server создает две таблицы, облегчающие манипулирование данными при применении триггеров. Таблицы вставки и удаления представляют собой временные резидентно хранящиеся в памяти таблицы, которые содержат строки, подвергшиеся воздействию команды, вызвавшей триггер.
Если триггер вызывается из команды DELETE, таблица удаления будет содержать строки, которые были удалены из таблицы. При вызове из команды INSERT таблица вставки будет содержать копию новых (вставляемых) строк. Физически оператор UPDATE представляет собой последовательное выполнение команды удаления DELETE и вставки INSERT, так что таблица удаления будет содержать старые значения, а таблица вставки – новые значения. Вы можете обращаться к содержимому этих таблиц из триггера, но вы не можете изменять их.
Напомним, что триггеры AFTER не вызываются до тех пор, пока не будет осуществлена модификация таблицы, т.е. строки в исходных таблицах уже подвергнутся изменениям. С другой стороны, триггер INSTEAD OF вызывается вместо действия, для которого он определен, поэтому таблица еще не будет изменена. Фактически, таблица вообще не будет изменена, если команда INSTEAD OF не назначит соответствующие команды.
Внимание! Триггер вызывается для команды, а не для строки. Вам следует проявлять осторожность при написании триггеров, затрагивающих несколько строк (в случае их наличия) в таблицах вставки и удаления. Глобальная переменная @@ROWCOUNT будет возвращать количество записей в этих таблицах, если вы обратитесь к ней в начале описания вашего триггера.
Используйте таблицу удаления
Перейдите к окну Query (Запрос), содержащему сценарий UpdateFunc.Нажмите кнопку Load Script (Загрузить сценарий)
Выделите сценарий tableTrigger и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
Нажмите кнопку Execute Query (Выполнить запрос)
Понятие о триггерах
Преимущество триггерной процедуры состоит в том, что SQL Server автоматически вызывает ее при обращении к команде, для которой она определена. Это повышает устойчивость базы данных к внешним воздействиям (робастность). Вам нет нужды беспокоиться о том, чтобы клиенты базы данных – пользователи или приложения – разбирались и пользовались всеми зависимостями данных и установленными для них правилами.
Предположим, в торговой организации существует правило, в соответствии с которым заказ на общую сумму 10000$ или выше требует проверки кредитоспособности. Если Credit Approved является столбцом таблицы Customer, а заказ добавляется в таблицу Order, вы не сможете использовать проверочное ограничение типа CHECK для реализации правила, не следует возлагать ответственность за его соблюдение на клиентов базы данных. Триггер предоставляет механизм, обеспечивающий исполнение правила внутри базы данных.
Триггеры также могут изолировать клиентов от изменений в правилах для базы данных. В продолжение предыдущего примера, если бизнес-правило будет изменено таким образом, что проверка кредитоспособности требуется только при покупках на сумму 15000$ и более, новое правило может быть применено на уровне базы данных, без необходимости проверки и модификации клиентских приложений.
Триггеры используются для бизнес-правил, которые могут быть реализованы с помощью табличных ограничений. До появления версии SQL Server 2000 триггеры в основном применялись для отслеживания каскадных изменений связанных данных, однако, как мы уже видели, это правило теперь может быть реализовано на табличном уровне. Однако большинство правил, относящееся к взаимодействию нескольких таблиц, такие как вычисление итоговых значений или ограничение значений на основе другой таблицы, как в нашем примере, гораздо проще реализовать через триггеры.
Совет. Хотя производительность триггеров обычно не представляет проблемы (в отличие от курсоров), следует всегда применять правила по возможности на самом низком уровне. Не используйте триггеры, если можно применить проверочное ограничение CHECK, а ограничение CHECK не следует использовать, если можно обойтись ограничением UNIQUE.
Создание триггеров
SQL Server накладывает ряд ограничений на действия, выполняемые триггерами. Вы не можете создавать (командой CREATE), изменять (командой ALTER) или удалять (командой DROP) базу данных с использованием триггера; вы не можете восстанавливать базу данных или файл журнала; и вы не можете выполнять определенные операции, которые изменяют конфигурацию SQL Server. (Если вас интересует полный перечень "запрещенных" команд, обратитесь к документации SQL Server Books Online.)
Если вы с помощью триггера меняете параметры базы данных, изменение будет действовать только на время выполнения триггера; после этого параметру будет возвращено его оригинальное значение.
Теоретически вы можете использовать оператор RETURN для возврата значения из триггера, но вы не можете полагаться на то, что клиентскому приложению известно о существовании триггера и о том, какие действия он выполняет. Обычно в таких случаях лучше использовать команду RAISERROR, поскольку в большинстве разрабатываемых приложений предусмотрена возможность обработки таких ошибок.
Триггеры AFTER
SQL Server поддерживает два различных типа триггеров: триггеры AFTER и триггеры INSTEAD OF. Триггеры AFTER вызываются после выполнения команды, которой они назначены, а триггеры INSTEAD OF вызываются вместо команды.
Триггеры AFTER вы можете использовать для команд INSERT, UPDATE и DELETE. Триггеры AFTER можно создавать только для таблиц, но не для представлений. Для каждой из этих трех команд могут быть установлены несколько триггеров. С другой стороны, один триггер может быть применен для любой комбинации этих трех команд.
Совет. Если вы создаете несколько триггеров для одной команды, вы можете использовать системную процедуру sp_settriggerorder для указания первого и последнего по порядку триггера, выполняемого для команды.
Триггер 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 могут быть объявлены для представлений, они чрезвычайно полезны для получения функциональных возможностей представления, которые не могут быть доступны иным способом. Например, SQL Server не дает возможности применить для представления оператор INSERT, содержащий фразу GROUP BY, но позволяет вам определить триггер INSTEAD OF INSERT для представления. Вы можете воспользоваться триггером для вставки записей в таблицы, лежащие в основе представления, тем самым давая знать пользователю, что новая строка была вставлена в представление.
Триггеры "BEFORE"
Триггера BEFORE не существует, но триггер INSTEAD OF может порождать команду, для которой он объявлен, и эта команда будет выдаваться, как если бы триггера INSTEAD OF не было.
Например, если вы хотите проверить некое условие до выполнения команды INSERT, вы можете объявить триггер INSTEAD OF INSERT. Триггер INSTEAD OF будет выполнять проверку, а затем выполнять команду INSERT для таблицы. Оператор INSERT будет выполняться обычным образом, не порождая рекурсивных вызовов триггера INSTEAD OF.
Понятие о пользовательских функциях
Пользовательские функции в зависимости от типа данных возвращаемых ими значений могут быть скалярными или табличными. Кроме того, они различаются по детерминизму. Детерминизм функции определяется постоянством ее результатов.
Функция является детерминированной, если при одном и том же заданном входном значении она всегда возвращает один и тот же результат. Так, встроенная функция DATEADD является детерминированной – добавление трех дней к дате 20 апреля 1958 г. всегда дает дату 23 апреля 1958 г.
Функция является недетерминированной, если она может возвращать различные значения при одном и том же заданном входном значении. Так, встроенная функция GETDATE является недетерминированной. Она будет при каждом вызове возвращать различные значения.
Детерминизм пользовательской функции не зависит от того, является ли она скалярной или табличной, – функции обоих этих типов могут быть как детерминированными, так и недетерминированными. Пользовательские функции считаются детерминированными, если они обращаются к объектам базы данных за пределами области действия (видимости) функции.
Недетерминированные функции не могут быть использованы для создания индексов или вычисляемых столбцов. Кластерные индексы не могут быть созданы для представления, если представление обращается к какой-либо недетерминированной функции (независимо от того, используется ли она в индексе, или нет).
Применение пользовательских функций
Синтаксис вызова скалярных функций схож с синтаксисом, используемым для встроенных функций Transact-SQL:
имя_владельца.имя_функции([список_параметров])
Имя_владельца для скалярной функции является обязательным. Вы не можете использовать синтаксис с именованными параметрами (например, @имя_параметра = значение), а также не можете не указывать (опускать) параметры, но вы можете применять ключевое слово DEFAULT для указания значения по умолчанию, как вы это делаете для хранимых процедур.
Совет. SQL Server предоставляет несколько встроенных пользовательских функций, не относящихся к обычным встроенным функциям. Они начинаются с fn_, а при их вызове используется специальный синтаксис ::имя_функции([список_параметров]). Символы :: заменяют имя_владельца и указывают на то, что функция является встроенной пользовательской функцией.
Для скалярной функции вы также можете использовать оператор EXECUTE:
EXECUTE @возвращаемое_значение = имя_функции(список_параметров)
Если вы используете оператор EXECUTE для пользовательской функции, вам не нужно указывать имя_владельца. В этом синтаксисе вы можете использовать именованные параметры:
EXECUTE @возвращаемое_значение = имя_функции @параметр = значение [, @параметр = значение [,...]]
Если вы используете именованные параметры, параметры не обязательно должны следовать в том порядке, в котором они указаны в объявлении функции, но вам необходимо указать все параметры; нельзя опускать ссылку на параметр для использования значения по умолчанию.
Для табличных пользовательских функций, как подставляемых, так и многооператорных, должен всегда использоваться тот же синтаксис, что и для встроенных функций:
имя_функции([список_параметров])
Имя_владельца здесь указывать не требуется, но необходимо включить все определенные параметры, как и при вызове любой пользовательской функции.
Применение пользовательских функций в операторах Transact-SQL
Скалярные пользовательские функции могут использоваться везде, где допустимо использовать тип данных, который они возвращают. Табличные пользовательские функции могут быть использованы только во фразе FROM оператора SELECT.
Примечание. Если фраза SELECT находится внутри оператора DECLARE CURSOR, курсор должен иметь тип STATIC и READ_ONLY.
Используйте скалярную функцию в операторе PRINT
Нажмите кнопку New Query (Новый запрос)
PRINT dbo.scalarFunction('German Chamomile')Нажмите кнопку Execute Query (Выполнить запрос)
Используйте скалярную функцию в операторе SELECT
Выберите вкладку Editor (Редактор) в окне Query (Запрос) и нажмите кнопку Clear Window (Очистить окно)
SELECT OilID, dbo.scalarFunction(OilName) FROM OilsНажмите кнопку Execute Query (Выполнить запрос)
Используйте табличную функцию в операторе SELECT
Выберите вкладку Editor (Редактор) в окне Query (Запрос) и нажмите кнопку Clear Window (Очистить окно)
SELECT * FROM tableFunction() Нажмите кнопку Execute Query (Выполнить запрос)
Закройте окно Query (Запрос), отказавшись от сохранения изменений при появлении окна-запроса.
Применение пользовательских функций в определениях таблиц
Пользовательские функции могут быть использованы в определениях таблиц, предоставляемых владельцем таблицы, но на параметры, используемые в этих функциях, накладываются некоторые ограничения.
При использовании в качестве типа данных для вычисляемого столбца, параметры пользовательской функции должны быть либо другими столбцами в таблице, либо константами. Это справедливо и в том случае, если пользовательская функция используется в качестве проверочного ограничения типа CHECK. Если пользовательская функция используется как значение по умолчанию для столбца, параметры должны быть константами.
Примените пользовательскую функцию в вычисляемом столбце
Перейдите к окну Query (Запрос), содержащему сценарий tableFunction.Нажмите кнопку Load Script (Загрузить сценарий)
Выделите сценарий computedColumn и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.
Нажмите кнопку Execute Query (Выполнить запрос)
Выделите сценарий testColumn и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.
Нажмите кнопку Execute Query (Выполнить запрос)
Выделите сценарий defaultColumn и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.
Нажмите кнопку Execute Query (Выполнить запрос)
Скалярные функции
Скалярные пользовательские функции возвращают скалярный (однозначный) результат, такой как строка или число. На типы данных, возвращаемых скалярной функцией, накладывается несколько ограничений. Запрещается использовать нескалярные типы, такие как курсоры и таблицы. Кроме того, скалярные функции не могут возвращать значения с типом timestamp, text, ntext или image, а также значения, имеющие тип данных, определенный пользователем, даже если базовый тип при этом является скаляром.
Скалярные функции могут использоваться везде, где может использоваться возвращаемое функцией значение с соответствующим типом данных. Это может быть список столбцов в фразе WHERE оператора SELECT, выражение, условие ограничения в определении таблицы или даже описание типа данных в столбце таблицы.
Создание пользовательских функций
Как и другие объекты базы данных, пользовательские функции создаются с помощью соответствующей разновидности команды CREATE. Синтаксис ее может варьироваться в зависимости от вида создаваемой пользовательской функции.
На операторы Transact-SQL внутри тела пользовательской функции накладываются два ограничения. Во-первых, функции не должны иметь побочных эффектов, – т.е., они не могут вносить какие-либо постоянные изменения в объекты в области видимости функции.
Например, если в пределах функции создается временная таблица, операторы внутри функции могут добавлять, модифицировать и удалять строки во временной таблице. Однако пользовательская функция не может изменять строки из постоянной таблицы.
Второе ограничение состоит в том, что операторы в пользовательской функции не могут вызывать какие-либо недетерминированные функции (встроенные или пользовательские) или обращаться к недетерминированным глобальным переменным, таким как @@TOTAL_ERRORS, которая возвращает количество ошибок, обнаруженных SQL Server с момента запуска сервера.
Создание скалярной функции
Оператор CREATE для скалярных пользовательских функций имеет достаточно простой синтаксис для функций всех типов:
CREATE FUNCTION имя_функции ([список_параметров]) RETURNS тип_данных AS BEGIN [операторы_tsql] RETURN (возвращаемое_значение) END
Имя_функции должно удовлетворять правилам, действующим для идентификаторов. Хотя список_параметров в описании оператора CREATE занимает иное место, чем список параметров для хранимой процедуры, они имеют аналогичный синтаксис:
@имя_параметра тип_данных [= значение_по_умолчанию]
Имя_параметра должно соответствовать правилам, принятым для идентификаторов, и начинаться с символа @. Пользовательские функции могут иметь до 1024 входных параметров. Выходные параметры пользовательские функции не поддерживают – единственным значением, возвращаемым функцией, является результат ее выполнения. Заметим, что список_параметров является необязательным, но наличие скобок обязательно.
Фраза RETURNS определяет тип возвращаемых функцией значений. Скалярные функции могут возвращать любые скалярные системные типы данных, за исключением timestamp, text, ntext или image.
Операторы BEGIN...END, которыми ограничиваются операторы_tsql, составляющие тело функции, являются обязательными, даже если тело функции состоит из одного оператора RETURN.
Создайте скалярную функцию
Нажмите кнопку New Query (Новый запрос)
Перейдите к папке SQL 2000 Step by Step в корневой директории, выделите сценарий scalarFunction и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.
Нажмите кнопку Execute Query (Выполнить запрос)
увеличить изображение
Примечание. Созданные здесь пользовательские функции мы используем в следующем разделе.
Создание табличных функций
Оператор CREATE FUNCTION поддерживает создание двух различных типов табличных функций: подставляемых и многооператорных. Тело подставляемой табличной функции состоит из единственного оператора SELECT, в то время как многооператорная табличная функция может состоять из любого числа операторов Transact-SQL.
Синтаксис для подставляемой табличной функции является усеченной разновидностью оператора CREATE FUNCTION. Блок BEGIN...END отсутствует, и нет никаких других операторов, кроме RETURN:
CREATE FUNCTION имя_функции (список_параметров) RETURNS таблица AS RETURN (оператор_выборки)
Создайте подставляемую табличную функцию
Перейдите к окну запроса, содержащему сценарий scalarFunction.Нажмите кнопку Load Script (Загрузить сценарий)
Выделите сценарий inlineFunction и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.
Нажмите кнопку Execute Query (Выполнить запрос)
увеличить изображение
Синтаксис оператора CREATE FUNCTION для многооператорной табличной функции сочетает элементы синтаксиса для скалярной и для подставляемой функций:
CREATE FUNCTION имя_функции (список_параметров) RETURNS @локальная_табличная_переменная TABLE (определение_таблицы) AS BEGIN операторы_tsql RETURN END
Подобно скалярным функциям, в многооператорной табличной функции команды Transact-SQL располагаются внутри блока BEGIN...END. Поскольку блок может содержать несколько операторов SELECT, в фразе RETURNS вы должны явно определить таблицу, которая будет возвращаться.
Поскольку оператор RETURN в многооператорной табличной функции всегда возвращает таблицу, заданную во фразе RETURNS, он должен выполняться без аргументов, – например, RETURN, а не RETURN @myTable.
Табличные функции
Табличные пользовательские функции возвращают таблицу и не заменяют хранимые процедуры или представления, но в определенных ситуациях они могут предоставить более широкие возможности, которые трудно реализовать с помощью этих объектов.
Так, таблица Oils в базе данных Aromatherapy является полностью нормализованной, поэтому более подробная информация, относящаяся к полям PlantParts и Cautions, хранится в других таблицах. Для среднего пользователя все это не имеет особого смысла, и большинству людей трудно будет воспринимать оператор SELECT, осуществляющий полную денормализацию строки в таблице Oils.
Использование представления может избавить пользователя от излишней сложности, но представления не поддерживают параметры, поэтому для клиента нет никакого способа указать, какой вид масла следует отобразить в процессе выполнения программы. Хранимые процедуры могут принимать параметры, но они не могут быть использованы во фразе FROM оператора SELECT, что существенно осложняет обработку результатов. Применение табличных функций решает обе эти проблемы.
Возьмем пользовательскую функцию с именем GetOilDetails, которая принимает название ароматического масла и возвращает более подробную информацию о нем. Ваш клиент может использовать следующий оператор Transact-SQL для получения в качестве результата полностью денормализованной информации о масле:
SELECT * FROM GetOilDetails('Basil')
Подобная возможность дает существенное преимущество, связанное с упрощением клиентского программного обеспечения.
