Unidad 2 - Vistas y Funciones

1. Unidad 2 - Vistas y Funciones Rocío Contreras Aguila Primer Semestre 2010

2. ¿Qué es una vista? • Una vista es una tabla virtual que muestra la información relevante para el usuario además que permite encapsular la complejidad de su implementación. • Una vista puede mostrar toda la información de una tabla o de la integración de información de más de una tabla. Es decir se puede afirmar que las vistas trabajan como especie de filtros de las tablas subyacentes que brindan la información presentada por la vista.

3. ¿Qué es una vista? • Entre las principales ventajas del empleo de las vistas podemos mencionar: • Permite mostrar un subconjunto de filas y/o columnas de una tabla. • Permite mostrar información de más de una tabla. • Permite realizar uniones entre dos o más tablas. • Permite mostrar informes resumen.

4. ¿Qué es una vista? • Otra de las ventajas de las vistas es que pueden generarse a partir de consultas distribuidas entre orígenes de datos heterogéneos, de tal manera que los usuarios invocan a las vistas en lugar de estar digitando complejos querys.

5. ¿Qué es una vista? • Antes de implementar sus vistas tenga en cuenta las siguientes consideraciones: • Sólo pueden crearse vistas en la base de datos activa, aunque las tablas y/o vistas que son parte de la definición puedan encontrarse en distintas bases de datos. • Se pueden crear vistas a partir de otras vistas. • No se pueden asociar defaults, rule y/o desencadenadores (Trigers) a una vista. • La consulta que forma la vista no puede incluir las cláusulas ORDER BY, COMPUTE o COMPUTE BY • No se pueden construir índices sobre las vistas • No se pueden crear vistas temporales, ni vistas basadas en tablas temporales. • Cada una de las columnas empleadas en la vista debe tener un encabezado.

6. Crear una Vista • Para crear vistas lo podemos hacer desde el administrador corporativo o a partir de la sentencia CREATE VIEW, cuya sintaxis es: CREATE VIEW <Nombre de la vista> [Encabezado1, ....] [WITH ENCRYPTION] AS Sentencias Select [WITH CHECK OPTION]

7. Crear una Vista • La cláusula WITH ENCRYPTION, permite ocultar la implementación de la vista, la cual puede ser vista con el stored procedure del sistem sp_helptext o desde la tabla del sistema syscomments. • La cláusula WITH CHECK OPTION, garantiza que los cambios hechos desde la vista puedan ser observados una vez que la operación finaliza.

8. Ejemplo 1 • Generar la base de datos con las siguientes tablas: • Alumnos {# codalu, nom, pat, mat} • Inscritos {# codalu,FK sec} • Secciones {#sec, FK codprofe, inicio, pension, vacantes, numhoras} • Calificaciones {FK codalu, FK sec, n1, n2} • Profesores {#codprofe, nombre, apellidos, categ, fecha_ing,pension}

9. Ejemplo 1

10. Ejemplo La vista relacion_de_promedios SELECT inscritos.coddalu as codigo, alumnos.nom + ' ' + alumnos.pat as nomape, inscritos.sec as seccion, profesores.nombre as profesor, (calificaciones.n1 + calificaciones.n2)/2 as promedio FROM alumnos, secciones,inscritos,calificaciones,profesores WHERE alumos.codalu=inscritos.codalu and inscritos.sec=secciones.sec and secciones.codprofe=profesores.codprofe and inscritos.sec=calificaciones.sec and inscritos.codalu=calificaciones.codalu and calificaciones.sec=secciones.sec

11. Ejemplo 1 • Para comprobar la creación de la vista en el Analizador de Consultas digite la siguiente instrucción: Select * From Relacion_de_Promedios GO Ver la sentencia que implementa la vista: Sp_HelpText Relacion_de_Promedios GO

12. Ejemplo 1 • Ver las columnas que se presentan en la vista: Sp_Depends Relación de Promedios GO • Desde el Query Anallyzer debe crear una vista que nos permita mostrar un informe resumen por sección que muestre la sección, el nombre del profesor, el total de alumnos, el mayor y menor promedio, el promedio de la sección y el monto acumulado de las pensiones.

13. Ejemplo 2 CREATE VIEW Resumen AS Select Inscritos.Sec As Seccion, MAX(nombre) As Profesor, Count(Inscritos.Codalu) As Alumnado, Max((N1+N2)/2) As MayorPromedio, Min((N1+N2)/2) As MenorPromedio, Avg((N1+N2)/2) As PromedioSec, Sum(Pension) As Acumulado From Inscritos INNER JOIN Calificaciones OnInscritos.codAlu = Calificaciones.codalu AND Inscritos.sec = Calificaciones.Sec INNER JOIN Secciones On Inscritos.Sec = Secciones.Sec INNER JOIN Profesores OnSecciones.CodProfe = Profesores.CodProfe GroupbyInscritos.Sec GO

14. Ejemplo 2 • Compruebe la información que devuelve la vista: Select * From Resumen GO • Mostrar la implementación de la vista: Sp_HelpText Resumen GO

15. Modificar Vistas • Para modificar la vista utilice la siguiente sintaxis: ALTER VIEW <Nombre de la Vista> [(Encabezado1, ...)] [WITH ENCRYPTION] AS <Sentencia SELECT> [WITH CHECK OPTION]

16. Modificar Vistas: ejemplo 4 ALTER VIEW RESUMEN WITH ENCRYPTION AS Select Inscritos.Sec As Seccion, MAX(nombre) As Profesor, Count(Inscritos.Codalu) As Alumnado, Max((N1+N2)/2) As MayorPromedio, Min((N1+N2)/2) As MenorPromedio, Avg((N1+N2)/2) As PromedioSec, Sum(Pension) As Acumulado From Inscritos INNER JOIN Calificaciones OnInscritos.codAlu = Calificaciones.codalu AND Inscritos.sec = Calificaciones.Sec INNER JOIN Secciones On Inscritos.Sec = Secciones.Sec INNER JOIN Profesores OnSecciones.CodProfe = Profesores.CodProfe GroupbyInscritos.Sec GO

17. Modificar Vistas: Ejemplo 4 • Ahora muestre la implementación de la vista con el siguiente comando: Sp_HelpText Resumen GO • Como se observa en el panel de resultados ahora ya no se muestra la implementación de la vista. • Lo mismo ocurrirá si consulta la información de la tabla del sistema SysComments. Select * FromSysComments GO

18. Eliminar Vistas • Para eliminar una vista emplear la siguiente sintaxis: DROP VIEW <Nombre de la vista> [,...n] • Como ejemplo podría utilizar la siguiente instrucción: DROP VIEW [Relacion_de_Promedios] GO Select * From [Relacion de Promedios] GO

19. Funciones • Una función en SQL Server se maneja bajo el mismo concepto que una función en un lenguaje de programación. • Es un sub algoritmo, que recibe, desarrolla y devuelve parámetros.

20. Como funcionan? • Una función en Sql Server, se comporta de la misma forma que las funciones que conocemos tradicionalmente, es decir devolviendo un valor. • Ese valor puede ser cualquier valor escalar, incluidas filas. • Se pueden asemejar a vistas con parámetros

21. Ejemplo 1 create function OrdersMonth(@MyMonth as int) returns tableasreturn (select * from orders where month(OrderDate)=@MyMonth)

22. Ejemplo 2 Create FUNCTION Suma (@a int, @b int) RETURNS int AS BEGIN DECLARE @c int SET @c = @a + @b RETURN (select @c) END

23. Limitaciones • Las funciones definidas por el usuario tienen algunas restricciones. • No todas las sentencias SQL son válidas dentro de una función.

24. Válidas • Las sentencias de asignación • Las sentencias de Control de Flujo • Sentencias SELECT • Modificación de variables locales • Operaciones de cursores sobre variables locales Sentencias INSERT, UPDATE, DELETE con variables Locales

25. Inválidas • Armar funciones no determinadas como GetDate() • Sentencias de modificación o actualización de tablas o vistas • Operaciones CURSOR FETCH que devuelven datos del cliente

26. Ejemplo 3 CREATE FUNCTION [dbo].[funcDatePart]( variable de entrada y tipo @DateTypevarchar(10), @Date Datetime ) RETURNS intASBEGINDECLARE *agregamos las variables que necesitemos para realizar la funcion@PeriodintSELECT @Period = (CASEWHEN @DateType = 'year' THEN datepart(year,@Date)WHEN @DateType = 'month' THEN datepart(month,@Date)WHEN @DateType = 'quarter' THEN datepart(quarter,@Date)WHEN @DateType = 'semester' THEN(CASEWHEN datepart(month,@Date) between 1 and 6THEN '1'WHEN datepart(month,@Date) between 7 and 12THEN '2'END)ELSE'0'END) *Retorno el valorRETURN @PeriodEND

27. Ejemplo 4 • CreatefunctionNumeroALetra(@numero int )returnsvarchar(200)asbeginDeclare @rango intDeclare @Resultado varchar(200)Declare @ResultAuxvarchar(200)Declare @auxvarchar(20)set @resultado='' set @resultaux=''set @aux =convert(varchar,@numero)Set @rango= len(@aux) • Si estamos por encima de 1000000 todo vuelve a ser igual que por debajo. • Trescientos cincuenta y un mil cuatrocientos treinta millones doscientas cincuenta mil tres • Si estuviese solo la primera parte sería igual pero sin millones.

28. Ejemplo 4 if @rango>6 beginset @Resultado = dbo.NumeroALetra(convert(int,left(@aux,len(@aux)-6))) + ' millones 'set @aux=right(@aux,6)end-- por aquí el rango siempre va a ser <=6 o si es mayor no me importaset @aux = convert(varchar,convert(int,@aux))-- si es 000345 solo interesa la última parte...set @rango=len(@aux)if @rango>3 beginset @Resultado =isnull(@resultado,'')+ dbo.NumeroALetra(convert(int,left(@aux,len(@aux)-3))) + ' mil 'set @aux=right(@aux,3)endelseset @aux = right('000'+@aux,3) -- necesito que tenga tres dígitos para que -- el algoritmo funcione bien....select @resultAux =case left(@aux,1) when '1' then 'Ciento 'when '2' then 'Doscientos 'when '3' then 'Trescientos 'when '4' then 'Cuatrocientos 'when '5' then 'Quinientos 'when '6' then 'Seiscientos 'when '7' then 'Setecientos 'when '8' then 'Ochocientos 'when '9' then 'Novecientos 'else ''endset @aux=right(@aux,2)

29. Ejemplo 4 • if convert(int,@aux)<16 beginselect @resultaux = isnull(@resultaux,'') +case convert(int,@aux)when 1 then 'Uno'when 2 then 'Dos'when 3 then 'Tres'when 4 then 'Cuatro'when 5 then 'Cinco'when 6 then 'Seis'when 7 then 'Siete'when 8 then 'Ocho 'when 9 then 'Nueve 'when 10 then 'Diez 'when 11 then 'Once 'when 12 then 'Doce 'when 13 then 'Trece 'when 14 then 'Catorce 'when 15 then 'Quince ' else ''endendelse -- es un número de dos cifras >15beginselect @resultaux=isnull(@resultaux,'')+case left(@aux,1)when '1' then 'Diez 'when '2' then 'Veinte 'when '3' then 'Treinta 'when '4' then 'Cuarenta 'when '5' then 'Cincuenta 'when '6' then 'Sesenta 'when '7' then 'Setenta 'when '8' then 'Ochenta 'when '9' then 'Noventa 'else ''end

30. Ejemplo 4 • ifright(@aux,1)<>'0' beginifleft(@aux,1)='1'set @resultaux=left(@resultaux,len(@resultaux)-1)+'ci'else-- Esto de arriba es el arreglo para poner diez o dieciseis.....set @resultaux = @resultaux + 'y 'endselect @resultaux=isnull(@resultaux,'')+case right(@aux,1)when '1' then 'Uno'when '2' then 'Dos'when '3' then 'Tres'when '4' then 'Cuatro'when '5' then 'Cinco'when '6' then 'Seis'when '7' then 'Siete'when '8' then 'Ocho 'when '9' then 'Nueve ' else ''endendset @resultado=isnull(@resultado,'') +isnull(@resultaux,'')if @resultado='' set @resultado='Cero.' return @resultadoend

31. Llamada • Select nombreusuario.nombrefunción(valores) • Ej: select Suma(4,5)

32. Funciones en Transact SQL • SQL Server proporciona al usuario la posibilidad de definir sus propias funciones, conocidad como UDF (user defined functions). Existen tres tipos de funciones. Estas son: • Funciones escalares. • Funciones en línea. • Funciones en línea de múltiples sentencias

33. Funciones escalares • Las funciones escalares devuelven un único valor de cualquier tipo de los datos tal como int, money, varchar, real, etc. • Para utilizar una función escalar debemos identificar el nombre de la función con el propietario de la misma. • Las funciones escalares son muy similares a procedimientos almacenados con parámetros de salida, pero estas pueden ser utilizadas en consultas de seleccion y en la clausula where de las mismas.

34. Sintaxis • La sintaxis para una función escalar es la siguiente: CREATE FUNCTION <Scalar_Function_Name, sysname, FunctionName> ( -- Lista de parámetros <@Param1, sysname, @p1> <Data_Type_For_Param1, , int>, ... ) -- Tipo de datos que devuelve la función. RETURNS <Function_Data_Type, ,int> AS BEGIN ... END

35. Ejemplo CREATE FUNCTION fn_MultiplicaSaldo ( @NumCuenta VARCHAR(20),@Multiplicador DECIMAL(10,2)) RETURNS DECIMAL(10,2) AS BEGIN DECLARE @Saldo DECIMAL(10,2), @Return DECIMAL(10,2) SELECT @Saldo = SALDO FROM CUENTAS WHERE NUMCUENTA = @NumCuenta SET @Return = @Saldo * @Multiplicador RETURN @Return END

36. Ejecución Funciones Escalares • La función ejecutará sus sentencias SELECT una vez por cada fila del conjunto de resultados devuelto por la consulta SELECT principal. SELECT IDCUENTA, NUMCUENTA, SALDO, FXALTA, -- Ejecucion de la funcion: dbo.fn_MultiplicaSaldo( NUMCUENTA, IDCUENTA) AS RESULTADO FROM CUENTAS

37. Ejemplo en un script DECLARE @NumCuenta VARCHAR(20), @Resultado DECIMAL(10,2) SET @NumCuenta = '200700000001' SET @Resultado = dbo.fn_MultiplicaSaldo(@NumCuenta, 30.5) PRINT @Resultado

38. Funciones en linea • Las funciones en línea son las funciones que devuelven un conjunto de resultados correspondientes a la ejecución de una sentencia SELECT. • No podemos utilizar la clausula ORDER BY en la sentencia de una función el línea. • Las funciones en línea pueden utilizarse dentro de joins o querys como si fueran una tabla normal.

39. Sintaxis CREATE FUNCTION <Inline_Function_Name, sysname, FunctionName> (-- Lista de parámetros <@param1, sysname, @p1> <Data_Type_For_Param1, , int>,... ) RETURNS TABLE AS RETURN ( -- Sentencia Transact SQL )

40. Ejemplo CREATE FUNCTION fn_MovimientosCuenta ( @NumCuenta VARCHAR(20) ) RETURNS TABLE AS RETURN ( SELECT MOVIMIENTOS.* FROM MOVIMIENTOS INNER JOIN CUENTAS ON MOVIMIENTOS.IDCUENTA = CUENTAS.IDCUENTA WHERE CUENTAS.NUMCUENTA = @NumCuenta )

41. Ejecución SELECT * FROM fn_MovimientosCuenta('200700000001') SELECT * FROM CUENTAS INNER JOIN CUENTAS_CLIENTE ON CUENTAS_CLIENTE.IDCUENTA = CUENTAS.IDCUENTA INNER JOIN CLIENTES ON CLIENTES.id = CUENTAS_CLIENTE.IDCLIENTE INNER JOIN fn_MovimientosCuenta('200700000001') A ON A.IDCUENTA= CUENTAS.IDCUENTA

42. Funciones en línea de múltiples sentencias • Las funciones en línea de múltiples sentencias son similares a las funciones en línea excepto que el conjunto de resultados que devuelven puede estar compuesto por la ejecución de varios consultas SELECT. • Este tipo de función se usa en situaciones donde se requiere una mayor lógica de proceso.

43. Funciones en línea de múltiples sentencias La sintaxis para una funciones de tabla de multi sentencias es la siguiente: CREATE FUNCTION <Table_Function_Name, sysname, FunctionName> (-- Lista de parámetros <@param1, sysname, @p1> <data_type_for_param1, , int>, … ) RETURNS -- variable de tipo tabla y su estructura <@Table_Variable_Name, sysname, @Table_Var> TABLE ( <Column_1, sysname, c1> <Data_Type_For_Column1, , int>, <Column_2, sysname, c2> <Data_Type_For_Column2, , int> ) AS BEGIN -- Sentencias que cargan de datos la tabla declarada RETURN END

44. Ejemplo Esta funcion busca la tres cuentas con mayor saldo y obtiene los tres últimos movimientos de cada una de éstas : CREATE FUNCTION fn_CuentaMovimietos() RETURNS @datos TABLE ( -- Estructura de la tabla que devuelve la funcion. NumCuentavarchar(20), Saldo decimal(10,2), Saldo_anterior decimal(10,2), Saldo_posterior decimal(10,2), Importe_Movimiento decimal(10,2), FxMovimientodatetime )

45. Ejemplo AS BEGIN -- Variables necesarias para la lógica de la funcion. DECLARE @idcuentaint, @numcuentavarchar(20), @saldo decimal(10,2) -- Cursor con las 3 cuentas de mayor saldo DECLARE CDATOS CURSOR FOR SELECT TOP 3 IDCUENTA, NUMCUENTA, SALDO FROM CUENTAS ORDER BY SALDO DESC

46. Ejemplo OPEN CDATOS FETCH CDATOS INTO @idcuenta, @numcuenta, @saldo -- Recorremos el cursor WHILE (@@FETCH_STATUS = 0) BEGIN -- Insertamos la cuenta en la variable de salida INSERT INTO @datos (NumCuenta, Saldo) VALUES (@numcuenta, @saldo) -- Insertamos los tres últimos movimientos de la cuenta

47. Ejemplo INSERT INTO @datos (Saldo_anterior, Saldo_posterior, Importe_Movimiento, FxMovimiento ) SELECT TOP 3 SALDO_ANTERIOR, SALDO_POSTERIOR, IMPORTE, FXMOVIMIENTO FROM MOVIMIENTOS WHERE IDCUENTA = @idcuenta ORDER BY FXMOVIMIENTO DESC -- Vamos a la siguiente cuenta FETCH CDATOS INTO @idcuenta, @numcuenta, @saldo END CLOSE CDATOS; DEALLOCATE CDATOS; RETURN END

48. Ejecución select * from fn_CuentaMovimietos()