Tipos de funciones
SQL Server 2008 admite tanto las funciones definidas por el usuario como las funciones del sistema integradas.
Funciones escalares
Las funciones escalares definidas por el usuario devuelven un único valor de datos del tipo definido en la cláusula RETURNS. Las funciones escalares simples no tienen un cuerpo de función; el valor escalar es el resultado de una instrucción de función única (a menudo una instrucción SELECT). En las funciones escalares de varias instrucciones, el cuerpo de la función se define en un bloque BEGIN...END y contiene una serie de instrucciones de Transact-SQL que devuelven un único valor. El tipo devuelto puede ser de cualquier tipo de datos excepto text, ntext, image, cursor, spatial, hierarchyID y timestamp.
En el ejemplo siguiente se utiliza la función escalar sencilla ufnGetInventoryStock para devolver la cantidad de inventario actual de aquellos productos que tienen un ProductModelID entre 75 y 80.
USE AdventureWorks;
GO
SELECT ProductModelID, Name, dbo.ufnGetInventoryStock(ProductID)AS CurrentSupply
FROM Production.Product
WHERE ProductModelID BETWEEN 75 and 80;
GO
En el ejemplo siguiente se crea una función escalar de varias instrucciones. La función toma un valor de entrada, ProductID, y devuelve la cantidad agregada del producto especificado en el inventario como valor de devolución único.
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
Funciones con valores de tabla
Las funciones con valores de tabla definidas por el usuario devuelven un tipo de datos table. Las funciones con valores de tabla insertados no tienen cuerpo; la tabla es el conjunto de resultados de una sola instrucción SELECT.
En el ejemplo siguiente se crea una función con valores de tabla insertados. La función toma un parámetro de entrada, Id. de cliente (almacén), y devuelve las columnas ProductID, Name, y el agregado de las ventas del año hasta la fecha como YTD Total para cada producto vendido en el almacén.
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
En el ejemplo siguiente se invoca la función y especifica el identificador de cliente 602.
SELECT * FROM Sales.ufn_SalesByStore (602);
En una función con valores de tabla de varias instrucciones, un bloque BEGIN…END define el cuerpo de la función y contiene una serie de instrucciones de Transact-SQL que generan e insertan filas en los resultados de la tabla.
En el ejemplo siguiente se crea una función con valores de tabla. La función toma el único parámetro de entrada EmployeeID y devuelve una lista de todos los empleados que dependen del identificador de empleado especificado 109 directa o indirectamente. A continuación, el identificador de empleado 109 se utiliza como parámetro de entrada en el ejemplo y se devuelve una lista de los empleados en la tabla de resultados.
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
Funciones integradas
SQL Server proporciona funciones integradas para ayudarle a realizar diversas operaciones. No se pueden modificar. Puede utilizar funciones integradas en instrucciones Transact-SQL para:
Tener acceso a información de las tablas del sistema de SQL Server sin tener acceso a las tablas del sistema directamente. Para obtener más información, vea Usar las funciones del sistema.
Realizar tareas habituales como SUM, GETDATE o IDENTITY. Para obtener más información, vea Funciones (Transact-SQL).
Las funciones integradas devuelven tipos de datos escalares o table. Por ejemplo, @@ERROR devuelve 0 si la última instrucción Transact-SQL se ejecutó correctamente. Si la instrucción generó un error, @@ERROR devuelve el número de error. Y la función SUM(parameter) devuelve la suma de todos los valores del parámetro.
Historial de cambios
Contenido actualizado |
|---|
Se quitó el contenido incorrecto de las funciones escalares insertadas. |
Vea también