SQL> @&1 SQL> /* @max_2_ProvPieza_v3.sql */ SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> SET linesize 132 SQL> SET pagesize 300 SQL> column color format A32 SQL> column numProv format 999 SQL> SQL> /* Añadir a la tabla Pieza un atributo para el total de proveedores de la pieza SQL> con la restricción de que dicho valor sea >=0 y por defecto tome el valor 0. SQL> */ SQL> ALTER TABLE Pieza ADD(numProv number DEFAULT 0 CONSTRAINT numProv_NoNeg CHECK(numProv>=0)); Tabla modificada. SQL> SQL> /* Crear un procedimiento para calcular el total de proveedores de cada pieza SQL> */ SQL> CREATE OR REPLACE PROCEDURE set_numProvPieza_PR 2 IS 3 BEGIN 4 UPDATE Pieza P SET numProv=(SELECT count(*) FROM Suministrar WHERE clvPieza = P.clvPieza); 5 END set_numProvPieza_PR; 6 / Procedimiento creado. SQL> SHOW ERRORS PROCEDURE set_numProv_Pieza_PR No hay errores. SQL> SQL> column OBJECT_NAME heading "nombre" format a20; SQL> column DATA_OBJECT_ID heading "id" format a2; SQL> column SUBOBJECT_NAME heading "subobjeto" format a9; SQL> SQL> /* mostrar todos los procedures del usuario */ SQL> SELECT * FROM user_objects WHERE object_type='PROCEDURE'; nombre subobjeto OBJECT_ID id OBJECT_TYPE CREATED LAST_DDL TIMESTAMP STATUS T G S -------------------- --------- ---------- ---------- ------------------ -------- -------- ------------------- ------- - - - PONERSUMINISTRO 2446212 PROCEDURE 13/05/16 15/05/16 2016-05-15:19:30:40 VALID N N N SET_NUMPROVPIEZA_PR 2446569 PROCEDURE 15/05/16 15/05/16 2016-05-15:21:47:14 VALID N N N SQL> SQL> /* asignar valor al total de proveedores de cada pieza SQL> */ SQL> EXECUTE set_numProvPieza_PR Procedimiento PL/SQL terminado correctamente. SQL> SQL> /* Crear un disparador para actualizar el nº de Proveedores por pieza SQL> */ SQL> CREATE OR REPLACE TRIGGER updateNumProvPieza_TR 2 AFTER INSERT OR DELETE OR UPDATE ON Suministrar 3 FOR EACH ROW 4 BEGIN 5 IF INSERTING THEN 6 UPDATE Pieza SET numProv=numProv+1 WHERE clvPieza = :NEW.clvPieza; 7 ELSIF DELETING THEN 8 UPDATE Pieza SET numProv=numProv-1 WHERE clvPieza = :OLD.clvPieza; 9 ELSIF UPDATING THEN 10 UPDATE Pieza SET numProv=numProv+1 WHERE clvPieza = :NEW.clvPieza; 11 UPDATE Pieza SET numProv=numProv-1 WHERE clvPieza = :OLD.clvPieza; 12 END IF; 13 END updateNumProvPieza_TR; 14 / Disparador creado. SQL> SHOW ERRORS TRIGGER updateNumProvPieza_TR No hay errores. SQL> SQL> /* añadir la restricción de que el número de proveedores de una pieza debe ser <= 2 SQL> */ SQL> ALTER TABLE Pieza ADD CONSTRAINT chkMaxProv CHECK(numProv<=2); Tabla modificada. SQL> SQL> SELECT * FROM Pieza; CLVPIEZA NOMBPIEZA COLOR NUMPROV ---------- -------------------------------- -------------------------------- ------- 91 TUERCA ROJO 1 92 TUERCA VERDE 2 93 TORNILLO AZUL 1 94 TORNILLO ROJO 1 95 TUERCA AZUL 1 96 PALANCA 0 97 TUBO GRIS 0 77 paño amarillo 0 78 formón gris 0 9 filas seleccionadas. SQL> SELECT * FROM Suministrar ORDER BY clvProv, clvPieza; CLVPROV CLVPIEZA ---------- ---------- 1 91 1 92 1 95 2 92 2 93 2 94 6 filas seleccionadas. SQL> SQL> -- Mostrar todos los disparadores y su estado SQL> SELECT TRIGGER_NAME, STATUS FROM USER_TRIGGERS; TRIGGER_NAME STATUS ------------------------------ -------- GANARETAPA ENABLED UPDATENUMPROVPIEZA_TR ENABLED UPDATE_MATRDXT ENABLED SQL> SQL> /* añadir un suministro para comprobar que funciona correctamente el disparador SQL> */ SQL> INSERT INTO suministrar VALUES (3, 92); INSERT INTO suministrar VALUES (3, 92) * ERROR en línea 1: ORA-02290: restricción de control (ASIGBD.CHKMAXPROV) violada ORA-06512: en "ASIGBD.UPDATENUMPROVPIEZA_TR", línea 3 ORA-04088: error durante la ejecución del disparador 'ASIGBD.UPDATENUMPROVPIEZA_TR' SQL> SQL> /* modificar la definición del disparador para gestionar las excepciones SQL> */ SQL> CREATE OR REPLACE TRIGGER updateNumProvPieza_TR 2 AFTER INSERT OR DELETE OR UPDATE ON Suministrar 3 FOR EACH ROW 4 BEGIN 5 IF INSERTING THEN 6 UPDATE Pieza SET numProv=numProv+1 WHERE clvPieza = :NEW.clvPieza; 7 ELSIF DELETING THEN 8 UPDATE Pieza SET numProv=numProv-1 WHERE clvPieza = :OLD.clvPieza; 9 ELSIF UPDATING THEN 10 UPDATE Pieza SET numProv=numProv+1 WHERE clvPieza = :NEW.clvPieza; 11 UPDATE Pieza SET numProv=numProv-1 WHERE clvPieza = :OLD.clvPieza; 12 END IF; 13 EXCEPTION 14 WHEN OTHERS THEN 15 IF SQLCODE = -2290 THEN DBMS_OUTPUT.PUT_LINE('ERROR: max. 2 Prov/Pieza'); 16 END IF; 17 RAISE; -- propagar la excepción 18 -- DBMS_OUTPUT.PUT_LINE('ERROR '||SQLCODE||' '||SQLERRM); 19 END updateNumProvPieza_TR; 20 / Disparador creado. SQL> SHOW ERRORS TRIGGER updateNumProvPieza_TR No hay errores. SQL> SQL> -- comprobar el funcionamiento del disparador SQL> INSERT INTO suministrar VALUES (3, 92); ERROR: max. 2 Prov/Pieza INSERT INTO suministrar VALUES (3, 92) * ERROR en línea 1: ORA-02290: restricción de control (ASIGBD.CHKMAXPROV) violada ORA-06512: en "ASIGBD.UPDATENUMPROVPIEZA_TR", línea 14 ORA-04088: error durante la ejecución del disparador 'ASIGBD.UPDATENUMPROVPIEZA_TR' SQL> SQL> /* probar que funciona el disparador que limita el número de proveedores a 2 SQL> gestionando la excepción generada al sobrepasar el número de proveedores SQL> */ SQL> BEGIN 2 INSERT INTO suministrar VALUES (3, 92); 3 EXCEPTION 4 WHEN OTHERS THEN 5 IF SQLCODE = -2290 THEN DBMS_OUTPUT.PUT_LINE('operación no permitida'); 6 ELSE DBMS_OUTPUT.PUT_LINE('ERROR '||SQLCODE||' '||SQLERRM); 7 END IF; 8 ROLLBACK; 9 END; 10 / ERROR: max. 2 Prov/Pieza operación no permitida Procedimiento PL/SQL terminado correctamente. SQL> SQL> /* se pueden definir nuevas excepciones para evitar los códigos numéricos SQL> */ SQL> DECLARE 2 check_violated EXCEPTION; -- declaración de la excepción 3 PRAGMA EXCEPTION_INIT (check_violated, -2290); -- asignar código de error a la excepción 4 BEGIN 5 INSERT INTO suministrar SELECT 3, clvPieza FROM Suministrar WHERE clvProv=2; 6 EXCEPTION 7 WHEN check_violated THEN 8 DBMS_OUTPUT.PUT_LINE('operación no permitida'); 9 ROLLBACK; 10 WHEN OTHERS THEN 11 DBMS_OUTPUT.PUT_LINE('ERROR '||SQLCODE||' '||SQLERRM); 12 ROLLBACK; 13 END; 14 / ERROR: max. 2 Prov/Pieza operación no permitida Procedimiento PL/SQL terminado correctamente. SQL> SQL> -- ejemplo de definición de una secuencia y de un procedimiento para añadir piezas SQL> ------------------------------------------------------------------------------- SQL> DROP SEQUENCE seq_clvPieza; Secuencia borrada. SQL> SQL> CREATE SEQUENCE seq_clvPieza MINVALUE 100 INCREMENT BY 2 START WITH 100; Secuencia creada. SQL> SQL> /* mostrar todas las secuencias definidas por el usuario */ SQL> SELECT * FROM USER_SEQUENCES; SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER ------------------------------ ---------- ---------- ------------ - - ---------- ----------- GENIDFILA 1 1,0000E+27 1 N N 20 21 SEQ_CLVPIEZA 100 1,0000E+27 2 N N 20 100 SEQ_CLVPROV 100 1,0000E+27 1 N N 20 140 SQL> SQL> CREATE OR REPLACE PROCEDURE ponerPieza ( 2 elTipo IN Pieza.nombPieza%TYPE, 3 elColor IN Pieza.color%TYPE) 4 AS 5 BEGIN 6 INSERT INTO Pieza VALUES(seq_clvPieza.NEXTVAL, UPPER(elTipo), UPPER(elColor),0); 7 END; 8 / Procedimiento creado. SQL> SHOW ERRORS PROCEDURE ponerPieza No hay errores. SQL> SQL> /* añadir una nueva pieza con el procedimiento creado */ SQL> EXECUTE ponerPieza('alicates','rosa') Procedimiento PL/SQL terminado correctamente. SQL> SELECT * FROM Pieza; CLVPIEZA NOMBPIEZA COLOR NUMPROV ---------- -------------------------------- -------------------------------- ------- 91 TUERCA ROJO 1 92 TUERCA VERDE 2 93 TORNILLO AZUL 1 94 TORNILLO ROJO 1 95 TUERCA AZUL 1 96 PALANCA 0 97 TUBO GRIS 0 77 paño amarillo 0 78 formón gris 0 100 ALICATES ROSA 0 10 filas seleccionadas. SQL> SQL> COMMIT; Confirmación terminada. SQL> SQL> /* al intentar añadir en una única sentencia más suministros de esta pieza SQL> que los permitidos se producirá error y no se inserta ningún suministro SQL> */ SQL> DECLARE 2 check_violated EXCEPTION; -- declaración de la excepción 3 PRAGMA EXCEPTION_INIT (check_violated, -2290); -- asignar código de error a la excepción 4 BEGIN 5 INSERT INTO suministrar 6 SELECT clvProv, seq_clvPieza.CURRVAL 7 FROM proveedor; 8 EXCEPTION 9 WHEN check_violated THEN 10 DBMS_OUTPUT.PUT_LINE('operación no permitida'); 11 ROLLBACK; 12 WHEN OTHERS THEN 13 DBMS_OUTPUT.PUT_LINE('ERROR '||SQLCODE||' '||SQLERRM); 14 ROLLBACK; 15 END; 16 / ERROR: max. 2 Prov/Pieza operación no permitida Procedimiento PL/SQL terminado correctamente. SQL> SELECT * FROM Suministrar; CLVPROV CLVPIEZA ---------- ---------- 1 91 1 92 2 92 2 93 2 94 1 95 6 filas seleccionadas. SQL> SQL> /* si sólo se añaden 2 suministros de esta pieza, ningún problema */ SQL> INSERT INTO suministrar 2 SELECT clvProv, seq_clvPieza.CURRVAL 3 FROM proveedor 4 WHERE clvProv < 3; 2 filas creadas. SQL> -- SELECT * FROM Suministrar; SQL> SQL> /* al intentar añadir uno más se vuelve a producir error */ SQL> INSERT INTO suministrar VALUES (3, seq_clvPieza.CURRVAL); ERROR: max. 2 Prov/Pieza INSERT INTO suministrar VALUES (3, seq_clvPieza.CURRVAL) * ERROR en línea 1: ORA-02290: restricción de control (ASIGBD.CHKMAXPROV) violada ORA-06512: en "ASIGBD.UPDATENUMPROVPIEZA_TR", línea 14 ORA-04088: error durante la ejecución del disparador 'ASIGBD.UPDATENUMPROVPIEZA_TR' SQL> SELECT * FROM Suministrar; CLVPROV CLVPIEZA ---------- ---------- 1 91 1 92 2 92 2 93 2 94 1 95 1 100 2 100 8 filas seleccionadas. SQL> --------------------------------------------------------- SQL> SQL> CREATE OR REPLACE PROCEDURE ponerSuministro ( 2 elProv IN suministrar.clvProv%TYPE, 3 laPieza IN suministrar.clvPieza%TYPE) 4 IS 5 check_violated EXCEPTION; -- declaración de la excepción 6 PRAGMA EXCEPTION_INIT (check_violated, -2290); -- asignar código de error a la excepción 7 BEGIN 8 INSERT INTO suministrar (clvProv, clvPieza) VALUES(elProv, laPieza); 9 EXCEPTION 10 WHEN check_violated THEN 11 DBMS_OUTPUT.PUT_LINE('operación no permitida'); 12 ROLLBACK; 13 WHEN OTHERS THEN 14 DBMS_OUTPUT.PUT_LINE('ERROR '||SQLCODE||' '||SQLERRM); 15 ROLLBACK; 16 END; 17 / Procedimiento creado. SQL> SHOW ERRORS PROCEDURE ponerSuministro No hay errores. SQL> SQL> EXECUTE ponerSuministro(3,92); ERROR: max. 2 Prov/Pieza operación no permitida Procedimiento PL/SQL terminado correctamente. SQL> SQL> SELECT * FROM Suministrar; CLVPROV CLVPIEZA ---------- ---------- 1 91 1 92 2 92 2 93 2 94 1 95 1 100 2 100 8 filas seleccionadas. SQL> SQL> SELECT * FROM Pieza P 2 WHERE numProv <> (SELECT count(*) FROM suministrar WHERE clvPieza = P.clvPieza); ninguna fila seleccionada SQL> --------------------------------------------------------- SQL> -- FINAL DEL TEST. Eliminar de la BD los elementos añadidos. SQL> SQL> -- eliminar los disparadores y procedures creados en este ejemplo SQL> DROP TRIGGER updateNumProvPieza_TR; Disparador borrado. SQL> DROP PROCEDURE set_numProvPieza_PR; Procedimiento borrado. SQL> DROP PROCEDURE ponerPieza; Procedimiento borrado. SQL> SQL> -- eliminar las tuplas añadidas SQL> DELETE FROM suministrar WHERE clvProv = 3; 0 filas suprimidas. SQL> DELETE FROM suministrar WHERE clvPieza >= 100; 2 filas suprimidas. SQL> DELETE FROM pieza WHERE clvPieza >= 100; 1 fila suprimida. SQL> SQL> -- eliminar el atributo numProv añadido a la tabla Pieza SQL> ALTER TABLE Pieza DROP COLUMN numProv; Tabla modificada. SQL> exit