hermosaprogramacion.com
http://www.hermosaprogramacion.com http://www .hermosaprogramacion.com/2014/06/sql-server-function/ /2014/06/sql-server-function/
Crear Funciones de Usuario en SQL Server James Revelo
Como habíamos visto en el articulo de funciones en MySQL, Como MySQL, una función función es es una ruti rutina na almacenada 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 MySQL las funciones definidas por el usuario en usuario en SQL SQL Server Server permiten permiten retornar tablas en los resultados . Esta característica brinda al programador facilidad a la hora de administrar sus bases de datos. datos. Existen 3 tipos: Funciones escalares escalares Funciones con valores de tabla en linea Funciones con valores de tabla y múltiples instrucciones La sintaxis sintaxis de creación creación de las tres es muy similar, solo se diferencian en el tipo de parámetros que retornan.
Funciones escalares en SQL Server Las funciones escalares son aquellas que reciben 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 no permite que las funciones escalares retornen en los tipos text, ntext, image, cursor y timestamp. Al igual que MySQL 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 instrucciones como en los procedimientos.. procedimientos Veamos la sintaxis de creación: CREATE FUNCTION nombre_función nombre_función ( [parámetro1, [parámetro1, parámetro2,...]) RETURNS tipo_de_dato [WITH ENCRYPTION | WITH SCHEMABINDING] [AS] BEGIN RETURN RETUR N valor_retornado 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.
Ejemplo de una función escalar de una Aerolínea 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 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
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 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.
Ejemplo de función de tabla sobre Tienda Digital 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éramossumar 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.
Funciones 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 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.
Ejemplo de función con Múltiple Instrucciones 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);
SCHEMABINDING en SQL Server 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.
Modificar una función definida por el usuario en SQL Server Para modificar una función debes usar 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;
Borrar una función definida por el usuario en SQL Server Elimina una función definida por el usuario con la famosa sentencia DROP. Mira como borramos una función que pronostica la cantidad de huéspedes futuros de un Hotel. DROP FUNCTION pronostico_anual_huespedes;
