Программирование в Microsoft SQL Server 2000

Пользовательские функции

Разбить на страницы
Показывать лекцию целиком

Вы научитесь:

  • создавать скалярную функцию;
  • создавать подставляемую табличную функцию;
  • создавать много-операторную табличную функцию;
  • использовать функции в операторе Transact-SQL;
  • использовать функции в операторе CREATE TABLE.
    •

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

    Понятие о пользовательских функциях

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

    Функция является детерминированной, если при одном и том же заданном входном значении она всегда возвращает один и тот же результат. Так, встроенная функция DATEADD является детерминированной – добавление трех дней к дате 20 апреля 1958 г. всегда дает дату 23 апреля 1958 г.

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

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

    Недетерминированные функции не могут быть использованы для создания индексов или вычисляемых столбцов. Кластерные индексы не могут быть созданы для представления, если представление обращается к какой-либо недетерминированной функции (независимо от того, используется ли она в индексе, или нет).

    Скалярные функции

    Скалярные пользовательские функции возвращают скалярный (однозначный) результат, такой как строка или число. На типы данных, возвращаемых скалярной функцией, накладывается несколько ограничений. Запрещается использовать нескалярные типы, такие как курсоры и таблицы. Кроме того, скалярные функции не могут возвращать значения с типом timestamp, text, ntext или image, а также значения, имеющие тип данных, определенный пользователем, даже если базовый тип при этом является скаляром.

    Скалярные функции могут использоваться везде, где может использоваться возвращаемое функцией значение с соответствующим типом данных. Это может быть список столбцов в фразе WHERE оператора SELECT, выражение, условие ограничения в определении таблицы или даже описание типа данных в столбце таблицы.

    Табличные функции

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

    Так, таблица Oils в базе данных Aromatherapy является полностью нормализованной, поэтому более подробная информация, относящаяся к полям PlantParts и Cautions, хранится в других таблицах. Для среднего пользователя все это не имеет особого смысла, и большинству людей трудно будет воспринимать оператор SELECT, осуществляющий полную денормализацию строки в таблице Oils.

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

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

    SELECT * FROM GetOilDetails('Basil')

    Подобная возможность дает существенное преимущество, связанное с упрощением клиентского программного обеспечения.

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

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

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

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

    Второе ограничение состоит в том, что операторы в пользовательской функции не могут вызывать какие-либо недетерминированные функции (встроенные или пользовательские) или обращаться к недетерминированным глобальным переменным, таким как @@TOTAL_ERRORS, которая возвращает количество ошибок, обнаруженных SQL Server с момента запуска сервера.

    Создание скалярной функции

    Оператор CREATE для скалярных пользовательских функций имеет достаточно простой синтаксис для функций всех типов:

    CREATE FUNCTION имя_функции ([список_параметров])
RETURNS тип_данных
AS
BEGIN
	[операторы_tsql]
	RETURN (возвращаемое_значение)
END

    Имя_функции должно удовлетворять правилам, действующим для идентификаторов. Хотя список_параметров в описании оператора CREATE занимает иное место, чем список параметров для хранимой процедуры, они имеют аналогичный синтаксис:

    @имя_параметра тип_данных [= значение_по_умолчанию]

    Имя_параметра должно соответствовать правилам, принятым для идентификаторов, и начинаться с символа @. Пользовательские функции могут иметь до 1024 входных параметров. Выходные параметры пользовательские функции не поддерживают – единственным значением, возвращаемым функцией, является результат ее выполнения. Заметим, что список_параметров является необязательным, но наличие скобок обязательно.

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

    Операторы BEGIN...END, которыми ограничиваются операторы_tsql, составляющие тело функции, являются обязательными, даже если тело функции состоит из одного оператора RETURN.

    Создайте скалярную функцию

  • Нажмите кнопку New Query (Новый запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer откроет новое окно Query (Запрос).
  • Нажмите кнопку Load Script (Загрузить сценарий)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла запроса).
  • Перейдите к папке SQL 2000 Step by Step в корневой директории, выделите сценарий scalarFunction и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.
  • Нажмите кнопку Execute Query (Выполнить запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer создаст пользовательскую функцию
  • Раскройте папку Functions базы данных Aromatherapy в панели Object Browser и нажмите клавишу F5, чтобы обновить содержимое окна. В списке в панели Object Browser будет отображена функция dbo.scalarFunction.
    •

    Примечание. Созданные здесь пользовательские функции мы используем в следующем разделе.

    Создание табличных функций

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

    Синтаксис для подставляемой табличной функции является усеченной разновидностью оператора CREATE FUNCTION. Блок BEGIN...END отсутствует, и нет никаких других операторов, кроме RETURN:

    CREATE FUNCTION имя_функции (список_параметров)
RETURNS таблица
AS
RETURN (оператор_выборки)

    Создайте подставляемую табличную функцию

  • Перейдите к окну запроса, содержащему сценарий scalarFunction.
  • Нажмите кнопку Load Script (Загрузить сценарий)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла запроса).
  • Выделите сценарий inlineFunction и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.
  • Нажмите кнопку Execute Query (Выполнить запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer создаст пользовательскую функцию
  • Выделите папку Functions в панели Object Browser и нажмите клавишу F5, чтобы обновить содержимое окна. Query Analyzer отобразит в списке функцию dbo.inlineFunction.
    •

    Синтаксис оператора CREATE FUNCTION для многооператорной табличной функции сочетает элементы синтаксиса для скалярной и для подставляемой функций:

    CREATE FUNCTION имя_функции (список_параметров)
RETURNS @локальная_табличная_переменная TABLE
	(определение_таблицы)
AS
BEGIN
	операторы_tsql
	RETURN
END

    Подобно скалярным функциям, в многооператорной табличной функции команды Transact-SQL располагаются внутри блока BEGIN...END. Поскольку блок может содержать несколько операторов SELECT, в фразе RETURNS вы должны явно определить таблицу, которая будет возвращаться.

    Поскольку оператор RETURN в многооператорной табличной функции всегда возвращает таблицу, заданную во фразе RETURNS, он должен выполняться без аргументов, – например, RETURN, а не RETURN @myTable.

    Создайте многооператорную табличную функцию

  • Перейдите к окну Query (Запрос), содержащему сценарий inlineFunction.
  • Нажмите кнопку Load Script (Загрузить сценарий)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла запроса).
  • Выделите сценарий tableFunction и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.
  • Нажмите кнопку Execute Query (Выполнить запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer создаст пользовательскую функцию.
  • Выделите папку Functions в панели Object Browser и нажмите клавишу F5, чтобы обновить содержимое окна. Query Analyzer отобразит в списке функцию dbo.tableFunction.
    •

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

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

    имя_владельца.имя_функции([список_параметров])

    Имя_владельца для скалярной функции является обязательным. Вы не можете использовать синтаксис с именованными параметрами (например, @имя_параметра = значение), а также не можете не указывать (опускать) параметры, но вы можете применять ключевое слово DEFAULT для указания значения по умолчанию, как вы это делаете для хранимых процедур.

    Совет. SQL Server предоставляет несколько встроенных пользовательских функций, не относящихся к обычным встроенным функциям. Они начинаются с fn_, а при их вызове используется специальный синтаксис ::имя_функции([список_параметров] ). Символы :: заменяют имя_владельца и указывают на то, что функция является встроенной пользовательской функцией.

    Для скалярной функции вы также можете использовать оператор EXECUTE:

    EXECUTE @возвращаемое_значение = имя_функции(список_параметров)

    Если вы используете оператор EXECUTE для пользовательской функции, вам не нужно указывать имя_владельца. В этом синтаксисе вы можете использовать именованные параметры:

    EXECUTE @возвращаемое_значение = имя_функции @параметр = значение
[, @параметр = значение [,...]]

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

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

    имя_функции([список_параметров])

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

    Применение пользовательских функций в операторах Transact-SQL

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

    Примечание. Если фраза SELECT находится внутри оператора DECLARE CURSOR, курсор должен иметь тип STATIC и READ_ONLY.

    Используйте скалярную функцию в операторе PRINT

  • Нажмите кнопку New Query (Новый запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer откроет новое окно Query (Запрос).
  • Введите следующий оператор SQL в окне Query (Запрос):
    PRINT dbo.scalarFunction('German Chamomile')
  • Нажмите кнопку Execute Query (Выполнить запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer выполнит оператор и отобразит результат.
    •

    Используйте скалярную функцию в операторе SELECT

  • Выберите вкладку Editor (Редактор) в окне Query (Запрос) и нажмите кнопку Clear Window (Очистить окно)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer очистит окно Query (Запрос).
  • Введите следующий оператор SQL в окне Query (Запрос):
    SELECT OilID, dbo.scalarFunction(OilName)
FROM Oils
  • Нажмите кнопку Execute Query (Выполнить запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer выполнит оператор и отобразит результат.
    •

    Используйте табличную функцию в операторе SELECT

  • Выберите вкладку Editor (Редактор) в окне Query (Запрос) и нажмите кнопку Clear Window (Очистить окно)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer очистит окно Query (Запрос).
  • Введите следующий оператор SQL в окне Query (Запрос):
    SELECT * FROM tableFunction()
  • Нажмите кнопку Execute Query (Выполнить запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer выполнит оператор и отобразит результат.
  • Закройте окно Query (Запрос), отказавшись от сохранения изменений при появлении окна-запроса.
    •

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

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

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

    Примените пользовательскую функцию в вычисляемом столбце

  • Перейдите к окну Query (Запрос), содержащему сценарий tableFunction.
  • Нажмите кнопку Load Script (Загрузить сценарий)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла запроса).
  • Выделите сценарий computedColumn и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.
  • Нажмите кнопку Execute Query (Выполнить запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer создаст функцию и таблицу.
  • Нажмите кнопку New Query (Новый запрос)в окне анализатора запросов Query Analyzer. Query Analyzer откроет новое окно Query (Запрос).
  • Нажмите кнопку Load Script (Загрузить сценарий)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла запроса).
  • Выделите сценарий testColumn и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.
  • Нажмите кнопку Execute Query (Выполнить запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer выполнит запрос и отобразит результат.
    •

    Примените пользовательскую функцию в определении DEFAULT

  • Перейдите к окну Query (Запрос), содержащему сценарий computedColumn.
  • Нажмите кнопку Load Script (Загрузить сценарий)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла запроса).
  • Выделите сценарий defaultColumn и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.
  • Нажмите кнопку Execute Query (Выполнить запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer создаст таблицу.
  • Перейдите к окну, содержащему функцию testColumn.
  • Нажмите кнопку Execute Query (Выполнить запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer выполнит запрос и отобразит результат.
    •

    Краткое содержание

    Чтобы ... Синтаксис оператора SQL
    Создать скалярную функцию 
    CREATE FUNCTION имя_функции([список_параметров])
RETURNS тип_данных               
AS                                           
BEGIN                    
[операторы_tsql]                    
RETURN (возвращаемое_значение)          
END
    Создать подставляемую табличную функцию 
    CREATE FUNCTION имя_функции(список_параметров)      
RETURNS таблица                      
AS                                       
RETURN (оператор_выборки)
    Создать многооператорную табличную функцию 
    CREATE FUNCTION имя_функции (список_параметров)
RETURNS @локальная_табличная_переменная TABLE (определение_таблицы)                 
AS                                              
BEGIN                        
операторы_tsql                    
RETURN                                        
END
    Использовать скалярную функцию имя_владельца.имя_функции([список_параметров])
    Использовать функцию в операторе EXECUTE 
    EXECUTE @возвращаемое_значение = имя_функции(список_параметров)                       
или                                                                 
EXECUTE @возвращаемое_значение = имя_функции                                               
@параметр = значение[, @параметр = значение[,...]]
    Использовать функцию в качестве вычисляемого столбца 
    имя_столбца AS функция                                                         
Параметры должны быть либо другими столбцами, либо константами
    Использовать функцию в качестве столбца умолчания DEFAULT 
    Имя_столбца тип_данных = функция                                    
Параметры должны быть константами
    Страницы:

    Вы научитесь:

  • создавать скалярную функцию;
  • создавать подставляемую табличную функцию;
  • создавать много-операторную табличную функцию;
  • использовать функции в операторе Transact-SQL;
  • использовать функции в операторе CREATE TABLE.
    •

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

    Понятие о пользовательских функциях

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

    Функция является детерминированной, если при одном и том же заданном входном значении она всегда возвращает один и тот же результат. Так, встроенная функция DATEADD является детерминированной – добавление трех дней к дате 20 апреля 1958 г. всегда дает дату 23 апреля 1958 г.

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

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

    Недетерминированные функции не могут быть использованы для создания индексов или вычисляемых столбцов. Кластерные индексы не могут быть созданы для представления, если представление обращается к какой-либо недетерминированной функции (независимо от того, используется ли она в индексе, или нет).

    Скалярные функции

    Скалярные пользовательские функции возвращают скалярный (однозначный) результат, такой как строка или число. На типы данных, возвращаемых скалярной функцией, накладывается несколько ограничений. Запрещается использовать нескалярные типы, такие как курсоры и таблицы. Кроме того, скалярные функции не могут возвращать значения с типом timestamp, text, ntext или image, а также значения, имеющие тип данных, определенный пользователем, даже если базовый тип при этом является скаляром.

    Скалярные функции могут использоваться везде, где может использоваться возвращаемое функцией значение с соответствующим типом данных. Это может быть список столбцов в фразе WHERE оператора SELECT, выражение, условие ограничения в определении таблицы или даже описание типа данных в столбце таблицы.

    Табличные функции

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

    Так, таблица Oils в базе данных Aromatherapy является полностью нормализованной, поэтому более подробная информация, относящаяся к полям PlantParts и Cautions, хранится в других таблицах. Для среднего пользователя все это не имеет особого смысла, и большинству людей трудно будет воспринимать оператор SELECT, осуществляющий полную денормализацию строки в таблице Oils.

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

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

    SELECT * FROM GetOilDetails('Basil')

    Подобная возможность дает существенное преимущество, связанное с упрощением клиентского программного обеспечения.

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

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

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

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

    Второе ограничение состоит в том, что операторы в пользовательской функции не могут вызывать какие-либо недетерминированные функции (встроенные или пользовательские) или обращаться к недетерминированным глобальным переменным, таким как @@TOTAL_ERRORS, которая возвращает количество ошибок, обнаруженных SQL Server с момента запуска сервера.

    Создание скалярной функции

    Оператор CREATE для скалярных пользовательских функций имеет достаточно простой синтаксис для функций всех типов:

    CREATE FUNCTION имя_функции ([список_параметров])
RETURNS тип_данных
AS
BEGIN
	[операторы_tsql]
	RETURN (возвращаемое_значение)
END

    Имя_функции должно удовлетворять правилам, действующим для идентификаторов. Хотя список_параметров в описании оператора CREATE занимает иное место, чем список параметров для хранимой процедуры, они имеют аналогичный синтаксис:

    @имя_параметра тип_данных [= значение_по_умолчанию]

    Имя_параметра должно соответствовать правилам, принятым для идентификаторов, и начинаться с символа @. Пользовательские функции могут иметь до 1024 входных параметров. Выходные параметры пользовательские функции не поддерживают – единственным значением, возвращаемым функцией, является результат ее выполнения. Заметим, что список_параметров является необязательным, но наличие скобок обязательно.

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

    Операторы BEGIN...END, которыми ограничиваются операторы_tsql, составляющие тело функции, являются обязательными, даже если тело функции состоит из одного оператора RETURN.

    Создайте скалярную функцию

  • Нажмите кнопку New Query (Новый запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer откроет новое окно Query (Запрос).
  • Нажмите кнопку Load Script (Загрузить сценарий)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла запроса).
  • Перейдите к папке SQL 2000 Step by Step в корневой директории, выделите сценарий scalarFunction и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.
  • Нажмите кнопку Execute Query (Выполнить запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer создаст пользовательскую функцию
  • Раскройте папку Functions базы данных Aromatherapy в панели Object Browser и нажмите клавишу F5, чтобы обновить содержимое окна. В списке в панели Object Browser будет отображена функция dbo.scalarFunction.
    •

    Примечание. Созданные здесь пользовательские функции мы используем в следующем разделе.

    Создание табличных функций

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

    Синтаксис для подставляемой табличной функции является усеченной разновидностью оператора CREATE FUNCTION. Блок BEGIN...END отсутствует, и нет никаких других операторов, кроме RETURN:

    CREATE FUNCTION имя_функции (список_параметров)
RETURNS таблица
AS
RETURN (оператор_выборки)

    Создайте подставляемую табличную функцию

  • Перейдите к окну запроса, содержащему сценарий scalarFunction.
  • Нажмите кнопку Load Script (Загрузить сценарий)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла запроса).
  • Выделите сценарий inlineFunction и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.
  • Нажмите кнопку Execute Query (Выполнить запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer создаст пользовательскую функцию
  • Выделите папку Functions в панели Object Browser и нажмите клавишу F5, чтобы обновить содержимое окна. Query Analyzer отобразит в списке функцию dbo.inlineFunction.
    •

    Синтаксис оператора CREATE FUNCTION для многооператорной табличной функции сочетает элементы синтаксиса для скалярной и для подставляемой функций:

    CREATE FUNCTION имя_функции (список_параметров)
RETURNS @локальная_табличная_переменная TABLE
	(определение_таблицы)
AS
BEGIN
	операторы_tsql
	RETURN
END

    Подобно скалярным функциям, в многооператорной табличной функции команды Transact-SQL располагаются внутри блока BEGIN...END. Поскольку блок может содержать несколько операторов SELECT, в фразе RETURNS вы должны явно определить таблицу, которая будет возвращаться.

    Поскольку оператор RETURN в многооператорной табличной функции всегда возвращает таблицу, заданную во фразе RETURNS, он должен выполняться без аргументов, – например, RETURN, а не RETURN @myTable.

    Создайте многооператорную табличную функцию

  • Перейдите к окну Query (Запрос), содержащему сценарий inlineFunction.
  • Нажмите кнопку Load Script (Загрузить сценарий)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла запроса).
  • Выделите сценарий tableFunction и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.
  • Нажмите кнопку Execute Query (Выполнить запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer создаст пользовательскую функцию.
  • Выделите папку Functions в панели Object Browser и нажмите клавишу F5, чтобы обновить содержимое окна. Query Analyzer отобразит в списке функцию dbo.tableFunction.
    •

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

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

    имя_владельца.имя_функции([список_параметров])

    Имя_владельца для скалярной функции является обязательным. Вы не можете использовать синтаксис с именованными параметрами (например, @имя_параметра = значение), а также не можете не указывать (опускать) параметры, но вы можете применять ключевое слово DEFAULT для указания значения по умолчанию, как вы это делаете для хранимых процедур.

    Совет. SQL Server предоставляет несколько встроенных пользовательских функций, не относящихся к обычным встроенным функциям. Они начинаются с fn_, а при их вызове используется специальный синтаксис ::имя_функции([список_параметров] ). Символы :: заменяют имя_владельца и указывают на то, что функция является встроенной пользовательской функцией.

    Для скалярной функции вы также можете использовать оператор EXECUTE:

    EXECUTE @возвращаемое_значение = имя_функции(список_параметров)

    Если вы используете оператор EXECUTE для пользовательской функции, вам не нужно указывать имя_владельца. В этом синтаксисе вы можете использовать именованные параметры:

    EXECUTE @возвращаемое_значение = имя_функции @параметр = значение
[, @параметр = значение [,...]]

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

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

    имя_функции([список_параметров])

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

    Применение пользовательских функций в операторах Transact-SQL

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

    Примечание. Если фраза SELECT находится внутри оператора DECLARE CURSOR, курсор должен иметь тип STATIC и READ_ONLY.

    Используйте скалярную функцию в операторе PRINT

  • Нажмите кнопку New Query (Новый запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer откроет новое окно Query (Запрос).
  • Введите следующий оператор SQL в окне Query (Запрос):
    PRINT dbo.scalarFunction('German Chamomile')
  • Нажмите кнопку Execute Query (Выполнить запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer выполнит оператор и отобразит результат.
    •

    Используйте скалярную функцию в операторе SELECT

  • Выберите вкладку Editor (Редактор) в окне Query (Запрос) и нажмите кнопку Clear Window (Очистить окно)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer очистит окно Query (Запрос).
  • Введите следующий оператор SQL в окне Query (Запрос):
    SELECT OilID, dbo.scalarFunction(OilName)
FROM Oils
  • Нажмите кнопку Execute Query (Выполнить запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer выполнит оператор и отобразит результат.
    •

    Используйте табличную функцию в операторе SELECT

  • Выберите вкладку Editor (Редактор) в окне Query (Запрос) и нажмите кнопку Clear Window (Очистить окно)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer очистит окно Query (Запрос).
  • Введите следующий оператор SQL в окне Query (Запрос):
    SELECT * FROM tableFunction()
  • Нажмите кнопку Execute Query (Выполнить запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer выполнит оператор и отобразит результат.
  • Закройте окно Query (Запрос), отказавшись от сохранения изменений при появлении окна-запроса.
    •

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

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

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

    Примените пользовательскую функцию в вычисляемом столбце

  • Перейдите к окну Query (Запрос), содержащему сценарий tableFunction.
  • Нажмите кнопку Load Script (Загрузить сценарий)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла запроса).
  • Выделите сценарий computedColumn и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.
  • Нажмите кнопку Execute Query (Выполнить запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer создаст функцию и таблицу.
  • Нажмите кнопку New Query (Новый запрос)в окне анализатора запросов Query Analyzer. Query Analyzer откроет новое окно Query (Запрос).
  • Нажмите кнопку Load Script (Загрузить сценарий)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла запроса).
  • Выделите сценарий testColumn и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.
  • Нажмите кнопку Execute Query (Выполнить запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer выполнит запрос и отобразит результат.
    •

    Примените пользовательскую функцию в определении DEFAULT

  • Перейдите к окну Query (Запрос), содержащему сценарий computedColumn.
  • Нажмите кнопку Load Script (Загрузить сценарий)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла запроса).
  • Выделите сценарий defaultColumn и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.
  • Нажмите кнопку Execute Query (Выполнить запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer создаст таблицу.
  • Перейдите к окну, содержащему функцию testColumn.
  • Нажмите кнопку Execute Query (Выполнить запрос)в панели инструментов анализатора запросов Query Analyzer. Query Analyzer выполнит запрос и отобразит результат.
    •

    Краткое содержание

    Чтобы ... Синтаксис оператора SQL
    Создать скалярную функцию 
    CREATE FUNCTION имя_функции([список_параметров])
RETURNS тип_данных               
AS                                           
BEGIN                    
[операторы_tsql]                    
RETURN (возвращаемое_значение)          
END
    Создать подставляемую табличную функцию 
    CREATE FUNCTION имя_функции(список_параметров)      
RETURNS таблица                      
AS                                       
RETURN (оператор_выборки)
    Создать многооператорную табличную функцию 
    CREATE FUNCTION имя_функции (список_параметров)
RETURNS @локальная_табличная_переменная TABLE (определение_таблицы)                 
AS                                              
BEGIN                        
операторы_tsql                    
RETURN                                        
END
    Использовать скалярную функцию имя_владельца.имя_функции([список_параметров])
    Использовать функцию в операторе EXECUTE 
    EXECUTE @возвращаемое_значение = имя_функции(список_параметров)                       
или                                                                 
EXECUTE @возвращаемое_значение = имя_функции                                               
@параметр = значение[, @параметр = значение[,...]]
    Использовать функцию в качестве вычисляемого столбца 
    имя_столбца AS функция                                                         
Параметры должны быть либо другими столбцами, либо константами
    Использовать функцию в качестве столбца умолчания DEFAULT 
    Имя_столбца тип_данных = функция                                    
Параметры должны быть константами
    Предыдущее занятие Следующее занятие
    Вернуться к учебному плану
    ^