• Author / Uploaded
• Jorge Luis Binda Casaboza

Citation preview

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 por finalmente retorna en 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.

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 ANDstrNomb re = @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 DAT ETIME,@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 Mov b ONInvb.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 ('ISBN0000000 0') 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

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,GETDA TE()) 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;