|
Версия для печати
На главную страницу
Microsoft SQL Server 2000. Определяемые пользователем функции (UDF).Microsoft
Corp. (оригинал: Microsoft
SQL Server 2000 User Defined Functions Whitepaper) Сокращенный
перевод Моисеенко С.И.
Часть 1
Часть 2
Часть 3 Часть
4
Когда имеет смысл переписать существующую хранимую процедуру или представление как
табличнозначную или скалярнозначную функцию
Если хранимая процедура удовлетворяет следующим критериям, она является хорошим
кандидатом на то, чтобы переписать ее в виде табличнозначной функции:
-
Логика выражается единственным оператором SELECT, однако хранимая процедура
только потому используется вместо представления, что необходимы параметры. Вот
как можно поступить.
Рассмотрим представление:
CREATE VIEW vw_CustomerNamesInWA AS
SELECT CustomerID, CompanyName
FROM Northwind.dbo.Customers
WHERE Region = 'WA'
Вы можете создать более общую версию, vw_CustomerNamesInRegion, заменив “WHERE Region = 'WA' “ на
“WHERE Region = @RegionParameter”, позволяя пользователю указать интересующую
его зону. Однако представления не поддерживают параметры в условиях отбора,
которые указываются в предложении WHERE. Вы должны создать хранимую процедуру,
чтобы обеспечить возможность параметризации. Например:
Create proc CustomerNames
( @RegionParameter nvarchar(30) )
AS
Begin
SELECT CustomerID, CompanyName
FROM Northwind.dbo.Customers
WHERE Region = @RegionParameter
GO
Вы можете обработать этот сценарий с помощью однострочной табличнозначной
функции, обеспечив параметры в условиях отбора предложения WHERE. Более того,
строковая функция позволит отфильтровать результирующий набор. Ниже приведен
пример функции, которая позволяет пользователю указать зону в операторе SELECT:
CREATE FUNCTION fn_CustomerNamesInRegion
( @RegionParameter nvarchar(30) )
RETURNS table
AS
RETURN (
SELECT CustomerID, CompanyName
FROM Northwind.dbo.Customers
WHERE Region= @RegionParameter
)
GO
-- Пример вызова функции для заданного региона
SELECT * FROM fn_CustomerNamesInRegion(N'WA')
Where CustomerId = 'pooja'
GO
-
Хранимая процедура не выполняет операций обновления, за исключением табличных
переменных.
-
Хранимая процедура не использует динамических операторов EXECUTE. • Хранимая
процедура возвращает один результирующий набор.
-
Если основной целью хранимой процедуры является построение промежуточного
результата, который должен быть загружен во временную таблицу, к которой
адресуется запрос в операторе SELECT, соединенный с другой таблицей. Например,
рассмотрим следующую последовательность:
INSERT #temp EXEC sp_getresults
SELECT ...FROM #temp, t1 WHERE
...
Хранимая процедура sp_getresults может быть переписана как
табличнозначная функция, и вы можете переписать операторы INSERT...EXEC с
помощью табличнозначных функций. Например, функции fn_results.
Кроме того, вы можете переписать предыдущий оператор так:
SELECT ...
FROM fn_results(), t1 WHERE ...
-
Если хранимая процедура выполняет некоторую процедурную логику (например,
цикл), котораянакапливает результатперед возвращением, она является подходящим
кандидатом для оформления в виде скалярнозначной функции.
Пример вычисления факториала. Функция факториала требует циклического
вычисления значений и, следовательно, не может быть записана в виде одного
скалярного выражения. Однако эта функция может быть переписана в виде
скалярнозначной функции, чтобы получить выгоду в виде вызова функции в строке.
Например:
create function factorial (@n int)
returns bigint
begin
declare @fact bigint, @count int
set @fact = 1
set @count = 2
while @count < = @n
begin
set @fact = @fact*@count
set @count = @count+1
end
return @fact
end
-
Если хранимая процедура работает только с набором скалярных входных значений,
не обращается к таблицам базы данных (за исключением табличных переменных) и
возвращает единственное выходное значение посредством выходного параметра.
Следующий пример представляет собой рекурсию, которая может быть записана как
скалярная функция, вызываемая рекурсивно. Например:
create function factRec(@n int)
Returns int
Begin
if @n = 0
return 1
return (@n * dbo.factRec(@n-1))
End
select dbo.factRec (10)
Вы можете также использовать однострочные (inline) функции для использования
преимущества индексных представлений. Индексное представление не может
использовать параметры в условиях отбора предложения WHERE, чтобы привязать
хранимый результирующий набор к конкретному пользователю. Однако вы можете
определить индексное представление, которое сохраняет вычисленный набор данных,
отвечающих представлению, а затем определить строчную функцию поверх индексного
представления, содержащую параметризованные условия отбора, позволяющие вернуть
пользователю его результаты. Если определение представления сложное, основная
часть работы, которую оно производит для получения результирующего набора,
составляют операции построения агрегатных значений или соединения нескольких
таблиц в процессе создания кластерного индекса на представлении. Если затем вы
создаете строчную функцию, которая ссылается на представление. Она может
применять параметризованные фильтры пользователя для извлечения конкретных
строк из результирующего набора, который был построен с помощью оператора
CREATE INDEX. Сложные агрегаты или соединения выполняются один раз во время
создания индекса, после чего последовательные запросы, использующие сточную
функцию, фильтруют строки из простого результирующего набора. Например:
-
Определить представление vw_QuarterlySales, которое
помещает агрегаты всех данных по продажам в результирующий набор, в котором
данные суммируются поквартально для всех торговых точек.
-
Создать кластерный индекс на vw_QuarterlySales
для материализации результирующего набора, который содержит суммарные данные.
-
Создать строчную функцию для фильтрации суммарных данных:
CREATE FUNCTION fn_QuarterlySalesByStore
(
@StoreID int
)
RETURNS table
AS RETURN (
SELECT *
FROM SalesDB.dbo.vw_QuarterlySales
WHERE StoreID = @StoreID
)
-
Пользователи, которые затем получают данные для конкретного магазина, применяют
строчную функцию:
SELECT *
FROM fn_QuarterlySalesByStore(14432)
Большая часть работы, которая необходима для выполнения запросов на шаге 4,
заключается в агрегации продаж по кварталам. Эта работа выполняется однажды на
шаге 2. Каждый отдельный оператор SELECT на шаге 4 использует функцию fn_QuarterlySalesByStore
для фильтрации агрегированных данных, которые специфичны для магазина
пользователя.
Как получить информацию о функциях
Если оператор CREATE FUNCTION выполняется успешно, имя функции сохраняется в
системной таблице sysobjects с типом FN для скалярнозначной и
TF – для табличнозначной функции.
Текст оператора CREATE FUNCTION сохраняется в системной таблице syscomments.
Если функция выполняется в первый раз, она компилируется.
Некоторые объекты каталога, содержат информацию о UDF:
-
Хранимая процедура sp_help
содержит информацию о UDF.
-
Хранимая процедура sp_helptext
содержит информацию об источнике UDF.
-
Функция OBJECT_PROPERTY. Функция OBJECT_PROPERTY поддерживает
следующие новые свойства.
Property
|
Description
|
IsScalarFunction
|
1 – Если функция
скалярнозначная. 0 – Если
функция табличнозначная. NULL – Если не функция.
|
IsTableFunction
|
1 - Если функция табличнозначная.
0 - Если функция скалярнозначная.
NULL - Если не функция
|
IsDeterministic
|
1 - Если детерминированная.
0 - Если недетерминированная.
NULL - Если не функция.
|
IsInlineFunction |
1 – Если однострочная.
0 – Если не однострочная.
NULL – Если не функция.
|
IsSchemaBound |
1 – Если привязана к схеме.
0 – Если не привязана схеме.
NULL – Если не функция или представление. |
Представления информационной схемы содержат информацию о UDF:
-
ROUTINES
-
PARAMETERS
-
ROUTINE_COLUMNS
Эти представления информационной схемы также содержат информацию о хранимых
процедурах:
Select * from INFORMATION_SCHEMA.routines where ROUTINE_NAME like
'myfun%'
ROUTINES
Это представление возвращает информацию, как о хранимых процедурах, так и о
функциях.
Column name
|
Data type
|
Description
|
SPECIFIC_CATALOG
|
nvarchar(128)
|
Конкретное имя каталога .
Для SQL Server 2000 это имя то же самое, что и OUTINE _ CATALOG .
|
SPECIFIC_SCHEMA
|
nvarchar(128)
|
Конкретное имя каталога .
Для SQL Server 2000 это имя то же самое , что и
ROUTINE_SCHEMA.
|
SPECIFIC_NAME
|
nvarchar(128)
|
Конкретное имя каталога .
Для SQL Server 2000 это имя то же самое , что и
ROUTINE_NAME.
|
ROUTINE_CATALOG
|
nvarchar(128)
|
Имя каталога функции .
|
ROUTINE_SCHEMA
|
nvarchar(128)
|
Имя владельца функции .
|
ROUTINE_NAME
|
nvarchar(128)
|
Имя функции .
|
ROUTINE_TYPE
|
nvarchar(20)
|
Возвращает PROCEDURE для хранимой процедуры и FUNCTION
для функций.
|
MODULE_CATALOG
|
nvarchar(128)
|
NULL. Зарезервировано .
|
MODULE_SCHEMA
|
nvarchar(128)
|
NULL. Зарезервировано .
|
MODULE_NAME
|
nvarchar(128)
|
NULL. Зарезервировано .
|
UDT_CATALOG
|
nvarchar(128)
|
NULL. Зарезервировано .
|
UDT_SCHEMA
|
nvarchar(128)
|
NULL. Зарезервировано .
|
UDT_NAME
|
nvarchar(128)
|
NULL. Зарезервировано .
|
DATA_TYPE
|
nvarchar(128)
|
Тип данных возвращаемого значения функции. Возвращает
таблицу или табличнозначную функцию.
|
CHARACTER_MAXIMUM_LENGTH
|
int
|
Максимальная длина в символах, если возвращаемый тип –
символьный.
|
CHARACTER_OCTET_LENGTH
|
int
|
Максимальная длина в байтах, если возвращаемый тип –
символьный.
|
COLLATION_CATALOG
|
nvarchar(128)
|
Имя коллации возвращаемого значения части каталога. Для
несимвольных типов возвращает NULL .
|
COLLATION_SCHEMA
|
nvarchar(128)
|
Имя коллации возвращаемого значения части схемы. Для
несимвольных типов возвращает NULL .
|
COLLATION_NAME
|
nvarchar(128)
|
Имя коллации возвращаемого значения . Для несимвольных
типов возвращает NULL .
|
CHARACTER_SET_CATALOG
|
nvarchar(128)
|
Имя в каталоге символьного набора возвращаемого
значения. Для несимвольных типов возвращает NULL .
|
CHARACTER_SET_SCHEMA
|
nvarchar(128)
|
Имя в схеме символьного набора возвращаемого значения.
Для несимвольных типов возвращает NULL .
|
CHARACTER_SET_NAME
|
nvarchar(128)
|
Имя набора символов возвращаемого значения. Для
несимвольных типов возвращает NULL .
|
NUMERIC_PRECISION
|
smallint
|
Точность числа возвращаемого значения . Для нечисловых
типов возвращает NULL .
|
NUMERIC_PRECISION_RADIX
|
smallint
|
Точность корня числа возвращаемого значения. Для
нечисловых типов возвращает NULL .
|
NUMERIC_SCALE
|
smallint
|
Масштаб возвращаемого значения . Для нечисловых типов
возвращает NULL.
|
DATETIME_PRECISION
|
smallint
|
Точность дробной части секунды, если возвращаемое
значениие имеет тип datetime . В противном случае возвращает NULL .
|
INTERVAL_TYPE
|
nvarchar(30)
|
NULL. Зарезервировано .
|
INTERVAL_PRECISION
|
smallint
|
NULL. Зарезервировано .
|
TYPE_UDT_CATALOG
|
nvarchar(128)
|
NULL. Зарезервировано .
|
TYPE_UDT_SCHEMA
|
nvarchar(128)
|
NULL. Зарезервировано .
|
TYPE_UDT_NAME
|
nvarchar(128)
|
NULL. Зарезервировано .
|
SCOPE_CATALOG
|
nvarchar(128)
|
NULL. Зарезервировано .
|
SCOPE_SCHEMA
|
nvarchar(128)
|
NULL. Зарезервировано .
|
SCOPE_NAME
|
nvarchar(128)
|
NULL. Зарезервировано .
|
MAXIMUM_CARDINALITY
|
bigint
|
NULL. Зарезервировано .
|
DTD_IDENTIFIER
|
nvarchar(128)
|
NULL. Зарезервировано .
|
ROUTINE_BODY
|
nvarchar(30)
|
Возвращает SQL для функции Transact - SQL , и EXTERNAL
для внешней функции.
В SQL Server 2000, функции всегда SQL .
|
ROUTINE_DEFINITION
|
nvarchar(4000)
|
Определяет текст функции или хранимой процедуры, если
функция или хранимая процедура не зашифрована. В противном случае NULL.
|
EXTERNAL_NAME
|
nvarchar(128)
|
NULL. Зарезервировано .
|
EXTERNAL_LANGUAGE
|
nvarchar(30)
|
NULL. Зарезервировано .
|
PARAMETER_STYLE
|
nvarchar(30)
|
NULL. Зарезервировано .
|
IS_DETERMINISTIC
|
nvarchar(10)
|
Возвращает YES , если процедура является
детерминированной.
Возвращает NO , если - недетерминированной.
Всегда возвращает NO для хранимых процедур.
|
SQL_DATA_ACCESS
|
nvarchar(30)
|
Возвращает одно из четырех значений:
· NONE - Функция не содержит SQL .
· CONTAINS – Функция возможно содержит SQL .
· READS – Функция возможно читает данные SQL .
· MODIFIES – Функция возможно модифицирует данные SQL .
В SQL Server 2000 возвращает READS для всех функция и MODIFIES для всех хранимых
процедур.
|
IS_NULL_CALL
|
nvarchar(10)
|
Указывает, будет ли вызываться процедура, если
какие-либо ее аргументы есть NULL .
В SQL Server 2000 всегда возвращает YES .
|
SQL_PATH
|
nvarchar(128)
|
NULL. Зарезервировано .
|
SCHEMA_LEVEL_ROUTINE
|
nvarchar(10)
|
Возвращает YES , если функция уровня схемы или NO в
противном случае.
В SQL Server 2000 всегда возвращает YES .
|
MAX_DYNAMIC_RESULT_SETS
|
smallint
|
Максимальное число динамических результирующих наборов,
возвращаемых процедурой.
Возвращает 0, если функции, и TBD , если хранимые процедуры.
|
IS_USER_DEFINED_CAST
|
nvarchar(10)
|
Возвращает YES , если функция cast является
пользовательской, и NO , если не пользовательской.
В SQL Server 2000 всегда возвращает NO .
|
IS_IMPLICITLY_INVOCABLE
|
nvarchar(10)
|
Возвращает YES , если процедура вызывается неявно, и NO
, если явно.
В SQL Server 2000 всегда возвращает NO .
|
CREATED
|
datetime
|
Время создания процедуры .
|
LAST_ALTERED
|
datetime
|
Время последней модификации процедуры .
|
Представление PARAMETERS
Column name
|
Data type
|
Description
|
SPECIFIC_CATALOG
|
nvarchar(128)
|
Имя каталога процедуры ( ROUTINE ), в которой
используется параметр.
|
SPECIFIC_SCHEMA
|
nvarchar(128)
|
Имя владельца ROUTINE , в которой используется
параметр.
|
SPECIFIC_NAME
|
nvarchar(128)
|
Имя ROUTINE , в которой используется параметр.
|
ORDINAL_POSITION
|
Smallint
|
Порядковый номер параметра, начиная с 1. Для
возвращаемого значения функции равно 0.
|
PARAMETER_MODE
|
nvarchar(10)
|
Возвращает IN , если это входной параметр, OUT , если -
выходной, и INOUT , если - входной/выходной параметр.
|
IS_RESULT
|
nvarchar(10)
|
Возвращает YES , если указывает результат процедуры,
которая является функцией. В противном случае возвращает NO.
|
AS_LOCATOR
|
nvarchar(10)
|
Возвращает YES , если объявляется как локатор. NO в
противном случае .
|
PARAMETER_NAME
|
nvarchar(128)
|
Имя параметра. NULL , если он соответствует
возвращаемому значению функции.
|
DATA_TYPE
|
nvarchar(128)
|
Тип данных параметра .
|
CHARACTER_MAXIMUM_LENGTH
|
int
|
Максимальная длина в символах для бинарных или
символьных типов данных. NULL в противном случае .
|
CHARACTER_OCTET_LENGTH
|
int
|
Максимальная длина в байтах для бинарных или символьных
типов данных. NULL в противном случае .
|
COLLATION_CATALOG
|
nvarchar(128)
|
Имя каталога коллации параметра . Если тип
несимвольный, возвращает NULL .
|
COLLATION_SCHEMA
|
nvarchar(128)
|
Имя схемы коллации параметра. Если тип несимвольный,
возвращает NULL .
|
COLLATION_NAME
|
nvarchar(128)
|
Имя коллации параметра. Если тип несимвольный,
возвращает NULL .
|
CHARACTER_SET_CATALOG
|
nvarchar(128)
|
Имя каталога набора символов параметра. Если тип
несимвольный, возвращает NULL .
|
CHARACTER_SET_SCHEMA
|
nvarchar(128)
|
Имя владельца набора символов параметра. Если тип
несимвольный , возвращает NULL
|
CHARACTER_SET_NAME
|
nvarchar(128)
|
Имя набора символов параметра. Если тип несимвольный,
возвращает NULL .
|
NUMERIC_PRECISION
|
tinyint
|
Точность данных приближенного числового, точного
числового, целочисленного или денежного типа. NULL в противном случае .
|
NUMERIC_PRECISION_RADIX
|
smallint
|
Точность корня приближенного числового, точного
числового, целочисленного или денежного типа. NULL в противном случае.
|
NUMERIC_SCALE
|
tinyint
|
Масштаб приближенного числового, точного числового,
целочисленного или денежного типа данных. NULL в противном случае.
|
DATETIME_PRECISION
|
smallint
|
Точность в долях секунды, если типом параметра является
datetime или smalldatetime . NULL в противном случае.
|
INTERVAL_TYPE
|
nvarchar(30)
|
NULL. Зарезервировано .
|
INTERVAL_PRECISION
|
smallint
|
NULL. Зарезервировано .
|
USER_DEFINED_TYPE_CATALOG
|
nvarchar(128)
|
NULL. Зарезервировано .
|
USER_DEFINED_TYPE_SCHEMA
|
nvarchar(128)
|
NULL. Зарезервировано .
|
USER_DEFINED_TYPE_NAME
|
nvarchar(128)
|
NULL. Зарезервировано .
|
SCOPE_CATALOG
|
nvarchar(128)
|
NULL. Зарезервировано .
|
SCOPE_SCHEMA
|
nvarchar(128)
|
NULL. Зарезервировано .
|
SCOPE_NAME
|
nvarchar(128)
|
NULL. Зарезервировано .
|
ROUTINE_COLUMNS
Это представление содержит строки для столбцов табличнозначных функций. Схема этого
представления та же самая, что и для представления COLUMNS, за исключением
того, что TABLE_CATALOG, TABLE_SCHEMA и TABLE_NAME ссылается на имя
табличнозначной функции.
Системные функции
SQL Server 2000 предоставляет набор функций Transact-SQL. Примерами функций
Transact-SQL являются табличнозначные функции, которые моделируются после наших
существующих системных хранимых процедур, которые возвращают табличные данные
(например, хранимые процедуры sp_helpdb, sp_helpindex
и т.д.).
Системные функции создаются в скрытой определяемой системой схеме в базе данных
master. Имена этих системных функций начинаются с префикса “fn_”.
Замечание: Системные функции означают для SQL Server компоненты,
обеспечивающие функциональность в форме функций Transact-SQL. Конечные
пользователи сами не могут создавать системные функции. Конечные пользователи
могут использовать существующие системные функции, предоставляемые SQL Server.
Вызов системных функций отличается от UDF в следующем:
-
Вы должны вызывать табличнозначные системные функции с префиксом ‘::’.
-
Вы должны вызывать скалярнозначные системные функции с неуточненным именем
(одна часть).
Когда вы вызываете системную функцию с помощью неуточненного имени, системная
функция выполняется в контексте той базы данных, к которой адресуется запрос.
Часть 1
Часть 2
Часть 3 Часть
4
На главную страницу
Версия для печати
|
|