Главная » Microsoft SQL Server, Базы данных » Скалярные функции

0

Скалярными называют те функции, которые возвращают одно значение. Эти функции могут принимать множество параметров, выполнять вычисления, но в результате выдают одно значение. Эти функции могут использоваться в любых выражениях, даже участвующих в ограничениях проверки. Значение возвращается функцией с помощью оператора return — эта команда должна завершать скалярную функцию.

должны быть детерминированными. Это значит, что для одних и тех же входных параметров они должны генерировать одно и то же выходное значение. По этой причине некоторые встроенные функции (например, getdate (), rasd () и newid ()) недопустимы в скалярных пользовательских функциях

В скалярных пользовательских функциях не допускаются операции обновления базы данных, но в то же время они могут работать с локальными временными таблицами. Они не могут возвращать данные BLOB (двоичные большие объекты) таких типов, как text, image и ntext, равно как табличные переменные и курсоры.

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

•                 _              – * *“ зующие доступ к базе данных, то функция будет более производительной, если

написать ее на каком-либо языке CLR, а не на Т-SQL. Более подробно о программировании на языках CLR мы поговорим в главе 27.

Создание скалярных функций

создаются, изменяются и удаляются с помощью тех же инструкций DDL, что и другие объекты, хотя синтаксис немного отличается, чтобы определить возвращаемое значение:

CREATE FUNCTION имя_функции (входные_параметры)

RETURNS тип_данных AS

BEGIN

текст_функции RETURN выражение END

В списке входных параметров должны быть указаны типы данных и, в случае необходимости, значения по умолчанию, аналогично хранимым процедурам (параметр=умолчание). Параметры функций отличаются от параметров хранимых процедур тем, что даже если определены значения по умолчанию, параметры все равно должны присутствовать в вызове функции (т.е. параметры с определенными по умолчанию значениями все равно обязательны). Чтобы запросить значение по умолчанию при вызове функции, ей передается ключевое слово default.

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

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

CREATE FUNCTION dbo.Multiply (@A INT, @B INT = 3)

RETURNS INT AS

BEGIN

RETURN @A * @B End go

SELECT dbo.Multiply (3,4)

SELECT dbo.Multiply (7, DEFAULT)

Результат выполнения пакета будет следующим:

12

21

Системные функции, разработанные компанией Microsoft, хранятся в базе дан- На заметку ных master и должны вызываться с префиксом в виде двух двоеточий

(: : имя_функции).

В следующем примере представлена более сложная скалярная функция fGetPrice, функционально аналогичная представленной в предыдущей главе хранимой процедуре pGetPrice, которая возвращала единственный результат в своем выходном параметре. Эта хранимая процедура возвращала корректную цену для заданной даты и детерминированной скидки клиента. Так как возвращаемое значение — единственное, эта операция является кандидатом на реализацию в виде скалярной функции.

Эта функция имеет такое же командное наполнение, как и приведенная выше хранимая процедура, за исключением того, что результат возвращается не с помощью выходного параметра @CurrPrice, а в операторе return. Параметр кода клиента имеет значение по умолчанию null. Еще одним отличием является то, что хранимая процедура использовала значение по умолчанию null для параметра даты, подставляя вместо него текущую дату с помощью функции get date (). Пользовательские функции являются детерминированными и не могут использовать функции типа getdate (), поэтому параметр даты в данном случае является обязательным. Ниже приведен текст скалярной функции fGetPrice ().

CREATE FUNCTION fGetPrice (

@Code CHAR(10),

@PriceDate DATETIME,

@ContactCode CHAR(15) = NULL)

RETURNS MONEY As

BEGIN

DECLARE @CurrPrice MONEY

DECLARE ©DiscountPercent NUMERIC (4,2)

– устанавливаем процент скидки

– если код заказчика не передан, скидка будет нулевой SELECT @DiscountPercent = CustomerType.DiscountPercent FROM dbo.Contact

JOIN dbo.CustomerType

ON contact.CustomerTypelD =

CustomerType.CustomerTypelD WHERE ContactCode = @ContactCode IF ©DiscountPercent IS NULL SET @DiscountPercent = 0 SELECT @CurrPrice = Price * (l-@DiscountPercent)

FROM dbo.Price JOIN dbo.Product

ON Price.ProductID = Product.ProductID WHERE Code = @Code AND EffectiveDate =

(SELECT MAX(EffectiveDate)

FROM dbo.Price JOIN dbo.Product

ON Price.ProductID = Product.ProductID WHERE Code = @Code

AND EffectiveDate <= @PriceDate)

RETURN @CurrPrice END

Вызов скалярных функций

могут использоваться в любом месте выражений, где допустимо одно значение. Пользовательские скалярные функции должны всегда вызываться с помощью двухкомпонентного имени (владелец, имя). В следующем примере продемонстрирован вызов функции fGetPriceO из базы данных OBXKites:

USE OBXKites

SELECT dbo.fGetPrice(‘1006′,GetDate(),DEFAULT)

SELECT dbo.fGetPrice(‘1001′,’5/1/2001′,NULL)

Результат выполнения пакета:

125.9500

14.9500

Дополнительная Функция dbo. GenColUpdate является пользовательской скалярной функцией, информация используемой при аудите, и будет рассмотрена в главе 24. Пользовательская –                функция dbo. TitleCase была создана в главе 8.

Создание функций со связанной схемой

Все три типа пользовательских функций могут быть созданы с использованием преимуществ связывания со схемой. Представления могут связываться со схемой, но эта функция недоступна для хранимых процедур. Связывание со схемой делает невозможным изменение и удаление всех объектов, от которых зависит функция. Если связанная со схемой функция обращается к таблице ТаЫеА, то к последней могут быть добавлены столбцы; в то же время не может быть изменен или удален ни один из существующих ее столбцов, равно как и вся таблица.

Для создания функции, связанной со схемой, вставьте параметр между ключевыми словами returns и as, как показано ниже.

CREATE FUNCTION FunctionName (Input Parameters)

RETURNS DataType WITH SCHEMA BINDING AS

BEGIN;

инструкции;

RETURNS выражение;

END;

Подключение к схеме не только предупреждает пользователя о том. что некоторая операция повлияет на объект, — оно отвергнет эти изменения. Для удаления связи со схемой, чтобы разрешить изменения, используется инструкция ALTER, отключающая это связывание.

Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.

По теме:

  • Комментарии