Advertisement
migand21

taller4 BD

Jul 13th, 2019
1,043
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. **TRIGGER**
  2.  
  3. CREATE OR REPLACE TRIGGER "SYSTEM"."UPDATE_EMPLEADO"
  4. AFTER UPDATE OF COMISION,NOMBRE,SALARIO ON EMPLEADO
  5. REFERENCING OLD AS OLD NEW AS NEW
  6. FOR each ROW
  7. DECLARE
  8.     codigo_auditoria NUMBER(10);
  9. BEGIN
  10.   SELECT COUNT(*) INTO codigo_auditoria FROM auditoria;
  11.   codigo_auditoria := codigo_auditoria + 1;
  12.  
  13.   INSERT INTO Auditoria(codigo, rut, antiguo_nombre, antiguo_salario, antiguo_comision, nuevo_nombre, nuevo_salario, nuevo_comision)
  14.   VALUES (codigo_auditoria, :OLD.rut,:OLD.nombre , :OLD.salario, :OLD.comision, :NEW.nombre, :NEW.salario, :NEW.comision);
  15. END;
  16. /
  17. ALTER TRIGGER "SYSTEM"."UPDATE_EMPLEADO" ENABLE;   
  18.  
  19.  
  20.  
  21. /* problema 1*/
  22. CREATE TABLE Cuenta (
  23.   numero NUMBER(10) NOT NULL,
  24.   nombre VARCHAR2(255) NOT NULL,
  25.   saldo  NUMBER(10) NOT NULL,
  26.   PRIMARY KEY (numero));
  27.  
  28. CREATE TABLE Transaccion (
  29.   numero    NUMBER(10) NOT NULL,
  30.   operacion VARCHAR2(255) NOT NULL,
  31.   nombre    VARCHAR2(255),
  32.   saldo     NUMBER(10),
  33.   PRIMARY KEY (numero,
  34.   operacion));
  35.  
  36. INSERT INTO Cuenta(numero, nombre, saldo) VALUES (1, 'usuario1', 10000);
  37. INSERT INTO Cuenta(numero, nombre, saldo) VALUES (2, 'usuario2', 25000);
  38. INSERT INTO Cuenta(numero, nombre, saldo) VALUES (3, 'usuario3', 5000);
  39. INSERT INTO Cuenta(numero, nombre, saldo) VALUES (4, 'usuario4', 15000);
  40. INSERT INTO Cuenta(numero, nombre, saldo) VALUES (5, 'usuario5', 45000);
  41. INSERT INTO Cuenta(numero, nombre, saldo) VALUES (6, 'usuario6', 55000);
  42.  
  43. INSERT INTO Transaccion(numero, operacion, nombre, saldo) VALUES (7, 'I', 'usuario7v2', 99999);
  44. INSERT INTO Transaccion(numero, operacion, nombre, saldo) VALUES (8, 'U', 'usuario8', 12345);
  45. INSERT INTO Transaccion(numero, operacion, nombre, saldo) VALUES (10, 'D', NULL, NULL);
  46.  
  47.  
  48. /* problema 2*/
  49. CREATE TABLE Empleado2 (
  50.   rut      VARCHAR2(255) NOT NULL,
  51.   nombre   VARCHAR2(255) NOT NULL,
  52.   salario  NUMBER(10) NOT NULL,
  53.   rutJefe  VARCHAR2(255) NOT NULL,
  54.   codDepto NUMBER(10) NOT NULL,
  55.   PRIMARY KEY (rut));
  56.  
  57. CREATE TABLE Departamento (
  58.   codigo NUMBER(10) NOT NULL,
  59.   nombre VARCHAR2(255) NOT NULL,
  60.   PRIMARY KEY (codigo));
  61.  
  62. ALTER TABLE Empleado2 ADD CONSTRAINT FKEmpleado2868487 FOREIGN KEY (rutJefe) REFERENCES Empleado2 (rut);
  63. ALTER TABLE Empleado2 ADD CONSTRAINT FKEmpleado2877001 FOREIGN KEY (codDepto) REFERENCES Departamento (codigo);
  64.  
  65. INSERT INTO Departamento(codigo, nombre) VALUES (1, 'Informatica');
  66. INSERT INTO Departamento(codigo, nombre) VALUES (2, 'Industrial');
  67. INSERT INTO Departamento(codigo, nombre) VALUES (3, 'Geologia');
  68. INSERT INTO Departamento(codigo, nombre) VALUES (4, ' Periodismo');
  69.  
  70. INSERT INTO Empleado2(rut, nombre, salario, rutJefe, codDepto) VALUES ('11222333-4', 'lukas cortes', 150000, '18791036-6', 1);
  71. INSERT INTO Empleado2(rut, nombre, salario, rutJefe, codDepto) VALUES ('11222333-5', 'lukas cortes2', 150000, '18791036-6', 1);
  72. INSERT INTO Empleado2(rut, nombre, salario, rutJefe, codDepto) VALUES ('11222333-6', 'lukas cortes3', 50000, '11222333-5', 2);
  73. INSERT INTO Empleado2(rut, nombre, salario, rutJefe, codDepto) VALUES ('11222333-7', 'lukas cortes4', 50000, '11222333-5', 3);
  74.  
  75.  
  76. /* problema 3*/
  77. CREATE TABLE Empleado (
  78.   rut      VARCHAR2(255) NOT NULL,
  79.   nombre   VARCHAR2(255) NOT NULL,
  80.   salario  NUMBER(10) NOT NULL,
  81.   comision NUMBER(10) NOT NULL,
  82.   PRIMARY KEY (rut));
  83.  
  84. CREATE TABLE Auditoria (
  85.   codigo           NUMBER(10) NOT NULL,
  86.   rut              VARCHAR2(255) NOT NULL,
  87.   antiguo_nombre   VARCHAR2(255) NOT NULL,
  88.   antiguo_salario  NUMBER(10) NOT NULL,
  89.   antiguo_comision NUMBER(10) NOT NULL,
  90.   nuevo_nombre     VARCHAR2(255) NOT NULL,
  91.   nuevo_salario    NUMBER(10) NOT NULL,
  92.   nuevo_comision   NUMBER(10) NOT NULL,
  93.   PRIMARY KEY (codigo));
  94.  
  95. INSERT INTO Empleado(rut, nombre, salario, comision) VALUES ('18791036-6', 'mgn', 12345, 10);
  96. UPDATE Empleado SET nombre = 'mgn4', salario = 111111, comision = 100 WHERE rut = '18791036-6';
  97.  
  98.  
  99.  
  100. /* testing */
  101. SELECT * FROM cuenta;
  102. SELECT * FROM transaccion;
  103.  
  104.  
  105. SELECT * FROM empleado2;
  106. SELECT * FROM departamento;
  107.  
  108.  
  109. SELECT * FROM empleado;
  110. SELECT * FROM auditoria;
  111.  
  112.  
  113.  
  114.  
  115. **PACKAGE**
  116.  
  117. CREATE OR REPLACE PACKAGE CONTROLADOR_PROBLEMA2 AS
  118.  
  119. PROCEDURE ingresar_empleado(rut IN VARCHAR2, nombre IN VARCHAR2, salario IN NUMBER, rutJefe IN VARCHAR2, depto IN NUMBER);
  120. PROCEDURE ingresar_depto(codigo IN NUMBER, nombre IN VARCHAR2);
  121. PROCEDURE incrementar_salarios(depto IN NUMBER,porcentaje IN NUMBER);
  122. PROCEDURE modificar_salario(rut_empleado IN VARCHAR2, new_sal IN NUMBER);
  123. PROCEDURE eliminar_empleado(rut_empleado IN VARCHAR2);
  124. PROCEDURE eliminar_depto(codigo_depto IN NUMBER);
  125. PROCEDURE cambiar_depto_empleado(rut_empleado IN VARCHAR2, new_depto IN NUMBER);
  126. FUNCTION salario_empleado(rut_empleado IN VARCHAR2) RETURN NUMBER;
  127. FUNCTION salario_depto(depto IN NUMBER) RETURN NUMBER;
  128. PROCEDURE cambiar_jefe(antiguo_jefe VARCHAR2, nuevo_jefe VARCHAR2);
  129.  
  130. END CONTROLADOR_PROBLEMA2;
  131.  
  132. CREATE OR REPLACE  PACKAGE BODY CONTROLADOR_PROBLEMA2 AS
  133.  
  134. PROCEDURE ingresar_empleado(rut IN VARCHAR2, nombre IN VARCHAR2, salario IN NUMBER, rutJefe IN VARCHAR2, depto IN NUMBER) IS
  135. BEGIN
  136.     INSERT INTO empleado2 VALUES (rut,nombre,salario,rutJefe,depto);
  137.     EXCEPTION
  138.         WHEN DUP_VAL_ON_INDEX THEN
  139.             DBMS_OUTPUT.PUT_LINE('El rut del empleado ya existe');
  140. END ingresar_empleado;
  141.  
  142. PROCEDURE ingresar_depto(codigo IN NUMBER, nombre IN VARCHAR2) IS
  143. BEGIN
  144.     INSERT INTO Departamento VALUES (codigo,nombre);
  145.     EXCEPTION
  146.         WHEN DUP_VAL_ON_INDEX THEN
  147.             DBMS_OUTPUT.PUT_LINE('El codigo de este departamento ya existe');
  148. END ingresar_depto;
  149.  
  150. PROCEDURE incrementar_salarios(depto IN NUMBER,porcentaje IN NUMBER) IS
  151. new_sal NUMBER(10);
  152. CURSOR c IS SELECT * FROM Empleado2 WHERE codDepto = depto;
  153. BEGIN
  154.     FOR fila IN c
  155.     LOOP  
  156.         new_sal := fila.salario*(1+(porcentaje/100));
  157.         UPDATE Empleado2 SET salario = new_sal WHERE rut = fila.rut;
  158.     END LOOP;  
  159. END incrementar_salarios;
  160.  
  161. PROCEDURE modificar_salario(rut_empleado IN VARCHAR2, new_sal IN NUMBER) IS
  162. BEGIN
  163.      UPDATE Empleado2 SET salario = new_sal WHERE rut = rut_empleado;
  164.      IF SQL%NOTFOUND THEN
  165.         DBMS_OUTPUT.PUT_LINE('Este empleado no existe');
  166.     END IF;
  167. END modificar_salario;
  168.  
  169. PROCEDURE eliminar_empleado(rut_empleado IN VARCHAR2) IS
  170. BEGIN
  171.     DELETE FROM Empleado2 WHERE rut = rut_empleado;
  172.    
  173.     IF SQL%NOTFOUND THEN
  174.         DBMS_OUTPUT.PUT_LINE('Este empleado no existe');
  175.     END IF;
  176. END eliminar_empleado;
  177.  
  178. PROCEDURE eliminar_depto(codigo_depto IN NUMBER) IS
  179. BEGIN
  180.     DELETE FROM Departamento WHERE codigo = codigo_depto;
  181.    
  182.     IF SQL%NOTFOUND THEN
  183.         DBMS_OUTPUT.PUT_LINE('Este Departamento no existe');
  184.     END IF;
  185. END eliminar_depto;
  186.  
  187. PROCEDURE cambiar_depto_empleado(rut_empleado IN VARCHAR2, new_depto IN NUMBER) IS
  188. BEGIN
  189.     UPDATE Empleado2 SET codDepto = new_depto WHERE rut = rut_empleado;
  190.      IF SQL%NOTFOUND THEN
  191.         DBMS_OUTPUT.PUT_LINE('Este empleado no existe');
  192.     END IF;
  193.    
  194. END cambiar_depto_empleado;
  195.  
  196. FUNCTION salario_empleado(rut_empleado IN VARCHAR2) RETURN NUMBER IS
  197. emp_sal NUMBER;
  198. BEGIN
  199.     SELECT salario INTO emp_sal FROM Empleado2 WHERE rut = rut_empleado;
  200.     RETURN emp_sal;
  201. END salario_empleado;
  202.  
  203. FUNCTION salario_depto(depto IN NUMBER) RETURN NUMBER IS
  204. sal_total NUMBER(10):=0;
  205. CURSOR c IS SELECT * FROM Empleado2 WHERE codDepto = depto;
  206. BEGIN
  207.     FOR fila IN c
  208.     LOOP  
  209.         sal_total := sal_total + fila.salario;
  210.     END LOOP;
  211.     RETURN sal_total;
  212. END salario_depto;
  213.  
  214. PROCEDURE cambiar_jefe(antiguo_jefe VARCHAR2, nuevo_jefe VARCHAR2) IS
  215. CURSOR c IS SELECT * FROM Empleado2 WHERE rutJefe = antiguo_jefe;
  216. BEGIN
  217.     FOR fila IN c
  218.     LOOP  
  219.         UPDATE Empleado2 SET rutJefe = nuevo_jefe WHERE rut = fila.rut;
  220.     END LOOP;  
  221. END cambiar_jefe;
  222.  
  223. END CONTROLADOR_PROBLEMA2;
  224.  
  225.  
  226. **PL/SQL PROBLEMA 1**
  227.  
  228. SET serveroutput ON;
  229. DECLARE
  230.     CURSOR c IS SELECT * FROM Transaccion;
  231.  
  232. PROCEDURE insertar(cod IN NUMBER, nom IN VARCHAR2, sal IN NUMBER) IS
  233. BEGIN
  234.     INSERT INTO cuenta VALUES (cod,nom,sal);
  235.    
  236.     EXCEPTION
  237.         WHEN DUP_VAL_ON_INDEX THEN
  238.             UPDATE cuenta SET nombre = nom, saldo = sal WHERE numero = cod;
  239. END;
  240.  
  241. PROCEDURE actualizar(cod IN NUMBER, nom IN VARCHAR2, sal IN NUMBER) IS
  242. BEGIN
  243.     UPDATE cuenta SET saldo = sal WHERE numero = cod;
  244.    
  245.     IF SQL%NOTFOUND THEN
  246.         INSERT INTO cuenta VALUES (cod,nom,sal);
  247.     END IF;
  248. END;
  249.  
  250. PROCEDURE eliminar(cod IN NUMBER) IS
  251. BEGIN
  252.     DELETE FROM cuenta WHERE numero = cod;
  253.    
  254.     IF SQL%NOTFOUND THEN
  255.         DBMS_OUTPUT.PUT_LINE('La cuenta a eliminar no existe');
  256.     END IF;
  257. END;
  258.  
  259. BEGIN
  260.     FOR fila IN c
  261.     LOOP  
  262.         IF fila.operacion = 'I' THEN
  263.             insertar(fila.numero,fila.nombre,fila.saldo);
  264.         ELSIF fila.operacion = 'U' THEN
  265.             actualizar(fila.numero,fila.nombre,fila.saldo);
  266.         ELSIF fila.operacion = 'D' THEN
  267.             eliminar(fila.numero);
  268.         ELSE
  269.             DBMS_OUTPUT.PUT_LINE('El codigo no es valido');
  270.         END IF;
  271.     END LOOP;
  272. END;
Advertisement
RAW Paste Data Copied
Advertisement