Como habíamos visto en el articulo de funciones en MySQL, una función es una rutina almacenada que recibe unos parámetros escalares de entrada, luego los procesa según el cuerpo definido de la función y por ultimo retorna en un resultado de un tipo especifico que permitirá cumplir un objetivo.
A diferencia de MySQL las funciones definidas por el usuario en SQL Server permiten retornar tablas en los resultados . Esta característica brinda al programador facilidad a la hora de administrar sus bases de datos.
Que tipos de funciones definidas por el usuario existen en SQL Server?
Existen 3 tipos:
- Funciones escalares
- Funciones con valores de tabla en linea
- Funciones con valores de tabla y múltiples instrucciones
La sintaxis de creación de las tres es muy similar, solo se diferencian en el tipo de parámetros que retornan.
Que es una función escalar?
Es aquella que recibe parámetros de entrada para ser procesados y al final retornar en un tipo de dato sencillo. Cuando hablo de sencillo me refiero a tipos de datos elementales como INT, FLOAT, VARCHAR, etc.
SQL Server no permite que las funciones escalares retornen en los tipos text, ntext, image, cursor y timestamp. Al igual que MySQL, usaremos la palabra reservada RETURNS para indicar el tipo de dato en el cual retornará la función. El cuerpo de una función escalar esta contenido en un bloque de instrucciones como en los procedimientos.
Veamos la sintaxis de creación:
CREATE FUNCTION nombre_función ( [parámetro1, parámetro2,...])
RETURNS tipo_de_dato
[WITH ENCRYPTION | WITH SCHEMABINDING]
[AS]
BEGIN<bloque_de_instrucciones>
RETURN valor_retornado
END
De la definición anterior ya conocemos el uso de la propiedad WITH ENCRYPTION vista en la creación de procedimientos en SQL Server. Mas adelante veremos para que es WITH SCHEMABINDING.
Puedes darme un ejemplo de funciones escalares porfis?
Claro! A continuación veremos un enunciado que debe ser solucionado con la creación de una función escalar. El problema esta basado en una base de datos de una Aerolínea. Observa:
Averigüe cuantas veces ha viajado un pasajero en un lapso de tiempo. Use la identificación del pasajero para consultar dicha información. En cuanto al lapso de tiempo, especifique una fecha de inicio y una fecha final.
CREATE FUNCTION cuanto_ha_viajado(@idpasajero INT, @fecha_inicio DATE , @fecha_final DATE)
RETURNS INT
AS
BEGIN
DECLARE @cantidad_ocasiones INT;
SELECT @cantidad_ocasiones = COUNT(a.idpasajero)
FROM Aerolinea.Boleto AS a
WHERE a.idpasajero = @idpasajero AND (fecha_compra BETWEEN @fecha_inicio AND @fecha_final);
IF ( @cantidad_ocasiones IS NULL)
SET @cantidad_ocasiones = 0;
RETURN @cantidad_ocasiones;
END
Que es una función con valores de tabla en linea?
Este tipo de función tiene la misma sintaxis que una función escalar, la única diferencia está en que retorna un tipo de dato TABLE, es decir, una tabla compuesta de registros. Veamos la variación de la sintaxis:
CREATE FUNCTION nombre_función ( [parámetro1, parámetro2,...])
RETURNS TABLE
[WITH ENCRYPTION | WITH SCHEMABINDING]
[AS]
RETURN (consulta_SELECT)
Las funciones que retornan tablas son muy útiles cuando tenemos consultas que tengan JOINS debido a la reducción de complejidad.
Miramos un ejemplo de una función con valores de tabla en línea?
Por supuesto!, supón que tenemos una base de datos comercial de una Tienda Digital que vende Hardware. Esta base de datos tiene la famosa relación entre clientes, pedidos y artículos. El requerimiento dice así:
Consulte todas las compras realizadas de un producto específico. Use el código del producto para generar los resultados y muestre el nombre del cliente que compro ese producto, la fecha en que se compro, el precio que tenia en ese momento y la cantidad comprada.
CREATE FUNCTION ventas_producto(@idproducto INT)
RETURNS TABLE
AS
RETURN(
SELECT C.NOMBRE, F.FECHA, DF.CANTIDAD, DF.PRECIO,P.IDPRODUCTO
FROM
CLIENTE AS C JOIN FACTURA AS F ON C.IDCLIENTE = F.IDCLIENTE
JOIN DETALLEFACTURA AS DF ON DF.IDFACTURA = F.IDFACTURA
JOIN PRODUCTO AS P ON P.IDPRODUCTO = DF.IDPRODUCTO
WHERE P.IDPRODUCTO = @idproducto)
La anterior función retorna en una tabla que representa todas las compras de un producto realizadas hasta el momento por los clientes. Ahora podemos realizar consultas sobre esta tabla, por ejemplo, si quisiéramos sumar el total de todas las ventas del articulo con código 1003, podríamos hacer la siguiente consulta:
SELECT SUM(CANTIDAD*PRECIO) FROM ventas_producto(1003)
Con esa función la base de datos de nuestra tienda virtual de hardware ganará velocidad de cálculo, además de ahorrar tiempo para los desarrolladores y administradores.
Que es una función con valores de tabla y múltiples instrucciones?
Este tipo de funciones son similares a las funciones de tabla en linea, solo que incluyen un bloque de sentencias para manipular la información antes de retornar la tabla. Su sintaxis de creación es la siguiente:
CREATE FUNCTION nombre_función ( [parámetro1, parámetro2,...])
RETURNS @variable_tabla TABLE (nombre_columna tipo,...)
[WITH ENCRYPTION | WITH SCHEMABINDING]
[AS]
BEGIN
<bloque de instrucciones>
RETURN
END
La anterior definición parametriza la variable tipo TABLE en que retornará la función. Es decir, definiremos como será su estructura antes de retornarla. Así que debemos especificar cada parámetro y su tipo.
Este tipo esta mas complicado, me explicas mediante un ejemplo?
Claro!, a continuación veremos un ejemplo sencillo. Fíjate en el siguiente enunciado:
Muestre todos los registros de la tabla EMPLEADO que tengan un salario mayor o igual a un valor establecido como parámetro . Además de ello combine en un solo campo el nombre y apellido del empleado y agregue un nuevo atributo que muestre la cantidad de días que lleva el empleado desde su fecha de ingreso.
CREATE FUNCTION reporte1_empleados(@salarioemp INT)
RETURNS @tabla TABLE (
IDEMPLEADO INT PRIMARY KEY NOT NULL IDENTITY,
NOMBRE VARCHAR(200) NOT NULL,
SALARIO INT NOT NULL,
DIASLABORANDO INT NOT NULL)
AS
BEGIN
INSERT @tabla
SELECT E.NOMBRE+' '+E.APELLIDO,E.SALARIO, DATEDIFF(DAY,E.FECHA_INGRESO,GETDATE())
FROM EMPLEADO AS E
WHERE E.SALARIO >= @salarioemp;
RETURN;
END
Con la anterior función retornamos en una nueva tabla personalizada como lo pedía el enunciado. Nota que antes de escribir el bloque de instrucciones hemos definido una variable tipo TABLE con una estructura de 4 columnas. Esta definición se parece mucho a la sintaxis CREATE TABLE para crear tablas. Y al final insertamos los datos con el formato que se pidió.
Si queremos ver que registros tiene la tabla retornada por la función, solo realizamos una consulta de la siguiente forma.
SELECT * FROM reporte1_empleados(1000);
Que es eso de SCHEMABINDING?
Esta restricción permite enlazar la función a un esquema de la base de datos, ya sea una tabla, vista, otras funciones, etc. Significa que todas la función no puede alterar ni borrar ningún objeto en la base de datos de la cual dependa, lo que provee seguridad a la información.
Por ejemplo, si una función quisiera alterar los registros de la tabla EMPLEADO, SCHEMABINDING evitaría que eso pasase, ya que esta dentro del esquema del cual depende la función en la base de datos.
A todas estas, como modifico una función?
Muy fácil!, con ALTER FUNCTION. Este comando te permite cambiar absolutamente toda la sintaxis de la función. Veamos como modificamos un función escalar que antes tomaba dos números para una suma y ahora deseamos que tome tres parámetros. Además queremos que proteja el esquema de la base de datos.
ALTER FUNCTION suma ( @a int, @b INT, @c INT)
RETURNS INT
WITH SCHEMABINDING
BEGIN
RETURN @a+@b+@c;
END;