Создание и управление индексами. Создание и управление встроенными процедурами. Оптимизация производительности запросов. Управление транзакциями и блокировками, страница 20

Примеры скалярных функций

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

Следующий пример создаёт функцию, которая получает дату и разделитель как переменные и переформатирует строку:

USE Northwind

GO

CREATE FUNCTION fn_DateFormat

(@indate datetime, @separator char(1))

RETURNS nvarchar(20)

BEGIN

RETURN CONVERT(nvarchar(20), datepart(mm, @indate))+@separator+

CONVERT(nvarchar(20), datepart(dd, @indate))+@separator+

CONVERT(nvarchar(20), datepart(yy, @indate))

END

Вы можете вызвать эту функцию следующим способом:

SELECT dbo.fn_DateFormat(GETDATE(), ':')

Использование многооператорных функций

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

Такие функции могут иметь комплексную логику и множество операторов Transact-SQL для создания таблицы. В некоторых случаях, где вы используете просмотрщик, вы можете использовать функцию в WHERE.

Примите к сведению следующие факты о таких функциях:

  Тело функции определяется в блоке BEGIN..END.

  В RETURN вы указываете таблицу, как возвращаемый тип.

  В RETURN вы указываете имя для таблицы и её формат. Имя переменной локально для функции.

Пример многооператорных функций

Следующий пример создаёт функцию, которая возвращает фамилию или имя и фамилию работника связанного с предоставляемым параметром:

USE Northwind

GO

CREATE FUNCTION fn_Employees

(@length nvarchar(9))

RETURNS @fn_Employees TABLE

(EmployeeID int PRIMARY KEY NOT NULL,

[Employee Name] nvarchar(61) NOT NULL)

AS

BEGIN

IF @length='ShortName'

INSERT @fn_Employees

SELECT EmployeeID, LastName

FROM Employees

ELSE

IF @length='LongName'

INSERT @fn_Employees

SELECT EmployeeID, (FirstName+' '+LastName)

FROM Employees

RETURN

END

Вы можете вызвать эту функцию следующим образом:

SELECT * FROM dbo.fn_Employees('ShortName')

Использование потоковых функций

Когда вы используете потоковые функции, примите к сведению следующие факты:

  Опция RETURN содержит единственный оператор SELECT. Результирующий набор оператора SELECT имеет форму таблицы, которую возвращает функция.

  Тело функции определяется в блоке BEGIN..END.

  В RETURN вы указываете таблицу, как возвращаемый тип.

  Вам не надо определять тип возвращаемых значений, потому что набор определяется по формату результирующего набора SELECT.

Пример потоковой функции

Следующий пример функции, которая получает значение региона в качестве параметра:

USE Northwind

GO

CREATE FUNCTION fn_CustomerNamesInRegion

(@RegionParameter nvarchar(30))

RETURNS TABLE

AS

RETURN(

SELECT CustomerID, CompanyName

FROM Northwind.dbo.Customers

WHERE Region=@RegionParameter

)

Пример вызова функции:

SELECT * FROM fn_CustomerNamesInRegion(N'WA')

Разработка триггеров

11.1 Введение в триггеры

В этой секции описываются триггеры, и описывается, когда и как их использовать.

Что такое триггеры

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

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

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

  Объявление триггера может содержать оператор ROLLBACK TRANSACTION даже если не существует соответствующего BEGIN TRANSACTION.

  Если срабатывает оператор ROLLBACK TRANSACTION, содержимое транзакции откатывается. Если операторы в триггере следуют за ROLLBACK TRANSACTION, операторы выполняются. Это может быть не обязательным при использовании команды RETURN.