Funciones con SQL Server

Una función es una rutina almacenada que recibe unos parámetros escalares de entrada, los procesa según la definición de la función y finalmente retorna un resultado de un tipo específico que permitirá su utilización con un objetivo.

Las funciones definidas por el usuario en SQL Server permiten retornar tablas en los resultados. Esta característica proporciona al programador facilidad a la hora de administrar sus bases de datos.

Existen tres tipos de funciones:

• Funciones escalares
• Funciones con valores de tabla en línea
• Funciones con valores de tabla y múltiples instrucciones

La sintaxis de creación de las tres es muy similar, sólo se diferencian en el tipo de parámetros que retornan.

Bloques de colores.

Funciones escalares

Las funciones escalares son aquellas que reciben parámetros de entrada para ser procesados y al final retornar en un valor con un tipo de dato sencillo. Es decir un  tipo de dato elemental como INT, FLOAT, VARCHAR, etc. Pues SQL Server no permite que este tipo de funciones retorne valores de tipo text, ntext, image, cursor y timestamp. Utilizaremos la palabra reservada Returns para indicar el tipo de dato en el cual retornará la función. El cuerpo de una función escalar estará contenido en un bloque de instrucciones como en los procedimientos almacenados.

Ejemplo.

CREATE FUNCTION [DBO].[Nombre_Funcion] 

     (

          @ID                       AS VARCHAR(7),

          @nombre                   AS VARCHAR (70),

          @tipo                      AS CHAR (2)

                )

RETURNS  VARCHAR(6)

AS

BEGIN

--Aquí puede ir cualquier código de SQL Server

     DECLARE @Valor_Retorno AS VARCHAR(6)

     DECLARE @Valor_Intermedio AS VARCHAR(6)

@Valor_Intermedio = SELECT Valor FROM DBO.tbTabla WHERE strCod = @ID AND strNombre = @nombre AND strTipo = @tipo

     SET @Valor_Retorno =  @Valor_Intermedio

     RETURN @Valor_Retorno

 END

GO

Otro ejemplo: Averiguar cuantas veces ha sacado un libro de la biblioteca un usuario en un lapso de tiempo determinado.

CREATE FUNCTION Uso_Biblioteca(@idUsuario INT, @fecha_inicio DATETIME, @fecha_final DATETIME)

RETURNS INT

AS

BEGIN

DECLARE @cantidad_ocasiones INT;

SELECT @cantidad_ocasiones = COUNT(a. idUsuario)

FROM Biblioteca.Sacar_Libro AS a

WHERE a.idUsuario = @ idUsuario AND (fecha_Utilizacion BETWEEN @fecha_inicio AND @fecha_final);

IF ( @cantidad_ocasiones IS NULL)

SET @cantidad_ocasiones = 0;

RETURN @cantidad_ocasiones;

END

Funciones con Valores de Tabla en Línea

Este tipo de función tiene la misma sintaxis que una función escalar, la única diferencia es que devuelve tipo de dato TABLE (una tabla compuesta de registros).

CREATE FUNCTION nombre_funcion ( [parametro1, parametro2,...])

RETURNS TABLE

[WITH ENCRYPTION | WITH SCHEMABINDING]

[AS]

RETURN (consulta_SELECT)

Las funciones que retornan tablas son muy útiles cuando tenemos consultas con  JOINs debido a la que se reduce la complejidad.

Ejemplo:

Consultar todas las salidas de la biblioteca realizadas para  un libro específico. Usar el código del libro para generar los resultados y  mostrar el nombre de la persona que lo sacó, la fecha de salida y de devolución.

CREATE FUNCTION Salidas_libro(@idlibro VARCHAR(12))

RETURNS TABLE

AS

RETURN(

SELECT Invb.Nombre_Libro, Usu.Nombre_Usuario,  MovB.Fecha_Salida, MobB.Fecha_Devolucion

FROM

tbInventario_Biblioteca AS Invb JOIN tbMovimientos_Biblioteca AS Movb ON Invb.IDCLIENTE = MovB.IDCLIENTE

JOIN tbUsuarios AS Usu ON Usu.IdUsuario = MovB.IDUsuario

WHERE Invb.IdLibro = @idLibro)

La función anterior devuelve una  tabla que representa todos los usuarios que han sacado de la biblioteca un libro determinado. Ahora  es posible realizar consultas sobre esta tabla, si deseamos saber el total de todas las veces qu ese ha sacado un libro  con código ISBN00000000, podríamos hacer la siguiente consulta:

SELECT Count(Invb.Nombre_Libro) FROM Salidas_libro ('ISBN00000000')

Con esa función la base de datos de nuestra biblioteca ganará velocidad de cálculo, además de ahorrar tiempo en el desarrollo de la aplicación.

.

Funciones con Valores de Tabla y Múltiples Instrucciones

Este tipo de funciones es similar a las funciones de tabla en línea, pero ahora 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_funcion ( [parametro1, 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 que retornará la función por lo que debemos especificar cada parámetro y su tipo.

Ejemplo de función con Múltiple Instrucciones

Mostrar todos los libros de la biblioteca que tengan fecha de publicación posterior la establecida por parámetro, meter en un solo campo los datos del libro y agregar un atributo que diga la cantidad de días que lleva sin prestarse.

CREATE FUNCTION reporte_libros(@fecha INT)

RETURNS @tbtabla_libros TABLE (

IDLibro INT PRIMARY KEY NOT NULL IDENTITY,

Nombre VARCHAR(200) NOT NULL,

FechaPublicacion DATETIME NOT NULL,

DiasSinPrestar INT NOT NULL)

AS

BEGIN

INSERT @tbtabla_libros

SELECT L.Nombre+' '+L.Autor,L.FechaPublicacion, DATEDIFF(DAY,L.Fecha_Prestamo,GETDATE())

FROM tbLibros AS L

WHERE L.FechaPublicacion >= @fecha;

RETURN;

END

La función anterior inserta filas en una nueva tabla. Antes de escribir el bloque de instrucciones hemos definido una variable de 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 solicitado. Si ahora queremos ver los registros que tiene la tabla retornada por la función, solo realizamos una consulta de la siguiente forma.

SELECT * FROM reporte_libros(1000);

Cláusula ENCRYPTION

Indica que el Motor de base de datos convertirá el texto original de la instrucción CREATE FUNCTION a un formato encriptado. La salida de los datos encriptados no será visible en ninguna de las vistas de catálogo. Los usuarios que no dispongan de acceso a las tablas del sistema o a los archivos de base de datos no podrán recuperar el texto protegido. Sin embargo, estará disponible para los usuarios con privilegios que puedan obtener acceso a las tablas del sistema

Cláusula SCHEMABINDING

Especifica que la función está enlazada a los objetos de base de datos a los que hace referencia. Cuando se especifica SCHEMABINDING, los objetos base no se pueden modificar de una forma que afecte a la definición de la función. En primer lugar, se debe modificar o quitar la propia definición de la función para quitar las dependencias en el objeto que se va a modificar.

Esto significa que las funciones no pueden alterar ni borrar ningún objeto en la base de datos de la cual dependan, lo que provee seguridad a la información. Por ejemplo, si una función intenta alterar los registros de la tabla tbLibros, SCHEMABINDING evitaría esa alteración, ya que está dentro del esquema del cual depende la función en la base de datos.

Modificar una función en SQL Server

Para modificar una función basta con sustituir CREATE FUNCTION por ALTER FUNCTION y redefinir las instrucciones que se encuentran dentro de la función. Este comando te permite cambiar absolutamente toda la sintaxis de la función siempre que nos refiramos a ella con su mismo nombre original.

Borrar una función en SQL Server

Elimina una función con la sentencia DROP.

DROP FUNCTION reporte_libros;