Скалярные функции

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

CREATE FUNCTION [ имя_схемы. ] имя _функции

([ { @имя_параметра [ AS ][ имя_схемы. ] тип_данных

[ = default ] [ READONLY ] }

[,...n ]

]

)

RETURNS тип_данных

[ WITH {ENCRYPTION | SCHEMABINDING | EXECUTE AS … },[…n] ]

[ AS ]

BEGIN

тело_функции

RETURN скалярное_выражение

END

Рассмотрим назначение параметров команды.

Функция может содержать один или несколько входных параметров либо не содержать ни одного. Каждый параметр должен иметь уникальное в пределах создаваемой функции имя и начинаться с символа "@". После имени указывается тип данных параметра. Дополнительно можно указать значение, которое будет автоматически присваиваться параметру (DEFAULT), если пользователь явно не указал значение соответствующего параметра при вызове функции.

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

Дополнительные параметры, с которыми должна быть создана функция, могут быть указаны посредством ключевого слова WITH. Благодаря ключевому слову ENCRYPTION код команды, используемый для создания функции, будет зашифрован, и никто не сможет просмотреть его. Эта возможность позволяет скрыть логику работы функции. Кроме того, в теле функции может выполняться обращение к различным объектам базы данных, а потому изменение или удаление соответствующих объектов может привести к нарушению работы функции. Чтобы избежать этого, требуется запретить внесение изменений, указав при создании этой функции ключевое слово SCHEMABINDING.

Между ключевыми словами BEGIN...END указывается набор команд, они и будут являться телом функции.

Когда в ходе выполнения кода функции встречается ключевое слово RETURN, выполнение функции завершается и как результат ее вычисления возвращается значение, указанное непосредственно после слова RETURN. Отметим, что в теле функции разрешается использование множества команд RETURN, которые могут возвращать различные значения. В качестве возвращаемого значения допускаются как обычные константы, так и сложные выражения. Единственное условие – тип данных возвращаемого значения должен совпадать с типом данных, указанным после ключевого слова RETURNS.

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

Пусть имеется таблица, в которой фиксируются все проводки по счетам.

R2([Номер счета], Сумма, [Дата проведения])

Если Сумма больше 0 то деньги поступают на счет, если меньше 0, то снимаются со счета

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

CREATE FUNCTION sales (@number VARCHAR(50))

RETURNS MONEY

AS
BEGIN

DECLARE @prihod MONEY

IF NOT EXISTS (SELECT * FROM R2

WHERE [Дата проведения]=@number AND ABS(DATEDIFF(DAY,GETDATE(),[Дата проведения]))<30)

SET @prihod=0

ELSE

SELECT @prihod=sum(Сумма)

FROM R2

WHERE [Дата проведения]=@number

AND ABS(DATEDIFF(DAY,GETDATE(),[Дата проведения]))<30

RETURN @prihod

END

GO

-- обращение к функции

PRINT 'Прибыль ='+CAST(Test.dbo.sales('a123') as varchar)