5.5. ACTIVIDADES RESUELTAS¶
5.5.1. Práctica 1¶
INSERT / UPDATE / DELETE
Haciendo uso del esquema E01 cuyo diseño físico realizamos en el tema 3, realiza las operaciones de manipulación de datos indicadas a continuación.
5.5.1.1. Realiza las siguientes inserciones (los datos puedes inventarlos).¶
- Inserta 2 profesores.
- Inserta 4 asignaturas.
- Inserta 10 alumnos.
- Cada alumno debe realizar al menos 2 asignaturas.
5.5.1.2. Introduce 2 profesores con el mismo NIF. ¿Qué sucede? ¿Por qué?¶
5.5.1.3. Introduce 2 alumnos con el mismo NumMatrícula. ¿Qué sucede? ¿Por qué?¶
5.5.1.4. Introduce 3 alumnos para los cuales no conocemos el número de teléfono.¶
5.5.1.5. Modifica los datos de los 3 alumnos anteriores para establecer un número de teléfono.¶
5.5.1.6. Para todos los alumnos, poner 2000 como año de nacimiento.¶
5.5.1.7. Para los profesores que tienen número de teléfono y NIF no comience por 9, poner ‘Informática’ como especialidad.¶
5.5.1.8. Cambia la asignación de asignaturas para los profesores. Es decir, las asignaturas impartidas por un profesor las dará el otro profesor y viceversa.¶
5.5.1.9. En la tabla Recibe borra todos los registros que pertenecen a una de las asignaturas.¶
5.5.1.10. En la tabla Asignatura borra dicha asignatura.¶
5.5.1.11. Borra el resto de asignaturas. ¿Qué sucede? ¿Por qué? ¿Como lo solucionarías? ¿Podría haberse evitado el problema con otro diseño físico?¿Cómo?¶
5.5.1.12. Borra todos los profesores. ¿Qué sucede? ¿Por qué? ¿Como lo solucionarías? ¿Podría haberse evitado el problema con otro diseño físico?¿Cómo?¶
5.5.1.13. Borra todos los alumnos. ¿Qué sucede? ¿Por qué? ¿Como lo solucionarías? ¿Podría haberse evitado el problema con otro diseño físico?¿Cómo?¶
SOLUCIÓN
-- PRÁCTICA 5.1
/* Diseño físico
CREATE TABLE ALUMNO
(
NUMMATRICULA NUMBER(3) PRIMARY KEY,
NOMBRE VARCHAR2(50),
FECHANACIMIENTO DATE,
TELEFONO CHAR(9)
);
CREATE TABLE PROFESOR
(
IDPROFESOR NUMBER(2) PRIMARY KEY,
NIF_P CHAR(9) UNIQUE,
NOMBRE VARCHAR2(50),
ESPECIALIDAD VARCHAR2(50),
TELEFONO CHAR(9)
);
CREATE TABLE ASIGNATURA
(
CODASIGNATURA CHAR(6) PRIMARY KEY,
NOMBRE VARCHAR2(30),
IDPROFESOR NUMBER(2) REFERENCES PROFESOR(IDPROFESOR)
);
CREATE TABLE RECIBE
(
NUMMATRICULA NUMBER(3) REFERENCES ALUMNO(NUMMATRICULA),
CODASIGNATURA CHAR(6) REFERENCES ASIGNATURA(CODASIGNATURA),
CURSOESCOLAR CHAR(9),
PRIMARY KEY (NUMMATRICULA, CODASIGNATURA, CURSOESCOLAR)
);
*/
-- 1. Inserción de datos
INSERT INTO PROFESOR VALUES (1, '12453645M', 'PACO', 'MATEMÁTICAS', '935210487');
INSERT INTO PROFESOR VALUES (2, '12453646N', 'LUCIA', 'LENGUAJE', '998210487');
INSERT INTO ASIGNATURA VALUES ('MATEMA', 'MATEMÁTICAS', 1);
INSERT INTO ASIGNATURA VALUES ('LENGUA', 'LENGUA', 2);
INSERT INTO ASIGNATURA VALUES ('BIOLOG', 'BIOLOGÍA', 1);
INSERT INTO ASIGNATURA VALUES ('QUIMIC', 'QUÍMICA', 2);
INSERT INTO ALUMNO VALUES (100, 'ANA', '08/12/1993', '658171701');
INSERT INTO ALUMNO VALUES (102, 'PEPE', '09/12/1993', '659271789');
INSERT INTO ALUMNO VALUES (103, 'JUAN', '10/12/1992', '660771709');
INSERT INTO ALUMNO VALUES (104, 'RODOLFO', '08/11/1990', '689271708');
INSERT INTO ALUMNO VALUES (105, 'ANGUSTIAS', '09/11/1993', '698191707');
INSERT INTO ALUMNO VALUES (106, 'AURELIO', '10/11/1993', '695171706');
INSERT INTO ALUMNO VALUES (107, 'ANACLETO', '12/03/1992', '620771705');
INSERT INTO ALUMNO VALUES (108, 'EUSEBIO', '20/04/1993', '689171704');
INSERT INTO ALUMNO VALUES (109, 'EUSTAQUIO', '25/05/1991', '641171703');
INSERT INTO ALUMNO VALUES (110, 'AMAPOLO', '27/06/1993', '689171702');
INSERT INTO RECIBE VALUES (100, 'MATEMA', '2016/2017');
INSERT INTO RECIBE VALUES (100, 'LENGUA', '2016/2017');
INSERT INTO RECIBE VALUES (102, 'MATEMA', '2016/2017');
INSERT INTO RECIBE VALUES (102, 'LENGUA', '2016/2017');
INSERT INTO RECIBE VALUES (103, 'MATEMA', '2016/2017');
INSERT INTO RECIBE VALUES (103, 'LENGUA', '2016/2017');
INSERT INTO RECIBE VALUES (104, 'MATEMA', '2016/2017');
INSERT INTO RECIBE VALUES (104, 'LENGUA', '2016/2017');
INSERT INTO RECIBE VALUES (105, 'BIOLOG', '2016/2017');
INSERT INTO RECIBE VALUES (105, 'QUIMIC', '2016/2017');
INSERT INTO RECIBE VALUES (106, 'BIOLOG', '2016/2017');
INSERT INTO RECIBE VALUES (106, 'QUIMIC', '2016/2017');
INSERT INTO RECIBE VALUES (107, 'BIOLOG', '2002/2003');
INSERT INTO RECIBE VALUES (107, 'LENGUA', '2002/2003');
INSERT INTO RECIBE VALUES (108, 'BIOLOG', '2016/2017');
INSERT INTO RECIBE VALUES (108, 'QUIMIC', '2016/2017');
INSERT INTO RECIBE VALUES (109, 'LENGUA', '2002/2003');
INSERT INTO RECIBE VALUES (109, 'BIOLOG', '2002/2003');
INSERT INTO RECIBE VALUES (110, 'QUIMIC', '2001/2002');
INSERT INTO RECIBE VALUES (110, 'BIOLOG', '2001/2002');
-- 2. Inserción de registros con la misma clave primaria.
-- Da error puesto que no puede haber dos registros con la misma clave primaria.
-- La clave primaria siempre tiene las restricciones de unicidad y valor no nulo.
INSERT INTO PROFESOR VALUES (3, '12453640B', 'JUAN', 'MATEMÁTICAS', '635210480');
INSERT INTO PROFESOR VALUES (3, '12453641C', 'JOSE', 'LENGUAJE', '698210481');
-- 3. Inserción de registros con la misma clave primaria.
-- Da error puesto que no puede haber dos registros con la misma clave primaria.
-- La clave primaria siempre tiene las restricciones de unicidad y valor no nulo.
INSERT INTO ALUMNO VALUES (111, 'EVA', '08/12/1993', '648171701');
INSERT INTO ALUMNO VALUES (111, 'LISA', '09/12/1993', '649271789');
-- 4. Inserción de registros con valores nulos. 3 formas distintas.
INSERT INTO ALUMNO VALUES (112, 'LORENA', '20/04/1993', NULL);
INSERT INTO ALUMNO (NUMMATRICULA, NOMBRE, FECHANACIMIENTO)
VALUES (113, 'VANESA', '25/05/1991');
INSERT INTO ALUMNO (NOMBRE, FECHANACIMIENTO, NUMMATRICULA)
VALUES ('SILVIA', '27/06/1993', 114);
-- 5. Actualización de campos de registro con valores nulos.
UPDATE ALUMNO SET TELEFONO='652148568' WHERE NUMMATRICULA=112;
UPDATE ALUMNO SET TELEFONO='653148568' WHERE NUMMATRICULA=113;
UPDATE ALUMNO SET TELEFONO='669148568' WHERE NUMMATRICULA=114;
-- 6. Actualización de año en un campo de fecha.
UPDATE ALUMNO SET FECHANACIMIENTO = TO_CHAR(FECHANACIMIENTO,'DDMM')||'2000';
-- 7. Actualización de campo si se cumple una condición.
UPDATE PROFESOR SET ESPECIALIDAD='INFORMATICA' WHERE TELEFONO IS NOT NULL AND NIF_P NOT LIKE '9%';
-- 8. Actualización. Intercambio de valores.
-- Para poder hacer el intercambio necesitamos un valor intermedio.
UPDATE PROFESOR SET ESPECIALIDAD='TEMP' WHERE ESPECIALIDAD = 'MATEMÁTICAS';
UPDATE PROFESOR SET ESPECIALIDAD='MATEMÁTICAS' WHERE ESPECIALIDAD = 'LENGUAJE';
UPDATE PROFESOR SET ESPECIALIDAD='LENGUAJE' WHERE ESPECIALIDAD = 'TEMP';
-- 9. Borrado de registros en una Relación.
DELETE RECIBE WHERE CODASIGNATURA = 'LENGUA';
-- 10. Borrado de registros de una Entidad sin relaciones.
DELETE ASIGNATURA WHERE CODASIGNATURA = 'LENGUA';
-- 11. Borrado de registros de una Entidad con relaciones.
-- No nos permite borrar directamente los registros de la tabla ASIGNATURA
-- ya que dichos registros participan en una relación.
-- No tendríamos esta limitación si en el diseño físico hubiesemos definido
-- la clave foránea que apunta a CODASIGNATURA con la cláusula ON DELETE CASCADE.
DELETE ASIGNATURA;
-- 12. Borrado de registros de una Entidad con relaciones.
-- No nos permite borrar directamente los registros de la tabla PROFESOR
-- ya que dichos registros participan en una relación.
-- No tendríamos esta limitación si en el diseño físico hubiesemos definido
-- la clave foránea que apunta a IDPROFESOR con la cláusula ON DELETE CASCADE.
DELETE PROFESOR;
-- 13. Borrado de registros de una Entidad con relaciones.
-- No nos permite borrar directamente los registros de la tabla ALUMNO
-- ya que dichos registros participan en una relación.
-- No tendríamos esta limitación si en el diseño físico hubiesemos definido
-- la clave foránea que apunta a NUMMATRICULA con la cláusula ON DELETE CASCADE.
DELETE ALUMNO;
-- En las 3 actividades anteriores la solución pasa por eliminar
-- la restricción de clave foránea y volverla a añadir con la cláusula
-- ON DELETE CASCADE.
-- Podemos conseguir esto cambiando el diseño físico:
-- ALTER TABLE RECIBE DROP CONSTRAINT FK2...;
-- ALTER TABLE RECIBE ADD CONSTRAINT FK2...
-- FOREIGN KEY(CODASIGNATURA) REFERENCES ASIGNATURA ON DELETE CASCADE;
-- ALTER TABLE ASIGNATURA DROP CONSTRAINT FK1...;
-- ALTER TABLE ASIGNATURA ADD CONSTRAINT FK1...
-- FOREIGN KEY(IDPROFESOR) REFERENCES PROFESOR ON DELETE CASCADE;
-- ALTER TABLE RECIBE DROP CONSTRAINT FK1...;
-- ALTER TABLE RECIBE ADD CONSTRAINT FK1...
-- FOREIGN KEY(NUMMATRICULA) REFERENCES ALUMNO ON DELETE CASCADE;
-- Sin embargo la solución se complica bastante puesto que
-- en el diseño físico habíamos creado las restricciones sin ponerle nombre.
-- Este es uno de los motivos por lo que es importante poner nombre a
-- las restricciones. Así, si luego necesitamos modificar el diseño físico
-- podremos hacerlo de forma sencilla.
5.5.2. Práctica 2¶
INSERT / UPDATE / DELETE
Haciendo uso del esquema E07 cuyo diseño físico realizamos en el tema 3, realiza las operaciones de manipulación de datos indicadas a continuación.
Teniendo en cuenta las siguientes restricciones que teníamos declaradas:
- No pueden ser nulos los siguientes campos: Nombre de Socio, Título de Película.
- Sexo toma los valores H o M.
- Por defecto si no se indica nada un actor o actriz no es Protagonista (este campo toma valores S o N).
- FechaDevolución debe ser mayor que FechaAlquiler.
5.5.2.1. Realiza las siguientes inserciones (los datos puedes inventarlos).¶
- Inserta 2 directores.
- Inserta 4 películas. Todas ellas están dirigidas por alguno de los directores anteriores.
- Inserta 2 ejemplares de cada película.
- Inserta 4 socios.
- Inserta como mínimo 6 actores.
- Cada película debe tener al menos el actor/atriz protagonista asociado.
- Todos los ejemplares deben tener al menos 1 alquiler.
5.5.2.2. Inserta valores para comprobar que la siguiente restricción funciona correctamente:¶
- No pueden ser nulos los siguientes campos: Nombre de Socio, Título de Película.
5.5.2.3. Inserta valores para comprobar que la siguiente restricción funciona correctamente:¶
- Sexo toma los valores H o M.
5.5.2.4. Inserta valores para comprobar que la siguiente restricción funciona correctamente:¶
- Por defecto si no se indica nada un actor o actriz no es Protagonista (este campo toma valores S o N).
5.5.2.5. Inserta valores para comprobar que la siguiente restricción funciona correctamente:¶
- FechaDevolución debe ser mayor que FechaAlquiler.
5.5.2.6. Cambia la nacionalidad para los directores. Por ejemplo de ‘Estadounidense’ a ‘USA’ o similar, dependiendo de los valores que hayas introducido.¶
5.5.2.7. Cambia la nacionalidad para los actores. Por ejemplo de ‘Estadounidense’ a ‘USA’ o similar, dependiendo de los valores que hayas introducido.¶
5.5.2.8. Modifica los datos de todos los socios para que el avalista sea un único socio, siempre el mismo para todos, excepto para el avalista mismo que no dispone de ninguno.¶
5.5.2.9. Elimina los socios cuyo número de teléfono empiece por una cifra inferior a 5. ¿Qué sucede?¿Por qué?¶
5.5.2.10. Elimina los socios cuyo número de teléfono empiece por una cifra superior o igual a 5. ¿Qué sucede?¿Por qué?¶
5.5.2.11. ¿Como lo solucionarías los problemas anteriores? ¿Podría haberse evitado el problema con otro diseño físico?¿Cómo?¶
5.5.2.12. Elimina todos los directores. ¿Qué sucede?¿Por qué?¶
5.5.2.13. Elimina 2 películas, las que desees.¿Qué sucede?¿Por qué?¿Como lo solucionarías? ¿Podría haberse evitado el problema con otro diseño físico?¿Cómo?¶
SOLUCIÓN
-- PRÁCTICA 5.2
/* Diseño físico
CREATE TABLE DIRECTOR
(
NOMBRE VARCHAR2(40) CONSTRAINT PK_DIRECTOR PRIMARY KEY,
NACIONALIDAD VARCHAR2(40)
);
CREATE TABLE PELICULA
(
ID NUMBER CONSTRAINT PK_PELICULA PRIMARY KEY,
TITULO VARCHAR2(40),
PRODUCTORA VARCHAR2(40),
NACIONALIDAD VARCHAR2(40),
FECHA DATE,
DIRECTOR VARCHAR2(40) CONSTRAINT FK_DIRECTOR
REFERENCES DIRECTOR(NOMBRE)
);
CREATE TABLE EJEMPLAR
(
IDPELICULA NUMBER,
NUMERO NUMBER(2),
ESTADO VARCHAR2(40),
CONSTRAINT PK_EJEMPLAR PRIMARY KEY(IDPELICULA, NUMERO),
CONSTRAINT FK_EJEMPLAR FOREIGN KEY(IDPELICULA)
REFERENCES PELICULA(ID)
);
CREATE TABLE ACTORES
(
NOMBRE VARCHAR2(40),
NACIONALIDAD VARCHAR2(40),
SEXO CHAR(1),
CONSTRAINT PK_ACTORES PRIMARY KEY(NOMBRE),
CONSTRAINT CK_SEXO CHECK (SEXO IN ('H', 'M'))
);
CREATE TABLE SOCIO
(
DNI CHAR(9),
NOMBRE VARCHAR2(40) CONSTRAINT NN_NOMBRE NOT NULL,
DIRECCION VARCHAR2(40),
TELEFONO CHAR(9),
AVALADOR CHAR(9),
CONSTRAINT PK_SOCIO PRIMARY KEY(DNI),
CONSTRAINT FK_SOCIO FOREIGN KEY(AVALADOR) REFERENCES SOCIO(DNI)
);
CREATE TABLE ACTUA
(
ACTOR VARCHAR2(40)
CONSTRAINT FK1_ACTUA REFERENCES ACTORES ON DELETE CASCADE,
IDPELICULA NUMBER
CONSTRAINT FK2_ACTUA REFERENCES PELICULA ON DELETE CASCADE,
PROTAGONISTA CHAR(1) DEFAULT 'N',
CONSTRAINT PK_ACTUA PRIMARY KEY(ACTOR, IDPELICULA),
CONSTRAINT CK_PROTAGONISTA CHECK (PROTAGONISTA IN ('S', 'N'))
);
CREATE TABLE ALQUILA
(
DNI CHAR(9),
IDPELICULA NUMBER,
NUMERO NUMBER(2),
FECHA_ALQUILER DATE,
FECHA_DEVOLUCION DATE,
CONSTRAINT PK_ALQUILA
PRIMARY KEY(DNI, IDPELICULA, NUMERO, FECHA_ALQUILER),
CONSTRAINT FK1_DNI FOREIGN KEY(DNI) REFERENCES SOCIO(DNI),
CONSTRAINT FK2_PELI FOREIGN KEY(IDPELICULA, NUMERO)
REFERENCES EJEMPLAR(IDPELICULA, NUMERO),
CONSTRAINT CK_FECHAS CHECK (FECHA_DEVOLUCION > FECHA_ALQUILER)
);
*/
-- 1. Inserción de datos
INSERT INTO DIRECTOR VALUES ('JUAN', 'ESPAÑOLA');
INSERT INTO DIRECTOR VALUES ('PEDRO', 'ESPAÑOLA');
INSERT INTO PELICULA VALUES (001, 'LA GRANJA', 'TELE5', 'RUSA', '10/03/1987', 'JUAN');
INSERT INTO PELICULA VALUES (002, 'MANDINGO', 'ANTENA3', 'JAPONESA', '11/03/1988', 'JUAN');
INSERT INTO PELICULA VALUES (003, 'FRANCO', 'INTERECONOMIA','ESPAÑOLA', '01/01/1990', 'PEDRO');
INSERT INTO PELICULA VALUES (004, 'COSMOS', 'TELE5', 'ITALIANA', '15/10/2000', 'PEDRO');
INSERT INTO EJEMPLAR VALUES (001, 01, 'BIEN');
INSERT INTO EJEMPLAR VALUES (001, 02, 'MAL');
INSERT INTO EJEMPLAR VALUES (002, 01, 'BIEN');
INSERT INTO EJEMPLAR VALUES (002, 02, 'MAL');
INSERT INTO EJEMPLAR VALUES (003, 01, 'BIEN');
INSERT INTO EJEMPLAR VALUES (003, 02, 'REGULAR');
INSERT INTO EJEMPLAR VALUES (004, 01, 'BIEN');
INSERT INTO EJEMPLAR VALUES (004, 02, 'REGULAR');
INSERT INTO SOCIO VALUES (15405978, 'DANIEL', 'C/ NUEVA, 1', 697565656, NULL);
INSERT INTO SOCIO VALUES (15405979, 'ANA', 'C/ ANCHA, 5', 697545454, 15405978);
INSERT INTO SOCIO VALUES (15405971, 'SILVIA', 'C/ FORT, 4', 697121212, 15405979);
INSERT INTO SOCIO VALUES (15405972, 'XAVI', 'C/ ANCHA, 2', 197232323, 15405971);
INSERT INTO ACTORES VALUES ('PENELOPE', 'ESPAÑOLA', 'M');
INSERT INTO ACTORES VALUES ('FRANCESCO', 'ITALIANA', 'H');
INSERT INTO ACTORES VALUES ('ANA', 'POLACA', 'M');
INSERT INTO ACTORES VALUES ('CARMEN', 'ESPAÑOLA', 'M');
INSERT INTO ACTORES VALUES ('ANDREA', 'ITALIANA', 'H');
INSERT INTO ACTORES VALUES ('VLADIMIR', 'RUSA', 'H');
INSERT INTO ACTUA VALUES ('ANDREA', 001, 'S');
INSERT INTO ACTUA VALUES ('VLADIMIR', 001, 'N');
INSERT INTO ACTUA VALUES ('CARMEN', 002, 'S');
INSERT INTO ACTUA VALUES ('PENELOPE', 003, 'S');
INSERT INTO ACTUA VALUES ('FRANCESCO',003, 'N');
INSERT INTO ACTUA VALUES ('ANA', 004, 'S');
INSERT INTO ALQUILA VALUES (15405978, 001,01, '01/01/2017', '03/01/2017');
INSERT INTO ALQUILA VALUES (15405979, 001,02, '01/01/2017', '03/01/2017');
INSERT INTO ALQUILA VALUES (15405971, 002,01, '01/02/2017', '03/02/2017');
INSERT INTO ALQUILA VALUES (15405978, 002,02, '01/02/2017', '03/02/2017');
INSERT INTO ALQUILA VALUES (15405972, 003,01, '01/03/2017', '03/03/2017');
INSERT INTO ALQUILA VALUES (15405972, 003,02, '01/03/2017', '03/03/2017');
INSERT INTO ALQUILA VALUES (15405979, 004,01, '01/04/2017', '03/04/2017');
INSERT INTO ALQUILA VALUES (15405979, 004,02, '01/04/2017', '03/04/2017');
-- 2. Comprobación de restricciones
INSERT INTO SOCIO VALUES (15405918, NULL, 'C/ ROSAL, 3', 697565656, NULL);
-- El campo de nombre no puede ser nulo, puesto que tiene una restricción
-- de valor no nulo.
INSERT INTO PELICULA VALUES (010, NULL, 'TELE5', 'RUSA', '10/03/1987', 'JUAN');
-- El campo título sí puede estar a nulo, puesto que no es clave primaria,
-- ni tiene restricción de valor no nulo.
-- 3. Comprobación de restricciones
INSERT INTO ACTORES VALUES ('PUTIN', 'RUSA', 'H');
INSERT INTO ACTORES VALUES ('KIM', 'AMERICANA','M');
INSERT INTO ACTORES VALUES ('ROSA', 'RUSA', 'K');
-- La última inserción da error, puesto que sólo se permiten valores H o M
-- para el campo sexo.
-- 4. Comprobación de restricciones
INSERT INTO ACTUA (ACTOR, IDPELICULA) VALUES ('ANA', 002);
-- Si no indicamos valor para el campo protagonista, se introduce
-- el indicado por defecto en la restricción, es decir, valor N.
SELECT * FROM ACTUA WHERE ACTOR = 'ANA' AND IDPELICULA = 002;
-- 5. Comprobación de restricciones
INSERT INTO ALQUILA VALUES (15405972, 004,01, '01/03/2017', '03/02/2017');
-- Dará error, puesto que tenemos una restricción tipo check que
-- obliga a que la fecha de devolución sea mayor a la fecha de alquiler.
-- 6. Actualización de registros.
UPDATE DIRECTOR SET NACIONALIDAD = 'ESP' WHERE NACIONALIDAD = 'ESPAÑOLA';
-- 7. Actualización de registros.
UPDATE ACTORES SET NACIONALIDAD = 'ESP' WHERE NACIONALIDAD = 'ESPAÑOLA';
-- 8. Actualización de registros.
UPDATE SOCIO SET AVALADOR = '15405978' WHERE DNI != 15405978;
-- 9. Eliminación de registros que posiblemente participan en una relación.
DELETE SOCIO WHERE REGEXP_LIKE(TELEFONO, '[0-4].*');
-- Al borrar los registros, si dichos registros participan en una relación,
-- por ejemplo en la relación alquiler, no nos va a dejar eliminarlos.
-- 10. Eliminación de registros que posiblemente participan en una relación.
DELETE SOCIO WHERE REGEXP_LIKE(TELEFONO, '[5-9].*');
-- Al borrar los registros, si dichos registros participan en una relación,
-- por ejemplo en la relación alquiler, no nos va a dejar eliminarlos.
-- 11. Modificación de FK para añadir ON DELETE CASCADE.
-- La mejor forma de proceder sería eliminar la clave foránea FK1_DNI
-- de la tabla ALQUILA y volverla a crear con la cláusula ON DELETE CASCADE.
-- Después de esto, ya podemos ejecutar las 2 sentencias anteriores.
ALTER TABLE ALQUILA DROP CONSTRAINT FK1_DNI;
ALTER TABLE ALQUILA ADD CONSTRAINT FK1_DNI FOREIGN KEY(DNI)
REFERENCES SOCIO ON DELETE CASCADE;
-- 12. Modificación de FK.
DELETE DIRECTOR;
-- No permite la eliminación de registros, puesto que dichos directores
-- tienen películas asociadas.
-- Podríamos modificar la FK_DIRECTOR de la tabla PELICULA para añadir
-- la cláusula ON DELETE CASCADE, pero en este caso es más aconsejable
-- establecer la cláusula ON DELETE SET NULL. En tanto que PELICULA es
-- una entidad fuerte y existen datos que con toda seguridad desearemos
-- mantener, es mejor esta segunda solución.
-- Después de esto, ya podemos eliminar los directores sin afectar
-- a las películas.
ALTER TABLE PELICULA DROP CONSTRAINT FK_DIRECTOR;
ALTER TABLE PELICULA ADD CONSTRAINT FK_DIRECTOR FOREIGN KEY(DIRECTOR)
REFERENCES DIRECTOR ON DELETE SET NULL;
-- 13. Modificación de FK.
DELETE PELICULA WHERE ID = 001 OR ID = 002;
-- No permite la eliminación de registros, puesto que dichas películas
-- tienen ejemplares asociados y además, probablemente, aparezcan en la
-- relación ACTUA. La entidad EJEMPLAR es una entidad débil, cuya
-- existencia no tiene sentido sin la entidad fuerte, y la relación
-- ACTUA no guarda datos de ninguna entidad, solo sus relaciones.
-- En ambos casos sí es aconsejable usar ON DELETE CASCADE.
-- Después de esto, ya podemos ejecutar la sentencia anterior.
ALTER TABLE EJEMPLAR DROP CONSTRAINT FK_EJEMPLAR;
ALTER TABLE EJEMPLAR ADD CONSTRAINT FK_EJEMPLAR FOREIGN KEY(IDPELICULA)
REFERENCES PELICULA ON DELETE CASCADE;
ALTER TABLE ACTUA DROP CONSTRAINT FK2_ACTUA;
ALTER TABLE ACTUA ADD CONSTRAINT FK2_ACTUA FOREIGN KEY(IDPELICULA)
REFERENCES PELICULA ON DELETE CASCADE;
5.5.3. Práctica 3¶
PL/SQL: Introducción
Las siguientes prácticas se realizarán dentro del esquema EMPLEADOS.
5.5.3.1. Realiza una conexión utilizando el cliente SQL*Plus y muestra el valor de las siguientes variables: USER, ESCAPE, LINESIZE, COLSEP, PAGESIZE, ECHO, SQLPROMPT¶
5.5.3.2. Desde el cliente SQL*Plus muestra el valor de las variables AUTOCOMMIT y SERVEROUTPUT.¶
5.5.3.3. Desde el cliente SQL*Plus ejecuta el comando HELP SHOW para ver la ayuda acerca del comando SHOW.¶
5.5.3.4. Desde el cliente SQL*Plus ejecuta el comando HELP SET para ver la ayuda acerca del comando SET.¶
5.5.3.5. Desde el cliente SQL*Plus pon a ON las variables SERVEROUTPUT y AUTOCOMMIT.¶
5.5.3.6. Crea un esquema llamado PLSQL con contraseña PLSQL y rol DBA para realizar las siguientes actividades.Ejecuta el siguiente bloque. Indica cuál es la salida.¶
5.5.3.7. Ejecuta el siguiente bloque. Indica cuál es la salida.¶
5.5.3.8. Ejecuta el siguiente bloque. Indica cuál es la salida.¶
5.5.3.9. Ejecuta el siguiente bloque. Indica cuál es la salida.¶
5.5.3.10. Ejecuta el siguiente bloque. Indica cuál es la salida.¶
5.5.3.11. Ejecuta el siguiente bloque. Indica cuál es la salida.¶
5.5.3.12. Ejecuta el siguiente bloque. Indica cuál es la salida.¶
5.5.3.13. Ejecuta el siguiente bloque. Indica cuál es la salida.¶
5.5.3.14. Ejecuta el siguiente bloque. Indica cuál es la salida.¶
SOLUCIÓN
-- PRÁCTICA 5.3
-- SQLPLUS EMPLEADOS/EMPLEADOS
-- SQL> SHOW USER
-- USER is "EMPLEADOS"
-- 1. Realiza una conexión utilizando el cliente SQL*Plus y muestra
-- el valor de las siguientes variables: USER, ESCAPE, LINESIZE, COLSEP, PAGESIZE, ECHO, SQLPROMPT
SHOW USER
SHOW ESCAPE
SHOW LINESIZE
SHOW COLSEP
SHOW PAGESIZE
SHOW ECHO
SHOW SQLPROMPT
-- 2. Desde el cliente SQL*Plus muestra el valor de las variables AUTOCOMMIT y SERVEROUTPUT.
SHOW AUTOCOMMIT
SHOW SERVEROUTPUT
-- 3. Desde el cliente SQL*Plus ejecuta el comando HELP SHOW para ver la ayuda acerca del comando SHOW.
HELP SHOW
-- 4. Desde el cliente SQL*Plus ejecuta el comando HELP SET para ver la ayuda acerca del comando SET.
HELP SET
-- 5. Desde el cliente SQL*Plus pon a ON las variables SERVEROUTPUT y AUTOCOMMIT.
SET SERVEROUTPUT ON
SET AUTOCOMMIT ON
-- 6. Crea un esquema llamado PLSQL con contraseña PLSQL y rol DBA para realizar las siguientes actividades. Ejecuta el siguiente bloque. Indica cuál es la salida.
BEGIN
IF 10 > 5 THEN
DBMS_OUTPUT.PUT_LINE ('Cierto');
ELSE
DBMS_OUTPUT.PUT_LINE ('Falso');
END IF;
END;
/
-- Salida:
-- Cierto
-- 7. Ejecuta el siguiente bloque. Indica cuál es la salida.
BEGIN
IF 10 > 5 AND 5 > 1 THEN
DBMS_OUTPUT.PUT_LINE ('Cierto');
ELSE
DBMS_OUTPUT.PUT_LINE ('Falso');
END IF;
END;
/
-- Salida:
-- Cierto
-- 8. Ejecuta el siguiente bloque. Indica cuál es la salida.
BEGIN
IF 10 > 5 AND 5 > 50 THEN
DBMS_OUTPUT.PUT_LINE ('Cierto');
ELSE
DBMS_OUTPUT.PUT_LINE ('Falso');
END IF;
END;
/
-- Salida:
-- Falso
-- 9. Ejecuta el siguiente bloque. Indica cuál es la salida.
BEGIN
CASE
WHEN 10 > 5 AND 5 > 50 THEN
DBMS_OUTPUT.PUT_LINE ('Cierto');
ELSE
DBMS_OUTPUT.PUT_LINE ('Falso');
END CASE;
END;
/
-- Salida:
-- Falso
-- 10. Ejecuta el siguiente bloque. Indica cuál es la salida.
BEGIN
FOR i IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE (i);
END LOOP;
END;
/
-- Salida: Números entre 1 y 10
-- 1
-- 2
-- 3
-- ...
-- 9
-- 10
-- 11. Ejecuta el siguiente bloque. Indica cuál es la salida.
BEGIN
FOR i IN REVERSE 1..10 LOOP
DBMS_OUTPUT.PUT_LINE (i);
END LOOP;
END;
/
-- Salida: Números entre 10 y 1
-- 10
-- 9
-- 8
-- ...
-- 2
-- 1
-- 12. Ejecuta el siguiente bloque. Indica cuál es la salida.
DECLARE
num NUMBER(3) := 0;
BEGIN
WHILE num<=100 LOOP
DBMS_OUTPUT.PUT_LINE (num);
num:= num+2;
END LOOP;
END;
/
-- Salida: Números pares entre 0 y 100
-- 0
-- 2
-- 4
-- ...
-- 98
-- 100
-- 13. Ejecuta el siguiente bloque. Indica cuál es la salida.
DECLARE
num NUMBER(3) := 0;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE (num);
IF num > 100 THEN EXIT; END IF;
num:= num+2;
END LOOP;
END;
/
-- Salida: Números pares entre 0 y 102
-- 0
-- 2
-- 4
-- ...
-- 100
-- 102
-- 14. Ejecuta el siguiente bloque. Indica cuál es la salida.
DECLARE
num NUMBER(3) := 0;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE (num);
EXIT WHEN num > 100;
num:= num+2;
END LOOP;
END;
/
-- Salida: Números pares entre 0 y 102
-- 0
-- 2
-- 4
-- ...
-- 100
-- 102
5.5.4. Práctica 4¶
PL/SQL: Procedimientos y Funciones
5.5.4.1. Crea un procedimiento llamado ESCRIBE para mostrar por pantalla el mensaje HOLA MUNDO.¶
5.5.4.2. Crea un procedimiento llamado ESCRIBE_MENSAJE que tenga un parámetro de tipo VARCHAR2 que recibe un texto y lo muestre por pantalla. La forma del procedimiento será la siguiente:¶
ESCRIBE_MENSAJE (mensaje VARCHAR2)
5.5.4.3. Crea un procedimiento llamado SERIE que muestre por pantalla una serie de números desde un mínimo hasta un máximo con un determinado paso. La forma del procedimiento será la siguiente:¶
SERIE (minimo NUMBER, maximo NUMBER, paso NUMBER)
5.5.4.4. Crea una función AZAR que reciba dos parámetros y genere un número al azar entre un mínimo y máximo indicado. La forma de la función será la siguiente:¶
AZAR (minimo NUMBER, maximo NUMBER) RETURN NUMBER
5.5.4.5. Crea una función NOTA que reciba un parámetros que será una nota numérica entre 0 y 10 y devuelva una cadena de texto con la calificación (Suficiente, Bien, Notable, ...). La forma de la función será la siguiente:¶
NOTA (nota NUMBER) RETURN VARCHAR2
SOLUCIÓN
-- PRÁCTICA 5.4
-- 1. Crea un procedimiento llamado ESCRIBE para mostrar por pantalla
-- el mensaje HOLA MUNDO.
CREATE OR REPLACE
PROCEDURE ESCRIBE IS
BEGIN
DBMS_OUTPUT.PUT_LINE ('HOLA MUNDO');
END ESCRIBE;
/
-- 2. Crea un procedimiento llamado ESCRIBE_MENSAJE que tenga
-- un parámetro de tipo VARCHAR2 que recibe un texto y lo muestre por pantalla.
-- La forma del procedimiento será la siguiente:
-- ESCRIBE_MENSAJE (mensaje VARCHAR2)
CREATE OR REPLACE
PROCEDURE ESCRIBE_MENSAJE (mensaje VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE (mensaje);
END ESCRIBE_MENSAJE;
/
-- 3. Crea un procedimiento llamado SERIE que muestre por pantalla una
-- serie de números desde un mínimo hasta un máximo con un determinado paso.
-- La forma del procedimiento será la siguiente:
-- SERIE (minimo NUMBER, maximo NUMBER, paso NUMBER)
CREATE OR REPLACE
PROCEDURE SERIE (minimo NUMBER, maximo NUMBER, paso NUMBER) IS
num NUMBER := minimo;
BEGIN
WHILE num <= maximo LOOP
DBMS_OUTPUT.PUT_LINE (num);
num := num + paso;
END LOOP;
END SERIE;
/
-- 4. Crea una función AZAR que reciba dos parámetros y genere
-- un número al azar entre un mínimo y máximo indicado.
-- La forma de la función será la siguiente:
-- AZAR (minimo NUMBER, maximo NUMBER) RETURN NUMBER
CREATE OR REPLACE
FUNCTION AZAR (minimo NUMBER, maximo NUMBER) RETURN NUMBER IS
rango NUMBER := maximo - minimo;
BEGIN
RETURN MOD(ABS(DBMS_RANDOM.RANDOM), rango) + minimo;
END AZAR;
/
-- 5. Crea una función NOTA que reciba un parámetros que será
-- una nota numérica entre 0 y 10 y devuelva una cadena de texto
-- con la calificación (Suficiente, Bien, Notable, ...).
-- La forma de la función será la siguiente:
-- NOTA (nota NUMBER) RETURN VARCHAR2
CREATE OR REPLACE
FUNCTION NOTA (nota NUMBER) RETURN VARCHAR2 IS
BEGIN
CASE
WHEN nota=10 OR nota=9 THEN RETURN 'Sobresaliente';
WHEN nota=8 OR nota=7 THEN RETURN 'Notable';
WHEN nota=6 THEN RETURN 'Bien';
WHEN nota=5 THEN RETURN 'Suficiente';
WHEN nota<5 AND nota>=0 THEN RETURN 'Insuficiente';
ELSE RETURN 'Nota no válida';
END CASE;
END NOTA;
/
5.5.5. Práctica 5¶
PL/SQL: Variables, registros y cursores
5.5.5.1. Escribe un procedimiento que muestre el número de empleados y el salario mínimo, máximo y medio del departamento de FINANZAS. Debe hacerse uso de cursores implícitos, es decir utilizar SELECT ... INTO.¶
5.5.5.2. Escribe un procedimiento que suba un 10% el salario a los EMPLEADOS con más de 2 hijos y que ganen menos de 2000 €. Para cada empleado se mostrará por pantalla el código de empleado, nombre, salario anterior y final. Utiliza un cursor explícito. La transacción no puede quedarse a medias. Si por cualquier razón no es posible actualizar todos estos salarios, debe deshacerse el trabajo a la situación inicial.¶
5.5.5.3. Escribe un procedimiento que reciba dos parámetros (número de departamento, hijos). Deberá crearse un cursor explícito al que se le pasarán estos parámetros y que mostrará los datos de los empleados que pertenezcan al departamento y con el número de hijos indicados. Al final se indicará el número de empleados obtenidos.¶
5.5.5.4. Escribe un procedimiento con un parámetro para el nombre de empleado, que nos muestre la edad de dicho empleado en años, meses y días.¶
SOLUCIÓN
-- PRÁCTICA 5.5
-- SQLPLUS EMPLEADOS/EMPLEADOS
-- 1. Escribe un procedimiento que muestre el número de empleados
-- y el salario mínimo, máximo y medio del departamento de FINANZAS.
-- Debe hacerse uso de cursores implícitos, es decir utilizar SELECT ... INTO.
CREATE OR REPLACE
PROCEDURE Finanzas AS
numero NUMBER;
maximo NUMBER;
minimo NUMBER;
media NUMBER;
dpto NUMBER;
BEGIN
SELECT NUMDE INTO dpto FROM DEPARTAMENTOS
WHERE UPPER(NOMDE) = 'FINANZAS';
SELECT COUNT(*), MAX(SALAR), MIN(SALAR), ROUND(AVG(SALAR), 2)
INTO numero, maximo, minimo, media
FROM EMPLEADOS WHERE NUMDE = dpto;
DBMS_OUTPUT.PUT_LINE('Departamento de FINANZAS');
DBMS_OUTPUT.PUT_LINE(numero || ' Empleados');
DBMS_OUTPUT.PUT_LINE(maximo || ' € es el salario máximo');
DBMS_OUTPUT.PUT_LINE(minimo || ' € es el salario mínimo');
DBMS_OUTPUT.PUT_LINE(media || ' € es el salario medio');
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No se han encontrado datos');
END Finanzas;
/
-- 2. Escribe un procedimiento que suba un 10% el salario a los EMPLEADOS
-- con más de 2 hijos y que ganen menos de 2000 €. Para cada empleado
-- se mostrará por pantalla el código de empleado, nombre, salario anterior y final.
-- Utiliza un cursor explícito. La transacción no puede quedarse a medias.
-- Si por cualquier razón no es posible actualizar todos estos salarios,
-- debe deshacerse el trabajo a la situación inicial.
CREATE OR REPLACE
PROCEDURE Subir_salarios AS
CURSOR c IS
SELECT NUMEM, NOMEM, SALAR, ROWID
FROM EMPLEADOS WHERE NUMHI > 2 AND SALAR < 2000;
sal_nuevo NUMBER;
BEGIN
FOR registro IN c LOOP
UPDATE EMPLEADOS SET SALAR = registro.SALAR*1.10
WHERE ROWID = registro.ROWID;
sal_nuevo := registro.SALAR*1.10;
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('Actualización no completada');
END IF;
DBMS_OUTPUT.PUT_LINE(registro.NUMEM || ' ' || registro.NOMEM
|| ' : ' || registro.SALAR || ' --> ' || sal_nuevo);
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END Subir_salarios;
/
-- 3. Escribe un procedimiento que reciba dos parámetros (número de departamento, hijos).
-- Deberá crearse un cursor explícito al que se le pasarán estos parámetros y
-- que mostrará los datos de los empleados que pertenezcan al departamento
-- y con el número de hijos indicados. Al final se indicará el número de empleados obtenidos.
CREATE OR REPLACE
PROCEDURE Dpto_Empleados_Hijos (
numero EMPLEADOS.NUMDE%TYPE,
hijos EMPLEADOS.NUMHI%TYPE )
AS
CURSOR c(numero EMPLEADOS.NUMDE%TYPE, hijos EMPLEADOS.NUMHI%TYPE) IS
SELECT NUMEM, NOMEM, NUMHI, NUMDE
FROM EMPLEADOS WHERE NUMDE = numero AND NUMHI = hijos;
contador NUMBER;
BEGIN
contador := 0;
FOR registro IN c (numero, hijos) LOOP
DBMS_OUTPUT.PUT_LINE(registro.NUMEM || ' ' || registro.NOMEM
|| ' ' || registro.NUMHI || ' ' || registro.NUMDE);
contador := contador + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(contador || ' Empleados obtenidos');
END Dpto_Empleados_Hijos;
/
-- 4. Escribe un procedimiento con un parámetro para el nombre de empleado,
-- que nos muestre la edad de dicho empleado en años, meses y días.
CREATE OR REPLACE
PROCEDURE Edad_Empleado (nombre EMPLEADOS.NOMEM%TYPE) AS
-- Utilizamos un cursor explícito por si existiese más de un empleado
-- con el mismo nombre.
CURSOR c(nom EMPLEADOS.NOMEM%TYPE) IS
SELECT NOMEM, FECNA
FROM EMPLEADOS WHERE NOMEM = nom;
meses NUMBER;
a NUMBER;
m NUMBER;
d NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('EMPLEADO: AÑOS MESES DÍAS');
FOR registro IN c(nombre) LOOP
meses := MONTHS_BETWEEN (SYSDATE, registro.FECNA);
a := meses/12;
m := MOD (meses, 12);
d := (m - TRUNC (m))*30;-- parte decimal de m multiplicada por 30
DBMS_OUTPUT.PUT_LINE(registro.NOMEM || ': '
|| TRUNC(a) || ' ' || TRUNC(m) || ' ' || TRUNC(d) );
END LOOP;
END Edad_Empleado;
/
5.5.6. Práctica 6¶
PL/SQL: Paquetes y Excepciones
5.5.6.1. Desarrolla el paquete ARITMETICA cuyo código fuente viene en este tema. Crea un archivo para la especificación y otro para el cuerpo. Realiza varias pruebas para comprobar que las llamadas a funciones y procedimiento funcionan correctamente.¶
5.5.6.2. Al paquete anterior añade una función llamada RESTO que reciba dos parámetros, el dividendo y el divisor, y devuelva el resto de la división.¶
5.5.6.3. Al paquete anterior añade un procedimiento sin parámetros llamado AYUDA que muestre un mensaje por pantalla de los procedimientos y funciones disponibles en el paquete, su utilidad y forma de uso.¶
5.5.6.4. Desarrolla el paquete GESTION. En un principio tendremos los procedimientos para gestionar los departamentos. Dado el archivo de especificación mostrado más abajo crea el archivo para el cuerpo. Realiza varias pruebas para comprobar que las llamadas a funciones y procedimientos funcionan correctamente.¶
SOLUCIÓN
-- PRÁCTICA 5.6
-- 1. 2. y 3. Paquete ARITMETICA. Especificación y cuerpo.
-- Pruebas para comprobar que las llamadas a funciones
-- y procedimiento funcionan correctamente.
-- PAQUETE ARITMETICA – Especificación
-- PACKAGE_ARITMETICA.SQL
CREATE OR REPLACE
PACKAGE aritmetica IS
version NUMBER := 1.0;
PROCEDURE mostrar_info;
PROCEDURE ayuda;
FUNCTION suma (a NUMBER, b NUMBER) RETURN NUMBER;
FUNCTION resta (a NUMBER, b NUMBER) RETURN NUMBER;
FUNCTION multiplica (a NUMBER, b NUMBER) RETURN NUMBER;
FUNCTION divide (a NUMBER, b NUMBER) RETURN NUMBER;
FUNCTION resto (a NUMBER, b NUMBER) RETURN NUMBER;
END aritmetica;
-- PAQUETE ARITMETICA – Cuerpo
-- PACKAGE_BODY_ARITMETICA.SQL
CREATE OR REPLACE
PACKAGE BODY aritmetica IS
PROCEDURE mostrar_info IS
BEGIN
DBMS_OUTPUT.PUT_LINE
('Paquete de operaciones aritméticas. Versión ' || version);
END mostrar_info;
PROCEDURE ayuda IS
BEGIN
DBMS_OUTPUT.PUT_LINE ('AYUDA DEL PAQUETE ARITMÉTICA');
DBMS_OUTPUT.PUT_LINE ('============================');
DBMS_OUTPUT.PUT_LINE ('Paquete con varias funciones aritméticas.');
DBMS_OUTPUT.PUT_LINE ('Las funciones disponibles son éstas:');
DBMS_OUTPUT.PUT_LINE ('- suma (num1, num2), para sumar 2 números');
DBMS_OUTPUT.PUT_LINE ('- resta (num1, num2), para restar');
DBMS_OUTPUT.PUT_LINE ('- multiplica(num1, num2), para multiplicar');
DBMS_OUTPUT.PUT_LINE ('- divide (num1, num2), para dividir');
DBMS_OUTPUT.PUT_LINE ('- resto (num1, num2), para resto de división');
DBMS_OUTPUT.PUT_LINE ('Además, existen 2 procedimientos:');
DBMS_OUTPUT.PUT_LINE ('- mostrar_info, para ver versión');
DBMS_OUTPUT.PUT_LINE ('- ayuda, para mostrar esta ayuda');
END ayuda;
FUNCTION suma (a NUMBER, b NUMBER) RETURN NUMBER IS
BEGIN
RETURN (a+b);
END suma;
FUNCTION resta (a NUMBER, b NUMBER) RETURN NUMBER IS
BEGIN
RETURN (a-b);
END resta;
FUNCTION multiplica (a NUMBER, b NUMBER) RETURN NUMBER IS
BEGIN
RETURN (a*b);
END multiplica;
FUNCTION divide (a NUMBER, b NUMBER) RETURN NUMBER IS
BEGIN
RETURN (a/b);
END divide;
FUNCTION resto (a NUMBER, b NUMBER) RETURN NUMBER IS
BEGIN
RETURN MOD(a,b);
END resto;
END aritmetica;
/
-- Pruebas
BEGIN
ARITMETICA.MOSTRAR_INFO;
ARITMETICA.AYUDA;
END;
/
SELECT ARITMETICA.SUMA (4,3) FROM DUAL;
SELECT ARITMETICA.RESTA (4,3) FROM DUAL;
SELECT ARITMETICA.MULTIPLICA(4,3) FROM DUAL;
SELECT ARITMETICA.DIVIDE (4,3) FROM DUAL;
SELECT ARITMETICA.RESTO (4,3) FROM DUAL;
-- 4. Paquete GESTION. Especificación y cuerpo.
-- Procedimientos para gestionar los departamentos.
-- Pruebas para comprobar que las llamadas a funciones
-- y procedimientos funcionan correctamente.
-- PAQUETE GESTION – Especificación
-- PACKAGE_GESTION.SQL
CREATE OR REPLACE
PACKAGE GESTION AS
PROCEDURE CREAR_DEP (nombre VARCHAR2, presupuesto NUMBER);
FUNCTION NUM_DEP (nombre VARCHAR2) RETURN NUMBER;
PROCEDURE MOSTRAR_DEP (numero NUMBER);
PROCEDURE BORRAR_DEP (numero NUMBER);
PROCEDURE MODIFICAR_DEP (numero NUMBER, presupuesto NUMBER);
END GESTION;
/
-- PAQUETE GESTION – Cuerpo
-- PACKAGE_BODY_GESTION.SQL
CREATE OR REPLACE
PACKAGE BODY GESTION AS
PROCEDURE CREAR_DEP (nombre VARCHAR2, presupuesto NUMBER) AS
num_dep NUMBER(3);
BEGIN
SELECT NUMDE INTO num_dep FROM DEPARTAMENTOS
WHERE NOMDE=nombre;
-- Si existe, no se produce excepción. Mostramos entonces el
-- siguiente mensaje.
DBMS_OUTPUT.PUT_LINE ('Departamento ' || nombre || ' no creado.');
DBMS_OUTPUT.PUT_LINE ('Ya existe un departamento con dicho nombre.');
-- Si no existe el nombre de departamento se produce una excepción
-- la cual aprovechamos para introducir los datos.
EXCEPTION
WHEN NO_DATA_FOUND THEN
SELECT MAX(NUMDE)+10 INTO num_dep FROM DEPARTAMENTOS;
INSERT INTO DEPARTAMENTOS (numde, nomde, presu)
VALUES (num_dep, nombre, presupuesto);
END CREAR_DEP;
FUNCTION NUM_DEP (nombre VARCHAR2) RETURN NUMBER AS
num_dep NUMBER;
BEGIN
SELECT NUMDE INTO num_dep FROM DEPARTAMENTOS WHERE NOMDE=nombre;
RETURN num_dep;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN -1;
END NUM_DEP;
PROCEDURE MOSTRAR_DEP (numero NUMBER) AS
emplea NUMBER(3);
nombre DEPARTAMENTOS.NOMDE%TYPE;
presu DEPARTAMENTOS.PRESU%TYPE;
BEGIN
SELECT NOMDE, PRESU INTO nombre, presu
FROM DEPARTAMENTOS WHERE NUMDE = numero;
DBMS_OUTPUT.PUT_LINE('Num. Dpto: ' || numero|| ' - Nombre Dpto: '
|| nombre || ' - Presupuesto ' || presu );
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('No existe este departamento.');
END MOSTRAR_DEP;
PROCEDURE BORRAR_DEP (numero NUMBER) AS
emplea NUMBER(3);
BEGIN
UPDATE EMPLEADOS
SET NUMDE = NULL WHERE NUMDE = numero;
DBMS_OUTPUT.PUT_LINE(TO_CHAR (SQL%ROWCOUNT)||' empleados afectados.');
DELETE DEPARTAMENTOS WHERE NUMDE = numero;
DBMS_OUTPUT.PUT_LINE(TO_CHAR (SQL%ROWCOUNT)||' departamentos borrados.');
END BORRAR_DEP;
PROCEDURE MODIFICAR_DEP (numero NUMBER, presupuesto NUMBER) AS
BEGIN
UPDATE DEPARTAMENTOS
SET PRESU = presupuesto
WHERE NUMDE = numero;
CASE SQL%ROWCOUNT
WHEN 0 THEN DBMS_OUTPUT.PUT_LINE('No existe el departamento ' || numero);
WHEN 1 THEN DBMS_OUTPUT.PUT_LINE('Actualización realizada.');
ELSE DBMS_OUTPUT.PUT_LINE('Algo raro ocurrió!!.');
END CASE;
END MODIFICAR_DEP;
END GESTION;
/
-- Pruebas
DECLARE
num NUMBER;
BEGIN
GESTION.CREAR_DEP ('MARKETING', 10);
GESTION.CREAR_DEP ('I+D', 20);
num := GESTION.NUM_DEP ('MARKETING');
GESTION.MOSTRAR_DEP ( num );
num := GESTION.NUM_DEP ('I+D');
GESTION.MOSTRAR_DEP ( num );
GESTION.MODIFICAR_DEP (GESTION.NUM_DEP ('MARKETING'), 12);
GESTION.MODIFICAR_DEP (GESTION.NUM_DEP ('I+D'), 22);
GESTION.MOSTRAR_DEP ( GESTION.NUM_DEP ('MARKETING'));
GESTION.MOSTRAR_DEP ( GESTION.NUM_DEP ('I+D'));
GESTION.BORRAR_DEP (GESTION.NUM_DEP ('MARKETING'));
GESTION.BORRAR_DEP (GESTION.NUM_DEP ('I+D'));
END;
/
5.5.7. Práctica 7¶
PL/SQL: Triggers y Excepciones
Previamente deberemos crear una tabla AUDITORIA_EMPLEADOS para registrar los eventos a auditar que ocurran sobre la tabla EMPLEADOS.
CREATE TABLE AUDITORIA_EMPLEADOS (descripcion VARCHAR2(200));
Y también crearemos una vista SEDE_DEPARTAMENTOS acerca de los departamentos y su localización.
CREATE VIEW SEDE_DEPARTAMENTOS AS
SELECT C.NUMCE, C.NOMCE, C.DIRCE,
D.NUMDE, D.NOMDE, D.PRESU, D.DIREC, D.TIDIR, D.DEPDE
FROM CENTROS C JOIN DEPARTAMENTOS D ON C.NUMCE=D.NUMCE;
También insertaremos en la tabla DEPARTAMENTOS uno llamado TEMP donde serán movidos los empleados cuyo departamento desaparezca.
INSERT INTO DEPARTAMENTOS VALUES (0, 10, 260, 'F', 10, 100, 'TEMP');
5.5.7.1. Crea un trigger que, cada vez que se inserte o elimine un empleado, registre una entrada en la tabla AUDITORIA_EMPLEADOS con la fecha del suceso, número y nombre de empleado, así como el tipo de operación realizada (INSERCIÓN o ELIMINACIÓN).¶
5.5.7.2. Crea un trigger que, cada vez que se modifiquen datos de un empleado, registre una entrada en la tabla AUDITORIA_EMPLEADOS con la fecha del suceso, valor antiguo y valor nuevo de cada campo, así como el tipo de operación realizada (en este caso MODIFICACIÓN).¶
5.5.7.3. Crea un trigger para que registre en la tabla AUDITORIA_EMPLEADOS las subidas de salarios superiores al 5%.¶
5.5.7.4. Deseamos operar sobre los datos de los departamentos y los centros donde se hallan. Para ello haremos uso de la vista SEDE_DEPARTAMENTOS creada anteriormente.¶
Al tratarse de una vista que involucra más de una tabla, necesitaremos crear un trigger de sustitución para gestionar las operaciones de actualización de la información. Crea el trigger necesario para realizar inserciones, eliminaciones y modificaciones en la vista anterior.
5.5.7.5. Realiza las siguientes operaciones para comprobar si el disparador anterior funciona correctamente.¶
SOLUCIÓN
-- PRÁCTICA 5.7
-- Tabla para auditoria de empleados.
CREATE TABLE AUDITORIA_EMPLEADOS (descripcion VARCHAR2(200));
-- Vista sobre 2 tablas: DEPARTAMENTOS y CENTROS
CREATE VIEW SEDE_DEPARTAMENTOS AS
SELECT C.NUMCE, C.NOMCE, C.DIRCE,
D.NUMDE, D.NOMDE, D.PRESU, D.DIREC, D.TIDIR, D.DEPDE
FROM CENTROS C JOIN DEPARTAMENTOS D ON C.NUMCE=D.NUMCE;
-- Alta de departamento TEMP donde serán movidos los empleados
-- cuyo departamento desaparezca.
INSERT INTO DEPARTAMENTOS VALUES (0, 10, 260, 'F', 10, 100, 'TEMP');
-- 1. Trigger que, cada vez que se inserte o elimine un empleado,
-- registre una entrada en la tabla AUDITORIA_EMPLEADOS con
-- la fecha del suceso, número y nombre de empleado, así como
-- el tipo de operación realizada (INSERCIÓN o ELIMINACIÓN).
CREATE OR REPLACE
TRIGGER Insercion_eliminacion_empleado
AFTER INSERT OR DELETE ON EMPLEADOS
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO AUDITORIA_EMPLEADOS
VALUES(TO_CHAR(SYSDATE,'DD/MM/YYYY HH:MI:SS') || ' - INSERCIÓN - '
|| :new.NUMEM || ' ' || :new.NOMEM );
ELSIF DELETING THEN
INSERT INTO AUDITORIA_EMPLEADOS
VALUES(TO_CHAR(SYSDATE,'DD/MM/YYYY HH:MI:SS') || ' - ELIMINACIÓN - '
|| :old.NUMEM || ' ' || :old.NOMEM );
END IF;
END Insercion_eliminacion_empleado;
-- 2. Trigger que, cada vez que se modifiquen datos de un empleado,
-- registre una entrada en la tabla AUDITORIA_EMPLEADOS con
-- la fecha del suceso, valor antiguo y valor nuevo de cada campo,
-- así como el tipo de operación realizada (en este caso MODIFICACIÓN).
CREATE OR REPLACE
TRIGGER Modificacion_empleado
AFTER UPDATE ON EMPLEADOS
FOR EACH ROW
DECLARE
cadena VARCHAR2(200);
BEGIN
cadena := TO_CHAR(SYSDATE,'DD/MM/YYYY HH:MI:SS')
|| ' - MODIFICACIÓN - ' || :new.NUMEM || ' ' || :new.NOMEM || ' - ';
IF UPDATING('NUMEM') THEN
cadena := cadena || 'Num. empleado: '
|| :old.NUMEM || '-->' || :new.NUMEM;
END IF;
IF UPDATING('NOMEM') THEN
cadena := cadena || ', Nombre: '
|| :old.NOMEM || '-->' || :new.NOMEM || ', ';
END IF;
IF UPDATING('SALAR') THEN
cadena := cadena || ', Salario: '
|| :old.SALAR || '-->' || :new.SALAR || ', ';
END IF;
IF UPDATING('COMIS') THEN
cadena := cadena || ', Comisión: '
|| :old.COMIS || '-->' || :new.COMIS || ', ';
END IF;
IF UPDATING('NUMHI') THEN
cadena := cadena || ', Hijos: '
|| :old.NUMHI || '-->' || :new.NUMHI || ', ';
END IF;
IF UPDATING('EXTEL') THEN
cadena := cadena || ', Extensión: '
|| :old.EXTEL || '-->' || :new.EXTEL || ', ';
END IF;
IF UPDATING('NUMDE') THEN
cadena := cadena || ', Num. Departamento: '
|| :old.NUMDE || '-->' || :new.NUMDE || ', ';
END IF;
INSERT INTO AUDITORIA_EMPLEADOS VALUES(cadena);
END Modificacion_empleado;
-- 3. Crea un trigger para que registre en la tabla AUDITORIA_EMPLEADOS
-- las subidas de salarios superiores al 5%.
CREATE OR REPLACE
TRIGGER Subida_salario
AFTER UPDATE OF SALAR ON EMPLEADOS
FOR EACH ROW
BEGIN
IF (:new.SALAR - :old.SALAR) > (:old.SALAR * 0.05) THEN
INSERT INTO AUDITORIA_EMPLEADOS
VALUES(TO_CHAR(SYSDATE,'DD/MM/YYYY HH:MI:SS')
|| ' - MODIFICACIÓN SALARIO - '
|| :old.NUMEM || ' ' || :old.NOMEM || ' - '
|| :old.SALAR || ' --> ' || :new.SALAR );
END IF;
END Subida_salario;
-- 4. Trigger necesario para realizar inserciones, eliminaciones
-- y modificaciones en la vista SEDE_DEPARTAMENTOS. Al tratarse de una vista
-- que involucra más de una tabla, necesitaremos hacer uso de
-- un trigger de sustitución para gestionar las operaciones de actualización
-- de la información.
CREATE OR REPLACE
TRIGGER Actualizacion_departamento
INSTEAD OF DELETE OR INSERT OR UPDATE ON SEDE_DEPARTAMENTOS
FOR EACH ROW
DECLARE
cantidad NUMBER(3);
BEGIN
-- Modificamos datos
IF UPDATING THEN
UPDATE CENTROS
SET NOMCE = :new.NOMCE, DIRCE = :new.DIRCE
WHERE NUMCE = :old.NUMCE;
UPDATE DEPARTAMENTOS
SET NUMCE = :new.NUMCE, NOMDE = :new.NOMDE, DIREC = :new.DIREC,
TIDIR = :new.TIDIR, PRESU = :new.PRESU, DEPDE = :new.DEPDE
WHERE NUMCE = :old.NUMCE AND NUMDE = :old.NUMDE;
-- Borramos datos
ELSIF DELETING THEN
-- Si el departamento tiene empleados
-- los movemos al departamento 'TEMP', luego borramos el partamento
-- Si el centro tiene departamentos, no borramos el centro.
SELECT COUNT(NUMDE) INTO cantidad
FROM EMPLEADOS WHERE NUMDE = :old.NUMDE;
IF cantidad > 0 THEN
UPDATE EMPLEADOS SET NUMDE = 0 WHERE NUMDE = :old.NUMDE;
END IF;
DELETE DEPARTAMENTOS WHERE NUMDE = :old.NUMDE;
SELECT COUNT(NUMCE) INTO cantidad
FROM DEPARTAMENTOS WHERE NUMCE = :old.NUMCE;
IF cantidad = 0 THEN
DELETE CENTROS WHERE NUMCE = :old.NUMCE;
END IF;
-- Insertamos datos
ELSIF INSERTING THEN
-- Si el centro o el departamento no existe lo damos de alta,
-- en otro caso actualizamos los datos
SELECT COUNT(NUMCE) INTO cantidad
FROM CENTROS WHERE NUMCE = :new.NUMCE;
IF cantidad = 0 THEN
INSERT INTO CENTROS
VALUES(:new.NUMCE, :new.NOMCE, :new.DIRCE);
ELSE
UPDATE CENTROS
SET NOMCE = :new.NOMCE, DIRCE = :new.DIRCE
WHERE NUMCE = :new.NUMCE;
END IF;
SELECT COUNT(NUMDE) INTO cantidad
FROM DEPARTAMENTOS WHERE NUMDE = :new.NUMDE;
IF cantidad = 0 THEN
INSERT INTO DEPARTAMENTOS
VALUES(:new.NUMDE, :new.NUMCE, :new.DIREC, :new.TIDIR,
:new.PRESU, :new.DEPDE, :new.NOMDE);
ELSE
UPDATE DEPARTAMENTOS
SET NUMCE = :new.NUMCE, DIREC = :new.DIREC, TIDIR = :new.TIDIR,
PRESU = :new.PRESU, DEPDE = :new.DEPDE, NOMDE = :new.NOMDE
WHERE NUMCE = :new.NUMCE;
END IF;
ELSE
RAISE_APPLICATION_ERROR(-20500, 'Error en la actualización');
END IF;
END Actualizacion_departamento;
-- 5. Operaciones para comprobar si el disparador anterior funciona correctamente.
-- Inserción de datos
INSERT INTO SEDE_DEPARTAMENTOS (NUMCE, NUMDE, NOMDE)
VALUES (30, 310, 'NUEVO1');
INSERT INTO SEDE_DEPARTAMENTOS (NUMCE, NUMDE, NOMDE)
VALUES (30, 320, 'NUEVO2');
INSERT INTO SEDE_DEPARTAMENTOS (NUMCE, NUMDE, NOMDE)
VALUES (30, 330, 'NUEVO3');
SELECT * FROM CENTROS;
SELECT * FROM DEPARTAMENTOS;
-- Borrado de datos
DELETE FROM SEDE_DEPARTAMENTOS WHERE NUMDE=310;
SELECT * FROM SEDE_DEPARTAMENTOS;
DELETE FROM SEDE_DEPARTAMENTOS WHERE NUMCE=30;
SELECT * FROM SEDE_DEPARTAMENTOS;
-- Modificación de datos
UPDATE SEDE_DEPARTAMENTOS
SET NOMDE='CUENTAS', TIDIR='F', NUMCE=20 WHERE NOMDE='FINANZAS';
SELECT * FROM DEPARTAMENTOS;
UPDATE SEDE_DEPARTAMENTOS
SET NOMDE='FINANZAS', TIDIR='P', NUMCE=10 WHERE NOMDE='CUENTAS';
SELECT * FROM DEPARTAMENTOS;