Practica 6

TEMA: TABLAS CRUZADAS Y CURSORES 1.- OBJETIVOS Al finalizar la práctica el estudiante estará en condiciones de:

-

Utilizar cursores para procesar los datos de una tabla, fila a fila.

2.- TRABAJO PREPARATORIO -

Estudiar conceptos de Cursores.

3.- BASE TEORICA COMPLEMENTARIA Las operaciones de una base de datos relacional actúan sobre un conjunto completo de filas. La sentencia SELECT actúa sobre tablas, y los resultados que devuelven también son tablas, que estás compuesto por todas las filas que satisfacen las condiciones de la cláusula WHERE de la instrucción. Las aplicaciones, especialmente las aplicaciones interactivas en línea, no siempre pueden trabajar de forma efectiva con el conjunto de resultados completo como una unidad. Estas aplicaciones necesitan un mecanismo que funcione con una fila o un pequeño bloque de filas a la vez. Los cursores son una extensión de los conjuntos de resultados que proporcionan el mecanismo de proceso fila afila o pequeños bloques de filas a la vez. Los cursores amplían el procesamiento de los resultados debido a que: •

Permiten situarse en filas específicas del conjunto de resultados.

•

Recuperan una fila o bloque de filas de la posición actual en el conjunto de resultados.

•

Aceptan modificaciones de los datos de las filas en la posición actual del conjunto de resultados

•

Aceptan diferentes grados de visibilidad de los cambios que realizan otros usuarios en los datos de la base de datos que se presentan en el conjunto de resultados.

•

Proporcionan instrucciones de Transact-SQL en secuencias de comandos, procedimientos almacenados y acceso de desencadenadores a los datos de un conjunto de resultados.

Proceso con cursores A continuación se muestra una plantilla del código que utiliza cursores. Las partes del código que se muestran en negrita son las palabras reservadas asociadas al manejo con cursores.

-- Declarar variables en las que se recuperará los atributos de cada fila declare @Variable1 tipo1, @Variable2 tipo2, ... -- Declarar el cursor declare nombre_cursor cursor for select ... -- Abrir el cursor open nombre_cursor -- Recuperar primer registro fecth next from nombre_cursor into @Variable1, @Variable2, … -- Bucle para procesar cada fila del cursor while @@Fetch_Status = 0 begin -- Aquí porner las instrucciones que procesen cada fila ... -- Siguiente registro fecth next from nombre_cursor into @Variable1, @Variable2, … end; -- while -- Cerrar cursor close nombre_cursor -- Liberar cursor deallocate nombre_cursor

4.- CONTENIDO DE LA PRÁCTICA. Para realizar esta práctica efectuar los siguiente: • •

Cargar el analizador de consultas de SQL-SERVER Crear la base de datos de DBCreditoRural en base a los scripts proporcionados por el docente.

Parte 1.- Tablas Cruzadas Problema 1.-

Determinar los importes prestados en cada mes del 2007 en las comunidades C001, C002 y C003. R(Mes, C001, C002, C003)

Digitar y ejecutar el siguiente script. /* ************************************************************** ***** PROBLEMA 1 ***** ************************************************************** */ /* Determinar los importes prestados en cada mes del 2007 en las comunidades C001, C002 y C003 R(Mes, C001, C002, C003) */ begin -- Seleccionar prestamos del 2007 select CodPrestatario, Month(FechaPrestamo) Mes, Importe into #T1 from Prestamo where Year(FechaPrestamo) = 2007 -- Recuperar la comunidad a la que pertenece cada prestamo select Mes, CodComunidad, Importe into #T2 from #T1 T inner join Prestatario P

on T.CodPrestatario = P.CodPrestatario -- Efectuar el Cross Table select Mes, "C001" = sum(case when CodComunidad = 'C001' then Importe else 0 end), "C002" = sum(case when CodComunidad = 'C002' then Importe else 0 end), "C003" = sum(case when CodComunidad = 'C002' then Importe else 0 end) from #T2 group by Mes end;

Al ejecutar el script se debe mostrar los siguientes datos:

Problema 2.-

Determinar los importes prestados y los saldos por cobrar en cada mes del 2007 en las comunidades C001, C002 y C003. R(Mes, C001_Pres, C001_Sal, C002_Pres, C002_Sal, C003_Pres, C003_Sal)

La ejecución del respectivo script debe mostrar el siguiente resultado:

Problema 3.-

Determinar los importes prestados en cada mes del 2007 en todas las comunidades. Por ejemplo, si hay 5 comunidades la tabla resultante debe ser similar a: R(Mes, C001, C002, C003, C004, C005) Nota.- No se conoce el número de comunidades

La ejecución del respectivo script debe mostrar el siguiente resultado:

Parte 2.- Cursores Problema 1.-

Determinar la relación de saldos acumulados para un determinado prestatario. Considerar sólo los préstamos con saldo. R(DocPrestamo,FechaPrestamo,Importe,FechaVencimiento,Saldo,SaldoAcumulado)

Digitar y ejecutar el siguiente script: /* ************************************************************** ***** PROBLEMA 1 ***** ************************************************************** */ /* Determinar la relacion de saldos acumulados para un determinado prestatario. Considerar solo los prestamos con saldo. R(DocPrestamo,FechaPrestamo,Importe,FechaVencimiento,Saldo,SaldoAcumulado) */ begin -- Inicializar Prestatario para el que se desea sus movimientos declare @CodPrestatario varchar(12); set @CodPrestatario = 'P03'; -- Crear tabla vacía de saldos acumulados select TOP 0 DocPrestamo, FechaPrestamo, Importe, FechaVencimiento, cast(0 as numeric(12,2)) Saldo, cast(0 as numeric(12,2)) SaldoAcumulado

into #TMP_SaldosAcumulados from Prestamo -- Determinar saldos de prestamos del prestatario select p.DocPrestamo, p.FechaPrestamo, p.Importe, p.FechaVencimiento, p.Importe-sum(IsNull(A.Importe,0)) as saldo into #TMP_Prestamo from Prestamo p Left outer Join Amortizacion A on P.DocPrestamo = A.DocPrestamo where p.CodPrestatario = @CodPrestatario group by p.DocPrestamo, p.FechaPrestamo, p.Importe,p.FechaVencimiento -- Recorrer registro a registro la tabla de prestamos, y agregar solo los -- prestamos con saldo a la tabla de saldos acumulados -- Declarar variables utilizadas por el cursor declare @DocPrestamo varchar(12), @FechaPrestamo DateTime, @Importe numeric(12,2), @FechaVencimiento DateTime, @Saldo numeric(12,2), @SaldoAcumulado numeric(12,2) -- Inicializar las variables set @SaldoAcumulado = 0; -- Declarar el cursor declare cursor_Prestamo cursor for select DocPrestamo, FechaPrestamo, Importe, FechaVencimiento, Saldo from #TMP_Prestamo; -- Abrir el cursor open cursor_Prestamo; -- Activar la primera fila del cursor fetch next from cursor_Prestamo into @DocPrestamo, @FechaPrestamo, @Importe, @FechaVencimiento, @Saldo -- Procesar cada fila repetitivamente while @@FETCH_STATUS = 0 begin -- Procesar solo los prestamos con saldo if @Saldo > 0 begin -- Acumular el saldo set @SaldoAcumulado = @SaldoAcumulado + @Saldo -- Insertar en la tabla de saldos acumulados insert into #TMP_SaldosAcumulados values(@DocPrestamo, @FechaPrestamo, @Importe, @FechaVencimiento, @Saldo, @SaldoAcumulado); end; -- Siguiente registro fetch next from cursor_Prestamo into @DocPrestamo, @FechaPrestamo, @Importe, @FechaVencimiento, @Saldo end; -- while -- Cerrar Cursor close cursor_Prestamo; deallocate cursor_Prestamo; -- Mostrar la tabla de saldos acumulados select * from #TMP_SaldosAcumulados end;

La ejecución del respectivo script debe mostrar el siguiente resultado:

Problema 2.-

Determinar la relación de los tres prestatarios rurales más representativos de cada comunidad. Los más representativos son los que tienen mayor número de préstamos.

La ejecución del respectivo script debe mostrar el siguiente resultado:

Problema 3.-

Determinar los movimientos de un prestatario. Debe mostrar cada préstamo con sus respectivas cancelaciones. Los importes del préstamo van en la columna del Debe y los importes de las cancelaciones van al haber. Considerar que un prestatario puede tener varios préstamos.

La ejecución del respectivo script debe mostrar el siguiente resultado: