• Articolo

Funzioni inline definite dall'utente

Le funzioni inline definite dall'utente sono un subset di funzioni definite dall'utente che restituiscono un tipo di dati table. Le funzioni inline possono essere utilizzate per ottenere la funzionalità delle viste parametrizzate.

Nell'esempio seguente vengono restituiti nomi di archivio e città per una regione specificata.

USE AdventureWorks2008R2;
GO
CREATE VIEW CustomersByRegion
AS
SELECT DISTINCT S.Name AS Store, A.City
FROM Sales.Store AS S
    JOIN Sales.BusinessEntityAddress AS BEA ON BEA.BusinessEntityID = S.BusinessEntityID
    JOIN Person.Address AS A ON A.AddressID = BEA.AddressID
    JOIN Person.StateProvince SP ON 
        SP.StateProvinceID = A.StateProvinceID
WHERE SP.Name = N'Washington';
GO

Questa vista sarebbe migliore se fosse più generalizzata e consentisse agli utenti di specificare la regione che sono interessati a visualizzare. Le viste, tuttavia, non supportano i parametri nelle condizioni di ricerca specificate nella clausola WHERE. Le funzioni inline definite dall'utente sono utilizzabili per supportare i parametri nelle condizioni di ricerca specificate nella clausola WHERE. Nell'esempio seguente viene creata una funzione inline che consente agli utenti di specificare la regione nella propria query:

USE AdventureWorks2008R2;
GO

IF OBJECT_ID(N'Sales.ufn_CustomerNamesInRegion', N'IF') IS NOT NULL
    DROP FUNCTION Sales.ufn_CustomerNamesInRegion;
GO
CREATE FUNCTION Sales.ufn_CustomerNamesInRegion
                 ( @Region nvarchar(50) )
RETURNS table
AS
RETURN (
        SELECT DISTINCT s.Name AS Store, a.City
        FROM Sales.Store AS s
        INNER JOIN Person.BusinessEntityAddress AS bea 
            ON bea.BusinessEntityID = s.BusinessEntityID 
        INNER JOIN Person.Address AS a 
            ON a.AddressID = bea.AddressID
        INNER JOIN Person.StateProvince AS sp 
            ON sp.StateProvinceID = a.StateProvinceID
        WHERE sp.Name = @Region
       );
GO
-- Example of calling the function for a specific region
SELECT *
FROM Sales.ufn_CustomerNamesInRegion(N'Washington')
ORDER BY City;
GO

Regole funzione inline definite dall'utente

Le funzioni inline definite dall'utente si attengono alle regole seguenti:

  • La clausola RETURN include solo la parola chiave table. Non è necessario definire il formato di una variabile restituita in quanto è impostato dal formato del set di risultati dell'istruzione SELECT nella clausola RETURN.

  • Non è presente alcun function_body delimitato da BEGIN e END.

  • La clausola RETURN include una singola istruzione SELECT tra parentesi. Il set di risultati dell'istruzione SELECT costituisce la tabella virtuale restituita dalla funzione. L'istruzione SELECT utilizzata in una funzione inline è soggetta alle stesse restrizioni delle istruzioni SELECT utilizzate nelle viste.

  • La funzione con valori di tabella accetta solo costanti oppure argomenti @local_variable.

Funzioni inline e viste indicizzate

Le funzioni inline possono inoltre essere utilizzate per aumentare le funzioni delle viste indicizzate. La vista indicizzata non può utilizzare i parametri nella proprie condizioni di ricerca della clausola WHERE per personalizzare il set di risultati archiviato in base a utenti specifici. È possibile, tuttavia, definire una vista indicizzata che archivia il set di dati completo corrispondente alla vista, e quindi definire una funzione inline sulla vista indicizzata che include le condizioni di ricerca parametrizzate che consentono agli utenti di personalizzare i propri risultati. Se la definizione della vista è complessa, la maggior parte del lavoro eseguito per compilare un set di risultati riguarda operazioni come la compilazione di aggregazioni o l'unione in join di più tabelle quando viene creato l'indice cluster sulla vista. Se successivamente si crea una funzione inline che fa riferimento alla vista indicizzata, la funzione può applicare i filtri parametrizzati dell'utente per restituire righe specifiche dal set di risultati materializzato della vista indicizzata. Ad esempio:

  1. Definire una vista vw_QuarterlySales che aggrega tutti i dati di vendita in un set di risultati che indica i dati di vendita riepilogati per trimestre di tutti i punti vendita.

  2. Creare un indice cluster su vw_QuarterlySales per materializzare un set di risultati che contenga i dati riepilogati.

  3. Creare una funzione inline per filtrare i dati riepilogati:

    CREATE FUNCTION dbo.ufn_QuarterlySalesByStore
     ( @StoreID int )
RETURNS table
AS
RETURN (
        SELECT *
        FROM SalesDB.dbo.vw_QuarterlySales
        WHERE StoreID = @StoreID
       )

  4. Gli utenti possono quindi ottenere i dati per il punto vendita di interesse selezionandoli dalla funzione inline:

    SELECT *
FROM fn_QuarterlySalesByStore(14432)

La maggior parte del lavoro necessario a rispondere alle query generate al passaggio 4 riguarda l'aggregazione dei dati di vendita per trimestre. Tale lavoro viene eseguito una volta al passaggio 2. Ogni singola istruzione SELECT nel passaggio 4 utilizza la funzione fn_QuarterlySalesByStore per estrarre i dati aggregati specifici del punto vendita di interesse.