Funciones escalares en SQL
Las funciones escalares, devuelven un único valor escalar cuyo tipo de dato será definido en la cláusula RETURNS.
A continuación, vamos a ver con ejemplos las sentencias, para crear, ejecutar, invocar, modificar y eliminar funciones escalares.
CREATE FUNCTION
La sentencia CREATE FUNCTION permite crear nuevas funciones en base de datos. La sintáxis básica es la siguiente:
CREATE FUNCTION nombreFuncion (parámetro1, parámetro2...)
RETURNS tipo de dato
AS
BEGIN
BLOQUE DE CÓDIGO
RETURN EXPRESIÓN
END
En el siguiente ejemplo creamos una nueva función llamada aplicarDescuento que como su nombre indica, nos permite pasar por parámetros de entrada: el descuento y el producto al que queremos aplicarlo:
CREATE FUNCTION aplicarDescuento(@Producto VARCHAR(55), @descuento int)
RETURNS VARCHAR(255)
AS
BEGIN
DECLARE @result VARCHAR(255);
DECLARE @nombreProducto VARCHAR(55);
SET @nombreProducto = (SELECT producto
FROM Pedidos
WHERE producto LIKE '%'+@Producto+'%');
IF @nombreProducto IS NOT NULL
BEGIN
DECLARE @precioDescuento float = (SELECT precio - @descuento
FROM Pedidos
WHERE producto = @nombreProducto);
SET @result = 'EL PRODUCTO: ' +
@nombreProducto +
' SERÁ REBAJADO HA: ' +
CAST (@precioDescuento AS VARCHAR(50))+ ' EUROS';
END
ELSE
SET @result = 'NO SE HA ENCONTRADO EL PRODUCTO';
RETURN @result
END
GO
Atendiendo a la siguiente función: aplicarDescuento sacamos las siguientes conclusiones para desgranar su funcionamiento:
-
Recibe dos parámetros de entrada:
@Productoy@descuento -
la variable
@nombreProductoguarda el valor exacto del campo producto de la tablaPedidosfiltrando por la cadena recibida en el parámetro de entrada@Producto. -
Si
@nombreProductose ha rellenado, utilizamos su valor para guardar en la variable@precioDescuentoel resultado de restar: el precio de base de datos del producto menos el valor recibido como parámetro de entrada de@descuento. Posteriormente seteamos la variable@resultcon un mensaje indicando el producto y precio rebajado y devolvemos el valor en elRETURN -
Si
@nombreProductono se ha rellenado, devolvemos en elRETURNla variable@resultseteada con el mensaje: NO SE HA ENCONTRADO EL PRODUCTO.
El comando GO escrito después del último END de la función, se utiliza para encapsular y ejecutar sentencias independientes desde un mismo script. No obstante, se recomienda declarar funciones en scripts independientes.
Una vez creada la nueva función podremos verla gráficamente desplegándola en la siguiente ruta de subdirectorios del menú de SQL Server Managment Studio:
Ejecución e Invocación
Podemos ejecutar funciones directamente desde la sentencia SELECT. Para ello, basta con que escribamos el nombre de la función después de la cláusula SELECT. Si nuestra función recibe parámetros de entrada, debemos introducir el valor respectivo dentro de los paréntesis de la función, respetando el orden en el que se declararon y el tipo de datos que pueden soportar.
SELECT dbo.aplicarDescuento('PLAY', 100)
También podremos invocarlas desde un bloque de código:
DECLARE @a INT = 5;
DECLARE @b INT = 1;
IF @a > @b OR @a =@b
BEGIN
print dbo.aplicarDescuento('PLAY', 100);
END
ELSE
PRINT 'NO SE CUMPLEN LAS CONDICIONES'
También es posible setear en una variable el valor devuelto por una función:
DECLARE @a INT = 5;
DECLARE @b INT = 1;
IF @a > @b OR @a =@b
BEGIN
DECLARE @descuento VARCHAR(255);
SET @descuento = dbo.aplicarDescuento('PLAY', 100);
PRINT @descuento
END
ELSE
PRINT 'NO SE CUMPLEN LAS CONDICIONES'
En los tres casos, el resultado de ejecución devuelve la cadena de texto: EL PRODUCTO: Play Station 5 SERÁ REBAJADO HA: 449.95 EUROS.
ALTER FUNCTION
ALTER FUNCTION define la sentencia para modificar funciones de usuario ya declaradas. Las modificaciones podrán afectar a los argumentos o parámetros de entrada, el tipo de dato devuelto en el RETURNS o en la lógica interna.
Para el ejemplo de modificación de nuestra función aplicarDescuento, supongamos la siguiente ejecución:
SELECT dbo.aplicarDescuento('P', 100)
La subconsulta ha devuelto más de un valor, lo que no es correcto cuando va a continuación de =, !=, <, <=, >, >= o cuando se utiliza como expresión.
Al haber introducido solo un carácter como primer parámetro, nuestra SELECT interna encuentra más de un registro que contenga una P en el campo producto.
Para evitar este error y obligar al usuario que sea más específico a la hora de introducir el nombre de un producto, vamos a realizar el siguiente ALTER FUNCTION:
ALTER FUNCTION aplicarDescuento(@Producto VARCHAR(55), @descuento int)
RETURNS VARCHAR(255)
AS
BEGIN
DECLARE @result VARCHAR(255);
DECLARE @nombreProducto VARCHAR(55);
DECLARE @numeroProductos INT;
SET @numeroProductos = (SELECT COUNT(*)
FROM Pedidos
WHERE producto like '%'+@Producto+'%');
IF @numeroProductos > 1
BEGIN
SET @result = 'DEBE ESPECIFICAR MEJOR EL NOMBRE DEL PRODUCTO';
RETURN @result
END
SET @nombreProducto = (SELECT producto
FROM Pedidos
WHERE producto like '%'+@Producto+'%');
IF @nombreProducto IS NOT NULL
BEGIN
DECLARE @precioDescuento float = (SELECT precio - @descuento
FROM Pedidos
WHERE producto = @nombreProducto );
SET @result = 'EL PRODUCTO: ' +
@nombreProducto +
' SERÁ REBAJADO HA: ' +
CAST (@precioDescuento AS VARCHAR(50)) +
' EUROS';
END
ELSE
SET @result = 'NO SE HA ENCONTRADO EL PRODUCTO';
RETURN @result
END
GO
Hemos declarado una nueva variable: @numeroProductos que en función del valor de @Producto recibido como parámetro, guarde el número de productos que ha encontrado en la tabla Pedidos. Si encuentra más de uno, devolverá un mensaje indicando al usuario que tiene que ser más específico.
SELECT dbo.aplicarDescuento('P', 100)
El resultado de ejecución devolverá la siguiente cadena de texto: DEBE ESPECIFICAR MEJOR EL NOMBRE DEL PRODUCTO.
DROP FUNCTION
La sentencia DROP FUNCTION elimina del sistema la función específica.
La siguiente ejecución elimina de nuestra base de datos la función aplicarDescuento():
DROP FUNCTION IF EXISTS dbo.aplicarDescuento;
Como en otros tipos de DROP, es recomendable utilizar siempre la cláusula IF EXISTS
