Saltar la navegación

Anexo VII.- Ejemplo de paquete.

Aquí puedes ver un ejemplo de un paquete que agrupa las principales tareas que llevamos a cabo con la base de datos de ejemplo.

CREATE OR REPLACE PACKAGE call_center AS     --inicialización 
     --Definimos los tipos que utilizaremos 
     SUBTYPE agente IS agentes%ROWTYPE; 
     SUBTYPE familia IS familias%ROWTYPE; 
     SUBTYPE oficina IS oficinas%ROWTYPE; 
     TYPE tAgentes IS TABLE OF agente; 
     TYPE tFamilias IS TABLE OF familia; 
     TYPE tOficinas IS TABLE OF oficina; 
     
     --Definimos las excepciones propias 
     referencia_no_encontrada exception; 
     referencia_encontrada exception; 
     no_null exception; 
     PRAGMA EXCEPTION_INIT(referencia_no_encontrada, -2291); 
     PRAGMA EXCEPTION_INIT(referencia_encontrada, -2292); 
     PRAGMA EXCEPTION_INIT(no_null, -1400); 
     
     --Definimos los errores que vamos a tratar 
     todo_bien                CONSTANT NUMBER := 0; 
     elemento_existente           CONSTANT NUMBER:= -1; 
     elemento_inexistente           CONSTANT NUMBER:= -2; 
     padre_existente          CONSTANT NUMBER:= -3; 
     padre_inexistente          CONSTANT NUMBER:= -4; 
     no_null_violado          CONSTANT NUMBER:= -5; 
     operacion_no_permitida          CONSTANT NUMBER:= -6; 
     
     --Definimos los subprogramas públicos 
     --Nos devuelve la oficina padre de un agente 
     PROCEDURE oficina_padre( mi_agente agente, padre OUT oficina ); 
     
     --Nos devuelve la oficina padre de una familia 
     PROCEDURE oficina_padre( mi_familia familia, padre OUT oficina ); 
     
     --Nos da los hijos de una familia 
     PROCEDURE dame_hijos( mi_familia familia, hijos IN OUT tAgentes ); 
     
     --Nos da los hijos de una oficina 
     PROCEDURE dame_hijos( mi_oficina oficina, hijos IN OUT tAgentes ); 
     
     --Inserta un agente 
     FUNCTION inserta_agente ( mi_agente agente ) 
     RETURN NUMBER; 
     
     --Inserta una familia 
     FUNCTION inserta_familia( mi_familia familia ) 
     RETURN NUMBER; 
     
     --Inserta una oficina 
     FUNCTION inserta_oficina ( mi_oficina oficina ) 
     RETURN NUMBER; 
     
     --Borramos una oficina 
     FUNCTION borra_oficina( id_oficina NUMBER ) 
     RETURN NUMBER; 
     
     --Borramos una familia 
     FUNCTION borra_familia( id_familia NUMBER ) 
     RETURN NUMBER; 
     
     --Borramos un agente 
     FUNCTION borra_agente( id_agente NUMBER ) 
     RETURN NUMBER; 
END call_center; 
/ 
CREATE OR REPLACE PACKAGE BODY call_center AS          --cuerpo 
     --Implemento las funciones definidas en la especificación 
     
     --Nos devuelve la oficina padre de un agente 
     PROCEDURE oficina_padre( mi_agente agente, padre OUT oficina ) IS 
          mi_familia familia; 
     BEGIN 
          IF (mi_agente.oficina IS NOT NULL) THEN 
               SELECT * INTO padre FROM oficinas 
               WHERE identificador = mi_agente.oficina; 
          ELSE 
               SELECT * INTO mi_familia FROM familias 
               WHERE identificador = mi_agente.familia; 
               oficina_padre( mi_familia, padre ); 
          END IF; 
     EXCEPTION 
          WHEN OTHERS THEN 
               padre := NULL; 
     END oficina_padre; 
     
     --Nos devuelve la oficina padre de una familia 
     PROCEDURE oficina_padre( mi_familia familia, padre OUT oficina ) IS 
          madre familia; 
     BEGIN 
          IF (mi_familia.oficina IS NOT NULL) THEN 
               SELECT * INTO padre FROM oficinas 
               WHERE identificador = mi_familia.oficina; 
          ELSE 
               SELECT * INTO madre FROM familias 
               WHERE identificador = mi_familia.familia; 
               oficina_padre( madre, padre ); 
          END IF; 
     EXCEPTION 
          WHEN OTHERS THEN 
               padre := NULL; 
     END oficina_padre; 
     
     --Nos da los hijos de una familia 
     PROCEDURE dame_hijos( mi_familia familia, hijos IN OUT tAgentes ) IS 
          CURSOR cHijos IS SELECT * FROM agentes 
          WHERE familia = mi_familia.identificador; 
          CURSOR cHijas IS SELECT * FROM familias 
          WHERE familia = mi_familia.identificador; 
          hijo agente; 
          hija familia; 
     BEGIN 
          --inicializamos la tabla si no lo está 
          if (hijos IS NULL) THEN 
               hijos := tAgentes(); 
          END IF; 
          --metemos en la tabla los hijos directos 
          OPEN cHijos; 
          LOOP 
               FETCH cHijos INTO hijo; 
               EXIT WHEN cHijos%NOTFOUND; 
               hijos.EXTEND; 
               hijos(hijos.LAST) := hijo; 
          END LOOP; 
          CLOSE cHijos; 
          --hacemos lo mismo para las familias hijas 
          OPEN cHijas; 
          LOOP 
               FETCH cHijas INTO hija; 
               EXIT WHEN cHijas%NOTFOUND; 
               dame_hijos( hija, hijos ); 
          END LOOP; 
          CLOSE cHijas; 
     EXCEPTION 
          WHEN OTHERS THEN 
               hijos := tAgentes(); 
     END dame_hijos; 
     
     --Nos da los hijos de una oficina 
     PROCEDURE dame_hijos( mi_oficina oficina, hijos IN OUT tAgentes ) IS 
          CURSOR cHijos IS SELECT * FROM agentes 
          WHERE oficina = mi_oficina.identificador; 
          CURSOR cHijas IS SELECT * FROM familias 
          WHERE oficina = mi_oficina.identificador; 
          hijo agente; 
          hija familia; 
     BEGIN 
          --inicializamos la tabla si no lo está 
          if (hijos IS NULL) THEN 
               hijos := tAgentes(); 
          END IF; 
          --metemos en la tabla los hijos directos 
          OPEN cHijos; 
          LOOP 
               FETCH cHijos INTO hijo; 
               EXIT WHEN cHijos%NOTFOUND; 
               hijos.EXTEND; 
               hijos(hijos.LAST) := hijo; 
          END LOOP; 
          CLOSE cHijos; 
          --hacemos lo mismo para las familias hijas 
          OPEN cHijas; 
          LOOP 
               FETCH cHijas INTO hija; 
               EXIT WHEN cHijas%NOTFOUND; 
               dame_hijos( hija, hijos ); 
          END LOOP; 
          CLOSE cHijas; 
     EXCEPTION 
          WHEN OTHERS THEN 
               hijos := tAgentes(); 
     END dame_hijos; 
     
     --Inserta un agente 
     FUNCTION inserta_agente ( mi_agente agente ) 
     RETURN NUMBER IS 
     BEGIN 
          IF (mi_agente.familia IS NULL and mi_agente.oficina IS NULL) THEN 
               RETURN operacion_no_permitida; 
          END IF; 
          IF (mi_agente.familia IS NOT NULL and mi_agente.oficina IS NOT NULL) THEN 
               RETURN operacion_no_permitida; 
          END IF; 
          INSERT INTO agentes VALUES (mi_agente.identificador, mi_agente.nombre, mi_agente.usuario, mi_agente.clave, mi_agente.habilidad, mi_agente.categoria, mi_agente.familia, mi_agente.oficina ); 
          COMMIT; 
          RETURN todo_bien; 
     EXCEPTION 
          WHEN referencia_no_encontrada THEN 
               ROLLBACK; 
               RETURN padre_inexistente; 
          WHEN no_null THEN 
               ROLLBACK; 
               RETURN no_null_violado; 
          WHEN DUP_VAL_ON_INDEX THEN 
               ROLLBACK; 
               RETURN elemento_existente; 
          WHEN OTHERS THEN 
               ROLLBACK; 
               RETURN SQLCODE; 
     END inserta_agente; 
     
     --Inserta una familia 
     FUNCTION inserta_familia( mi_familia familia ) 
     RETURN NUMBER IS 
     BEGIN 
          IF (mi_familia.familia IS NULL and mi_familia.oficina IS NULL) THEN 
               RETURN operacion_no_permitida; 
          END IF; 
          IF (mi_familia.familia IS NOT NULL and mi_familia.oficina IS NOT NULL) THEN 
               RETURN operacion_no_permitida; 
          END IF; 
          INSERT INTO familias VALUES ( mi_familia.identificador, mi_familia.nombre, mi_familia.familia, mi_familia.oficina ); 
          COMMIT; 
          RETURN todo_bien; 
     EXCEPTION 
          WHEN referencia_no_encontrada THEN 
               ROLLBACK; 
               RETURN padre_inexistente; 
          WHEN no_null THEN 
               ROLLBACK; 
               RETURN no_null_violado; 
           WHEN DUP_VAL_ON_INDEX THEN 
               ROLLBACK; 
               RETURN elemento_existente; 
          WHEN OTHERS THEN 
               ROLLBACK; 
               RETURN SQLCODE; 
     END inserta_familia; 
     
     --Inserta una oficina 
     FUNCTION inserta_oficina ( mi_oficina oficina ) 
     RETURN NUMBER IS 
     BEGIN 
          INSERT INTO oficinas VALUES (mi_oficina.identificador, mi_oficina.nombre, mi_oficina.domicilio, mi_oficina.localidad, mi_oficina.codigo_postal ); 
          COMMIT; 
          RETURN todo_bien; 
     EXCEPTION 
          WHEN no_null THEN 
               ROLLBACK; 
               RETURN no_null_violado; 
          WHEN DUP_VAL_ON_INDEX THEN 
               ROLLBACK; 
               RETURN elemento_existente; 
          WHEN OTHERS THEN 
               ROLLBACK; 
               RETURN SQLCODE; 
     END inserta_oficina; 
     
     --Borramos una oficina 
     FUNCTION borra_oficina( id_oficina NUMBER ) 
     RETURN NUMBER IS 
          num_ofi NUMBER; 
     BEGIN 
          SELECT COUNT(*) INTO num_ofi FROM oficinas 
          WHERE identificador = id_oficina; 
          IF (num_ofi = 0) THEN 
               RETURN elemento_inexistente; 
          END IF; 
          DELETE oficinas WHERE identificador = id_oficina; 
          COMMIT; 
          RETURN todo_bien; 
     EXCEPTION 
          WHEN OTHERS THEN 
               ROLLBACK; 
               RETURN SQLCODE; 
     END borra_oficina; 
     
     --Borramos una familia 
     FUNCTION borra_familia( id_familia NUMBER ) 
     RETURN NUMBER IS 
          num_fam NUMBER; 
     BEGIN 
          SELECT COUNT(*) INTO num_fam FROM familias 
          WHERE identificador = id_familia; 
          IF (num_fam = 0) THEN 
               RETURN elemento_inexistente; 
          END IF; 
          DELETE familias WHERE identificador = id_familia; 
          COMMIT; 
          RETURN todo_bien; 
     EXCEPTION 
          WHEN OTHERS THEN 
               ROLLBACK; 
               RETURN SQLCODE; 
     END borra_familia; 
      
     --Borramos un agente 
     FUNCTION borra_agente( id_agente NUMBER ) 
     RETURN NUMBER IS 
          num_ag NUMBER; 
     BEGIN 
          SELECT COUNT(*) INTO num_ag FROM agentes 
          WHERE identificador = id_agente; 
          IF (num_ag = 0) THEN 
               RETURN elemento_inexistente; 
          END IF; 
          DELETE agentes WHERE identificador = id_agente; 
          COMMIT; 
          RETURN todo_bien; 
     EXCEPTION 
          WHEN OTHERS THEN 
               ROLLBACK; 
               RETURN SQLCODE; 
     END borra_agente; 
END call_center; 
/