Определяемые пользователем функции (UDF) (часть 4)

Books and articles about SQL

Сменить язык на: Русский

20 April 2024 05:57:38

Main

Database fundamentals

Articles about MS SQL

Articles about Oracle

Articles about PostgreSQL

Additions for sql-ex.ru

• Oracle optimization

• Common articles

• Additional materials

• Errors in exercises on database "Computer firm"

• Errors in exercises on database "Ships"

• Errors in exercises on database "Recycling firm"

• Errors in exercises on database "Aeroflot"

• Database scripts

• DML exercises

• Feedback

• Developers & Thanks

• Recommended links

• Link exchange

• Our buttons

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

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

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

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

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