Создание определяемых пользователем функций (ядро СУБД)
В этой статье описывается, как создать определяемую пользователем функцию (UDF) в SQL Server с помощью Transact-SQL.
ограничения
- Определяемые пользователем функции нельзя использовать для выполнения действий, изменяющих состояние базы данных.
- Определяемые пользователем функции не могут содержать OUTPUT INTO предложение, содержащее таблицу в качестве целевой цели.
- Определяемые пользователем функции не могут возвращать несколько результирующих наборов. Используйте хранимую процедуру, если нужно возвращать несколько результирующих наборов.
- Обработка ошибок в функциях, определяемых пользователем, ограниченна. UDF не поддерживает TRY. CATCH @ERROR или RAISERROR .
- Определяемые пользователем функции не могут вызывать хранимую процедуру, но могут вызывать расширенную хранимую процедуру.
- Определяемые пользователем функции не могут использовать динамические таблицы SQL или временные таблицы. Табличные переменные разрешены к использованию.
- SET операторы не допускаются в определяемой пользователем функции.
- Предложение FOR XML не допускается.
- Определяемые пользователем функции могут быть вложенными, то есть из одной функции может быть вызвана другая. Уровень вложенности увеличивается на единицу каждый раз, когда начинается выполнение вызванной функции и уменьшается на единицу, когда ее выполнение завершается. Вложенность определяемых пользователем функций не может превышать 32 уровней. Превышение максимального уровня вложенности приводит к ошибке выполнения для всей цепочки вызываемых функций. Каждый вызов управляемого кода из определяемой пользователем функции Transact-SQL считается одним уровнем вложенности из 32 возможных. Это ограничение не распространяется на методы, вызываемые из управляемого кода.
- Следующие инструкции компонента Service Broker не могут быть включены в определение определяемой пользователем функции Transact-SQL:
- BEGIN DIALOG CONVERSATION
- END CONVERSATION
- GET CONVERSATION GROUP
- MOVE CONVERSATION
- RECEIVE
- SEND
Разрешения
Требуется разрешение CREATE FUNCTION на базу данных и разрешение ALTER для схемы, в которой создается функция. Если в функции указан определяемый пользователем тип, требуется разрешение EXECUTE на этот тип.
Примеры скалярных функций
Скалярная функция (скалярная UDF)
В следующем примере создается скалярная функция с несколькими операторами (скалярная UDF) в базе данных AdventureWorks2022. Функция имеет один входной параметр ProductID и возвращает одно значение — количество указанного товара на складе.
IF OBJECT_ID (N'dbo.ufnGetInventoryStock', N'FN') IS NOT NULL DROP FUNCTION ufnGetInventoryStock; GO CREATE FUNCTION dbo.ufnGetInventoryStock(@ProductID int) RETURNS int AS -- Returns the stock level for the product. BEGIN DECLARE @ret int; SELECT @ret = SUM(p.Quantity) FROM Production.ProductInventory p WHERE p.ProductID = @ProductID AND p.LocationID = '6'; IF (@ret IS NULL) SET @ret = 0; RETURN @ret; END;В следующем примере функция ufnGetInventoryStock используется для получения сведений о количестве товаров с идентификаторами ProductModelID от 75 до 80.
SELECT ProductModelID, Name, dbo.ufnGetInventoryStock(ProductID)AS CurrentSupply FROM Production.Product WHERE ProductModelID BETWEEN 75 and 80;Дополнительные сведения и примеры скалярных функций см. в статье CREATE FUNCTION (Transact-SQL).
Примеры табличных функций
Встроенная табличная функция (TVF)
В следующем примере создается встроенная табличная функция (TVF) в базе данных AdventureWorks2022. Функция имеет один входной параметр — идентификатор клиента (магазина) — и возвращает столбцы ProductID , Name и столбец YTD Total со сведениями о продажах продукта за текущий год.
IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL DROP FUNCTION Sales.ufn_SalesByStore; GO CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int) RETURNS TABLE AS RETURN ( SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total' FROM Production.Product AS P JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID WHERE C.StoreID = @storeid GROUP BY P.ProductID, P.Name );В следующем примере функция вызывается с идентификатором 602.
SELECT * FROM Sales.ufn_SalesByStore (602);Функция с табличным значением с несколькими операторами (MSTVF)
В следующем примере в базе данных AdventureWorks2022 создается функция с табличным значением с несколькими операторами (MSTVF). Функция имеет один входной параметр EmployeeID и возвращает список всех сотрудников, которые напрямую или косвенно отчитываются перед заданным сотрудником. Затем функция вызывается с указанием идентификатора сотрудника 109.
IF OBJECT_ID (N'dbo.ufn_FindReports', N'TF') IS NOT NULL DROP FUNCTION dbo.ufn_FindReports; GO CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER) RETURNS @retFindReports TABLE ( EmployeeID int primary key NOT NULL, FirstName nvarchar(255) NOT NULL, LastName nvarchar(255) NOT NULL, JobTitle nvarchar(50) NOT NULL, RecursionLevel int NOT NULL ) --Returns a result set that lists all the employees who report to the --specific employee directly or indirectly.*/ AS BEGIN WITH EMP_cte(EmployeeID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel) -- CTE name and columns AS ( SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, 0 -- Get the initial list of Employees for Manager n FROM HumanResources.Employee e INNER JOIN Person.Person p ON p.BusinessEntityID = e.BusinessEntityID WHERE e.BusinessEntityID = @InEmpID UNION ALL SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, RecursionLevel + 1 -- Join recursive member to anchor FROM HumanResources.Employee e INNER JOIN EMP_cte ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode INNER JOIN Person.Person p ON p.BusinessEntityID = e.BusinessEntityID ) -- copy the required columns to the result of the function INSERT @retFindReports SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel FROM EMP_cte RETURN END; GOВ следующем примере функция вызывается с идентификатором сотрудника 1.
SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel FROM dbo.ufn_FindReports(1);Дополнительные сведения и примеры встроенных табличных функций (встроенные TVFs) и многофакторных табличных значений функций (MSTVFs) см. в статье CREATE FUNCTION (Transact-SQL).
Рекомендации
Если определяемая пользователем функция (UDF) не создается с SCHEMABINDING предложением, изменения, внесенные в базовые объекты, могут повлиять на определение функции и вызвать непредвиденные результаты. Рекомендуется реализовать один из следующих методов, чтобы убедиться, что функция не становится устаревшей из-за изменений в его базовых объектах:
- WITH SCHEMABINDING Укажите предложение при создании UDF. Это гарантирует, что объекты, на которые ссылается определение функции, нельзя изменять, если функция также не изменяется.
- Выполняйте хранимую процедуру sp_refreshsqlmodule после изменения любого объекта, указанного в определении функции UDF.
При создании UDF, которая не обращается к данным, укажите SCHEMABINDING этот параметр. Это не позволит оптимизатору запросов создавать ненужные операторы очередей для планов запроса, содержащих такие определяемые пользователем функции. Дополнительные сведения об очередях см. в справочнике по логическим и физическим операторам Showplan. Дополнительные сведения о создании функций, привязанных к схеме, см. в соответствующем разделе.
Присоединение к MSTVF в предложении FROM возможно, но может привести к снижению производительности. SQL Server не может использовать все оптимизированные методы для некоторых инструкций, которые могут быть включены в MSTVF, что приводит к неоптимальному плану запросов. Чтобы получить наилучшую производительность, по возможности задавайте соединения не между функциями, а между базовыми таблицами.
MSTVFs имеет фиксированное кратность 100 начиная с SQL Server 2014 (12.x) и 1 для более ранних версий SQL Server.
Начиная с SQL Server 2017 (14.x), оптимизируя план выполнения, использующий MSTVFs, может использовать чередованное выполнение, что приводит к использованию фактического кратности вместо приведенной выше эвристики.
ANSI_WARNINGS не учитывается при передаче параметров в хранимой процедуре, определяемой пользователем функции или при объявлении и установке переменных в инструкции пакетной службы. Например, если объявить переменную как char(3), а затем присвоить ей значение длиннее трех символов, данные будут усечены до размера переменной, а инструкция INSERT или UPDATE завершится без ошибок.
См. также
- Определяемые пользователем функции
- CREATE FUNCTION (Transact-SQL)
- ALTER FUNCTION (Transact-SQL)
- DROP FUNCTION (Transact-SQL)
- DROP PARTITION FUNCTION (Transact-SQL)
Functions SQL Server
В SQL Server функция представляет собой хранимую программу, в которую вы можете передавать параметры и возвращать значение.
Create Function
Вы можете создавать свои собственные функции в SQL Server (Transact-SQL). Давайте рассмотрим подробнее.
Синтаксис
Синтаксис Functions в SQL Server (Transact-SQL):
CREATE FUNCTION [schema_name.]function_name
( [ @parameter [ AS ] [type_schema_name.] datatype
[ = default ] [ READONLY ]
, @parameter [ AS ] [type_schema_name.] datatype
[ = default ] [ READONLY ] ]
)
RETURNS return_datatype
[ WITH < ENCRYPTION
| SCHEMABINDING
| RETURNS NULL ON NULL INPUT
| CALLED ON NULL INPUT
| EXECUTE AS Clause ]
[ AS ]
BEGIN
[declaration_section]
executable_section
RETURN return_value
END;Параметры или аргументы
schema_name — имя схемы, которой принадлежит эта функция.
function_name — наименование функции в SQL Server.
@parameter — один или несколько параметров, которые передаются в функцию.
type_schema_name — схема, которая владеет типом данных, если это применимо.
datatype — тип данных для @parameter .
default — значение по умолчанию для назначения параметру @parameter .
READONLY — это означает, что @parameter не может быть перезаписана функцией.
return_datatype — тип данных возвращаемого значения функции.
ENCRYPTION — это означает, что источник для функции не будет сохранен как обычный текст в системных представлениях SQL Server.
SCHEMABINDING — это означает, что базовые объекты не могут быть изменены, чтобы влиять на функцию.
RETURNS NULL ON NULL INPUT — это означает, что функция вернет NULL, если любые параметры имеют значение NULL, без необходимости выполнять функцию.
CALL ON NULL INPUT — это означает, что функция будет выполняться, даже если любые параметры имеют NULL.
EXECUTE AS — устанавливает контекст безопасности для выполнения функции.
return_value — значение, возвращаемое функцией.Пример
Рассмотрим пример создания функции в SQL Server (Transact-SQL).
Ниже приведен простой пример функции:Как создать функцию в sql
Для создания функции можно попробовать воспользоваться CREATE FUNCTION .
Например в таком формате:
В этом примере создается функция GetTotalPrice , которая принимает два параметра: @ProductId и @Quantity . Функция использует эти параметры, чтобы выбрать цену товара из таблицы Products , а затем умножает цену на количество и возвращает общую стоимость.
Важно учитывать, что создание функции может отличаться в зависимости от используемой СУБД и ее синтаксиса.
Функции Oracle PL/SQL
Функция — это подпрограмма, которая вычисляет значение.
В этом учебном пособии вы узнаете, как создавать и удалять функции в Oracle/PLSQL с синтаксисом и примерами.
СОЗДАТЬ ФУНКЦИЮ (CREATE FUNCTION)
Как и на других языках программирования, вы можете создавать свои собственные функции в Oracle.
Синтаксис:
CREATE [OR REPLACE] FUNCTION имя_функции
[ (параметр [, параметр, . ]) ]
RETURN тип_данных IS | AS
[локальные объявления]
BEGIN
исполняемые предложения
[EXCEPTION
обработчики исключений]
END [имя_функции];При создании процедуры или функции, вы можете определить три типа параметров, которые могут быть объявлены:
IN — Параметр может ссылаться на процедуру или функцию. Значение параметра не может быть изменено процедурой или функцией.OUT — параметр не может ссылаться на процедуру или функцию, но значение параметра может быть изменено процедурой или функцией.
IN OUT — Параметр может ссылаться на процедуру или функцию и значения параметра может быть изменено процедурой или функцией.
Пример
Рассмотрим на примере, как создать функцию в Oracle.