 |
Создание функций пользователя
|
|
|
|
Функции класса Scalar
Создание функций этого типа выполняется с помощью команды CREATE FUNCTION, имеющей следующий синтаксис:
CREATE FUNCTION имя_функции ([список_параметров])
RETURNS тип_данных
AS
BEGIN [операторы_tsql]
RETURN (возвращаемое_значение)
END.
Рассмотрим назначение и использование параметров команды.
При выборе имени функции следует учитывать, что ее имя должно быть уникальным в пределах владельца. Более того, не должно существовать объектов, принадлежащих тому же владельцу и имеющих аналогичное имя. Как и все идентификаторы объектов, имя функции имеет тип данных sysname. Таким образом, имя функции автоматически поддерживает стандарт Unicode и может быть длиной до 128 символов. При выборе имени функции необходимо следовать стандартным правилам именования объектов. Если же в имени функции предполагается использовать недопустимые символы (например, пробел, точку, слэш и т.д.), то требуются ограничители (квадратные скобки или двойные кавычки).
Функция пользователя может быть создана только в текущей базе данных. Если же необходимо создать функцию в иной базе данных, то следует установить ее в качестве текущей. Напомним, что это можно сделать с помощью команды USE database_name.
Как было сказано выше, функция может содержать ни одного, один или более входных параметров. Список_параметров имеет синтаксис: Конструкция @имя_параметра тип_данных [=значение_по_умолчанию]. Каждый параметр должен иметь имя, уникальное в пределах создаваемой функции, и начинаться с символа @. После имени параметра через пробел необходимо указать тип данных, который будет иметь параметр. Как уже было сказано, допускается задание любого из поддерживаемых SQL Server 2000 типов данных, включая пользовательские, но за исключением типов данных timestamp (rowversion), text, ntext, image, table и cursor. Дополнительно можно указать значение (default), которое будет автоматически присваиваться параметру, если пользователь явно не указал значение соответствующего параметра при вызове функции. Естественно, значение по умолчанию должно быть того же типа, что и сам параметр. Как видно из синтаксиса команды CREATE FUNCTION, можно указать множество параметров. Для этого достаточно просто перечислить их определения через запятую.
|
|
|
С помощью конструкции RETURNS задается тип данных, который будет иметь возвращаемое функцией значение. Слово RETURNS является ключевым и должно указываться всегда. Как уже было сказано, функции класса Scalar могут возвращать значения любых типов данных за исключением типов timestamp (rowversion), text, ntext, image, table и cursor.
После ключевого слова AS следует определение собственно тела функции. В принципе ключевое слово AS может быть опущено.
Между ключевыми словами BEGIN...END указывается набор команд, которые, собственно, и будут являться телом функции.
Когда в ходе выполнения кода функции встречается ключевое слово RETURN, выполнение функции завершается и как результат вычисления функции возвращается значение, указанное непосредственно после ключевого слова RETURN. Отметим, что в теле функции разрешается использование множества команд RETURN, которые могут возвращать различные значения. В качестве возвращаемого значения допустимы как обычные константы, так и сложные выражения. Единственное требование – тип данных возвращаемого значения должен совпадать с типом данных, указанных после ключевого слова RETURNS.
Рассмотрим пример простой функции, которая возвращает произведение трех переменных:
Пример 1.
CREATE FUNCTION test (@i integer, @j integer, @k integer)
RETURNS integer AS
BEGIN
return @i*@j*@k
END
Скалярную функцию можно использовать, например, с помощью оператора PRINT.
|
|
|
Для этого в окне Query введите:
PRINT dbo.test(‘4,2,10’)
Пример 2.
CREATE FUNCTION Examp (@X nvarchar(50))
RETURNS nvarchar(50)
AS
BEGIN
DECLARE @K nvarchar(50)
SELECT @K=StName+' '+StSurname+'-'+DisName
FROM Grooup,Disciplines
WHERE StName=@X
RETURN @K
END.
В этом примере создается функция, которая выводит на экран имя студента, фамилию и дисциплину, которую он изучает.
В переменную Х пользователь вводит имя студента, которое сравнивается с именами в таблице GROUP.
StName и StSurname – столбцы таблицы GROUP, DisName – название дисциплины из таблицы Disciplines.
Функции класса Inline
Создание функций этого типа выполняется с помощью команды CREATE FUNCTION, имеющей следующий синтаксис:
CREATE FUNCTION имя_функции (список_параметров)
RETURNS table
AS
RETURN (оператор_выборки)
Основная часть параметров, используемая при создании функции типа Inline, была рассмотрена ранее при рассмотрении создания функции типа Scalar. Тем не менее создание функции типа Inline все же имеет свою специфику. Как видно, после ключевого слова RETURNS всегда должно указываться значение table. Таким образом, функция Inline должна строго возвращать значение типа table. Как видно, структура возвращаемого значение типа table не указывается явно при описании собственно типа данных. Вместо этого сервер будет автоматически использовать для возвращаемого значения table ту структуру, что возвращает запрос SELECT, который является единственной командой функции. Код этого запроса указывается с помощью аргумента оператор_выборки.
Особенностью функции класса Inline является то, что структура значения table создается динамически в ходе выполнения запроса, а не указывается явно при определении типа после ключевого слова RETURNS. Как уже было сказано, возвращаемое функцией значение table может быть использовано непосредственно в запросе. То есть в разделе FROM запроса можно задавать имя функции с указанием всех входных параметров.
Рассмотрим пример создания функции, возвращающей фамилии всех студентов, код которых больше заданного:
Пример 3.
CREATE FUNCTION test1 (@i integer)
returns table
return select Family from Students where ID>=@i
Функции класса Multi-statement
Создание функций этого типа выполняется с помощью команды CREATE FUNCTION, имеющей следующий синтаксис:
CREATE FUNCTION имя_функции (список_параметров)
RETURNS @локальная_табличная_переменная TABLE
|
|
|
(определение_таблицы)
AS
BEGIN
операторы_tsql
RETURN
END
Большая часть параметров команды была рассмотрена при описании функций типа Scalar и Inline.
Как уже было сказано ранее, функция класса Multi-statement, как и функция класса Inline возвращает значение типа table. Однако, в отличие от функции класса Inline, при создании функции класса Multi-statement необходимо явно задать структуру возвращаемого значения. Эта структура указывается непосредственно после ключевого слова TABLE и, таким образом, является частью определения возвращаемого типа данных.
Возвращаемый набор данных должен формироваться с помощью команд INSERT, выполняемых в теле функции. В теле функции могут использоваться различные конструкции Transact-SQL, с помощью которых можно контролировать значения в возвращаемом функцией наборе строк (row set). При работе с командой INSERT необходимо явно указать имя объекта, в который необходимо вставить строки. Поэтому в функции класса Multi-statement, в отличие от функции класса Inline, необходимо присвоить какое-то имя значению table. Это имя и указывается с помощью параметра @локальная_табличная_переменная.
Завершение работы функции, помимо ошибок выполнения, происходит в случае, когда в коде функции встречается ключевое слово RETURN. В отличие от функции класса Scalar, при использовании команды RETURN не нужно указывать возвращаемое значение. Сервер автоматически возвратит набор данных типа table, имя и структура которого была указана после ключевого слова RETURNS. Напомним, что в теле функции может быть более одной команды RETURN.
|
|
|
