Soluciones Del Examen

DROP TABLE autores; CREATE TABLE autores (id_autor NUMBER(4) NOT NULL, nombre VARCHAR2(18), apellidos VARCHAR2(40), nacionalidad VARCHAR2(15)); DROP TABLE editoriales; CREATE TABLE editoriales (id_editorial NUMBER(3) NOT NULL, nombre VARCHAR2(20)); DROP TABLE libros; CREATE TABLE libros (id_libro NUMBER(4) NOT NULL, autor NUMBER(4), titulo VARCHAR2(35), editorial NUMBER(3), paginas NUMBER(4), genero VARCHAR2(15)); DROP TABLE socios; CREATE TABLE socios (id_socio NUMBER(4) NOT NULL, nombre VARCHAR2(18), apellido VARCHAR2(20), fecha_nacimiento DATE); DROP TABLE prestamos; CREATE TABLE prestamos (id_socio NUMBER(4), id_libro NUMBER(4), fecha_prestamo DATE, devuelto VARCHAR2(1)); DROP TABLE estanterias; CREATE TABLE estanterias (id_estanteria NUMBER(3), piso NUMBER(1), capacidad NUMBER(4)); INSERT INSERT INSERT INSERT INSERT INSERT

INTO INTO INTO INTO INTO INTO

autores autores autores autores autores autores

VALUES VALUES VALUES VALUES VALUES VALUES

(1, (2, (3, (4, (5, (6,

'CARMEN', 'IGLESIAS', 'ESPA�A'); 'FRANZ', 'KAFKA', 'CHECOSLOVAQUIA'); 'MATILDE', 'ASENSI', 'ESPA�A'); 'DAN', 'BROWN', 'ESTADOS UNIDOS'); 'STEPHENIE', 'MEYER', 'ESTADOS UNIDOS'); 'JUAN JOSE', 'MILLAS', 'ESPA�A');

INSERT INTO editoriales VALUES (1, 'PLANETA'); INSERT INTO editoriales VALUES (2, 'ALFAGUARA'); INSERT INTO editoriales VALUES (3, 'BRUGUERA'); INSERT INTO libros 'HISTORIA'); INSERT INTO libros 'HISTORIA'); INSERT INTO libros 'HISTORIA'); INSERT INTO libros INSERT INTO libros INSERT INTO libros INSERT INTO libros INSERT INTO libros INSERT INTO libros

VALUES (1, 1, 'NO SIEMPRE LO PEOR ES CIERTO', 2, 335, VALUES (2, 1, 'EL PENSAMIENTO DE MONTESQUIEU', 2, 576, VALUES (3, 1, 'RAZ�N, SENTIMIENTO Y UTOP�A', 2, 560, VALUES VALUES VALUES VALUES VALUES VALUES

(4, (5, (6, (7, (8, (9,

2, 2, 2, 3, 3, 3,

'LA METAMORFOSIS', 3, 390, 'CLASICO'); 'EL PROCESO', 3, 543, 'CLASICO'); 'CARTA AL PADRE', 1, 339, 'CLASICO'); 'EL SAL�N DE �MBAR', 1, 220, 'NOVELA'); 'IACOBUS', 1, 384, 'NOVELA'); 'EL �LTIMO CAT�N', 3, 576, 'NOVELA');

INSERT INTO libros INSERT INTO libros INSERT INTO libros INSERT INTO libros INSERT INTO libros INSERT INTO libros INSERT INTO libros INSERT INTO libros INSERT INTO libros INSERT INTO libros INSERT INTO libros INSERT INTO libros INSERT INTO libros 'ILUSTRADO'); INSERT INTO libros INSERT INTO libros 'NOVELA');

VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES

INSERT INSERT INSERT INSERT INSERT INSERT INSERT

INTO INTO INTO INTO INTO INTO INTO

socios socios socios socios socios socios socios

VALUES VALUES VALUES VALUES VALUES VALUES VALUES

INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT

INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO

prestamos prestamos prestamos prestamos prestamos prestamos prestamos prestamos prestamos prestamos prestamos prestamos prestamos prestamos prestamos prestamos prestamos prestamos prestamos prestamos prestamos prestamos prestamos prestamos prestamos prestamos prestamos prestamos prestamos prestamos prestamos prestamos

(10, (11, (12, (13, (14, (15, (16, (17, (18, (19, (20, (21, (22,

3, 3, 3, 4, 4, 4, 5, 5, 5, 5, 6, 6, 6,

'EL ORIGEN PERDIDO', 2, 512, 'NOVELA'); 'TODO BAJO EL CIELO', 1, 460, 'NOVELA'); 'TIERRA FIRME', 1, 240, 'NOVELA'); '�NGELES Y DEMONIOS', 1, 550, 'NOVELA'); 'LA CONSPIRACI�N', 1, 560, 'NOVELA'); 'EL C�DIGO DA VINCI', 3, 577, 'NOVELA'); 'CREP�SCULO', 2, 480, 'JUVENIL'); 'LUNA NUEVA', 2, 540, 'JUVENIL'); 'ECLIPSE', 2, 600, 'JUVENIL'); 'AMANECER', 2, 780, 'JUVENIL'); 'PAPEL MOJADO', 2, 340, 'NOVELA'); 'NO MIRES DEBAJO DE LA CAMA', 3, 390, 'NOVELA'); 'N�MEROS PARES, IMPARES E IDIOTAS', 2, 440,

VALUES (23, 6, 'TRILOG�A DE LA SOLEDAD', 1, 445, 'NOVELA'); VALUES (24, 6, 'HAY ALGO QUE NO ES COMO ME DICEN', 3, 604, (1, (2, (3, (4, (5, (6, (7,

VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES

'FRANCISCO', 'VELA', '30/01/1979'); 'MANUEL', 'ALONSO', '04/07/1985'); 'ANA', 'GIL', '19/07/1982'); 'MAR�A', 'FERN�NDEZ', '12/03/1992'); 'BEL�N', 'CARVAJAL', '12/03/1992'); 'LAURA', 'MEN�NDEZ', '12/03/1992'); 'SONSOLES', 'TABOADA', '12/03/1992');

(1, (2, (2, (3, (4, (5, (4, (5, (6, (1, (2, (3, (4, (5, (6, (3, (4, (1, (2, (1, (1, (5, (6, (7, (4, (5, (2, (3, (6, (3, (4, (5,

2, '13/02/2009', 'S'); 1, '14/02/2009', 'S'); 3, '14/02/2009', 'S'); 4, '15/02/2009', 'S'); 6, '15/02/2009', 'S'); 5, '15/02/2009', 'S'); 8, '15/02/2009', 'S'); 21, '16/02/2009', 'S'); 20, '16/02/2009', 'N'); 12, '16/02/2009', 'S'); 16, '16/02/2009', 'S'); 15, '16/02/2009', 'S'); 5, '16/02/2009', 'S'); 7, '16/02/2009', 'N'); 11, '17/02/2009', 'N'); 12, '17/02/2009', 'N'); 16, '17/02/2009', 'S'); 17, '17/02/2009', 'S'); 18, '17/02/2009', 'S'); 19, '17/02/2009', 'S'); 21, '17/02/2009', 'S'); 7, '17/02/2009', 'S'); 6, '17/02/2009', 'S'); 16, '17/02/2009', 'S'); 9, '17/02/2009', 'S'); 5, '18/02/2009', 'N'); 6, '18/02/2009', 'N'); 9, '18/02/2009', 'S'); 16, '19/02/2009', 'S'); 9, '19/02/2009', 'N'); 1, '19/02/2009', 'N'); 2, '19/02/2009', 'N');

INSERT INSERT INSERT INSERT INSERT

INTO INTO INTO INTO INTO

prestamos prestamos prestamos prestamos prestamos

VALUES VALUES VALUES VALUES VALUES

INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT

INTO INTO INTO INTO INTO INTO INTO INTO

estanterias estanterias estanterias estanterias estanterias estanterias estanterias estanterias

(6, (1, (2, (3, (4,

VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES

3, '19/02/2009', 'N'); 16, '19/02/2009', 'N'); 15, '19/02/2009', 'N'); 17, '19/02/2009', 'N'); 22, '19/02/2009', 'N');

(1, (2, (3, (4, (5, (6, (7, (8,

1, 1, 1, 2, 2, 2, 2, 2,

50); 75); 90); 35); 49); 30); 100); 56);

DROP VIEW libros_prestados; CREATE VIEW libros_prestados AS SELECT id_socio, fecha_prestamo, titulo FROM prestamos p, libros l WHERE p.id_libro=l.id_libro AND devuelto='N'; COMMIT; /***************** EJERCICIO 1 ***********************/ CREATE OR REPLACE PROCEDURE listado1 AS CURSOR c1 IS SELECT titulo, paginas, nombre, apellidos FROM libros, autores WHERE id_autor=autor; BEGIN FOR datos IN c1 LOOP DBMS_OUTPUT.PUT_LINE(datos.titulo || ', de: ' || datos.nombre || ' ' || datos.apellidos); END LOOP; END; / /***************** EJERCICIO 2 ***********************/ CREATE OR REPLACE FUNCTION nombre_editorial (cod NUMBER) RETURN editoriales.nombre%TYPE AS nom editoriales.nombre%TYPE; BEGIN SELECT nombre INTO nom FROM editoriales WHERE id_editorial=cod; RETURN nom; END; / /***************** EJERCICIO 3 ***********************/ CREATE OR REPLACE PROCEDURE listado2 IS CURSOR c1 IS SELECT titulo, nombre, apellidos, editorial, autor FROM libros, autores WHERE autor=id_autor

ORDER BY id_autor; autor_ant libros.autor%TYPE :=-1; tot_libros NUMBER(3) :=0; tot_autor NUMBER(2) :=0; BEGIN FOR datos IN c1 LOOP IF datos.autor<>autor_ant THEN IF autor_ant<>-1 THEN DBMS_OUTPUT.PUT_LINE('Total: ' || tot_autor); tot_autor:=0; END IF; DBMS_OUTPUT.PUT_LINE('****************** ' || datos.nombre || ' ' || datos.apellidos || ' *********************'); END IF; DBMS_OUTPUT.PUT_LINE(datos.titulo || ' -- Editorial: ' || nombre_editorial(datos.editorial)); tot_autor:=tot_autor+1; tot_libros:=tot_libros+1; autor_ant:=datos.autor; END LOOP; DBMS_OUTPUT.PUT_LINE('Total: ' || tot_autor); DBMS_OUTPUT.PUT_LINE('--------------------------------------------------'); DBMS_OUTPUT.PUT_LINE('--- Total de libros: ' || tot_libros); END; / /***************** EJERCICIO 4 ***********************/ CREATE OR REPLACE PROCEDURE aumenta_cap(pi NUMBER, num NUMBER) AS CURSOR c1 IS SELECT capacidad FROM estanterias WHERE piso=pi FOR UPDATE; BEGIN FOR datos IN c1 LOOP UPDATE estanterias SET capacidad=capacidad+num WHERE CURRENT OF c1; END LOOP; END; / /***************** EJERCICIO 5 ***********************/ CREATE OR REPLACE TRIGGER tr_devol AFTER UPDATE OF devuelto ON prestamos FOR EACH ROW WHEN (new.devuelto='S') BEGIN INSERT INTO devoluciones VALUES (:new.id_socio, :new.id_libro, SYSDATE); END; / /***************** EJERCICIO 6 ***********************/ CREATE OR REPLACE PROCEDURE listado3 AS dia_ant DATE := '01/01/1900'; gen_ant libros.genero%TYPE := '-1'; s_dev NUMBER(1) :=0;

n_dev NUMBER(1) :=0; tot_gen NUMBER(3) :=0; tot_dia NUMBER(3) :=0; tot NUMBER(3) :=0; tot_hi NUMBER(3):=0; tot_ju NUMBER(3):=0; tot_cl NUMBER(3):=0; tot_il NUMBER(3):=0; tot_no NUMBER(3):=0; CURSOR c1 IS SELECT titulo, devuelto, genero, fecha_prestamo FROM libros l, prestamos p WHERE l.id_libro=p.id_libro ORDER BY fecha_prestamo, genero; BEGIN FOR datos IN c1 LOOP IF (datos.genero <> gen_ant OR datos.fecha_prestamo <> dia_ant) IF gen_ant <> '-1' THEN DBMS_OUTPUT.PUT_LINE('==Total : ' || tot_gen); tot_gen:=0; END IF; END IF; IF datos.fecha_prestamo <> dia_ant THEN IF dia_ant <> '01/01/1900' THEN

THEN

DBMS_OUTPUT.PUT_LINE('����������������������������������������������'); DBMS_OUTPUT.PUT_LINE('**** PRESTADOS: ' || tot_dia || ' ** DEVUELTOS: ' || s_dev || ' ** NO DEVUELTOS: ' || n_dev); tot_dia:=0; s_dev:=0; n_dev:=0; END IF; DBMS_OUTPUT.PUT_LINE('*********************** ' || datos.fecha_prestamo || ' ***********************'); END IF; IF (datos.genero <> gen_ant OR datos.fecha_prestamo <> dia_ant) THEN DBMS_OUTPUT.PUT_LINE('----- ' || datos.genero || ' -----'); END IF; DBMS_OUTPUT.PUT_LINE(datos.titulo); tot_gen:=tot_gen+1; tot_dia:=tot_dia+1; tot:=tot+1; IF datos.devuelto='S' THEN s_dev:=s_dev+1; ELSE n_dev:=n_dev+1; END IF; IF datos.genero='HISTORIA' THEN tot_hi:=tot_hi+1; ELSIF datos.genero='NOVELA' THEN tot_no:=tot_no+1; ELSIF datos.genero='JUVENIL' THEN tot_ju:=tot_ju+1; ELSIF datos.genero='ILUSTRADO' THEN tot_il:=tot_il+1; ELSIF datos.genero='CLASICO' THEN tot_cl:=tot_cl+1; END IF;

gen_ant:=datos.genero; dia_ant:=datos.fecha_prestamo; END LOOP; DBMS_OUTPUT.PUT_LINE('==Total : ' || tot_gen); DBMS_OUTPUT.PUT_LINE('����������������������������������������������'); DBMS_OUTPUT.PUT_LINE('**** PRESTADOS: ' || tot_dia || ' ** DEVUELTOS: ' || s_dev || ' ** NO DEVUELTOS: ' || n_dev); DBMS_OUTPUT.PUT_LINE('============================================================ ==========='); DBMS_OUTPUT.PUT_LINE('TOTAL DE LIBROS PRESTADOS: ' || tot); DBMS_OUTPUT.PUT_LINE ('Total de libros de historia: ' || tot_hi); DBMS_OUTPUT.PUT_LINE ('Porcentaje de historia: ' || TRUNC(tot_hi*100/tot,2) || '%'); DBMS_OUTPUT.PUT_LINE ('Total de libros de novela: ' || tot_no); DBMS_OUTPUT.PUT_LINE ('Porcentaje de novela: ' || TRUNC(tot_no*100/tot,2) || '%'); DBMS_OUTPUT.PUT_LINE ('Total de libros juveniles: ' || tot_ju); DBMS_OUTPUT.PUT_LINE ('Porcentaje de juveniles: ' || TRUNC(tot_ju*100/tot,2) || '%'); DBMS_OUTPUT.PUT_LINE ('Total de libros ilustrados: ' || tot_il); DBMS_OUTPUT.PUT_LINE ('Porcentaje de ilustrados: ' || TRUNC(tot_il*100/tot,2) || '%'); DBMS_OUTPUT.PUT_LINE ('Total de libros cl�sicos: ' || tot_cl); DBMS_OUTPUT.PUT_LINE ('Porcentaje de cl�sicos: ' || TRUNC(tot_cl*100/tot,2) || '%'); END; / /***************** EJERCICIO 7 ***********************/ CREATE OR REPLACE TRIGGER tr_vista_prestados INSTEAD OF INSERT OR DELETE ON libros_prestados FOR EACH ROW DECLARE id_lib libros.id_libro%TYPE; BEGIN IF INSERTING THEN SELECT id_libro INTO id_lib FROM libros WHERE titulo=:new.titulo; INSERT INTO prestamos VALUES (:new.id_socio, id_lib, SYSDATE, 'N'); ELSIF DELETING THEN SELECT id_libro INTO id_lib FROM libros WHERE titulo=:old.titulo; UPDATE prestamos SET devuelto='S' WHERE id_socio=:old.id_socio AND id_libro=id_lib; END IF; END; / /***************** EJERCICIO 8 ***********************/ CREATE OR REPLACE PROCEDURE dar_baja_libros (num libros.id_libro%TYPE) AS prestado EXCEPTION; CURSOR c1 IS SELECT id_libro FROM prestamos WHERE id_libro=num AND devuelto='N'; total NUMBER(1):=0;

datos libros%ROWTYPE; BEGIN COMMIT; FOR datos IN c1 LOOP total:=c1%ROWCOUNT; END LOOP; IF total>0 THEN RAISE prestado; END IF; SELECT * INTO datos FROM libros WHERE id_libro=num; INSERT INTO baja_libros VALUES (datos.id_libro, datos.autor, datos.titulo, datos.editorial, datos.paginas, datos.genero); DELETE FROM libros WHERE id_libro=num; COMMIT; EXCEPTION WHEN prestado THEN DBMS_OUTPUT.PUT_LINE('El libro est� prestado y no se puede dar de baja'); ROLLBACK; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error'); ROLLBACK; END; / /***************** EJERCICIO 9 ***********************/ CREATE OR REPLACE PACKAGE gest_biblio AS PROCEDURE insertar_socio(nom VARCHAR2, ape VARCHAR2, fecha DATE); PROCEDURE insertar_autor(nom VARCHAR2, ape VARCHAR2, nac VARCHAR2); PROCEDURE insertar_libro(aut NUMBER, tit VARCHAR2, edi NUMBER, pag NUMBER, gen VARCHAR2); PROCEDURE datos_libro(num NUMBER); PROCEDURE datos_libro(tit VARCHAR2); PROCEDURE devolucion(soc NUMBER, lib NUMBER); PROCEDURE estadistica; PROCEDURE estado_socio(num NUMBER); END; CREATE OR REPLACE PACKAGE BODY gest_biblio AS PROCEDURE insertar_socio(nom VARCHAR2, ape VARCHAR2, fecha DATE) AS ulti NUMBER(3); BEGIN SELECT MAX(id_socio) INTO ulti FROM socios; ulti:=ulti+1; INSERT INTO socios VALUES (ulti, nom, ape, fecha); END; PROCEDURE insertar_autor(nom VARCHAR2, ape VARCHAR2, nac VARCHAR2) AS ulti NUMBER(3); BEGIN SELECT MAX(id_autor) INTO ulti FROM autores; ulti:=ulti+1; INSERT INTO autores VALUES (ulti, nom, ape, nac); END;

PROCEDURE insertar_libro(aut NUMBER, tit VARCHAR2, edi NUMBER, pag NUMBER, gen VARCHAR2) AS ulti NUMBER(3); BEGIN SELECT MAX(id_libro) INTO ulti FROM libros; ulti:=ulti+1; INSERT INTO libros VALUES (ulti, aut, tit, edi, pag, gen); END; PROCEDURE datos_libro(num NUMBER) AS tit libros.titulo%TYPE; nom autores.nombre%TYPE; ape autores.apellidos%TYPE; pag libros.paginas%TYPE; gen libros.genero%TYPE; i_edi libros.editorial%TYPE; edi editoriales.nombre%TYPE; veces NUMBER(3); BEGIN

SELECT titulo, nombre, apellidos, paginas, genero, editorial INTO tit, nom, ape, pag, gen, i_edi FROM libros, autores WHERE id_libro=num and autor=id_autor; SELECT nombre INTO edi FROM editoriales WHERE id_editorial=i_edi; SELECT COUNT(*) INTO veces FROM prestamos WHERE id_libro=num; DBMS_OUTPUT.PUT_LINE('T�tulo: ' || tit || ' Autor: ' || nom || ' ' || ape); DBMS_OUTPUT.PUT_LINE('P�ginas: ' || pag || ' Editorial: ' || edi || ' G�nero: ' || gen); DBMS_OUTPUT.PUT_LINE('Ha sido prestado ' || veces || ' veces'); END; PROCEDURE datos_libro(tit VARCHAR2) AS CURSOR c1 IS SELECT id_libro FROM libros WHERE titulo LIKE '%' || tit || '%'; BEGIN FOR datos IN c1 LOOP datos_libro(datos.id_libro); END LOOP; END; PROCEDURE devolucion(soc NUMBER, lib NUMBER) AS BEGIN UPDATE prestamos SET devuelto='S' WHERE id_socio=soc AND id_libro=lib; END; PROCEDURE estadistica AS CURSOR c1 IS

BEGIN

SELECT titulo, COUNT(devuelto) veces FROM libros l, prestamos p WHERE l.id_libro=p.id_libro(+) GROUP BY titulo ORDER BY veces DESC;

FOR datos IN c1 LOOP DBMS_OUTPUT.PUT_LINE(datos.titulo || ' ** Prestado ' || datos.veces || ' veces'); END LOOP; END; PROCEDURE estado_socio(num NUMBER) AS tomados NUMBER(3); si_dev NUMBER(3); no_dev NUMBER(3); CURSOR c1 IS SELECT titulo FROM libros l, prestamos p WHERE id_socio=num AND l.id_libro=p.id_libro AND devuelto='N'; BEGIN

END; END; /

SELECT COUNT(*) INTO tomados FROM prestamos WHERE id_socio=num; SELECT COUNT(*) INTO si_dev FROM prestamos WHERE id_socio=num AND devuelto='S'; SELECT COUNT(*) INTO no_dev FROM prestamos WHERE id_socio=num AND devuelto='N'; DBMS_OUTPUT.PUT_LINE('Libros prestados: ' || tomados); DBMS_OUTPUT.PUT_LINE('Libros devueltos: ' || si_dev); DBMS_OUTPUT.PUT_LINE('Libros no devueltos: ' || no_dev); DBMS_OUTPUT.PUT_LINE('T�tulos de los libros no devueltos: '); FOR datos IN c1 LOOP DBMS_OUTPUT.PUT_LINE(datos.titulo); END LOOP;