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

Как и всякий другой язык программирования, T-SQL позволяет создавать пользовательские функции[2]. Эти функции в зависимости от типа возвращаемого значения могут быть скалярными или табличными.

Скалярные функции не могут возвращать значения с типами timestamp, text, ntext и image, а также с пользовательским типом. Скалярные пользовательские функции можно применять везде, где можно использовать возвращаемое значение встроенной функции.

На рис. 21.7 представлен шаблон скалярной функции в окне User-defined Function Properties, а на рис.21.5 — функция, заменившая этот шаблон.

Рис. 21.7

Шаблон скалярной функции в окне User-defined Function Properties

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

Синтаксис

скалярная функция (Scalar Function)

CREATE FUNCTION [ owner_name. ] function_name
([ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [,...n ] ])

RETURNS scalar_return_data_type

[ WITH < function_option> [ [, ]...n] ]

[ AS ]

BEGIN
function_body
RETURN scalar_expression
END

встраиваемая табличная функция (Inline Table-valued Function) < $I[]встраиваемая табличная функция (Inline Table-valued Function)>

CREATE FUNCTION [ owner_name. ] function_name
([ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [,...n ] ])

RETURNS TABLE

[ WITH < function_option > [ [, ]...n ] ]

[ AS ]

RETURN [ (] select_stmt [) ]

многооператорная табличная функция (Multi-statement Table-valued Function) < $I[]многооператорная табличная функция (Multi-statement Table-valued Function)>

CREATE FUNCTION [ owner_name. ] function_name
([ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [,...n ] ])

RETURNS @return_variable TABLE < table_type_definition >

[ WITH < function_option > [ [, ]...n ] ]

[ AS ]

BEGIN
function_body
RETURN
END

< function_option >:: =
{ ENCRYPTION | SCHEMABINDING }

< table_type_definition >:: =
({ column_definition | table_constraint } [,...n ])

Назначение аргументов:

owner_name — имя ID-пользователя-владельца.

function_name — имя пользовательской функции, которое должно отвечать правилам именования идентификаторов и быть уникальным для базы данных и ее владельца.

@parameter_name — параметр (аргумент) пользовательской функции, для которого можно указать значение по умолчанию (при помощи ключевого слова default). Функция может иметь до 1024 параметров. Имя параметра должно начинаться символом @.

scalar_parameter_data_type — тип данных параметра; можно использовать все скалярные типы данных, включая bigint и sql_variant. Нельзя использовать тип timestamp и определенный пользователем тип; нельзя также использовать нескалярные типы, такие как курсор и табличный тип.

scalar_return_data_type — тип данных, возвращаемых функцией; может быть любым скалярным типом, поддерживаемым SQL Server, кроме text, ntext, image и timestamp.

scalar_expression — скалярное выражение, определяющее возвращаемое значение скалярной функции.

TABLE — ключевое слово, указывающее на то, что возвращаемое значение table-valued-функции является таблицей.

Во встраиваемой табличной функции возвращаемое TABLE-значение определяется посредством единственной SELECT-инструкции. В многооператорной табличной функции @return_variable — это TABLE-переменная, используемая для сохранения и накопления возвращаемых функцией строк.

function_body — «тело» функции, содержащее набор операторов языка T-SQL; function_body используется только в скалярных и многооператорных табличных функциях. В скалярных функциях function_body — это набор операторов языка T-SQL, определяющих возвращаемое скалярное значение. В многооператорной табличной функции function_body — это набор операторов, предназначенный для заполнения возвращаемого табличного значения.

select_stmt — единственная SELECT-инструкция, определяющая возвращаемое значение во встраиваемой табличной функции.

ENCRYPTION — ключевое слово, указывающее, что SQL Server шифрует столбцы системной таблицы, содержащие текст в операторе CREATE FUNCTION. Это защищает функцию от публикаций при репликации.

SCHEMABINDING — ключевое слово, указывающее, что функция связана с объектами базы данных, на которые она ссылается. Если функция создается с опцией SCHEMABINDING, то объекты базы данных, на которые она ссылается, не могут изменяться (с использованием оператора ALTER) или удаляться (оператором DROP).

На рис. 21.8 представлена несложная скалярная пользовательская функция prc, которая возвращает общее количество товара из таблицы Запасы, используя в качестве аргумента код товара.

Рис. 21.8

Несложная скалярная пользовательская функция

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

Рис. 21.9

Скалярная функция prc существенно упрощает код процедуры нахождения ведомости товаров

Рис. 21.10 демонстрирует вызов и результат работы процедуры, приведенной на рис. 21.9.

Рис. 21.10

Вызов и результат работы процедуры, приведенной на рис. 21.9

При написании приложений, выполняемых на сервере, также часто возникает необходимость в формировании уникальных строковых ключей. В главе «Процедуры и функции» подробно рассматривается алгоритм и приведен VB-код для решения подобной задачи. Этот же алгоритм несложно использовать для написания пользовательской функции на языке Transact-QSL 2000:

Листинг 21.4 Функция формировании уникальных строковых ключей на SQL Server

1 CREATE FUNCTION NextCode(@PrevCode as nvarchar(100))

2 RETURNS varchar(100)

3 AS

4 BEGIN

5 DECLARE @mas1 as varchar(37)

6 DECLARE @RetCode as nvarchar(100)

7 DECLARE @LenMas1 as Int

8 DECLARE @LenMasNewCode as Int

9 DECLARE @s as char, @i as Int

10 DECLARE @posi as Int

11 SET @mas1 = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'

12 SET @RetCode = ''

13 SET @LenMasNewCode=LEN(@PrevCode)

14 SET @LenMas1 = Len(@mas1)

15 SET @i=@LenMasNewCode

16 WHILE @i > 0

17 BEGIN

18 SET @s = SUBSTRING(@PrevCode, @i, 1) /* текущий PrevCode-символ */

19 /*положение i-го символа в заданном диапазоне: */

20 SET @posi = CHARINDEX(@s, @mas1, 1)

21 IF @posi = @LenMas1

22 BEGIN

23 /* символ последний в диапазоне: */

24 SET @RetCode = SUBSTRING(@mas1, 1, 1) + @RetCode

25 END

26 ELSE

27 BEGIN

28 /* выбор следующего символа из диапазона

29 и окончание работы функции: */

30 SET @RetCode = SUBSTRING(@PrevCode, 1, @i - 1) +

31 SUBSTRING(@mas1, @posi + 1, 1) + @RetCode

32 SET @i = 0

33 END

34 SET @i = @i - 1

35 END

36 RETURN @RetCode /* возвращаемое значение функции */

37 END

Эта функция может использоваться при формировании строки, отличающейся от входной строки на «единицу», в процедурах, хранимых на SQL Server.

Табличная пользовательская функция возвращает таблицу. Назначение табличных функций — не в замене хранимых процедур или представлений, и они предоставляют б о льшие возможности, чем эти элементы. Подробности о табличных и многооператорных табличных функциях можно найти в справочной системе по SQL Server 2000.