/*  Ejemplos SQL para entorno SQLPlus Oracle 8  */

/* Eliminación esquema anterior */

DROP PROCEDURE Currar;
DROP TRIGGER   ContabHorasProy;

DROP  TABLE Participar;
ALTER TABLE Departamento  DROP CONSTRAINT FK_Departamento;
DROP  TABLE HistorialSalario;
DROP  TABLE Empleado;
DROP  TABLE Departamento;
DROP  TABLE Proyecto;

DROP  CLUSTER clusterEmpDep;

/* Creación del nuevo esquema */

CREATE CLUSTER clusterEmpDep(numDep char(2));

CREATE INDEX ind_clusterEmpDep ON CLUSTER clusterEmpDep;

CREATE TABLE Departamento (
  codDep        char(2)     PRIMARY KEY,
  nombre        varchar(15) UNIQUE NOT NULL,
  director      char(4))
 CLUSTER clusterEmpDep(codDep);

CREATE TABLE Empleado (
  codEmp        char(4),
  nombre        varchar(20) NOT NULL,
  puesto        varchar(15),
  salario       numeric(8),
  codDepto      char(2),
  FinContrato   date,
  CONSTRAINT PK_Empleado PRIMARY KEY (codEmp),
  CONSTRAINT FK_Empleado FOREIGN KEY (codDepto) REFERENCES Departamento, /*ON UPDATE CASCADE*/
  CHECK (salario > 0))
 CLUSTER clusterEmpDep(codDepto);

CREATE UNIQUE INDEX ind_nombre ON Empleado(nombre);

CREATE INDEX ind_dep ON Empleado(codDepto);

CREATE TABLE Proyecto (
  numProy       numeric(5)  PRIMARY KEY,
  titulo        varchar(20) NOT NULL,
  presupuesto   numeric(9),
  horas         number(4));

CREATE TABLE Participar (
  codEmp        char(4),
  numProy       numeric(5),
  CONSTRAINT PK_Participar PRIMARY KEY (codEmp, numProy),
  FOREIGN KEY (codEmp)  REFERENCES Empleado(codEmp),
  FOREIGN KEY (numProy) REFERENCES Proyecto(numProy) ON DELETE CASCADE);


/* Inserción de datos */

INSERT INTO Departamento VALUES ('01', 'Software',       'A369');
INSERT INTO Departamento VALUES ('02', 'Hardware',       'A325');
INSERT INTO Departamento VALUES ('03', 'Comunicaciones', 'A369');
INSERT INTO Departamento VALUES ('04', 'Mantenimiento',  'A777');

INSERT INTO Empleado
 VALUES ('A369', 'Fernández', 'jefeProyecto', 5500000, '01', to_date('28-02-98', 'DD-MM-YY'));
INSERT INTO Empleado
 VALUES ('A269', 'Martinez',  'analista',     4500000, '01', to_date('18-12-97', 'DD-MM-YY'));
INSERT INTO Empleado
 VALUES ('A359', 'López',     'analista',        NULL, '02', to_date('12-10-97', 'DD-MM-YY'));
INSERT INTO Empleado
 VALUES ('A367', 'González',  'programador',  3000000, '01', to_date('08-02-99', 'DD-MM-YY'));
INSERT INTO Empleado
 VALUES ('A569', 'Rodriguez', 'programador',  2800000, NULL, to_date('02-09-99', 'DD-MM-YY'));
INSERT INTO Empleado
 VALUES ('A389', 'Sánchez',   'programador',  2600000, '03', to_date('22-06-98', 'DD-MM-YY'));
INSERT INTO Empleado
 VALUES ('A361', 'Juarez',    'programador',  2300000, '02', to_date('04-10-99', 'DD-MM-YY'));
INSERT INTO Empleado
 VALUES ('A777', 'Ruperez',    NULL,          3500000, '04', to_date('15-02-99', 'DD-MM-YY'));
INSERT INTO Empleado
 VALUES ('A325', 'Vázquez',   'jefeProyecto', 6000000, '04', to_date('15-07-97', 'DD-MM-YY'));
INSERT INTO Empleado
 VALUES ('A364', 'Márquez',   'analista',     3800000, '04', to_date('30-03-98', 'DD-MM-YY'));
INSERT INTO Empleado
 VALUES ('A969', 'Jiménez',   'programador',  2000000, NULL, to_date('31-05-97', 'DD-MM-YY'));
INSERT INTO Empleado
 VALUES ('A312', 'Pérez',     'programador',  1500000, '04', to_date('23-02-99', 'DD-MM-YY'));

INSERT INTO Proyecto VALUES (1, 'SoftIntegral', 15000000, 0);
INSERT INTO Proyecto VALUES (2, 'Web Magic',    25000000, 0);
INSERT INTO Proyecto VALUES (3, 'Copia y Pega', 17000000, 0);
INSERT INTO Proyecto VALUES (4, 'Word Maker',    5000000, 0);
INSERT INTO Proyecto VALUES (5, 'ContaWell',     2000000, 0);
INSERT INTO Proyecto VALUES (6, 'Saucer Attack', 9000000, 0);

INSERT INTO Participar VALUES ('A369', 1);
INSERT INTO Participar VALUES ('A359', 1);
INSERT INTO Participar VALUES ('A389', 1);
INSERT INTO Participar VALUES ('A777', 2);
INSERT INTO Participar VALUES ('A969', 2);
INSERT INTO Participar VALUES ('A325', 3);
INSERT INTO Participar VALUES ('A777', 4);
INSERT INTO Participar VALUES ('A312', 4);


/* Modificación del esquema inicial */

ALTER TABLE empleado MODIFY (nombre varchar(40));

ALTER TABLE Participar ADD (NumHoras number(4), CONSTRAINT CH_NumHoras CHECK (NumHoras>0));
UPDATE Participar SET NumHoras = 15 * numProy;

UPDATE Empleado SET salario = salario * 1.1;

COMMIT;

ALTER TABLE Departamento
  ADD CONSTRAINT FK_Departamento FOREIGN KEY (director) REFERENCES Empleado(codEmp);


/* Definición de procedimientos y disparadores */

CREATE OR REPLACE PROCEDURE Currar (
 emp   IN Participar.codemp%type,
 proy  IN Participar.numproy%type,
 horas IN Participar.numhoras%type)
 AS
  begin
    UPDATE Participar
    SET    numHoras = numHoras + horas
    WHERE  emp = codemp AND proy = numproy;
  end;
.
RUN;

CREATE OR REPLACE TRIGGER ContabHorasProy
AFTER UPDATE ON Participar
FOR EACH ROW
WHEN (NEW.numHoras > 0)
 begin
   UPDATE Proyecto
   SET    horas = horas + :NEW.numHoras - :OLD.numHoras
   WHERE  numProy = :NEW.numProy;
 end ContabHorasProy;
.
RUN;

CREATE TABLE HistorialSalario (
  codEmp              char(4),
  salarioAnt          numeric(8),
  fechaCambioSalario  date,
  CONSTRAINT PK_HistSalario    PRIMARY KEY (codEmp, fechaCambioSalario),
  CONSTRAINT FK_HistSalarioEmp FOREIGN KEY (codEmp) REFERENCES Empleado);

CREATE OR REPLACE TRIGGER GuardarHistorialSalario
BEFORE UPDATE ON Empleado
FOR EACH ROW
 begin
  if (:OLD.Salario <> :NEW.salario)
   then INSERT INTO HistorialSalario VALUES (:OLD.codEmp, :OLD.salario, sysdate);
  end if;
 end GuardarHistorialSalario;
.
RUN;