Saltar al contenido

Solución seminario bases de datos activas con SGBD Oracle

Como ya finalizó el primer cuatrimestre, compartiré con vosotros parte de los ejercicios de ciertas asignaturas para que aprendais cosas nuevas, useis como referencia o compareis una vez halleis una solución. Esta vez toca la solución del primer seminario de la asignatura Administración y Diseño de Bases de Datos. Si estás leyendo esto y no cursas la asignatura, puede que este ejercicio sea también de tu interés…
Fuente: Oracle logos | Autor: Propiedad de Oracle Company

Primero creamos las tablas necesarias para la realización del ejercicio:

CREATE TABLE empleados
(  dni NUMBER(8),
   VARCHAR2(15),
   nom_jefe VARCHAR2(20),
   PRIMARY KEY (dni)
);

CREATE TABLE empleados_baja
(  dni NUMBER(8) PRIMARY KEY,
   nomemp VARCHAR2(15),
   nom_jefe VARCHAR2(20),
   fecha date
);

1. Crear un trigger que inserte una fila en la tabla empleados_baja cuando se borre una fila en la tabla empleados. Los datos a insertar son los correspondientes al empleado que se da de baja. En la columna fecha se grabará la variable del sistema SYSDATE que almacena la fecha actual. El trigger sería:

create or replace TRIGGER oracleTrigger1
AFTER DELETE ON EMPLEADOS
REFERENCING OLD AS empleadoEliminado
FOR EACH ROW
BEGIN
   INSERT INTO EMPLEADOS_BAJA VALUES(:empleadoEliminado.DNI, :empleadoEliminado.NOMEMP, :empleadoEliminado.NOM_JEFE, SYSDATE);
END oracleTrigger1;

Ahora vamos a probarlo primero insertando una tupla y después borrándola:

INSERT INTO EMPLEADOS (DNI,NOMEMP,NOM_JEFE) VALUES (44444444,'Juanito','Pepe');
DELETE FROM empleados WHERE NOMEMP='Juanito';

¡Y el trigger funciona!

2. Crear un procedimiento que elimine de la tabla empleados, el empleado cuyo nombre se le pasa como parámetro.


create or replace
PROCEDURE eliminarEmpleado (nombre IN VARCHAR2)
IS
   -- No hace falta declarar variables.
BEGIN
   -- Eliminamos el empleado.
DELETE FROM EMPLEADOS WHERE nomemp=nombre;
END eliminarEmpleado;

Ahora volvemos a insertar un nuevo usuario para eliminarlo a través del procedimiento almacenado:


INSERT INTO EMPLEADOS (DNI,NOMEMP,NOM_JEFE) VALUES (33333333,'Marta','Pepe');
EXEC eliminarempleado('Marta');

3. Crear una tarea que inserte según la hora actual en 2 minutos los siguientes datos:
DNI 12345678
Nomemp: Pedro
Nom_jefe: Jose
Además, la tarea debe ejecutarse mensualmente.


BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name => 'insert2min',
   job_type => 'PLSQL_BLOCK',
   job_action => 'BEGIN INSERT INTO EMPLEADOS (DNI,NOMEMP,NOM_JEFE) VALUES (12345678,''Pedro'',''Jose''); END;',
   start_date => '2/11/2011 17:12:00',
   repeat_interval => 'FREQ=MONTHLY',
   enabled => TRUE,
   comments => 'Ejercicio 3 seminario BD activas');
END;

La habilitamos:


exect dbms_scheduler.enable('insert2min');

Podemos forzar la ejecución de la tarea programada mediante la sentencia:


exec dbms_scheduler.run_job('insert2min', TRUE);

Finalmente eliminamos el trabajo para que no se esté ejecutando una vez al mes en el servidor:


exec dbms_scheduler.drop_job('insert2min');


Publicado enAdministración y diseño de bases de datosApuntesConsultas