Caractersticas Objeto Relacionales en Oracle Francisco Moreno Universidad

Características Objeto Relacionales en Oracle Francisco Moreno Universidad Nacional

Colecciones: VARRAYS (Variable ARRAYs) n n n En la intersección de una fila y de una columna se puede tener un arreglo (colección de elementos indexados). En Oracle se denomina VARRAY Todos los elementos del VARRAY son del mismo tipo de datos Al igual que las tablas anidadas sus elementos pertenecen a un tipo de datos: - Primitivo - Definido por el usuario: puede ser incluso el de otro VARRAY (VARRAY de VARRAYs)

VARRAYS El total de elementos del VARRAY no puede sobrepasar el tamaño definido n Los VARRAYs se pueden considerar como tablas anidadas limitadas (soportan un número limitado de elementos) n Al igual que para las tablas anidadas, se debe definir el tipo para el VARRAY n

Ejemplo Modelo Entidad Relación DPTO # código * nombre * conmutador conformado por adscrito a EMPLEADO # cédula * nombre

VARRAYs A continuación se crea una tabla de departamentos y a cada departamento se le colocará un VARRAY de REFs correspondiente a los empleados que trabajan en dicho dpto, es decir, un VARRAY de punteros.

Gráficamente: DPTO código nombre Note que el empleado con puntero 543 trabaja en dos dptos. conmutador 11 Ventas 2103 20 Cont. 2132 38 Aseo 3390 mis_emps FE 2 AE 1 BC 1 543 43 E 543 Vacío EMP cédula nombre oid 10 Ana FE 2 20 Beto AE 1 40 Rolli 43 E 90 80 Lalo Sara BC 1 543 Punteros

Implementación Se crea el tipo y la tabla para los empleados: DROP TYPE emp_tip FORCE; CREATE OR REPLACE TYPE emp_tip AS OBJECT( cedula NUMBER(8), nombre VARCHAR 2(15) ); / DROP TABLE EMPLEADO; CREATE TABLE empleado OF emp_tip (cedula PRIMARY KEY);

INSERT INSERT n INTO INTO empleado empleado VALUES(10, 'Ana'); VALUES(20, 'Beto'); VALUES(40, 'Rolli'); VALUES(90, 'Lalo'); VALUES(80, 'Sara'); Ahora se crea un tipo VARRAY de 10 posiciones. Cada posición guarda el puntero de un empleado: DROP TYPE emp_varray FORCE; CREATE OR REPLACE TYPE emp_varray AS VARRAY(10) OF REF emp_tip; VARRAY /

Se crea la tabla de departamentos: cada departamento tiene su VARRAY de punteros a empleados: DROP TYPE dep_tip FORCE; CREATE TYPE dep_tip AS OBJECT( codigo NUMBER(3), nombre VARCHAR 2(10), conmutador NUMBER(10), mis_emps emp_varray); emp_varray / DROP TABLE dep; CREATE TABLE dep OF dep_tip(codigo PRIMARY KEY, mis_emps NOT NULL);

Se insertan los datos del dpto 11: INSERT INTO dep VALUES (11, 'Ventas', 2103, emp_varray((SELECT REF(e) FROM empleado WHERE cedula = 10), (SELECT REF(e) FROM empleado WHERE cedula = 20), (SELECT REF(e) FROM empleado WHERE cedula = 90), (SELECT REF(e) FROM empleado WHERE cedula = 80) ) ); e e

Se insertan los datos de los dptos 20 y 38: INSERT INTO dep VALUES (20, 'Cont', 2132, emp_varray((SELECT REF(e) FROM empleado e WHERE cedula = 40), (SELECT REF(e) FROM empleado e WHERE cedula = 80) ) ); INSERT INTO dep VALUES (38, 'Aseo', 3390, emp_varray() Array vacío (no es equivalente a un array NULL) );

Selección n La siguiente consulta imprime cada dpto con el REF* de sus empleados: SELECT * FROM dep; n Para imprimir cada dpto junto con los datos de cada uno de sus empleados se procede así: * Nota: algunos productos imprimen los datos de los empleados en vez de su puntero.

Acá también al desanidar surge la columna COLUMN_VALUE: SELECT nombre, DEREF(e. COLUMN_VALUE) FROM dep, TABLE(mis_emps) e; SELECT nombre, e. COLUMN_VALUE. cedula AS ced FROM dep, TABLE(mis_emps) e;

Poco soporte en el SQL de Oracle para inserción, actualización o borrado de los elementos de un VARRAY. Para estas operaciones se recomienda usar PL/SQL. Veamos un ejemplo: Inserción de un empleado en un departamento. Se crea un procedimiento:

Código del dpto Cédula del empleado CREATE OR REPLACE PROCEDURE insemp(midep IN NUMBER, miemp IN NUMBER) AS varr emp_varray; /* Se declara un VARRAY de punteros a empleados */ refmiemp REF emp_tip; -- Puntero a un empleado BEGIN /*Se obtiene el VARRAY de empleados del dpto enviado: */ SELECT mis_emps INTO varr FROM dep WHERE codigo = midep; Continúa…

/*Se comprueba que no se está en el límite del VARRAY*/ IF varr. COUNT = varr. LIMIT THEN RAISE_APPLICATION_ERROR(-20564, 'No hay sitio para otro emp. en el dpto ' || midep); END IF; --Se obtiene el REF del empleado enviado SELECT REF(e) INTO refmiemp FROM empleado e WHERE cedula = miemp; Continúa…

--Comprobar que el emp. no esté repetido FOR contador IN 1. . varr. COUNT LOOP IF varr(contador) = refmiemp THEN RAISE_APPLICATION_ERROR(-20764, 'El emp ' || miemp || ' ya trabaja en el dpto ' || midep); END IF; END LOOP; Nótese como se accede a un elemento del VARRAY. Continúa…

--Prepara el VARRAY para un nuevo elemento varr. EXTEND; --Activa la celda y le pone un null varr(varr. COUNT) : = refmiemp; --Asigna el elemento --¡Actualizar en la base de datos! UPDATE dep SET mis_emps = varr WHERE codigo = midep; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20111, 'Dato no existe'); END; / Nota: si se hace un EXTEND y el VARRAY está lleno, genera error: ORA-06532: Subscript outside of limit

n Nota: Recordar que en PL/SQL una tabla anidada se puede manipular igual que un VARRAY. Los atributos EXTEND, COUNT, DELETE y otros también funcionan con tablas anidadas en PL/SQL. n n Invocación del procedimiento: EXECUTE insemp(38, 40); EXECUTE insemp(38, 80);

Ejercicio resuelto: Agregar al tipo emp_tip un método que devuelva un VARRAY con los punteros de los departamentos en los que trabaja cada empleado. Ver solución en: http: //www. medellin. unal. edu. co/~fjmoreno/bd 2/Ejevarrays. REF. txt Una alternativa para evitar el método, es redundar datos y colocarle a cada empleado su VARRAY de REFs de departamentos donde trabaja.

Se debe evaluar si se justifica hacerlo: analizar aspectos de almacenamiento, consistencia, velocidad, mantenimiento de código, entre otros. n En conclusión, ver cuales son las prioridades y procurar un balance… n

Tablas anidadas Vs. VARRAYs Tablas Anidadas n n n Almacenadas out of line Se columnas se pueden indexar* No limitadas en número de elementos Soporte “directo” en SQL para INSERT, DELETE y UPDATE de filas de la tabla anidada Apropiadas para grandes conjuntos de elementos VARRAYS n n n Almacenados in-line (si < 4 k) No soportan índices Limitados en el número de elementos aunque se puede ampliar Se recomienda PL/SQL para insertar, borrar y actualizar elementos del varray Útiles para conjuntos “pequeños” y que posean un límite conocido y estable su tiempo de respuesta es “bueno” * Ejemplo. CREATE INDEX nest_prod ON store_detalles(id_producto); Ver la presentación de tablas anidadas.

Ejercicios n n n Crear un procedimiento que elimine un empleado de un dpto. Imprimir el código de todos los departamentos que tienen el mismo conjunto de empleados que el dpto 20. Agregar al tipo emp_tip un método que devuelva un VARRAY con los codigos (no los punteros) de los departamentos en los que trabaja cada empleado.