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;
/