Condividi tramite

Creare funzioni definite dall'utente (motore di database)

Questo argomento descrive come creare una funzione definita dall'utente in SQL Server usando Transact-SQL.

Contenuto dell'articolo

Prima di iniziare

Limitazioni e restrizioni

  • Le funzioni definite dall'utente non possono essere usate per eseguire azioni che modificano lo stato del database.

  • Le funzioni definite dall'utente non possono contenere una clausola OUTPUT INTO con una tabella come destinazione.

  • Le funzioni definite dall'utente non possono restituire più set di risultati. Si consiglia di utilizzare una stored procedure se è necessario restituire più set di risultati.

  • La gestione degli errori è limitata in una funzione definita dall'utente. Una funzione definita dall'utente non supporta TRY...CATCH, @ERROR o RAISERROR.

  • Le funzioni definite dall'utente non possono chiamare una procedura archiviata, ma possono chiamare una procedura archiviata estesa.

  • Le funzioni definite dall'utente non possono usare tabelle SQL o temporanee dinamiche. Le variabili di tabella sono consentite.

  • Le istruzioni SET non sono consentite in una funzione definita dall'utente.

  • La clausola FOR XML non è consentita

  • Le funzioni definite dall'utente possono essere annidate; ovvero una funzione definita dall'utente può chiamare un'altra. Il livello di annidamento viene incrementato all'avvio dell'esecuzione della funzione chiamata e decrementato al termine dell'esecuzione della funzione chiamata. Le funzioni definite dall'utente possono essere annidate fino a 32 livelli. Il superamento dei livelli massimi di annidamento causa l'esito negativo dell'intera catena di funzioni chiamanti. Qualsiasi riferimento al codice gestito da una funzione definita dall'utente Transact-SQL viene conteggiato come un livello rispetto al limite di annidamento di 32 livelli. I metodi richiamati da codice gestito non vengono inclusi nel conteggio per questo limite.

  • Non è possibile includere le istruzioni di Service Broker seguenti nella definizione di una funzione Transact-SQL definita dall'utente:

    • AVVIA CONVERSAZIONE DI DIALOGO

    • TERMINA CONVERSAZIONE

    • OTTIENI GRUPPO DI CONVERSAZIONE

    • sposta la conversazione

    • RICEVERE

    • INVIA

Sicurezza

Autorizzazioni

È richiesta l'autorizzazione CREATE FUNCTION nel database e l'autorizzazione ALTER per lo schema in cui viene creata la funzione. Se per la funzione viene specificato un tipo definito dall'utente, è necessario disporre dell'autorizzazione EXECUTE per tale tipo.

Funzioni scalari

Nell'esempio seguente viene creata una funzione scalare multistatement nel database AdventureWorks2012. La funzione accetta un valore di input, un ProductIDe restituisce un singolo valore di dati, la quantità aggregata del prodotto specificato nell'inventario.

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;  
GO

Nell'esempio seguente viene utilizzata la ufnGetInventoryStock funzione per restituire la quantità di inventario corrente per i prodotti con un ProductModelID valore compreso tra 75 e 80.

SELECT ProductModelID, Name, dbo.ufnGetInventoryStock(ProductID)AS CurrentSupply  
FROM Production.Product  
WHERE ProductModelID BETWEEN 75 and 80;

Funzioni Table-Valued

Nell'esempio seguente viene creata una funzione inline con valori di tabella nel database AdventureWorks2012. La funzione accetta un parametro di input, un ID cliente (store) e restituisce le colonne ProductID, Namee l'aggregazione delle vendite da inizio anno come YTD Total per ogni prodotto venduto al negozio.

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  
);

Nell'esempio seguente viene richiamata la funzione e viene specificato l'ID cliente 602.

SELECT * FROM Sales.ufn_SalesByStore (602);

Nell'esempio seguente viene creata una funzione con valori di tabella nel database AdventureWorks2012. La funzione accetta un singolo parametro di input, un EmployeeID e restituisce un elenco di tutti i dipendenti che segnalano direttamente o indirettamente al dipendente specificato. La funzione viene quindi richiamata specificando l'ID dipendente 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  
-- Example invocation  
SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel  
FROM dbo.ufn_FindReports(1);

Vedere anche

Funzioni definite dall'utente
CREATE FUNCTION (Transact-SQL)

  • Last updated on