Books and articles about SQL Rambler's Top100 Сменить язык на: Русский 29 March 2024 08:24:12


www.sql-ex.ru
Skip Navigation Links  

 

Print  Версия для печати

На главную страницу

Microsoft SQL Server 2000. Определяемые пользователем функции (UDF).

Microsoft Corp. (оригинал: Microsoft SQL Server 2000 User Defined Functions Whitepaper)
Сокращенный перевод Моисеенко С.И.

Часть 1   Часть 2    Часть 3   Часть 4  

Типы функций

SQL Server 2000 поддерживает три типа UDF:

  • Скалярнозначные функции
  • Однострочные (inline) табличнозначные функции
  • Многооператорные табличнозначные фунции

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

Пользовательская скалярная функция возвращает в качестве ответа единственное значение при каждом вызове функции. Функции являются скалярнозначными, если предложение RETURNS определяет один из скалярных типов данных. Скалярнозначные функции могут быть определены с помощью нескольких операторов Transact-SQL. Например:

CREATE FUNCTION CubicVolume
(@CubeLength decimal (4,1), @CubeWidth decimal (4,1), @CubeHeight decimal (4,1))
RETURNS decimal (12,3) -- тип данных возвращаемого значения.
AS
BEGIN
     RETURN (@CubeLength * @CubeWidth * @CubeHeight)
END

В данном примере предложение RETURNS определяет, что функция будет возвращать скалярное значение типа decimal. Предложение RETURNS может использовать любой из скалярных типов данных, которые поддерживает SQL Server, за исключением timestamp, text, ntext или image.

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

Вы должны вызывать скалярнозначную функцию либо при помощи двойного имени в форме ИмяВладельца.ИмяФункции или тройного имени в форме ИмяБД. ИмяВладельца.ИмяФункции. Вы не можете вызывать скалярнозначную функцию при помощи простого (неуточненного) имени, чтобы отличать вызовы системных функций и UDF. Приведенную выше функцию можно вызвать следующим образом:

Select dbo.CubicVolume(12.2,10.6,10.0)

Однострочные табличнозначные функции

Для однострочных табличнозначных функций предложение RETURNS задает TABLE без указания списка столбцов. Однострочные функции представляют собой табличнозначные функции, которые определяются единственным оператором SELECT, представляющим тело функции. Столбцы, включающие типы данных таблицы, возвращаемой функцией, определяются списком SELECT оператора SELECT, который определяет функцию. Например:

CREATE FUNCTION fn_CustomerNamesInRegion
      (@RegionParameter nvarchar (30))
RETURNS TABLE
AS
RETURN 
     (
     SELECT CustomerID, CompanyName 
         FROM Northwind.dbo.Customers 
         WHERE Region = @RegionParameter 
     )

Предложение RETURNS в примере просто указывает TABLE без списка столбцов. Также единственный оператор RETURN, который содержит оператор SELECT, определяет тело функции.

Ниже приведен пример вызова рассмотренной выше однострочной табличнозначной функции:

SELECT * FROM fn_CustomerNamesInRegion (N'WA')

Многооператорные табличнозначные функции

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

CREATE FUNCTION LargeOrderShippers 
    (@FreightParm money)
RETURNS @OrderShipperTab TABLE 
    ( 
    ShipperID int, 
    ShipperName nvarchar (80), 
    OrderID int, 
    ShippedDate datetime, 
    Freight money 
    )
AS
BEGIN 
    INSERT @OrderShipperTab 
        SELECT S.ShipperID, S.CompanyName, 
            O.OrderID, O.ShippedDate, O.Freight 
        FROM Shippers AS S INNER JOIN Orders AS O 
            ON S.ShipperID = O.ShipVia 
        WHERE O.Freight > @FreightParm 
    RETURN
END

Предложение RETURNS в данном примере определяет локальную возвращаемую переменную типа table с именем @OrderShipperTab, а также определяет структуру таблицы с определениями столбцов.

Операторы в теле функции вставляют строки в переменную @OrderShipperTab, создавая табличный набор строк, который возвращается функцией. Заметим, что оператор RETURN не имеет аргумента. Значение возвращаемой переменной функции возвращается как значение функции.

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

SELECT *
FROM LargeOrderShippers ($500)

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

Правила вызова функций пользователя

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

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

Вычисляемые столбцы

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

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

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

Вы можете использовать данный запрос для получения списка сотрудников, которые получили премию больше $1000.00:

Select EmpId, Fname, Lname from Employees where dbo.BONUS(Salary,Comm) > 1000

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

Если вычисляемый столбец создается с помощью пользовательской функции, которая рассчитывает премию (BONUS)

CREATE TABLE [Employees] 
       (
       [EmpId] [Char] (9) NOT NULL, 
       [Salary] [Decimal](10,2),
       [Comm] [Decimal](10,2), 
       BONUS as dbo.BONUS(Salary,Comm)
       )

и вы создаете индекс на вычисляемом столбце BONUS

CREATE NONCLUSTERED INDEX Nc_Idx3 ON Employees (BONUS)

то для следующего запроса оптимизатор выполнит индексный поиск (Index Seek):

Select EmpId, Fname, Lname from Employees where BONUS > 1000

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

Запросы

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

Список Select

Наиболее частое использование UDF находит в вызовах в предложении select оператора SELECT.

Скалярнозначная функция MyDateFormat возвращает строковое представление значения datetime в пользовательском формате, который не поддерживается SQL Server. Входное значение datetime и символ-разделитель используются в качестве параметров. Например:

use pubs
go
create function MyDateFormat(@indate datetime, @Separator char(1)=’-‘)
returns nchar(20)
as
begin 
    return 
    convert(nvarchar(20),
    datepart(dd, @indate))
    + @Separator
    + convert(nvarchar(20), 
    datepart(mm, @indate))
    + @Separator
    + convert(nvarchar(20),
    datepart(yy, @indate))
end
go

-- Выборка
select ord_num, dbo.MyDateFormat(ord_date, ‘:’)
from sales
go

Другим хорошим примером использования UDF в списке выбора оператора SELECT является Web-сайт, обеспечивающий персонализацию пользователя. Web-сайт отображает фондовые квоты, счет бейсбольных матчей и десятку новостейдня, а также обеспечивает пользователя локализованным прогнозом погоды. Лучшим способом получить информацию о погоде является написание функции, которая возвращает высшую и низшую температуры на основе вводимого пользователем zip-кода места или города проживания. Например:

Select dbo.weather (Zip) from Address

Эта функция использует информацию zip-кода или города и адресует запрос к БД на ближайшей метеостанции. Затем функция возвращает данные типа varchar, которые содержат строку с разделенными запятыми высшую и низшую температуры для каждого дня и характеристику погодных условий.

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

Select PROPER (name), address, city, state, zip from customers

Как отмечалось выше, UDF в списке SELECT (или в предложении WHERE) выполняются один раз для каждой строки табличного выражения в предложении FROM. Планы выполнения скалярнозначной функции кешируются, поэтому не будет перекомпиляции, связанной к каждым вызовом UDF в запросе. Однако имеются потери производительности, связанные с вызовом кешированного плана, установкой параметров и возвратом результирующего значения. Из-за этих потерь, если тело UDF содержит единственное скалярное выражение, запрос, вызывающий UDF, выполняется медленнее, чем запрос, в котором записано это выражение. Например:

create function multiply (int a, int b)
Returns int as
Begin return a*b
End
Select multiply (T.a, T.b) from T,
-- will be slower than
Select a*b from T

Если запрос включает большое число строк и если вычисление ограничивается единственным выражением, рассмотрите вариант отказаться от использования UDF; вместо этого используйте встроенное в запрос выражение. Однако если вычисление не может быть выражено в виде единственного скалярного выражения, пишите запрос с использованием UDF.

Часть 1   Часть 2    Часть 3   Часть 4  

На главную страницу

Print  Версия для печати


Usage of any materials of this site is possible
only under condition of mandatory allocation of the direct link to a site
http://www.sqlbooks.ru
on each page where used materials are placed.

 Main   Articles    Books 
Рейтинг@Mail.ru Rambler's Top100 Alt Упражнения по SQL: обучение, тестирование, сертификация по языку SQL Copyright c 2002-2006. All rights reserved.