• Author / Uploaded
• Donald Lopez

• Categories
• SQL
• Software
• Datos
• Recuperación de información
• Informática

Citation preview

Universidad Mariano Gálvez Facultad de Ingeniería en Sistemas Sede Regional Chiquimula Curso: Diseño de Bases de Datos Catedrático: Ing. Manuel Noriega

Practica de Oracle (Triggers) Instrucciones: La resolución de la práctica debe de ser de forma individual. Puede hacer uso de todo el material necesario. Genere un script con todas las sentencias SQL que utilizo para resolver la práctica con sus comentarios respectivos, el cual deberá de entregar al finalizar la misma. A cada una de las estructuras que creará en la base de datos, favor de agregar al final del nombre las iníciales de sus nombres y apellidos. Ejemplo: para la tabla AUTORES, el nombre de su estructura debe de ser AUTORES_MFNP. 1. Cree las siguientes tablas con la siguiente estructura: Autores = { ID: Nombre: Apellidos: }

numérico (PK) carácter (50) carácter (50)

Libros = { ISBN: Género: Título: Páginas: Precio: Copyright: Autor1: Autor2: Autor3: }

carácter (10) (PK) carácter (20) carácter (100) numérico numérico numérico (4) numérico (FK autores) numérico (FK autores) numérico (FK autores)

Estadísticas = { Género: carácter (20) Total_Libros: numérico Precio_Medio: numérico }

1

2. Cree y estudie el código de los siguientes disparadores. Trate de expresar con palabras cuál es el resultado esperado de su ejecución. /* TRIGGER 1*/ CREATE OR REPLACE TRIGGER ActualizarEstadisticas AFTER INSERT OR DELETE OR UPDATE ON libros DECLARE CURSOR c_Estadisticas IS SELECT genero, COUNT(*) total_libros, AVG(precio) precio_medio FROM libros GROUP BY genero; v_genero estadisticas.genero%TYPE; v_total_libros estadisticas.total_libros%TYPE; v_precio_medio estadisticas.precio_medio%TYPE; BEGIN DELETE FROM estadisticas; OPEN c_Estadisticas; LOOP FETCH c_Estadisticas INTO v_genero, v_total_libros, v_precio_medio; EXIT WHEN c_Estadisticas%NOTFOUND; INSERT INTO estadisticas (genero, total_libros, precio_medio) VALUES (v_genero, v_total_libros, v_precio_medio); END LOOP; CLOSE c_Estadisticas; END ActualizarEstadisticas;

/* TRIGGER 2*/ CREATE OR REPLACE TRIGGER ActualizarEstadisticas_FOR AFTER INSERT OR DELETE OR UPDATE ON libros DECLARE CURSOR c_Estadisticas IS SELECT genero, COUNT(*) total_libros, AVG(precio) precio_medio FROM libros GROUP BY genero; BEGIN DELETE FROM estadisticas; FOR v_RegistroEstadisticas in c_Estadisticas LOOP INSERT INTO estadisticas (genero, total_libros, precio_medio) VALUES (v_RegistroEstadisticas.genero, v_RegistroEstadisticas.total_libros, v_RegistroEstadisticas.precio_medio); END LOOP; END ActualizarEstadisticas_FOR;

3. Para comprobar el funcionamiento del disparador: a) Consulte la totalidad de las 3 tablas creadas 2

b) Inserte los datos de varios autores y a continuación utilice los datos de dichos autores para insertar datos en la tabla Libros. Abajo se proporcionan los valores a insertar. c) Repita la consulta de las 3 tablas y compruebe el efecto de la ejecución del disparador d) Elimine de la tabla libros 2 de los libros del género Oracle Basics y consulte la tabla Estadísticas. ¿El disparador funciona también cuando se borran filas? AUTORES ID NOMBRE APELLIDOS 1 Marlene Thierault 2 Rachel Charmichael 3 James Viscusi 4 Michael Abbey 5 Michael Corey 6 Gaja Vaidyanatha 7 Kirtikumar Deshpande 8 John Kostelac 9 Ian Abramson 10 Kenny Smith 11 Stephan Haisley 15 David James 16 Graham Seibert 17 Simon Russell 18 Bastin Gerald 19 Nigel King 20 Dan Natchek LIBROS ISBN 72122048 72131454 72192798 72194618 72133791 72222492

GÉNERO Oracle Basics Oracle Basics Oracle Basics Oracle Basics Oracle Ebusiness Oracle Ebusiness

TÍTULO Oracle8i: A Beginner''s Guide Oracle Performance Tuning Oracle9i: A Beginner''s Guide Oracle Backup & Recovery Oracle E-Business Suite Oracle9i Application Server

PÁGS. Precio Cpyrt 765 440.99 1999 101 404.73 2001 535 369.99 2002 101 309.99 2002 820 539.99 2002 544 475.99 2002

Aut1 4 6 4 10 15 4

Aut2 5 7 5 11 16 8

Aut3 8 9 17 15

4. Cree una vista con la siguiente estructura (LIBROS_AUTORES):

a) Consulte la vista que acaba de crear b) Inserte en la vista la siguiente tupla: 3

(‘72122048’, ‘Oracle8i: A Beginner''s Guide’, ‘Steve’, ‘Blow’); ¿Qué sucede? 5. Cree y estudie el código del siguiente disparador y trate de explicar cuál es el efecto de su ejecución. /* secuencia de números para asegurar que no se repite el identificador asignado a un autor */ CREATE SEQUENCE autor_id_sequence START WITH 10000; /* secuencia de números para asegurar que no se repite el identificador asignado a un libro */ CREATE SEQUENCE isbn_sequence START WITH 75000; CREATE OR REPLACE TRIGGER insertlibrosautores INSTEAD OF INSERT ON libros_autores REFERENCING NEW AS NEW OLD AS OLD DECLARE v_Libro libros%ROWTYPE; v_AutorID autores.id%TYPE; v_isbn libros.isbn%TYPE; BEGIN BEGIN SELECT id INTO v_AutorID FROM autores WHERE nombres = :new.nombres AND apellidos = :new.apellidos; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO autores (id, nombres, apellidos) VALUES (autor_id_sequence.NEXTVAL, :new.nombres, :new.apellidos) RETURNING ID INTO v_AutorID; END; BEGIN SELECT * INTO v_Libro FROM libros WHERE isbn = :new.isbn; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO libros (isbn, titulo) VALUES (isbn_sequence.NEXTVAL, :new.titulo) RETURNING isbn INTO v_isbn; SELECT * INTO v_Libro FROM libros WHERE isbn = v_isbn; END; IF v_Libro.autor1 IS NULL THEN

4

UPDATE libros SET autor1 = v_AutorID WHERE isbn = v_isbn; ELSIF v_libro.autor2 IS NULL THEN UPDATE libros SET autor2 = v_AutorID WHERE isbn = v_isbn; ELSIF v_libro.autor3 IS NULL THEN UPDATE libros SET autor3 = v_AutorID WHERE isbn = v_isbn; ELSE RAISE_APPLICATION_ERROR(-2005, v_libro.titulo || ' ya tiene 2 autores'); END IF; END InsertLibrosAutores;

6. Elimine los disparadores que se ejecutan sobre la tabla LIBROS y repita la inserción del apartado 5.b ¿Qué sucede ahora? 7. En este apartado comprobará el orden de ejecución de los triggers. Cree los siguientes objetos CREATE SEQUENCE orden_triggers START WITH 1 INCREMENT BY 1; CREATE OR REPLACE PACKAGE PaqueteTriggers AS -- Contador global v_Contador NUMBER; END PaqueteTriggers; CREATE TABLE tabla_temp ( Numero NUMBER, Mensaje VARCHAR2(200) );

8. Ahora creará 7 triggers diferentes sobre la tabla Libros para comprobar cuando se dispara cada tipo de disparador y cuando son ejecutadas las acciones que contiene. /* En primer lugar, un disparador que se dispara antes de cada sentencia de actualización lanzada sobre la tabla Libros. */ CREATE OR REPLACE TRIGGER AntesSentencia BEFORE UPDATE ON libros BEGIN -- Resetea el contador. PaqueteTriggers.v_Contador := 0; INSERT INTO tabla_temp (numero, mensaje) VALUES (orden_triggers.NEXTVAL, 'Antes de la Sentencia (befote): contador = ' || PaqueteTriggers.v_Contador); -- Y lo incrementa para cuando se lance el siguiente trigger. PaqueteTriggers.v_Contador := PaqueteTriggers.v_Contador + 1; END AntesSentencia;

5

/* A continuación un disparador que se lanza después de cada sentencia de actualización lanzada sobre la tabla Libros */ CREATE OR REPLACE TRIGGER DespuesSentencia1 AFTER UPDATE ON libros BEGIN INSERT INTO tabla_temp (numero, mensaje) VALUES (orden_triggers.NEXTVAL, 'Despues Sentencia 1: contador = ' || PaqueteTriggers.v_Contador); -- Actualiza el valor del contador. PaqueteTriggers.v_Contador := PaqueteTriggers.v_Contador + 1; END DespuesSentencia1; /* Un disparador igual al anterior pero cambiando el nombre para poder identificar el efecto de su ejecución */ CREATE OR REPLACE TRIGGER DespuesSentencia2 AFTER UPDATE ON libros BEGIN INSERT INTO tabla_temp (numero, mensaje) VALUES (orden_triggers.NEXTVAL, 'Despues Sentencia 2: contador = ' || PaqueteTriggers.v_Contador); -- Actualiza el valor del contador. PaqueteTriggers.v_Contador := PaqueteTriggers.v_Contador + 1; END DespuesSentencia2; /

Siguiendo la misma estructura que los anteriores, cree tres disparadores que se ejecuten antes de la sentencia y a nivel de fila: AntesFila1, AntesFila2 y AntesFila3 y cuide de que cada uno muestre los mensajes correspondientes: ( …… BEFORE UDPATE ON Libros FOR EACH ROW ……) Finalmente, cree un disparador que se ejecute después de cada sentencia a nivel de fila: DespuesFila 9. Ahora lance una sentencia de actualización sobre la tabla Libros de forma que todos los libros del género ‘Oracle Ebusiness’ pasen a ser del género ‘Nuevo Genero’ 10. Consulte la totalidad de la tabla tabla_temp y a partir de los datos que contiene, deduzca cuál es el orden de ejecución de los disparadores.

6