Post on 20-Jul-2015
DAI - Ing. Arturo Rozas Huacho 2
5.1.- Arquitectura Cliente/ServidorSistemas de Base de Datos
TCP/IPTCP/IP BASE DEDATOS
DAI - Ing. Arturo Rozas Huacho 3
5.1.- Arquitectura Cliente/Servidor…Sistemas de Base de Datos
Servidores
• Servidores de archivosCompart ir documentos, imágenes, planos de ingeniería y otros objetos de grandes dimensiones.
• Servidores de bases de datosIntercambio de mensajes Solicitud/Respuesta mediante instrucciones SQL.
• Servidores de transacciones El cl iente invoca procedimientos remotos que residen en el servidor. Los enunciados de SQL aciertan o fallan todos como una sola unidad.(OLAP: Online Transaction Processing)
DAI - Ing. Arturo Rozas Huacho 4
5.1.- Arquitectura Cliente/Servidor…Sistemas de Base de Datos
Servidores
•Servidores de groupware
Administración de documentos, proceso de imágenes, aplicaciones multicompartidas, flujo de trabajo.
•Servidores de objetos
CORBA, DCOM, etc.
Programación Distribuida
•Servidores WEB
DAI - Ing. Arturo Rozas Huacho 5
5.1.- Arquitectura Cliente/ServidorSistemas de Base de Datos
Middleware
ClienteServidor
Elementos de Construcción de Cliente/Servidor
DAI - Ing. Arturo Rozas Huacho 6
5.1.- Arquitectura Cliente/Servidor…Sistemas de Base de Datos
Servidor
• Espera las solicitudes de los clientes
• Ejecuta muchas solicitudes al mismo t iempo
• Atiende primero a los cl ientes VIP (very important person)
• Emprende y opera actividades de tareas en segundo plano
• Se mantiene ejecutándose permanentemente
• Se vuelve cada vez más grande y más amplio
DAI - Ing. Arturo Rozas Huacho 7
5.1.- Arquitectura Cliente/Servidor…Sistemas de Base de Datos
¿Qué es middleware?
Es un término vago que abarca a todo el software distribuido necesario para el soporte de interacciones entre clientes y servidores.
Es el enlace que permite que un cliente obtenga un servicio de un servidor.
DAI - Ing. Arturo Rozas Huacho 8
5.2.- Servicios de Base de DatosSistemas de Base de Datos
Los servicios de BD se almacenan en la base de datos y pueden ser funciones y procedimientos.
BASE DE DATOS
Aplicación
Aplicación
Aplicación
Datos
Solicitud Servicios (SQL)
Clientes
ServidorServicio 1Servicio 2…Servicio n
DAI - Ing. Arturo Rozas Huacho 9
5.2.- Servicios de BDSistemas de Base de Datos
Los servicios de BD pueden ser de t ipo función o de t ipo procedimiento (stored procedure)
Son un grupo de sentencias SQL que es compilado una vez, y luego puede ser ejecutado muchas veces.
Las funciones y los procedimientos almacenados se almacenan en la base de datos y constituyen los servicios que ofrece la base de datos (Cliente/Servidor).
Las funciones y los procedimientos almacenados cuando se ejecutan no t ienen que ser recompilados cada vez, este hecho hace que sean muy eficientes.
DAI - Ing. Arturo Rozas Huacho 10
5.3.- Funciones Definidas por el UsuarioSistemas de Base de Datos
Se tienen tres t ipos: Escalares: Retornan un t ipo de los datos
como int, money, varchar, real, etc. Pueden ser ut i l izadas dentro de sentencias SQL.
De tabla en Linea: Retornan la salida de una simple declaración SELECT. La salida se puede uti l izar dentro de composiciones o consultas como si fuera una tabla de estándar.
De mult isentencia: Dan como resultado una tabla, puede estar constituido por varias sentencias SQL, y puede l lamar a otras funciones. La salida también se puede uti l izar en operaciones de tabla.
DAI - Ing. Arturo Rozas Huacho 11
5.3.- Funciones Definidas por el Usuario…Sistemas de Base de Datos
Funciones escalares:/* ******************************************** Function que convierte una nota de tipo texto a número ********************************************* */CREATE FUNCTION fnNota (@NotaTexto varchar(3))RETURNS intASBEGIN declare @Nota int; set @Nota = case when @NotaTexto = 'NSP' then 0 else Cast(@NotaTexto as int) end; return(@Nota);END
DAI - Ing. Arturo Rozas Huacho 12
5.3.- Funciones Definidas por el Usuario…Sistemas de Base de Datos
Funciones escalares:/* ********************************************* Function que convierte una nota a número ********************************************** */CREATE FUNCTION fnRecuperarNota (@Semestre varchar(7), @Cod_Asignatura varchar(5), @Cod_Alumno varchar(6))RETURNS intASBEGIN declare @Nota int; select @Nota = dbo.fnNota(Nota) from MATRICULA where (Semestre = @Semestre) and (Cod_Asignatura = @Cod_Asignatura) and (Cod_Alumno = @Cod_Alumno) return(@Nota)END
DAI - Ing. Arturo Rozas Huacho 13
5.3.- Funciones Definidas por el Usuario…Sistemas de Base de Datos
Funciones de tabla en Línea:/* ******************************************************* Function que devuelve las asignaturas aprobadas ******************************************************* */ALTER FUNCTION fnAsignaturasAprobadas(@Cod_CP varchar(2))RETURNS TABLEAS RETURN (SELECT M.Semestre, M.Cod_CP, M.Cod_Alumno,
M.Cod_Asignatura, A.Nombre_Asignatura, A.Creditos, A.Categoria, Nota = dbo.fnNota(M.Nota) FROM MATRICULA M inner join ASIGNATURA A ON (M.Cod_Asignatura = A.Cod_Asignatura) and (M.Cod_CP = A.Cod_CP) WHERE ((M.Cod_CP = @Cod_CP) or (@Cod_Cp = '*')) and (dbo.fnNota(M.Nota) > 10))
DAI - Ing. Arturo Rozas Huacho 14
5.3.- Funciones Definidas por el Usuario…Sistemas de Base de Datos
Funciones de multisentencia:/
* **********************************************************
Function que devuelve los creditos acumulados semestre a
semestre de los alumnos de una carrera o en general
********************************************************** */
CREATE FUNCTION fnCreditosAcumuladosSemestre(@Cod_CP varchar(2))
RETURNS @taCreditosAcumuladosSemestre TABLE (Semestre varchar(7),
Cod_Alumno varchar(6),
Paterno varchar(15),
Materno varchar(15),
Nombres varchar(15),
Cod_Cp varchar(2),
Creditos int,
Creditos_Acum int)
AS
BEGIN
-- Crear tabla temporal para acumular creditaje
declare @taCreditosAcumulados table(Semestre varchar(7),
Cod_Alumno varchar(6),
Creditos int,
Creditos_Acum int)
DAI - Ing. Arturo Rozas Huacho 15
5.3.- Funciones Definidas por el Usuario…Sistemas de Base de Datos
Funciones de multisentencia: -- Recuperar información a la tabla temporal insert into @taCreditosAcumulados select Semestre, Cod_Alumno, sum(Creditos), sum(Creditos) from dbo.fnAsignaturasAprobadas(@Cod_CP) group by Semestre, Cod_Alumno order by Cod_Alumno, Semestre
-- Acumular los creditos por semestre -- Declarar e inicializar variables declare @Cod_Alumno varchar(6), @Creditos_Acum int; set @Cod_Alumno = ''; set @Creditos_Acum = 0;
DAI - Ing. Arturo Rozas Huacho 16
5.3.- Funciones Definidas por el Usuario…Sistemas de Base de Datos
Funciones de multisentencia: -- Acumular creditos update @taCreditosAcumulados set @Creditos_Acum = Creditos_Acum = case when @Cod_Alumno <>
Cod_Alumno then Creditos else @Creditos_Acum +
Creditos end, @Cod_Alumno = Cod_Alumno;
-- Devolver resultado INSERT INTO @taCreditosAcumuladosSemestre SELECT C.Semestre, A.Cod_Alumno, A.Paterno, A.Materno, A.Nombres, A.Cod_CP, C.Creditos, C.Creditos_Acum from @taCreditosAcumulados C inner join ALUMNO A ON C.Cod_Alumno = A.Cod_Alumno RETURNEND
DAI - Ing. Arturo Rozas Huacho 17
5.4- Procedimientos Almacenados…Sistemas de Base de Datos
Se escriben uti l izando el lenguaje de programación de la base de Datos. Permiten declaración de variables, sentencias de asignación, estructuras selectivas y repetit ivas.
Aceptan parámetros de entrada y salida Pueden retornar conjunto de datos así como
también un solo valor. Permiten la programación modular y puede
hacer referencia a otros procedimientos almacenados, con lo que se puede simplif icar una serie de instrucciones complejas.
Pueden ser ejecutados desde otros procedimientos almacenados, vistas, disparadores y consultas en general.
DAI - Ing. Arturo Rozas Huacho 18
5.4- Procedimientos Almacenados…Sistemas de Base de Datos
CREATE PROCEDURE nombre procedimiento
@parametros tipo de dato [= valor],
@parametros_salida tipo de dato OUTPUT
AS
[DECLARE @Variables tipos]
BEGIN
sentencias sql
END
Sintaxis :
DAI - Ing. Arturo Rozas Huacho 19
5.4- Procedimientos Almacenados…Sistemas de Base de Datos
Escribir procedimientos almacenados
CREATE PROCEDURE sp_AlumnosPorCarrera @Cod_Carrera varchar(2)AS BEGIN -- Seleccionar alumnos de la carrera indicada SELECT * FROM ALUMNO WHERE Cod_Carrera = @Cod_CarreraEND
Escribir un procedimiento para determinar la relación de alumnos de una determinada carrera
Constituye el código fuente, se debe almacenar como un archivo de extensión SQL.
Este código se debe compilar y almacenar en la base de datos, para que pueda ser invocado como un servicio.
DAI - Ing. Arturo Rozas Huacho 20
5.4- Procedimientos Almacenados…Sistemas de Base de Datos
[DECLARE @variables tipo de variable]
EXEC/EXECUTE nombre_procedimiento [@parametros], [@variables]
Ejecución de un procedimiento.Sintaxis :
Ejemplo de ejecución:
exec sp_AlumnosPorCarrera ‘IN’
DAI - Ing. Arturo Rozas Huacho 21
5.4- Procedimientos Almacenados…Sistemas de Base de Datos
Ejercicios:Dadas las siguientes tablas
Comunidad( CodComunidad, Nombre)
Prestatario( CodPrestatario, Nombres, Sexo, EstadoCivil, DocIdentidad, CodComunidad)
Oficial_Credito( CodOficial, Nombres, Email )
Prestamo( DocPrestamo, FechaPrestamo, Importe, FechaVencimiento, CodPrestatario, CodOficial )
Amortizacion( DocCancelacion, FechaCancelacion, Importe, DocPrestamo)
DAI - Ing. Arturo Rozas Huacho 22
5.4- Procedimientos Almacenados…Sistemas de Base de Datos
Ejercicios (Problema 1)
create procedure spu_Saldo_Comunidad @CodComunidad varchar(12)asbegin -- Determinar los montos cancelados por cada documento select DocPrestamo, sum(Importe) MontoCancelado into #Montos_Cancelados from Amortizacion group by DocPrestamo
-- Determinar los saldos de cada Prestamo select P.DocPrestamo, P.CodPrestatario, (P.Importe - IsNull(M.MontoCancelado,0)) Saldo into #Saldos_Prestamos from Prestamo P left outer join #Montos_Cancelados M on P.DocPrestamo = M.DocPrestamo
Determinar la relacion de Comunidades, con los saldos de los prestamos pertenecientes a la comunidad.R(CodComunidad, Nombre, Saldo)
DAI - Ing. Arturo Rozas Huacho 23
5.4- Procedimientos Almacenados…Sistemas de Base de Datos
Ejercicios (Problema 1 …)
-- Determinar los saldos de cada prestatario de la comunidad -- especificada select S.CodPrestatario, sum(S.Saldo) Saldo, P.CodComunidad into #Saldos_Prestatario from #Saldos_Prestamos S, Prestatario P where (S.CodPrestatario = P.CodPrestatario) and (P.CodComunidad = @CodComunidad) group by S.CodPrestatario, P.CodComunidad
-- Determinar los saldos de cada comunidad select S.CodComunidad, C.Nombre, sum(S.Saldo) Saldo from #Saldos_Prestatario S, Comunidad C where S.CodComunidad = C.CodComunidad group by S.CodComunidad, C.Nombre
end;
DAI - Ing. Arturo Rozas Huacho 24
5.4- Procedimientos Almacenados…Sistemas de Base de Datos
Ejercicios (Problema 2)
Escribir un procedimiento almacenado que obtenga los movimientos para un determinado prestatario, considerando los saldos por préstamo y los saldos por prestatario. El procedimiento debe mostrar la relación de todos los préstamos del prestatario y para cada préstamo sus respectivas cancelaciones, tal como se i lustra en el ejemplo siguiente:
R(FechaMov,DocPrestamo,DocCancelacion,Debe,Haber,SaldoPrestamo,SaldoTotal) 02/02/06 PA-101 1000 1000 1000 31/03/06 RC-421 200 800 800 30/04/06 RC-467 300 500 500 15/04/06 PA-246 1600 1600 2100 30/04/06 RC-478 700 900 1400 31/05/06 RC-523 900 0 500 ...
DAI - Ing. Arturo Rozas Huacho 25
5.4- Procedimientos Almacenados…Sistemas de Base de Datos
Ejercicios (Problema 2 …)
create procedure sp_Prestatario_Rural_Movimientos @CodPrestatario varchar(15)asbegin -- Crear tabla vacia de movimientos create table #Movimientos ( Fecha_Mov DateTime, DocPrestamo varchar(15), DocCancelacion varchar(15), Debe numeric(15,2), Haber numeric(15,2), SaldoPrestamo numeric(15,2), SaldoTotal numeric(15,2) )
DAI - Ing. Arturo Rozas Huacho 26
5.4- Procedimientos Almacenados…Sistemas de Base de Datos
Ejercicios (Problema 2 …)
-- Recuperar Prestamos y cancelaciones de movimientos -- Declarar variables utilizadas por el cursor declare @DocPrestamo varchar(15), @FechaPrestamo DateTime, @ImportePrestamo numeric(15,2), @SaldoPrestamo numeric(15,2), @SaldoTotal numeric(15,2); -- Inicializar saldo set @SaldoPrestamo = 0; set @SaldoTotal = 0; -- Declarar el cursor declare cursor_Prestamo cursor for select DocPrestamo, FechaPrestamo, ImportePrestamo from Prestamo where CodPrestatario = @CodPrestatario; -- Abrir el cursor open cursor_Prestamo;
DAI - Ing. Arturo Rozas Huacho 27
5.4- Procedimientos Almacenados…Sistemas de Base de Datos
Ejercicios (Problema 2 …) -- Activar la primera fila del cursor fetch next from cursor_Prestamo into @DocPrestamo, @FechaPrestamo, @ImportePrestamo -- Procesar cada fila repetitivamente while @@FETCH_STATUS = 0 begin -- Inicializar saldo prestamo set @SaldoPrestamo = @ImportePrestamo; -- Actualizar saldo total set @SaldoTotal = @SaldoTotal + @ImportePrestamo; -- Insertar prestamo en tabla de movimientos insert into #Movimientos values(@FechaPrestamo,@DocPrestamo,'', @ImportePrestamo,0,@SaldoPrestamo, @SaldoTotal) -- Procesar cancelaciones del prestamo declare @DocCancelacion varchar(15), @FechaCancelacion DateTime, @ImporteCancelacion numeric(15,2);
DAI - Ing. Arturo Rozas Huacho 28
5.4- Procedimientos Almacenados…Sistemas de Base de Datos
Ejercicios (Problema 2 …) -- Declarar el cursor anidado declare cursor_Cancelacion cursor for select DocCancelacion, FechaCancelacion, ImporteCancelacion from Cancelacion where DocPrestamo = @DocPrestamo; -- Abrir el cursor open cursor_Cancelacion; -- Activar la primera fila del cursor fetch next from cursor_Cancelacion into @DocCancelacion, @FechaCancelacion, @ImporteCancelacion -- Procesar cada fila repetitivamente while @@FETCH_STATUS = 0 begin -- Actualizar saldos set @SaldoPrestamo = @SaldoPrestamo - @ImporteCancelacion; set @SaldoTotal = @SaldoTotal - @ImporteCancelacion;
DAI - Ing. Arturo Rozas Huacho 29
5.4- Procedimientos Almacenados…Sistemas de Base de Datos
Ejercicios (Problema 2 …) -- Insertar prestamo en tabla de movimientos insert into #Movimientos values(@FechaCancelacion,'',@DocCancelacion, 0, @ImportePrestamo, @SaldoPrestamo, @SaldoTotal) -- Siguiente registro fetch next from cursor_Cancelacion into @DocCancelacion, @FechaCancelacion, @ImporteCancelacion end; -- while -- Cerrar Cursor close cursor_Cancelacion; deallocate cursor_Cancelacion; -- Siguiente registro de prestamo fetch next from cursor_Prestamo into @DocPrestamo, @FechaPrestamo, @ImportePrestamo end; -- while -- Cerrar Cursor close cursor_Prestamo; deallocate cursor_Prestamo; -- Mostrar Movimientos select * from #Movimientosend;