SandovalCastroTellezGonzalez_PC13 - Jorge González

Vista previa del material en texto

UNIVERSIDAD NACIONAL AUTÓNOMA DE MÉXICO
FACULTAD DE INGENIERÍA
Bases de Datos (T)
Grupo: 05 - Semestre: 2022-1
Práctica Complementaria 13:
Programación con SQL. Parte 2.
FECHA DE ENTREGA: 22/12/2021
Profesor:
Rodríguez Campos Jorge Alberto Ing.
Alumnos:
Téllez González Jorge Luis
Sandoval Castro Josmar Leonardo
Facultad de Ingeniería Bases de Datos______________________________________________________________________________________________________________
1. Objetivo
Poner en práctica los conceptos de programación PL/SQL para realizar el correcto manejo de
cursores, procedimientos almacenados y objetos LOB.
2. C1. Código
Ejercicio 1: Procedimiento de generación de pagos
--@Autor: Sandoval Castro Josmar Leonardo, Téllez González Jorge Luis
--@Fecha creación: 19/12/2021
--@Descripción: Script para la generación de pagos.
set serveroutput on
create or replace procedure sp_genera_pagos_tarjeta(
p_importe in number
)
is
v_num_pago_anterior number;
v_num_pago number;
v_pagos_registrados number := 0;
v_importe_total number := 0;
--Cursor: selecciona clientes que no han cubierto el precio del auto.
cursor cur_cliente_tarjeta is
select c.cliente_id, a.auto_id, nvl(max(pa.num_pago),0) numero_ultimo_pago
from cliente c
join tarjeta_cliente tc
on c.cliente_id = tc.cliente_id
join auto a
on c.cliente_id = a.cliente_id
left join pago_auto pa
on a.auto_id = pa.auto_id
group by c.cliente_id, a.auto_id, a.precio
having sum(nvl(pa.importe,0)) < a.precio
order by cliente_id, auto_id;
--Para cada cliente, recoge su último número de pago y obtiene el pago siguiente.
--Con lo anterior, registra el pago con el valor de entrada.
begin
for c in cur_cliente_tarjeta loop
v_num_pago_anterior := c.numero_ultimo_pago;
v_num_pago := v_num_pago_anterior + 1;
insert into pago_auto(num_pago, auto_id, fecha_pago, importe)
values(v_num_pago, c.auto_id, sysdate, p_importe);
v_pagos_registrados := v_pagos_registrados + 1;
v_importe_total := v_importe_total + p_importe;
end loop;
dbms_output.put_line('Pagos registrados: '|| v_pagos_registrados);
dbms_output.put_line('Total del importe: '|| v_importe_total);
end;
/
show errors;
1
Facultad de Ingeniería Bases de Datos______________________________________________________________________________________________________________
Ejercicio 1: Script de prueba
--@Autor: Castro Josmar Leonardo, Téllez González Jorge Luis
--@Fecha creación: 19/12/2021
--@Descripción: Bloque anónimo de prueba para
--el script s-01-genera-pagos-tarjeta.
declare
v_num_pagos_antes_insercion number;
v_num_teorico_inserciones number;
v_num_esperado_pagos number;
v_num_pagos_despues number;
v_auto_id number;
v_cliente_id number;
v_precio number;
v_deuda number;
cursor cur_por_actualizar is
select c.cliente_id, a.auto_id, a.precio, (a.precio- nvl(sum(pa.importe),0))
deuda from cliente c
join tarjeta_cliente tc
on c.cliente_id = tc.cliente_id
join auto a
on c.cliente_id = a.cliente_id
left join pago_auto pa
on a.auto_id = pa.auto_id
group by c.cliente_id, a.auto_id, a.precio
having sum(nvl(pa.importe,0)) < a.precio
order by c.cliente_id, a.auto_id;
begin
dbms_output.put_line('Lista de clientes con deuda pendiente: ');
dbms_output.put_line('Auto_id cliente_id precio deuda');
open cur_por_actualizar;
loop
fetch cur_por_actualizar into v_cliente_id, v_auto_id, v_precio, v_deuda;
exit when cur_por_actualizar%notfound;
dbms_output.put_line(v_auto_id||' '||v_cliente_id||' '||v_precio
||' '||v_deuda);
end loop;
v_num_teorico_inserciones := cur_por_actualizar%rowcount;
close cur_por_actualizar;
select count(*) into v_num_pagos_antes_insercion
from pago_auto;
v_num_esperado_pagos := v_num_pagos_antes_insercion + v_num_teorico_inserciones;
--Invoca al procedimiento de generación de pagos.
sp_genera_pagos_tarjeta(50000);
--Cuenta las filas resultantes tras invocarlo.
select count(*) into v_num_pagos_despues from pago_auto;
dbms_output.put_line('Se han ejecutado exitosamente los pagos por la cantidad de $50000
para los clientes con deuda pendiente.');
dbms_output.put_line('Número de pagos registrados antes de la inserción:
'||v_num_pagos_antes_insercion);
dbms_output.put_line('Número de inserciones teórico: '|| v_num_teorico_inserciones);
dbms_output.put_line('Número de registros en pago_auto esperados: '||v_num_esperado_pagos);
2
Facultad de Ingeniería Bases de Datos______________________________________________________________________________________________________________
dbms_output.put_line('Número de registros en pago_auto despues de la inserción: '
||v_num_pagos_despues);
if v_num_pagos_despues = v_num_esperado_pagos then
dbms_output.put_line('OK. PRUEBA EXITOSA!');
end if;
rollback;
end;
/
show errors
Ejercicio 2: Registro de autos nuevos
--@Autor: Sandoval Castro Josmar Leonardo, Téllez González Jorge Luis
--@Fecha creación: 14/12/2021
--@Descripción: Script poder atualizar la foto de los autos.
prompt configurando directorio
connect sys/system as sysdba
create or replace directory fotos_dir as '/tmp/P13/autos';
grant read, write on directory fotos_dir to tgsc_p1201_autos;
prompt creando procedimiento con usuario tgsc_p1201_autos
connect tgsc_p1201_autos/tgsc
set serveroutput on
create or replace procedure sp_actualiza_foto_auto(
p_auto_id in number, p_num_imagenes in number
) is
v_bfile bfile;
v_src_offset number;
v_dest_offset number;
v_blob blob;
v_src_length number;
v_dest_length number;
v_nombre_archivo varchar(50);
begin
for v_index in p_auto_id..p_auto_id + p_num_imagenes loop
v_nombre_archivo:= 'auto-'||v_index||'.jpg';
dbms_output.put_line('Cargando foto para '||v_nombre_archivo);
--Validando si el archivo existe
v_bfile:=bfilename('FOTOS_DIR',v_nombre_archivo);
if dbms_lob.fileexists(v_bfile)=0 then
raise_application_error(-20001,'El archivo '||v_nombre_archivo||' no existe.');
end if;
--abrir archivo
if dbms_lob.isopen(v_bfile)=1 then
raise_application_error(-20001,'El archivo '||v_nombre_archivo||' está abierto, no se
puede usar.');
end if;
--abriendo archivo
dbms_lob.open(v_bfile,dbms_lob.lob_readonly);
--actualizando blob en la tabla
--asegurarse que la tabla auto contenga datos y la columna foto debe tener blob vacío
--Asigfnar v_blob
select foto into v_blob
from auto
where auto_id = v_index
for update;
--Escribiendo bytes
v_src_offset:=1;
v_dest_offset:=1;
dbms_lob.loadblobfromfile(
dest_lob => v_blob,
src_bfile => v_bfile,
amount => dbms_lob.getlength(v_bfile),
dest_offset => v_dest_offset,
3
Facultad de Ingeniería Bases de Datos______________________________________________________________________________________________________________
src_offset => v_src_offset
);
--Cerrando archivo
dbms_lob.close(v_bfile);
--Validando escritura/carga
v_src_length:=dbms_lob.getlength(v_bfile);
v_dest_length:=dbms_lob.getlength(v_blob);
if v_dest_length <> v_src_length then
raise_application_error(-20001,'El archivo'||v_nombre_archivo||'no se cargó
correctamente');
end if;
end loop;
end;
/
show errors
Ejercicio 2: Script de prueba
--@Autor: Sandoval Castro Josmar Leonardo, Téllez González Jorge Luis
--@Fecha creación: 20/12/2021
--@Descripción: Bloque de prueba de inserción de imágenes
set serveroutput on
declare
v_numero_fotos number;
begin
sp_actualiza_foto_auto(1,50);
select count(*) into v_numero_fotos
from auto
where dbms_lob.getlength(foto) > 0;
if v_numero_fotos = 51 then
dbms_output.put_line('Prueba exitosa');
end if;
end;
/
show errors;
rollback;
4
Facultad de IngenieríaBases de Datos______________________________________________________________________________________________________________
3. C2. Ejecución
Ejercicio 1: Script de prueba
5
Facultad de Ingeniería Bases de Datos______________________________________________________________________________________________________________
6
Facultad de Ingeniería Bases de Datos______________________________________________________________________________________________________________
Ejercicio 2: Script de prueba
7
Facultad de Ingeniería Bases de Datos______________________________________________________________________________________________________________
4. Conclusiones
A través del desarrollo de los ejercicios de esta práctica los conceptos básicos de la
programación en PL/SQL han sido aplicados nuevamente, siendo el uso de bloques anónimos
uno de los elementos clave para la realización de pruebas de validez para los
procedimientos/triggers/funciones que se implementaron tanto en esta como en la práctica
anterior.
El manejo de cursores, si bien puede llegar a resultar confuso (y en más de una ocasión nos
generó problemas para recuperar información de ellos), aplicado correctamente resulta ser un
elemento extremadamente útil para recuperar grandes lotes de información, visualizarlos e
incluso manipularlos de forma eficiente; lo que nos ha permitido observar con mayor detalle
elementos que son comunes en la programación de bases productivas y que pueden ser
replicados para motivos del proyecto final del curso.
Finalmente, considerando que se logró implementar la actualización de lotes de registros
empleando cursores y, además, se comprendió el uso de procedimientos para la manipulación
de elementos gráficos, se puede considerar que el objetivo principal de esta práctica se ha
cumplido con éxito. Los elementos desarrollados serán cconsiderados como base para
retormarlos durante el desarrollo del proyecto final.
8