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 le funzioni scalari semplici non è disponibile alcun corpo della funzione. Il valore scalare corrisponde al risultato di una singola istruzione FUNCTION, in genere un'istruzione SELECT. Nelle funzioni scalari con istruzioni multiple il corpo della funzione è definito in un blocco BEGIN...END e include una serie di istruzioni Transact-SQL che restituiscono un singolo valore. Il tipo restituito può essere qualsiasi tipo di dati ad eccezione di text, ntext, image, cursor, spatial, hierarchyID e timestamp.
Nell'esempio seguente viene utilizzata la funzione scalare singola ufnGetInventoryStock per individuare 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
Negli esempi seguenti viene creata una funzione scalare con istruzioni multiple. La funzione accetta un valore di input, un valore ProductID e restituisce la quantità aggregata del prodotto specificato nelle scorte come singolo valore restituito.
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
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 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);
In una funzione con valori di tabella con istruzioni multiple il blocco BEGIN…END definisce il corpo della funzione e contiene una serie di istruzioni Transact-SQL che creano e inseriscono righe nella tabella dei risultati.
Nell'esempio seguente viene creata una funzione con valori di tabella. La funzione accetta il solo parametro di input EmployeeID e restituisce un elenco di tutti i dipendenti che fanno riferimento direttamente o indirettamente al dipendente specificato con ID 109. Il dipendente con ID 109 viene quindi utilizzato come parametro di input nell'esempio e nella tabella dei risultati viene restituito un elenco di dipendenti.
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 in istruzioni Transact-SQL per effettuare le operazioni seguenti:
Accedere alle informazioni dalle 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 restituirà il numero di errore. La funzione SUM(parameter) restituisce la somma di tutti i valori per il parametro.
Cronologia modifiche
Aggiornamento del contenuto |
|---|
Rimozione di contenuto non corretto nella sezione relativa alle funzioni scalari inline. |
Vedere anche