Tipi di funzioni
SQL Server 2005 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 AdventureWorks;
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 AdventureWorks;
GO
SELECT ProductModelID, Name, dbo.ufnGetInventoryStock(ProductID)AS CurrentSupply
FROM Production.Product
WHERE ProductModelID BETWEEN 75 and 80;
GO
Funzioni valutate a livello di tabella
Le funzioni valutate a livello di tabella definite dall'utente restituiscono un tipo di dati table. Per una funzione inline valutata a livello di tabella non è disponibile alcun corpo della funzione. La tabella corrisponde al seti di risultati di una singola istruzione SELECT.
Nell'esempio seguente viene creata una funzione inline valutata a livello 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 AdventureWorks;
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 'YTD 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
WHERE SH.CustomerID = @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 valutata a livello di tabella con istruzioni multiple, il corpo della funzione, definito in un blocco BEGIN...END, include una serie di istruzioni Transact-SQL che creano e inseriscono righe nella tabella che verrà restituita.
Nell'esempio seguente viene creata una funzione valutata a livello 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 AdventureWorks;
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,
Name nvarchar(255) NOT NULL,
Title nvarchar(50) NOT NULL,
EmployeeLevel int NOT NULL,
Sort nvarchar (255) NOT NULL
)
--Returns a result set that lists all the employees who report to the
--specific employee directly or indirectly.*/
AS
BEGIN
WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort) AS
(SELECT CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName),
e.Title,
e.EmployeeID,
1,
CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName)
FROM HumanResources.Employee AS e
JOIN Person.Contact AS c ON e.ContactID = c.ContactID
WHERE e.EmployeeID = @InEmpID
UNION ALL
SELECT CONVERT(Varchar(255), REPLICATE ('| ' , EmployeeLevel) +
c.FirstName + ' ' + c.LastName),
e.Title,
e.EmployeeID,
EmployeeLevel + 1,
CONVERT (Varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' +
LastName)
FROM HumanResources.Employee as e
JOIN Person.Contact AS c ON e.ContactID = c.ContactID
JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
-- copy the required columns to the result of the function
INSERT @retFindReports
SELECT EmployeeID, Name, Title, EmployeeLevel, Sort
FROM DirectReports
RETURN
END;
GO
-- Example invocation
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM dbo.ufn_FindReports(109)
ORDER BY Sort;
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 nelle istruzioni Transact-SQL per eseguire le operazioni seguenti:
- Accedere alle informazioni delle tabelle di sistema SQL Server senza accedere direttamente alle tabelle di sistema. Per ulteriori informazioni, vedere Utilizzo delle funzioni di sistema.
- Eseguire operazioni comuni come SUM, GETDATE o IDENTITY. Per ulteriori informazioni, vedere Funzioni (Transact-SQL).
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 restituisce il numero di errore. La funzione SUM(parameter) restituisce la somma di tutti i valori per il parametro.
Vedere anche
Altre risorse
Informazioni sulle funzioni definite dall'utente