SQL Server 2012. Создает определяемую пользователем функцию в SQL Server 2012

Создает определяемую пользователем функцию в SQL Server 2012. Определяемая пользователем функция представляет собой подпрограмму Transact-SQL или среды CLR, которая принимает параметры, выполняет действия, такие как сложные вычисления, а затем возвращает результат этих действий в виде значения. Возвращаемое значение может быть скалярным значением или таблицей. При помощи этой инструкции можно создать подпрограмму, которую можно повторно использовать следующими способами.

· В инструкциях Transact-SQL, например SELECT.

· В приложениях, вызывающих функцию.

· В определении другой пользовательской функции.

· Для параметризации представления или улучшения функциональности индексированного представления.

· Для определения столбца таблицы.

· Для определения ограничения CHECK на столбец.

· Для замены хранимой процедуры.

Синтаксические обозначения в Transact-SQL

Синтаксис

--Transact-SQL Scalar Function SyntaxCREATE FUNCTION [ schema_name. ] function_name ([ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type [ = default ] [ READONLY ] } [,...n ] ])RETURNS return_data_type [ WITH <function_option> [,...n ] ] [ AS ] BEGIN function_body RETURN scalar_expression END[; ]

Аргументы

schema_name

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

function_name

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

Примечание

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

@ parameter_name

Аргумент пользовательской функции. Может быть объявлен один или несколько аргументов.

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

Определяет имя параметра, используя знак @ как первый символ. Имя параметра должно соответствовать правилам для идентификаторов. Параметры являются локальными в пределах функции, в разных функциях могут быть использованы одинаковые имена параметров. Параметры могут использоваться только вместо констант. Они не могут использоваться вместо имен таблиц, имен столбцов или имен других объектов базы данных.

Примечание

Параметры ANSI_WARNINGS не годятся для передачи в хранимые процедуры, пользовательские функции и при объявлении и установке переменных в пакетных инструкциях. Например, если объявить переменную типа char(3), а затем присвоить ей значение длиннее трех символов, данные будут усечены до размера переменной, а инструкция INSERT или UPDATE завершится без ошибок.

[ type_schema_name. ] parameter_data_type

Тип данных параметра (возможно, с указанием схемы, которой он принадлежит). Для функций Transact-SQL допустимы любые типы данных, включая определяемые пользователем типы данных CLR и определяемые пользователем табличные типы, за исключением типа данных timestamp. Для функций CLR допустимы все типы данных, включая определяемые пользователем типы данных CLR, за исключением типов данных text, ntext, image, определяемых пользователем табличных типов и типов данных timestamp. Нескалярные типы cursor и table не могут быть указаны в качестве типов данных параметров ни для функций Transact-SQL, ни для функций CLR.

Если аргумент type_schema_name не определен, то компонент Компонент Database Engine ищет аргумент scalar_parameter_data_type в следующем порядке:

· в схеме, содержащей имена системных типов данных SQL Server;

· в установленной по умолчанию для текущего пользователя схеме в текущей базе данных.

· в схеме dbo в текущей базе данных.

[ = default ]

Значение по умолчанию для аргумента. Если определено значение default, то функция выполняется даже в том случае, если для данного аргумента значение не указано.

Примечание

Для функций CLR также могут указываться значения параметров по умолчанию, кроме типов varchar(max) и varbinary(max).

Если параметр функции имеет значение по умолчанию, то для него должно быть указано ключевое слово DEFAULT для получения функцией значения по умолчанию. Поведение ключевого слова DEFAULT отличается от использования аргументов со значениями по умолчанию в хранимых процедурах, когда неуказанный аргумент неявно принимает значение по умолчанию. Однако ключевое слово DEFAULT не требуется при вызове скалярной функции с помощью инструкции EXECUTE.

READONLY

Указывает, что параметр не может быть обновлен или изменен при определении функции. Если тип параметра является определяемым пользователем табличным типом, то должно быть указано ключевое слово READONLY.

return_data_type

Возвращаемое значение скалярной функции, определяемой пользователем. Для функций Transact-SQL допустимы любые типы данных, включая определяемые пользователем типы данных CLR, за исключением типа данных timestamp. Для функций CLR допустимы все типы данных, включая определяемые пользователем типы данных CLR, за исключением типов данных text, ntext, image и timestamp. Нескалярные типы данных cursor и table не могут быть указаны в качестве возвращаемых типов данных ни для функций Transact-SQL, ни для функций CLR.

function_body

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

Для скалярных функций function_body представляет собой ряд инструкций Transact-SQL, которые в совокупности вычисляют скалярное выражение.

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

scalar_expression

Указывает скалярное значение, возвращаемое скалярной функцией.

CREATE FUNCTION dbo.CalculateCircleArea (@Radius float = 1.0)

RETURNS float

WITH RETURNS NULL ON NULL INPUT

AS

BEGIN

RETURN PI() * POWER(@Radius, 2);

END;

GO

-- Тестируем функцию

SELECT dbo.CalculateCircleArea(10);

SELECT dbo.CalculateCircleArea(NULL);

SELECT dbo.CalculateCircleArea(2.5);

GO