Compartir a través de

  • Artículo

Funciones insertadas definidas por el usuario

Las funciones insertadas definidas por el usuario son un subconjunto de funciones definidas por el usuario que devuelven un tipo de datos table. Las funciones insertadas pueden utilizarse para obtener la funcionalidad de vistas con parámetros.

En el ejemplo siguiente se devuelven los nombres de almacén y las ciudades de una región especificada.

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

Esta vista se puede mejorar haciéndola más generalizada y permitiendo a los usuarios especificar la región que desean ver. No obstante, las vistas no admiten parámetros en las condiciones de búsqueda especificadas en la cláusula WHERE. Las funciones insertadas definidas por el usuario pueden utilizarse para admitir parámetros en las condiciones de búsqueda especificadas en la cláusula WHERE. En el ejemplo siguiente se crea una función insertada que permite a los usuarios especificar la región en la consulta:

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

Reglas de las funciones insertadas definidas por el usuario

Las funciones insertadas definidas por el usuario siguen las reglas siguientes:

  • La cláusula RETURNS sólo contiene la palabra clave table. No es necesario definir el formato de una variable de retorno, ya que se define mediante el formato del conjunto de resultados de la instrucción SELECT en la cláusula RETURN.

  • function_body no está delimitada por BEGIN ni END.

  • La cláusula RETURN contiene una sola instrucción SELECT entre paréntesis. El conjunto de resultados de la instrucción SELECT forma la tabla devuelta por la función. La instrucción SELECT utilizada en una función insertada está sujeta a las mismas restricciones que las instrucciones SELECT utilizadas en las vistas.

  • La función con valores de tabla sólo acepta constantes o argumentos @local_variable.

Funciones insertadas y vistas indizadas

Las funciones insertadas también pueden utilizarse para aumentar la eficacia de las vistas indizadas. Por sí misma, la vista indizada no puede utilizar parámetros en las condiciones de búsqueda de su cláusula WHERE para adaptar a usuarios específicos el conjunto de resultados almacenado. No obstante, se puede definir una vista indizada que almacene un conjunto completo de datos que coincida con la vista y, a continuación, definir una función insertada para la vista indizada con condiciones de búsqueda con parámetros que permitan a los usuarios adaptar los resultados. Si la definición de la vista es compleja, la mayor parte del trabajo realizado para generar un conjunto de resultados incluye operaciones como la de generar agregados o la de combinar varias tablas al crear el índice clúster en la vista. Si después crea una función insertada que haga referencia a la vista indizada, la función puede aplicar los filtros con parámetros del usuario para devolver filas específicas del conjunto de resultados materializado de la vista indizada. Por ejemplo:

  1. Se define una vista vw_QuarterlySales que agrega todos los datos de ventas a un conjunto de resultados, y éste presenta los datos de ventas de todos los almacenes, resumidos y por trimestres.

  2. Se crea un índice clúster en vw_QuarterlySales para materializar un conjunto de resultados que contenga los datos resumidos.

  3. Se crea una función insertada para filtrar los datos resumidos:

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

  4. Los usuarios pueden entonces obtener los datos de un almacén específico seleccionándolos a partir de la función insertada:

    SELECT *
FROM fn_QuarterlySalesByStore(14432)

La mayor parte del trabajo necesario para atender las consultas emitidas en el paso 4 es agregar los datos de ventas por trimestre. Este trabajo se hace una vez en el paso 2. Cada instrucción SELECT individual del paso 4 utiliza la función fn_QuarterlySalesByStore para filtrar los datos agregados específicos de un almacén.