Informática fácil: PL/SQL. Práctica de cursores y excepciones

Crea las siguientes tablas e introduce registros de prueba:

EQUIPOS

CodEquipo VARCHAR2, tamaño 4 Clave Primaria

Nombre VARCHAR2, tamaño 30 No Nulo

Localidad VARCHAR2, tamaño 15

PARTIDOS

CodPartido VARCHAR2, tamaño 4 Clave Primaria

CodEquipoLocal VARCHAR2, tamaño 4 Clave Ajena

CodEquipoVisitante VARCHAR2, tamaño 4 Clave Ajena

Fecha FECHA No puede ser de Julio o Agosto

Jornada VARCHAR2, tamaño 20

Gol_Local NUMBER(2)

Gol_Visitante NUMBER(2)

CREATE TABLE EQUIPOS

(

CODEQUIPO VARCHAR2 (4),

NOMBRE VARCHAR2 (30) NOT NULL,

LOCALIDAD varchar2 (15),

CONSTRAINT PK_CODEQUIPO PRIMARY KEY (CODEQUIPO)

);

CREATE TABLE PARTIDOS

(

CODPARTIDO VARCHAR2 (4),

CODEQUIPOLOCAL VARCHAR2 (4),

CODEQUIPOVISITANTE VARCHAR2 (4),

FECHA DATE,

JORNADA VARCHAR2 (20),

GOL_LOCAL NUMBER (2),

GOL_VISITANTE NUMBER (2),

CONSTRAINT PK_CODPARTIDO PRIMARY KEY (CODPARTIDO),

CONSTRAINT FK_CODEQUIPOLOCAL FOREIGN KEY (CODEQUIPOLOCAL) REFERENCES EQUIPOS1(CODEQUIPO),

CONSTRAINT FK_CODEQUIVISITANTE FOREIGN KEY (CODEQUIPOVISITANTE) REFERENCES EQUIPOS1(CODEQUIPO) ON DELETE CASCADE

);

INSERT INTO EQUIPOS1 VALUES (1111, 'SEVILLA', 'SEVILLA');

INSERT INTO EQUIPOS1 VALUES (1112, 'BETIS', 'SEVILLA');

INSERT INTO EQUIPOS1 VALUES (1113, 'MALAGA', 'MALAGA');

INSERT INTO PARTIDOS1 VALUES (2222, 1111, 1112,TO_DATE('01-ENE-2014', 'DD-MON-YYYY'), 'PRIMERA', 1,2);

INSERT INTO PARTIDOS1 VALUES (2223, 1111, 1113,TO_DATE('02-ENE-2014', 'DD-MON-YYYY'),'SEGUNDA', 3,2);

INSERT INTO PARTIDOS1 VALUES (2224, 1112, 1113,TO_DATE('03-ENE-2014', 'DD-MON-YYYY'),'PRIMERA', 4,1);

1.- Crea una función DevolverNombreEquipo que reciba un código de equipo y devuelva el nombre del mismo.

Si el equipo no existe devuelve la cadena “Error en código”.

CREATE OR REPLACE FUNCTION DevolverNombreEquipo (p_codequipo equipos1.codequipo%TYPE)

RETURN equipos1.nombre%TYPE

v_nombre equipos1.nombre%TYPE;

BEGIN

SELECT nombre INTO v_nombre

FROM equipos1

WHERE codequipo=p_codequipo;

RETURN v_nombre;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE ('error de código' ||p_codequipo );

RETURN -1;

END;

set serveroutput on;

/*LLAMADA A LA FUNCION*/

DECLARE

v_nombre equipos1.nombre%TYPE;

BEGIN

v_nombre:=DevolverNombreEquipo(1111);

dbms_output.put_line('el nombre del equipo es: '||v_nombre);

END;

2.- Crea una procedimiento DevolverGolesEquipo que reciba el código de un equipo y devuelva el total de goles a favor y total de goles en contra.

Contempla las excepciones oportunas.

CREATE FUNCTION DevolverGolesEquipo (@codigo AS INT )

RETURNS @goles TABLE ([Goles a favor] INT,[Goles en contra] INT)

BEGINDECLARE @afavor INT

DECLARE @encontra INT

SET @afavor =

((SELECT SUM(Gol_Local) FROM Partidos WHERE

CodEquipoLocal= @codigo GROUP BY CodEquipoLocal)

+ (SELECT SUM(Gol_Visitante) FROM Partidos WHERE

CodEquipoVisitante = @codigo GROUP BY CodEquipoLocal))

SET @encontra =

((SELECT SUM(Gol_Visitante) FROM Partidos WHERE

CodEquipoLocal = @codigo GROUP BY CodEquipoLocal)

+ (SELECT SUM(Gol_Local) FROM Partidos WHERE

CodEquipoVisitante = @codigo GROUP BY CodEquipoLocal))

INSERT INTO @goles VALUES (@afavor,@encontra)

RETURN

END

3. Crea un procedimiento DevolverResultadosEquipo que reciba el código de un equipo y devuelva el número de partidos que ha ganado, el número de partidos que ha perdido y el número de partidos que ha empatado. Contempla las excepciones oportunas.

CREATE PROCEDURE DevolverResultadosEquipo (@codigo AS INT)

BEGIN

DECLARE @victorias INT

DECLARE @empates INT

DECLARE @derrotas INT

SET @victorias = ISNULL((SELECT COUNT(*) FROM Partidos

WHERE Gol_Local > Gol_Visitante

GROUP BY CodEquipoLocal HAVING CodEquipoLocal = @codigo),0)

SET @victorias = @victorias + ISNULL((SELECT COUNT(*) FROM

Partidos WHERE Gol_Local < Gol_Visitante

GROUP BY CodEquipoVisitante HAVING CodEquipoVisitante =@codigo),0)

SET @empates = ISNULL((SELECT COUNT(*) FROM

PartidosWHERE Gol_Local = Gol_Visitante

GROUP BY CodEquipoLocal HAVING CodEquipoLocal = @codigo),0)

SET @empates = @empates + ISNULL((SELECT COUNT(*) FROMPartidos

WHERE Gol_Local = Gol_Visitante

GROUP BY CodEquipoVisitante HAVING CodEquipoVisitante =@codigo),0)

SET @derrotas = ISNULL((SELECT COUNT(*) FROM

PartidosWHERE Gol_Local < Gol_Visitante

GROUP BY CodEquipoLocal HAVING CodEquipoLocal = @codigo),0)

SET @derrotas = @derrotas + ISNULL((SELECT COUNT(*) FROMPartidos

WHERE Gol_Local > Gol_Visitante

GROUP BY CodEquipoVisitante HAVING CodEquipoVisitante =@codigo),0)

SELECT @victorias [Victorias],@empates [empates],@derrotas

[Derrotas]

END

ejercicio 4

CREATE TABLE SOCIOS

(

DNI VARCHAR2 (10) NOT NULL,

NOMBRE VARCHAR2 (20) NOT NULL,

DIRECCION VARCHAR2 (20),

PENALIZACIONES NUMBER (2) DEFAULT 0,

CONSTRAINT PK_DNI_SOC PRIMARY KEY (DNI)

);

INSERT INTO SOCIOS VALUES (11111111, 'ANA', 'CALLE 1',1);

INSERT INTO SOCIOS VALUES (11111112, 'ROCIO', 'CALLE 2',0);

INSERT INTO SOCIOS VALUES (11111113, 'JAVIER', 'CALLE 3',2);

CREATE TABLE LIBROS

(

REFLIBRO VARCHAR2 (10) NOT NULL,

NOMBRE VARCHAR2 (30) NOT NULL,

AUTOR VARCHAR2 (20) NOT NULL,

GENERO VARCHAR2 (10),

ANYOPUBLICACION NUMBER,

EDITORIAL VARCHAR2 (10),

CONSTRAINT PK_REFLIBRO PRIMARY KEY (REFLIBRO)

);

INSERT INTO LIBROS VALUES ('1111111A', 'LIBRO1', 'AUTOR1','GENERO1',2000,'EDITORIAL1');

INSERT INTO LIBROS VALUES ('1111111B', 'LIBRO2', 'AUTOR2','GENERO2',2000,'EDITORIAL2');

INSERT INTO LIBROS VALUES ('1111111C', 'LIBRO3', 'AUTOR3','GENERO3',2000,'EDITORIAL3');

CREATE TABLE PRESTAMOS

(

DNI VARCHAR2 (10) NOT NULL,

REFLIBRO VARCHAR2 (10) NOT NULL,

FECHAPRESTAMO DATE NOT NULL,

DURACION NUMBER (2) DEFAULT 24,

CONSTRAINT PK_PRESTAMOS PRIMARY KEY (DNI,REFLIBRO,FECHAPRESTAMO),

CONSTRAINT FK_DNI_PREST FOREIGN KEY (DNI) REFERENCES SOCIOS(DNI),

CONSTRAINT FK_REFLIBRO_PREST FOREIGN KEY (REFLIBRO) REFERENCES LIBROS(REFLIBRO)ON DELETE CASCADE

);

INSERT INTO PRESTAMOS VALUES (11111111, '1111111A', TO_DATE('01-ENE-2014', 'DD-MON-YYYY'),1);

INSERT INTO PRESTAMOS VALUES (11111112, '1111111B', TO_DATE('02-ENE-2014', 'DD-MON-YYYY'),2);

INSERT INTO PRESTAMOS VALUES (11111113, '1111111C', TO_DATE('03-ENE-2014', 'DD-MON-YYYY'),3);

Realiza un procedimiento llamado listadotresmasprestados que nos muestre por pantalla un listado de los tres libros más prestados y los socios a los que han sido prestados con el siguiente formato:

NombreLibro1 NumPrestamosLibro1 GeneroLibro1

DNISocio1 FechaPrestamoalSocio1

...

DNISocion FechaPrestamoal Socion

NombreLibro2 NumPrestamosLibro2 GeneroLibro2

DNISocio1 FechaPrestamoalSocio1

...

DNISocion FechaPrestamoalSocion

NombreLibro3 NumPrestamosLibro3 GeneroLibro3

DNISocio1 FechaPrestamoalSocio1

...

DNISocion FechaPrestamoalSocion

El procedimiento debe gestionar adecuadamente las siguientes excepciones:

La tabla Libros está vacía.

La tabla Socios está vacía.

Hay menos de tres libros que hayan sido prestados.

Create or replace procedure comprobar_excepciones_ej1

e_libros_vacia exception;

e_socios_vacia exception;

e_pocos_prestamos exception;

v_numlibros NUMBER;

v_numsocios NUMBER;

v_numlibrosprestados NUMBER;

begin

select count(*) into v_numlibros

from libros;

select count(*) into v_numsocios

from socios;

select count(distinct reflibro) into v_numlibrosprestados

from prestamos;

if v_numlibros = 0 then

raise e_libros_vacia;

end if;

if v_numsocios = 0 then

raise e_socios_vacia;

end if;

if v_numlibrosprestados < 4 then

raise e_pocos_prestamos;

end if;

exception

when e_libros_vacia then

dbms_output.put_line('La tabla socios está vacía. Informe abortado');

raise;

when e_socios_vacia then

dbms_output.put_line('La tabla libros está vacía. Informe abortado');

raise;

when e_pocos_prestamos then

dbms_output.put_line('Se han prestado tan solo '||v_numlibrosprestados||' libros. Informe abortado');

raise;

end comprobar_excepciones_ej1;

//////////////////////////////////////////////////////////////////////////////////////////////////////

create or replace procedure mostrar_cabecera_informe

begin

dbms_output.put_line('INFORME DE LIBROS MÁS PRESTADOS');

dbms_output.put_line('_______________________________');

end mostrar_cabecera_informe;

///////////////////////////////////////////////////////////////////////////////////////////////////////

create or replace procedure mostrar_prestamos(p_cod libros.reflibro%TYPE)

cursor c_prestamos is

select dni, fechaprestamo

from prestamos

where reflibro=p_cod;

begin

for v_prestamos in c_prestamos loop

dbms_output.put_line(rpad(v_prestamos.dni,20)||v_prestamos.fechaprestamo);

end loop;

end mostrar_prestamos;

////////////////////////////////////////////////////////////////////////////////////////////////////////

create or replace procedure cuatro_mas_prestados

cursor c_cuatro is

select l.reflibro, nombre, genero, count(*) as numprestamos

from libros l, prestamos p

where l.reflibro=p.reflibro

group by l.reflibro, nombre, genero

order by numprestamos desc;

v_cuatro c_cuatro%ROWTYPE;

begin

comprobar_excepciones_ej1;

mostrar_cabecera_informe;

open c_cuatro;

fetch c_cuatro into v_cuatro;

while c_cuatr

o%found and c_cuatro%rowcount<=4 loop

dbms_output.put_line(rpad(v_cuatro.nombre,20)||rpad(v_cuatro.numprestamos,20)||v_cuatro.genero);

mostrar_prestamos(v_cuatro.reflibro);

fetch c_cuatro into v_cuatro;

end loop;

close c_cuatro;

end cuatro_mas_prestados;

5. Partiendo del siguiente script, crea la BD correspondiente a los alumnos matriculados en algunos de los módulos de 1º y 2º curso del CFS y sus correspondientes notas:

REM ******** TABLAS ALUMNOS, ASIGNATURAS, NOTAS: ***********

DROP TABLE ALUMNOS cascade constraints;

CREATE TABLE ALUMNOS

(

DNI VARCHAR2(10) NOT NULL,

APENOM VARCHAR2(30),

DIREC VARCHAR2(30),

POBLA VARCHAR2(15),

TELEF VARCHAR2(10)

);

DROP TABLE ASIGNATURAS cascade constraints;

CREATE TABLE ASIGNATURAS

(

COD NUMBER(2) NOT NULL,

NOMBRE VARCHAR2(25)

);

DROP TABLE NOTAS cascade constraints;

CREATE TABLE NOTAS

(

DNI VARCHAR2(10) NOT NULL,

COD NUMBER(2) NOT NULL,

NOTA NUMBER(2)

);

INSERT INTO ASIGNATURAS VALUES (1,'Prog. Leng. Estr.');

INSERT INTO ASIGNATURAS VALUES (2,'Sist. Informáticos');

INSERT INTO ASIGNATURAS VALUES (3,'Análisis');

INSERT INTO ASIGNATURAS VALUES (4,'FOL');

INSERT INTO ASIGNATURAS VALUES (5,'RET');

INSERT INTO ASIGNATURAS VALUES (6,'Entornos Gráficos');

INSERT INTO ASIGNATURAS VALUES (7,'Aplic. Entornos 4ªGen');

INSERT INTO ALUMNOS VALUES

('12344345','Alcalde García, Elena', 'C/Las Matas, 24','Madrid','917766545');

INSERT INTO ALUMNOS VALUES

('4448242','Cerrato Vela, Luis', 'C/Mina 28 - 3A', 'Madrid','916566545');

INSERT INTO ALUMNOS VALUES

('56882942','Díaz Fernández, María', 'C/Luis Vives 25', 'Móstoles','915577545');

INSERT INTO NOTAS VALUES('12344345', 1,6);

INSERT INTO NOTAS VALUES('12344345', 2,5);

INSERT INTO NOTAS VALUES('12344345', 3,6);

INSERT INTO NOTAS VALUES('4448242', 4,6);

INSERT INTO NOTAS VALUES('4448242', 5,8);

INSERT INTO NOTAS VALUES('4448242', 6,4);

INSERT INTO NOTAS VALUES('4448242', 7,5);

INSERT INTO NOTAS VALUES('56882942', 5,7);

INSERT INTO NOTAS VALUES('56882942', 6,8);

INSERT INTO NOTAS VALUES('56882942', 7,9);

COMMIT;

a) Diseña una función llamada nota_media que reciba un nombre de un módulo y devuelva la nota media obtenida por los alumnos matriculados en el mismo.

Create or replace function nota_media( p_nombre_modulo asignaturas.nombre%TYPE)

return NUMBER

v_nota_media NUMBER;

begin

select avg(nota) into v_nota_media

from notas

where cod = (select cod

from asignaturas

where nombre=p_nombre_modulo);

return v_nota_media;

end nota_media;

create or replace procedure probar_nota_media( nombre_modulo asignaturas.nombre%TYPE)

begin

dbms_output.put_line(nota_media(nombre_modulo));

end probar_nota_media;

b) Diseña un procedimiento al que pasemos como parámetro de entrada el nombre de uno de los módulos existentes en la BD y visualice el nombre de los alumnos que lo han cursado junto a su nota.

Al final del listado debe aparecer el nº de suspensos, aprobados, notables y sobresalientes.

Asimismo, deben aparecer al final los nombres y notas de los alumnos que tengan la nota más alta y la más baja.

Debes comprobar que las tablas tengan almacenada información y que exista el módulo cuyo nombre pasamos como parámetro al procedimiento.

create or replace procedure listadonotas( p_nombre_modulo asignaturas.nombre%TYPE )

cont_sus NUMBER:=0;

cont_apr NUMBER:=0;

cont_not NUMBER:=0;

cont_sob NUMBER:=0;

v_nombre_mejor alumnos.apenom%TYPE;

v_nombre_peor alumnos.apenom%TYPE;

v_nota_mejor notas.nota%TYPE:=-1;

v_nota_peor notas.nota%TYPE:=11;

cursor c_notas is

select dni, nota

from notas

where cod=(select cod

from asignaturas

where nombre=p_nombre_modulo);

begin

comprobar_excepciones_ej2(p_nombre_modulo);

mostrar_cabecera_informe_notas(p_nombre_modulo);

for v_notas in c_notas loop

mostrar_nota_de_un_alumno(v_notas.dni,v_notas.nota);

contar_tipos_notas(v_notas.nota,cont_sus,cont_apr,cont_not,cont_sob);

comprobar_si_es_la_mejor(v_notas.dni, v_notas.nota, v_nombre_mejor, v_nota_mejor);

comprobar_si_es_la_peor(v_notas.dni, v_notas.nota, v_nombre_peor, v_nota_peor);

end loop;

mostrar_por_calificacion( cont_sus,cont_apr,cont_not,cont_sob );

mostrar_mejor_y_peor (v_nombre_mejor, v_nota_mejor, v_nombre_peor, v_nota_peor);

end listadonotas;

//////////////////////////////////////////////////////////////////////////////////////////////////////

create or replace procedure comprobar_excepciones_ej2( p_nombre_modulo asignaturas.nombre%TYPE)

v_numalumnos number;

v_numasignaturas number;

v_numnotas number;

v_existemodulo number;

e_alumnos_vacia exception;

e_asignaturas_vacia exception;

e_notas_vacia exception;

e_modulo_inexistente exception;

begin

select count(*) into v_numalumnos

from alumnos;

select count(*) into v_numasignaturas

from asignaturas;

select count(*) into v_numnotas

from notas;

select count(*) into v_existemodulo

from asignaturas

where nombre=p_nombre_modulo;

if v_numalumnos=0 then

raise e_alumnos_vacia;

end if;

if v_numasignaturas = 0 then

raise e_asignaturas_vacia;

end if;

if v_numnotas=0 then

raise e_notas_vacia;

end if;

if v_existemodulo = 0 then

raise e_modulo_inexistente;

end if;

exception

when e_alumnos_vacia then

dbms_output.put_line('La tabla Alumnos está vacía. Informe abortado');

raise;

when e_asignaturas_vacia then

dbms_output.put_line('La tabla Asignaturas está vacía. Informe abortado');

raise;

when e_notas_vacia then

dbms_output.put_line('La tabla Notas está vacía. Informe abortado');

raise;

when e_modulo_inexistente then

dbms_output.put_line('El modulo '||p_nombre_modulo||' no existe. Informe abortado');

raise;

end comprobar_excepciones_ej2;

//////////////////////////////////////////////////////////////////////////////////////////////////////

create or replace procedure mostrar_cabecera_informe_notas( p_nombre_modulo asignaturas.nombre%TYPE)

begin

dbms_output.put_line('LISTADO DE NOTAS DEL MÓDULO '||p_nombre_modulo);

dbms_output.put_line(rpad('_', 28+length(p_nombre_modulo),'_'));

end mostrar_cabecera_informe_notas;

//////////////////////////////////////////////////////////////////////////////////////////////////////

create or replace procedure mostrar_nota_de_un_alumno(p_dni alumnos.dni%TYPE, p_nota notas.nota%TYPE)

v_nombre alumnos.apenom%TYPE;

begin

select apenom into v_nombre

from alumnos

where dni=p_dni;

dbms_output.put_line(rpad(v_nombre,40)||p_nota);

end mostrar_nota_de_un_alumno;

//////////////////////////////////////////////////////////////////////////////////////////////////////

create or replace procedure contar_tipos_notas(p_nota notas.nota%TYPE, p_sus in out number,

p_apr in out number, p_not in out number,

p_sob in out number)

begin

case

when (p_nota<5) then p_sus:=p_sus+1;

when (p_nota>=5 and p_nota<7) then p_apr:=p_apr+1;

when (p_nota>=7 and p_nota<9) then p_not:=p_not+1;

when (p_nota>=9) then p_sob:=p_sob+1;

end case;

end contar_tipos_notas;

//////////////////////////////////////////////////////////////////////////////////////////////////////

create or replace procedure comprobar_si_es_la_mejor(p_dni notas.dni%TYPE, p_nota notas.nota%TYPE,

p_nombre in out alumnos.apenom%TYPE,

p_notamax in out notas.nota%TYPE)

begin

if (p_nota>p_notamax) then

select apenom into p_nombre

from alumnos

where dni= p_dni;

p_notamax:=p_nota;

end if;

end comprobar_si_es_la_mejor;

//////////////////////////////////////////////////////////////////////////////////////////////////////

create or replace procedure comprobar_si_es_la_peor (p_dni notas.dni%TYPE, p_nota notas.nota%TYPE,

p_nombre in out alumnos.apenom%TYPE,

p_notamin in out notas.nota%TYPE)

begin

if (p_nota<p_notamin) then

select apenom into p_nombre

from alumnos

where dni= p_dni;

p_notamin:=p_nota;

end if;

end comprobar_si_es_la_peor;

//////////////////////////////////////////////////////////////////////////////////////////////////////

create or replace procedure mostrar_por_calificacion (p_sus number, p_apr number,

p_not number, p_sob number)

begin

dbms_output.put_line('SUBTOTALES POR CALIFICACIÓN' );

dbms_output.put_line('___________________________' );

dbms_output.put_line(rpad('Suspensos: ', 16) || p_sus );

dbms_output.put_line(rpad('Aprobados: ', 16) || p_apr );

dbms_output.put_line(rpad('Notables: ', 16) || p_not );

dbms_output.put_line(rpad('Sobresalientes:', 16) || p_sob );

end mostrar_por_calificacion;

//////////////////////////////////////////////////////////////////////////////////////////////////////

create or replace procedure mostrar_mejor_y_peor (p_nombre_mejor alumnos.apenom%TYPE,

p_nota_mejor notas.nota%TYPE,

p_nombre_peor alumnos.apenom%TYPE,

p_nota_peor notas.nota%TYPE)

begin

dbms_output.put_line('La mejor nota es un '||p_nota_mejor||', obtenido por '||p_nombre_mejor );

dbms_output.put_line('La peor nota es un '||p_nota_peor||', obtenido por '||p_nombre_peor );

end mostrar_mejor_y_peor;

//////////////////////////////////////////////////////////////////////////////////////////////////////

6. A partir de las tablas creadas con el siguiente script:

DROP TABLE productos CASCADE CONSTRAINTS;

CREATE TABLE productos

(

CodProducto VARCHAR2(10) CONSTRAINT p_cod_no_nulo NOT NULL,

Nombre VARCHAR2(20) CONSTRAINT p_nom_no_nulo NOT NULL,

LineaProducto VARCHAR2(10),

PrecioUnitario NUMBER(6),

Stock NUMBER(5),

PRIMARY KEY (CodProducto)

);

DROP TABLE ventas CASCADE CONSTRAINTS;

CREATE TABLE ventas

(

CodVenta VARCHAR2(10) CONSTRAINT cod_no_nula NOT NULL,

CodProducto VARCHAR2(10) CONSTRAINT pro_no_nulo NOT NULL,

FechaVenta DATE,

UnidadesVendidas NUMBER(3),

PRIMARY KEY (CodVenta)

);

INSERT INTO productos VALUES ('1','Procesador P133', 'Proc',15000,20);

INSERT INTO productos VALUES ('2','Placa base VX', 'PB', 18000,15);

INSERT INTO productos VALUES ('3','Simm EDO 16Mb', 'Memo', 7000,30);

INSERT INTO productos VALUES ('4','Disco SCSI 4Gb', 'Disc',38000, 5);

INSERT INTO productos VALUES ('5','Procesador K6-2', 'Proc',18500,10);

INSERT INTO productos VALUES ('6','Disco IDE 2.5Gb', 'Disc',20000,25);

INSERT INTO productos VALUES ('7','Procesador MMX', 'Proc',15000, 5);

INSERT INTO productos VALUES ('8','Placa Base Atlas','PB', 12000, 3);

INSERT INTO productos VALUES ('9','DIMM SDRAM 32Mb', 'Memo',17000,12);

INSERT INTO ventas VALUES('V1', '2', '22/09/97',2);

INSERT INTO ventas VALUES('V2', '4', '22/09/97',1);

INSERT INTO ventas VALUES('V3', '6', '23/09/97',3);

INSERT INTO ventas VALUES('V4', '5', '26/09/97',5);

INSERT INTO ventas VALUES('V5', '9', '28/09/97',3);

INSERT INTO ventas VALUES('V6', '4', '28/09/97',1);

INSERT INTO ventas VALUES('V7', '6', '02/10/97',2);

INSERT INTO ventas VALUES('V8', '6', '02/10/97',1);

INSERT INTO ventas VALUES('V9', '2', '04/10/97',4);

INSERT INTO ventas VALUES('V10','9', '04/10/97',4);

INSERT INTO ventas VALUES('V11','6', '05/10/97',2);

INSERT INTO ventas VALUES('V12','7', '07/10/97',1);

INSERT INTO ventas VALUES('V13','4', '10/10/97',3);

INSERT INTO ventas VALUES('V14','4', '16/10/97',2);

INSERT INTO ventas VALUES('V15','3', '18/10/97',3);

INSERT INTO ventas VALUES('V16','4', '18/10/97',5);

INSERT INTO ventas VALUES('V17','6', '22/10/97',2);

INSERT INTO ventas VALUES('V18','6', '02/11/97',2);

INSERT INTO ventas VALUES('V19','2', '04/11/97',3);

INSERT INTO ventas VALUES('V20','9', '04/12/97',3);

a) Realiza un procedimiento que actualice la columna Stock de la tabla Productos a partir de los registros de la tabla Ventas.

El citado procedimiento debe informarnos por pantalla si alguna de las tablas está vacía o si el stock de un producto pasa a ser negativo, en cuyo caso se parará la actualización.

create or replace procedure actualizar_stock

cursor c_ventas is

select codproducto, sum(unidadesvendidas) as totalvend

from ventas

group by codproducto;

v_ventas c_ventas%ROWTYPE;

ind_ventas NUMBER :=0; -- indicador para ver si ventas está vacía

num_productos NUMBER:=0; -- contador para ver si hay productos

e_no_hay_productos exception;

begin

select count(*) into num_productos

from productos;

if (num_productos=0) then

raise e_no_hay_productos;

end if;

open c_ventas;

fetch c_ventas into v_ventas;

while c_ventas%FOUND loop

tratar_producto(v_ventas.codproducto, v_ventas.totalvend);

fetch c_ventas into v_ventas;

ind_ventas:=1;

end loop;

close c_ventas;

if (ind_ventas=0) then

dbms_output.put_line('La tabla ventas está vacía');

end if;

exception

when e_no_hay_productos then

dbms_output.put_line('La tabla productos está vacía');

end actualizar_stock;

create or replace procedure tratar_producto( cod productos.codproducto%TYPE,totalvend NUMBER)

e_stock_negativo exception;

stock_antiguo NUMBER;

stock_nuevo NUMBER;

begin

select stock into stock_antiguo

from productos

where codproducto=cod;

stock_nuevo:=stock_antiguo – totalvend;

if ( stock_nuevo < 0) then

raise e_stock_negativo;

end if;

update productos

set stock = stock_nuevo

where codproducto = cod;

exception

when e_stock_negativo then

dbms_output.put_line('Error de stock negativo en producto'||cod);

raise;

end tratar_producto;

Informática fácil

domingo, 17 de agosto de 2014

PL/SQL. Práctica de cursores y excepciones

No hay comentarios:

Publicar un comentario