Создание и управление индексами. Создание и управление встроенными процедурами. Оптимизация производительности запросов. Управление транзакциями и блокировками, страница 19

С SQL Server вы можете создавать ваши собственные функции, добавляющие и расширяющие функции, предоставляемые системой. Функции могут получать 0 или более параметров и возвращать скалярное значение или таблицу. Входные параметры могут быть любого типа, исключая timestamp, cursor, table. Функции не поддерживают выходных параметров.

Сервер SQL поддерживает три типа функций определённых пользователем:

  Скалярные функции – похожи на встроенные функции;

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

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

10.2 Объявление функций определённых пользователем

В этой секции описывается создание, изменение и удаление функций, определённых пользователем. Здесь также описываются разрешения.

Создание функции, определённой пользователем

Создание функций очень похоже на создание процедур и просмотрщиков. Вы создаёте функции с помощью оператора CREATE 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

Поточная функция:

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 [ ) ]

Многооператорные функции:

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 ] )

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

USE Northwind

GO

CREATE FUNCTION fn_NewRegion

(@myinput nvarchar(30))

RETURNS nvarchar(30)

BEGIN

IF @myinput IS NULL

SET @myinput='Not Applicable';

RETURN @myinput

END

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

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

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

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

  Объекты, на которые ссылается функций, не используют ссылки из двух частей именования: owner.objectname.

  Функция и объекты расположены в одной базе данных;

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

Изменение и удаление функций

Вы можете изменять функцию с помощью оператора ALTER FUNCTION. Следующий пример показывает, как изменять таблице

ALTER FUNCTION dbo.fn_NewRegion

<Новое тело функции>

Для удаления функции используется оператор DROP FUNCTION.

DROP FUNCTION dbo.fn_NewRegion

10.3 Примеры функций определённых пользователем

В этой секции описываются примеры всех трёх типов функций:

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

Скалярные функции возвращают единственное значение в виде типа данных, определённого в RETURNS. Тело функции определяется в блоке BEGIN..END, содержащем серию операторов Transact-SQL, которые возвращают значение. Входные значения могут быть любого типа, исключая timestamp, cursor, table.