taller4 BD

**TRIGGER**

CREATE OR REPLACE TRIGGER "SYSTEM"."UPDATE_EMPLEADO"
AFTER UPDATE OF COMISION,NOMBRE,SALARIO ON EMPLEADO
REFERENCING OLD AS OLD NEW AS NEW
FOR each ROW
DECLARE
    codigo_auditoria NUMBER(10);
BEGIN
  SELECT COUNT(*) INTO codigo_auditoria FROM auditoria;
  codigo_auditoria := codigo_auditoria + 1;

  INSERT INTO Auditoria(codigo, rut, antiguo_nombre, antiguo_salario, antiguo_comision, nuevo_nombre, nuevo_salario, nuevo_comision)
  VALUES (codigo_auditoria, :OLD.rut,:OLD.nombre , :OLD.salario, :OLD.comision, :NEW.nombre, :NEW.salario, :NEW.comision);
END;
/
ALTER TRIGGER "SYSTEM"."UPDATE_EMPLEADO" ENABLE;



/* problema 1*/
CREATE TABLE Cuenta (
  numero NUMBER(10) NOT NULL,
  nombre VARCHAR2(255) NOT NULL,
  saldo  NUMBER(10) NOT NULL,
  PRIMARY KEY (numero));

CREATE TABLE Transaccion (
  numero    NUMBER(10) NOT NULL,
  operacion VARCHAR2(255) NOT NULL,
  nombre    VARCHAR2(255),
  saldo     NUMBER(10),
  PRIMARY KEY (numero,
  operacion));

INSERT INTO Cuenta(numero, nombre, saldo) VALUES (1, 'usuario1', 10000);
INSERT INTO Cuenta(numero, nombre, saldo) VALUES (2, 'usuario2', 25000);
INSERT INTO Cuenta(numero, nombre, saldo) VALUES (3, 'usuario3', 5000);
INSERT INTO Cuenta(numero, nombre, saldo) VALUES (4, 'usuario4', 15000);
INSERT INTO Cuenta(numero, nombre, saldo) VALUES (5, 'usuario5', 45000);
INSERT INTO Cuenta(numero, nombre, saldo) VALUES (6, 'usuario6', 55000);

INSERT INTO Transaccion(numero, operacion, nombre, saldo) VALUES (7, 'I', 'usuario7v2', 99999);
INSERT INTO Transaccion(numero, operacion, nombre, saldo) VALUES (8, 'U', 'usuario8', 12345);
INSERT INTO Transaccion(numero, operacion, nombre, saldo) VALUES (10, 'D', NULL, NULL);


/* problema 2*/
CREATE TABLE Empleado2 (
  rut      VARCHAR2(255) NOT NULL,
  nombre   VARCHAR2(255) NOT NULL,
  salario  NUMBER(10) NOT NULL,
  rutJefe  VARCHAR2(255) NOT NULL,
  codDepto NUMBER(10) NOT NULL,
  PRIMARY KEY (rut));

CREATE TABLE Departamento (
  codigo NUMBER(10) NOT NULL,
  nombre VARCHAR2(255) NOT NULL,
  PRIMARY KEY (codigo));

ALTER TABLE Empleado2 ADD CONSTRAINT FKEmpleado2868487 FOREIGN KEY (rutJefe) REFERENCES Empleado2 (rut);
ALTER TABLE Empleado2 ADD CONSTRAINT FKEmpleado2877001 FOREIGN KEY (codDepto) REFERENCES Departamento (codigo);

INSERT INTO Departamento(codigo, nombre) VALUES (1, 'Informatica');
INSERT INTO Departamento(codigo, nombre) VALUES (2, 'Industrial');
INSERT INTO Departamento(codigo, nombre) VALUES (3, 'Geologia');
INSERT INTO Departamento(codigo, nombre) VALUES (4, ' Periodismo');

INSERT INTO Empleado2(rut, nombre, salario, rutJefe, codDepto) VALUES ('11222333-4', 'lukas cortes', 150000, '18791036-6', 1);
INSERT INTO Empleado2(rut, nombre, salario, rutJefe, codDepto) VALUES ('11222333-5', 'lukas cortes2', 150000, '18791036-6', 1);
INSERT INTO Empleado2(rut, nombre, salario, rutJefe, codDepto) VALUES ('11222333-6', 'lukas cortes3', 50000, '11222333-5', 2);
INSERT INTO Empleado2(rut, nombre, salario, rutJefe, codDepto) VALUES ('11222333-7', 'lukas cortes4', 50000, '11222333-5', 3);


/* problema 3*/
CREATE TABLE Empleado (
  rut      VARCHAR2(255) NOT NULL,
  nombre   VARCHAR2(255) NOT NULL,
  salario  NUMBER(10) NOT NULL,
  comision NUMBER(10) NOT NULL,
  PRIMARY KEY (rut));

CREATE TABLE Auditoria (
  codigo           NUMBER(10) NOT NULL,
  rut              VARCHAR2(255) NOT NULL,
  antiguo_nombre   VARCHAR2(255) NOT NULL,
  antiguo_salario  NUMBER(10) NOT NULL,
  antiguo_comision NUMBER(10) NOT NULL,
  nuevo_nombre     VARCHAR2(255) NOT NULL,
  nuevo_salario    NUMBER(10) NOT NULL,
  nuevo_comision   NUMBER(10) NOT NULL,
  PRIMARY KEY (codigo));

INSERT INTO Empleado(rut, nombre, salario, comision) VALUES ('18791036-6', 'mgn', 12345, 10);
UPDATE Empleado SET nombre = 'mgn4', salario = 111111, comision = 100 WHERE rut = '18791036-6';



/* testing */
SELECT * FROM cuenta;
SELECT * FROM transaccion;


SELECT * FROM empleado2;
SELECT * FROM departamento;


SELECT * FROM empleado;
SELECT * FROM auditoria;




**PACKAGE**

CREATE OR REPLACE PACKAGE CONTROLADOR_PROBLEMA2 AS

PROCEDURE ingresar_empleado(rut IN VARCHAR2, nombre IN VARCHAR2, salario IN NUMBER, rutJefe IN VARCHAR2, depto IN NUMBER);
PROCEDURE ingresar_depto(codigo IN NUMBER, nombre IN VARCHAR2);
PROCEDURE incrementar_salarios(depto IN NUMBER,porcentaje IN NUMBER);
PROCEDURE modificar_salario(rut_empleado IN VARCHAR2, new_sal IN NUMBER);
PROCEDURE eliminar_empleado(rut_empleado IN VARCHAR2);
PROCEDURE eliminar_depto(codigo_depto IN NUMBER);
PROCEDURE cambiar_depto_empleado(rut_empleado IN VARCHAR2, new_depto IN NUMBER);
FUNCTION salario_empleado(rut_empleado IN VARCHAR2) RETURN NUMBER;
FUNCTION salario_depto(depto IN NUMBER) RETURN NUMBER;
PROCEDURE cambiar_jefe(antiguo_jefe VARCHAR2, nuevo_jefe VARCHAR2);

END CONTROLADOR_PROBLEMA2;

CREATE OR REPLACE  PACKAGE BODY CONTROLADOR_PROBLEMA2 AS

PROCEDURE ingresar_empleado(rut IN VARCHAR2, nombre IN VARCHAR2, salario IN NUMBER, rutJefe IN VARCHAR2, depto IN NUMBER) IS
BEGIN
    INSERT INTO empleado2 VALUES (rut,nombre,salario,rutJefe,depto);
    EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
            DBMS_OUTPUT.PUT_LINE('El rut del empleado ya existe');
END ingresar_empleado;

PROCEDURE ingresar_depto(codigo IN NUMBER, nombre IN VARCHAR2) IS
BEGIN
    INSERT INTO Departamento VALUES (codigo,nombre);
    EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
            DBMS_OUTPUT.PUT_LINE('El codigo de este departamento ya existe');
END ingresar_depto;

PROCEDURE incrementar_salarios(depto IN NUMBER,porcentaje IN NUMBER) IS
new_sal NUMBER(10);
CURSOR c IS SELECT * FROM Empleado2 WHERE codDepto = depto;
BEGIN
    FOR fila IN c
    LOOP
        new_sal := fila.salario*(1+(porcentaje/100));
        UPDATE Empleado2 SET salario = new_sal WHERE rut = fila.rut;
    END LOOP;
END incrementar_salarios;

PROCEDURE modificar_salario(rut_empleado IN VARCHAR2, new_sal IN NUMBER) IS
BEGIN
     UPDATE Empleado2 SET salario = new_sal WHERE rut = rut_empleado;
     IF SQL%NOTFOUND THEN
        DBMS_OUTPUT.PUT_LINE('Este empleado no existe');
    END IF;
END modificar_salario;

PROCEDURE eliminar_empleado(rut_empleado IN VARCHAR2) IS
BEGIN
    DELETE FROM Empleado2 WHERE rut = rut_empleado;

    IF SQL%NOTFOUND THEN
        DBMS_OUTPUT.PUT_LINE('Este empleado no existe');
    END IF;
END eliminar_empleado;

PROCEDURE eliminar_depto(codigo_depto IN NUMBER) IS
BEGIN
    DELETE FROM Departamento WHERE codigo = codigo_depto;

    IF SQL%NOTFOUND THEN
        DBMS_OUTPUT.PUT_LINE('Este Departamento no existe');
    END IF;
END eliminar_depto;

PROCEDURE cambiar_depto_empleado(rut_empleado IN VARCHAR2, new_depto IN NUMBER) IS
BEGIN
    UPDATE Empleado2 SET codDepto = new_depto WHERE rut = rut_empleado;
     IF SQL%NOTFOUND THEN
        DBMS_OUTPUT.PUT_LINE('Este empleado no existe');
    END IF;

END cambiar_depto_empleado;

FUNCTION salario_empleado(rut_empleado IN VARCHAR2) RETURN NUMBER IS
emp_sal NUMBER;
BEGIN
    SELECT salario INTO emp_sal FROM Empleado2 WHERE rut = rut_empleado;
    RETURN emp_sal;
END salario_empleado;

FUNCTION salario_depto(depto IN NUMBER) RETURN NUMBER IS
sal_total NUMBER(10):=0;
CURSOR c IS SELECT * FROM Empleado2 WHERE codDepto = depto;
BEGIN
    FOR fila IN c
    LOOP
        sal_total := sal_total + fila.salario;
    END LOOP;
    RETURN sal_total;
END salario_depto;

PROCEDURE cambiar_jefe(antiguo_jefe VARCHAR2, nuevo_jefe VARCHAR2) IS
CURSOR c IS SELECT * FROM Empleado2 WHERE rutJefe = antiguo_jefe;
BEGIN
    FOR fila IN c
    LOOP
        UPDATE Empleado2 SET rutJefe = nuevo_jefe WHERE rut = fila.rut;
    END LOOP;
END cambiar_jefe;

END CONTROLADOR_PROBLEMA2;


**PL/SQL PROBLEMA 1**

SET serveroutput ON;
DECLARE
    CURSOR c IS SELECT * FROM Transaccion;

PROCEDURE insertar(cod IN NUMBER, nom IN VARCHAR2, sal IN NUMBER) IS
BEGIN
    INSERT INTO cuenta VALUES (cod,nom,sal);

    EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
            UPDATE cuenta SET nombre = nom, saldo = sal WHERE numero = cod;
END;

PROCEDURE actualizar(cod IN NUMBER, nom IN VARCHAR2, sal IN NUMBER) IS
BEGIN
    UPDATE cuenta SET saldo = sal WHERE numero = cod;

    IF SQL%NOTFOUND THEN
        INSERT INTO cuenta VALUES (cod,nom,sal);
    END IF;
END;

PROCEDURE eliminar(cod IN NUMBER) IS
BEGIN
    DELETE FROM cuenta WHERE numero = cod;

    IF SQL%NOTFOUND THEN
        DBMS_OUTPUT.PUT_LINE('La cuenta a eliminar no existe');
    END IF;
END;

BEGIN
    FOR fila IN c
    LOOP
        IF fila.operacion = 'I' THEN
            insertar(fila.numero,fila.nombre,fila.saldo);
        ELSIF fila.operacion = 'U' THEN
            actualizar(fila.numero,fila.nombre,fila.saldo);
        ELSIF fila.operacion = 'D' THEN
            eliminar(fila.numero);
        ELSE
            DBMS_OUTPUT.PUT_LINE('El codigo no es valido');
        END IF;
    END LOOP;
END;