Поделиться через

  • Статья

ALTER FUNCTION (Transact-SQL)

Изменяет существующую функцию Transact-SQL или CLR, которая была предварительно создана при помощи инструкции CREATE FUNCTION, без изменения разрешений и без влияния на какие-либо зависимые функции, хранимые процедуры или триггеры.

Значок ссылки на разделСинтаксические обозначения в Transact-SQL

Синтаксис

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

Inline Table-valued Functions
ALTER FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ =default ] } 
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

Multistatement Table-valued Functions
ALTER FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ =default ] } 
    [ ,...n ]
  ]
)
RETURNS @return_variable TABLE <table_type_definition>
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
        function_body 
        RETURN
    END
[ ; ]

CLR Functions
ALTER FUNCTION [ schema_name. ] function_name ( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type 
    [ =default ] } 
    [ ,...n ]
)
RETURNS { return_data_type | TABLE <clr_table_type_definition> }
    [ WITH <clr_function_option> [ ,...n ] ]
    [ AS ] EXTERNAL NAME <method_specifier>
[ ; ]

<method_specifier>::=
    assembly_name.class_name.method_nameFunction Options<function_option>::= 
{
    [ ENCRYPTION ]
  | [ SCHEMABINDING ]
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ]
}

<clr_function_option>::=
}
    [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ]
}

Table Type Definitions<table_type_definition>:: = ( { <column_definition> <column_constraint> 
  | <computed_column_definition> } 
    [ <table_constraint> ] [ ,...n ]
)<clr_table_type_definition>:: = ( { column_name data_type } [ ,...n ] )<column_definition>::=
{
    { column_name data_type }
    [ [ DEFAULT constant_expression ] 
      [ COLLATE collation_name ] | [ ROWGUIDCOL ]
    ]
    | [ IDENTITY [ (seed ,increment ) ] ]
    [ <column_constraint> [ ...n ] ] 
}
<column_constraint>::= 
{
    [ NULL | NOT NULL ] 
    { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NONCLUSTERED ] 
        [ WITH FILLFACTOR = fillfactor 
        | WITH ( < index_option > [ , ...n ] )
      [ ON { filegroup | "default" } ]
  | [ CHECK (logical_expression ) ] [ ,...n ]
}

<computed_column_definition>::=column_name AS computed_column_expression<table_constraint>::=
{ 
    { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NONCLUSTERED ] 
      (column_name [ ASC | DESC ] [ ,...n ] )
        [ WITH FILLFACTOR = fillfactor 
        | WITH ( <index_option> [ , ...n ] )
  | [ CHECK (logical_expression ) ] [ ,...n ]
}

<index_option>::=
{ 
    PAD_INDEX = { ON | OFF } 
  | FILLFACTOR = fillfactor 
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF } 
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS ={ ON | OFF } 
}

Аргументы

  • 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 не определен, то SQL Server 2005 Database Engine ищет аргумент parameter_data_type в следующем порядке:

    • Схема, содержащая имена системных типов данных SQL Server.

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

    • Схема dbo текущей базы данных.

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

    ПримечаниеПримечание

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

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

  • 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
    Указывает на то, что скалярная функция возвращает скалярное значение.

  • TABLE
    Указывает, что возвращаемым значением функции, возвращающее табличное значение, является таблица. Функциям, возвращающим табличное значение, могут передаваться только константы и **@**local_variables.

    Во встроенных возвращающих табличное значение функциях возвращаемое значение TABLE определяется при использовании единственной инструкции SELECT. Встроенные функции не имеют соответствующих возвращаемых переменных.

    В функциях, возвращающих табличное значение из нескольких инструкций, переменная **@**return_variable имеет тип TABLE и используется для сохранения данных и накопления строк, которые будут возвращены в качестве значения функции. Аргумент **@**return_variable может быть указан только для функций Transact-SQL, но не для функций CLR.

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

  • EXTERNAL NAME <method_specifier>assembly_name.class_name.method_name
    Указывает метод сборки, привязываемый к функции. Значение аргумента assembly_name должно соответствовать имени существующей сборки в SQL Server в текущей базе данных, для которой включена видимость. Значение аргумента class_name должно быть допустимым идентификатором SQL Server, указывающим имя класса в сборке. Если для класса через точку (.) указано пространство имен, имя класса должно быть выделено квадратными скобками ([]) или двойными кавычками (""). Значение аргумента method_name должно быть допустимым идентификатором SQL Server, указывающим имя статического метода в указанном классе.

    ПримечаниеПримечание

    По умолчанию SQL Server не производит выполнение кода CLR. Допускается создание, изменение и удаление объектов базы данных, содержащих ссылки на модули среды CLR, однако SQL Server их не выполняет до тех пор, пока не будет включен параметр clr enabled. Для включения параметра используйте хранимую процедуру sp_configure.

  • <table_type_definition> ( { <column_definition> <column_constraint> | <computed_column_definition> } [ <table_constraint> ] [ ,...n ])
    Определяет тип данных таблицы для функции Transact-SQL. Объявление таблицы включает определения столбцов, а также ограничений для столбцов и таблиц.

  • < clr_table_type_definition > ( { column_namedata_type } [ ,...n ] )
    Определяет табличные типы данных для функции CLR. Объявление таблицы включает только имена столбцов и типы данных.

<function_option>::= and <clr_function_option>::=

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

  • ENCRYPTION
    Указывает на то, что компонент Database Engine шифрует столбцы представления каталога, содержащие текст инструкции ALTER FUNCTION. Использование параметра ENCRYPTION препятствует публикации данной функции при помощи репликации SQL Server. Параметр ENCRYPTION для функций CLR указывать нельзя.

  • SCHEMABINDING
    Указывает, что функция привязана к объектам базы данных, которые содержат ссылки на нее. Это препятствует внесению изменений в функцию в случае, если другие привязанные к схеме объекты ссылаются на нее.

    Привязка функции к ссылающимся на нее объектам удаляется в следующих случаях:

    • При удалении функции.

    • При изменении функции инструкцией ALTER, если не указан параметр SCHEMABINDING.

    Список условий, которые необходимо учитывать перед привязкой функции к схеме, см. в разделе CREATE FUNCTION (Transact-SQL).

  • RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT
    Указывает атрибут OnNULLCall скалярной функции. Если данный аргумент не указан, по умолчанию предполагается CALLED ON NULL INPUT. Это означает, что текст функции выполняется даже в том случае, если в качестве аргумента передано значение NULL.

    Если атрибут RETURNS NULL ON NULL INPUT указан для функции CLR, это означает, что SQL Server может вернуть NULL, не вызывая при этом тело функции в том случае, если в качестве какого-либо из аргументов указано значение NULL. Если определенный в <method_specifier> метод уже обладает пользовательским атрибутом, указывающим на RETURNS NULL ON NULL INPUT, то инструкция ALTER FUNCTION указывает на CALLED ON NULL INPUT, инструкция ALTER FUNCTION обладает правом старшинства. Атрибут OnNULLCall не может быть указан для функций CLR, возвращающих табличное значение.

  • Предложение EXECUTE AS
    Указывает контекст безопасности, в котором выполняется определяемая пользователем функция. Таким образом, можно отслеживать то, какая пользовательская учетная запись SQL Server используется для проверки разрешений функции на осуществление ссылки на любые объекты базы данных.

    ПримечаниеПримечание

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

    Дополнительные сведения см. в разделе EXECUTE AS, предложение (Transact-SQL).

< column_definition >::=

Определяет тип данных таблицы. Декларация таблицы включает определения столбцов и ограничений. Для функций CLR могут быть указаны только column_name и data_type.

  • column_name
    Имя столбца в таблице. Имена столбцов должны соответствовать требованиям для идентификаторов и быть уникальными для таблицы. Значение column_name может содержать от 1 до 128 символов.

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

  • DEFAULT constant_expression
    Указывает значение, присваиваемое столбцу, в том числе, когда при вставке его значение явно не указано. В качестве значения constant_expression может быть указана константа, NULL или значение системной функции. Определения DEFAULT могут применяться к любым столбцам, кроме тех, которые имеют свойство IDENTITY. Предложение DEFAULT не может быть указано для функций CLR, возвращающих табличное значение.

  • COLLATE collation_name
    Задает параметры сортировки для столбца. Если не указано, столбцу назначаются параметры сортировки, принятые в базе данных по умолчанию. Имя параметров сортировки может быть либо именем параметров сортировки Windows, либо именем параметров сортировки SQL. Дополнительные сведения см. в разделах Имя параметров сортировки Windows (Transact-SQL) и Имя параметров сортировки SQL Server (Transact-SQL).

    Предложение COLLATE может быть использовано для изменения параметров сортировки только для столбцов типов char, varchar, nchar и nvarchar.

    Предложение COLLATE не может быть указано для функций CLR, возвращающих табличное значение.

  • ROWGUIDCOL
    Указывает, что новый столбец является столбцом глобального уникального идентификатора строки. Только один столбец uniqueidentifier в таблице может быть назначен в качестве столбца ROWGUIDCOL. Свойство ROWGUIDCOL может быть присвоено только столбцу типа uniqueidentifier.

    Свойство ROWGUIDCOL не обеспечивает уникальности значений, хранимых в столбце. Кроме того, данное свойство не производит автоматическое формирование значений для новых строк, вставляемых в таблицу. Для создания уникальных значений произвольного столбца используйте функцию NEWID в инструкции INSERT. Может быть указано значение по умолчанию. При этом функция NEWID не может быть указана в качестве значения по умолчанию.

  • IDENTITY
    Указывает, что новый столбец является столбцом идентификаторов. При добавлении в таблицу новой строки SQL Server формирует для столбца уникальное, последовательное значение. Столбцы идентификаторов обычно используются с ограничением PRIMARY KEY для поддержания уникальности идентификаторов строк в таблице. Свойство IDENTITY присвоено столбцам типа tinyint, smallint, int, bigint, decimal(p,0) или numeric(p,0). Для каждой таблицы можно создать только один столбец идентификаторов. Ограниченные значения по умолчанию и ограничения DEFAULT не могут использоваться в столбце идентификаторов. Необходимо указывать либо оба параметра и (seed, и increment), либо ни тот, ни другой. Если ни одно из значений не указано, то действительно значение по умолчанию — (1,1).

    Предложение IDENTITY не может быть указано для функций CLR, возвращающих табличное значение.

    • seed
      Целочисленное значение, присваиваемое первой строке в таблице.

    • increment
      Целочисленное значение, добавляемое к значению seed для каждой последующей строки таблицы.

< column_constraint >::= and < table_constraint>::=

Определяет ограничение для указанного столбца или таблицы. Для функций CLR единственное допустимое ограничение — NULL. Именованные ограничения недопустимы.

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

  • PRIMARY KEY
    Ограничение, обеспечивающее целостность сущностей для указанного столбца через уникальный индекс. В возвращающих табличное значение пользовательских функциях ограничение PRIMARY KEY может быть создано только для одного столбца таблицы. Ограничение PRIMARY KEY не может быть указано для функций CLR, возвращающих табличное значение.

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

  • CLUSTERED | NONCLUSTERED
    Указывает, что для ограничения PRIMARY KEY или UNIQUE создается кластеризованный или некластеризованный индекс. Ограничения PRIMARY KEY используют параметр CLUSTERED, а ограничения UNIQUE используют параметр NONCLUSTERED.

    Параметр CLUSTERED может быть указан только для одного ограничения. Если параметр CLUSTERED указан для ограничения UNIQUE и указано ограничение PRIMARY KEY, то PRIMARY KEY использует NONCLUSTERED.

    Параметры СLUSTERED и NONСLUSTERED не могут быть указаны для функций CLR, возвращающих табличное значение.

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

    • logical_expression
      Логическое выражение, возвращающее значения TRUE или FALSE.

<computed_column_definition>::=

Указывает вычисляемый столбец. Дополнительные сведения о вычисляемых столбцах см. в разделе Инструкция CREATE TABLE (Transact-SQL).

  • column_name
    Имя вычисляемого столбца.

  • computed_column_expression
    Выражение, определяющее значение вычисляемого столбца.

<index_option>::=

Указывает параметры индекса для индекса PRIMARY KEY или UNIQUE. Дополнительные сведения о параметрах индекса см. в разделе CREATE INDEX (Transact-SQL).

  • PAD_INDEX = { ON | OFF }
    Определяет разреженность индекса. Значение по умолчанию — OFF.

  • FILLFACTOR = fillfactor
    Указывает значение в процентах, показывающее, насколько полным компонент Database Engine должен сделать конечный уровень каждой страницы индекса во время создания или замены индекса. Аргумент fillfactor должен быть целым числом от 1 до 100. Значение по умолчанию равно 0.

  • IGNORE_DUP_KEY = { ON | OFF }
    Определяет ответ на ошибку, случающуюся, когда операция вставки пытается вставить в уникальный индекс повторяющиеся значения ключа. Параметр IGNORE_DUP_KEY применяется только к операциям вставки, производимым после создания или перестроения индекса. Значение по умолчанию — OFF.

  • STATISTICS_NORECOMPUTE = { ON | OFF }
    Указывает, выполнялся ли перерасчет статистики распределения. Значение по умолчанию — OFF.

  • ALLOW_ROW_LOCKS = { ON | OFF }
    Указывает, разрешена ли блокировка строк. Значение по умолчанию — ON.

  • ALLOW_PAGE_LOCKS = { ON | OFF }
    Указывает, разрешена ли блокировка страниц. Параметр по умолчанию — ON.

Замечания

ALTER FUNCTION не может использоваться для преобразования скалярной функции в функцию, возвращающую табличное значение, или наоборот. Также ALTER FUNCTION не может использоваться для преобразования одной встроенной функции в функцию из нескольких инструкций или наоборот. ALTER FUNCTION не может использоваться для преобразования функции Transact-SQL в функцию среды CLR или наоборот.

Следующие инструкции компонента Service Broker не могут быть включены в определение Transact-SQL пользовательской функции:

  • BEGIN DIALOG CONVERSATION

  • END CONVERSATION

  • GET CONVERSATION GROUP

  • MOVE CONVERSATION

  • RECEIVE

  • SEND

Разрешения

Требует разрешения ALTER для функции или для схемы. Если функция задает определяемый пользователем тип данных, требуется разрешение EXECUTE для этого типа.