Condividi tramite

  • Articolo

Tipi di funzioni

SQL Server 2008 supporta funzioni definite dall'utente e funzioni di sistema predefinite.

Funzioni scalari

Le funzioni scalari definite dall'utente restituiscono un singolo valore di dati del tipo definito nella clausola RETURNS. Per una funzione scalare inline, non è disponibile alcun corpo della funzione. Il valore scalare corrisponde al risultato di una singola istruzione. Per una funzione scalare con istruzioni multiple, il corpo della funzione, definito in un blocco BEGIN...END, include una serie di istruzioni Transact-SQL che restituiscono un solo valore. Il tipo restituito può essere qualsiasi tipo di dati ad eccezione di text, ntext, image, cursor e timestamp.

Negli esempi seguenti viene creata una funzione scalare con istruzioni multiple. La funzione accetta un valore di input, un valore ProductID e restituisce un singolo valore di dati, la quantità aggregata del prodotto specificato nelle scorte.

USE AdventureWorks2008R2;
GO
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 funzione ufnGetInventoryStock per conoscere la quantità di scorte dei prodotti il cui valore ProductModelID è compreso tra 75 e 80.

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

Funzioni con valori di tabella

Le funzioni con valori di tabella definite dall'utente restituiscono un tipo di dati table. Per una funzione inline con valori di tabella non è disponibile alcun corpo della funzione. La tabella corrisponde al set di risultati di una singola istruzione SELECT.

Nell'esempio seguente viene creata una funzione inline con valori di tabella. La funzione accetta un parametro di input, un ID (punto vendita) cliente e restituisce le colonne ProductID, Name e l'aggregazione delle vendite per l'anno in corso come valore YTD Total per ogni prodotto venduto al punto vendita.

USE AdventureWorks2008R2;
GO
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
);
GO

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

SELECT * FROM Sales.ufn_SalesByStore (602);

Per una funzione con valori di tabella con istruzioni multiple, il corpo della funzione, definito in un blocco BEGIN...END, include una serie di istruzioni Transact-SQL che compilano e inseriscono righe nella tabella che verrà restituita.

Nell'esempio seguente viene creata una funzione con valori di tabella. La funzione accetta un solo parametro di input, un valore EmployeeID e restituisce un elenco di tutti i dipendenti che fanno riferimento direttamente o indirettamente al dipendente specificato. La funzione viene quindi richiamata specificando l'ID dipendente 109.

USE AdventureWorks2008R2;
GO
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); 

GO

Funzioni predefinite

Le funzioni predefinite di SQL Server consentono di eseguire un'ampia gamma di operazioni. Tali funzioni non possono essere modificate. È possibile utilizzare le funzioni predefinite in istruzioni Transact-SQL per effettuare le operazioni seguenti:

Le funzioni predefinite restituiscono tipi di dati scalari o table. La funzione @@ERROR, ad esempio, restituisce 0 se l'ultima istruzione Transact-SQL è stata eseguita correttamente. Se l'istruzione ha generato un errore, la funzione @@ERROR restituirà il numero di errore. La funzione SUM(parameter) restituisce la somma di tutti i valori per il parametro.