Características Objeto Relacionales en Oracle 10G*

Características Objeto Relacionales en Oracle 10G* * Tomado del curso de Francisco Moreno Seminario de Bases de Datos

Seminario de Bases de Datos Colecciones Las colecciones en Oracle son de 2 Tipos: • Tablas Anidadas (Nested Tables)‏ • Varrays (Variable Arrays )‏

Seminario de Bases de Datos Tablas Anidadas La intersección de una fila y una columna puede contener una tabla ¿Violación a la primera forma normal? Álgebra y Cálculo para este tipo de relaciones en: Roth, M.A.; Korth, H.F.; Silberschatz, A. "Extended Algebra and Calculus for ~1NF Relational Databases“, Reporte Técnico TR.85.19, Universidad de Texas, Austin, 1985.

Seminario de Bases de Datos Primero se debe definir el tipo de la tabla anidada que desea crear El tipo de datos de la tabla anidada puede estar basado en un tipo de datos: - Primitivo - Definido por el usuario (típicamente) - Incluso en el de otra tabla anidada (tablas anidadas de tablas anidadas etc.)‏

Seminario de Bases de Datos Cada tabla anidada puede contener un número ilimitado de filas Son una alternativa para eliminar relaciones 1 a muchos haciendo en algunos casos más natural el diseño El lenguaje para su manipulación puede resultar complejo

Seminario de Bases de Datos Ejemplo Modelo Entidad Relación compuesta de DETALLE #id_producto *cantidad ORDEN # id_orden *fecha en Veamos algunas alternativas para implementar este modelo Nota: La relación de orden a detalle podría ser obligatoria…

Seminario de Bases de Datos Relacional: 2 tablas y manejo de clave foránea DETALLE #id_producto #Id_orden (cf)‏ *cantidad ORDEN # id_orden *fecha Objeto relacional: Primera forma conREFs: -Crear los tipos para ORDEN y para DETALLE -Crear las tablas tipadas correspondientes -En el tipo DETALLE el atributo id_orden en vez de ser una clave foránea, se convierte en un REF que apunta hacia una tabla tipada de órdenes

Seminario de Bases de Datos Objeto relacional: Segunda forma con tablas anidadas Se crea un tipo tabla anidada para manejar los detalles Se crea una tabla “clásica” para manejar las órdenes con columnas: - id_orden - fecha - detalles: La cual será una tabla anidada de detalles, donde cada detalle consta de - id_producto - cantidad

Gráficamente: id_orden fecha detalles 11 Julio 13 de 2003 id_producto cantidad 1 100 2 90 34 Mayo 2 de 2003 Vacía 78 Junio 23 de 2002 id_producto cantidad 1 150 Tabla de Órdenes Seminario de Bases de Datos

Seminario de Bases de Datos Se crea normalmente el tipo para los detalles: DROP TYPE detalle_tip FORCE; CREATE OR REPLACE TYPE detalle_tip AS OBJECT( id_producto NUMBER(3), cantidad NUMBER(10)); /

Seminario de Bases de Datos Se crea el tipo de la tabla anidada basada en el tipo detalle_tip : CREATE OR REPLACE TYPE nest_detalle AS TABLE OF detalle_tip; / Un tipo de tabla anidada puede estar basado en un tipo primitivo, por ejemplo: CREATE OR REPLACE TYPE hobbies AS TABLE OF VARCHAR2(10); /

Seminario de Bases de Datos Ahora ya es posible declarar la columna detalles de tipo nest_detalle (tabla anidada de detalles): DROP TABLE orden PURGE; CREATE TABLE orden ( id_orden NUMBER(3) PRIMARY KEY, fecha DATE NOT NULL, detalles nest_detalle)‏ NESTED TABLE detalles STORE AS store_detalles; ¿Qué significa?

Seminario de Bases de Datos detalles es el nombre de la columna y contiene para cada orden su tabla anidada de detalles. store_detalles es el nombre físico del lugar (tabla) donde se almacenan todas las tablas anidadas de la columna detalles. Esta tabla no se puede accesar directamente*, sólo a través de la columna detalles. Directamente es “intocable”, sólo se puede describir… * Aunque existe un HINT, que no se verá acá, que permite hacerlo…

Seminario de Bases de Datos Inserción de datos INSERT INTO orden VALUES(100,SYSDATE, nest_detalle( detalle_tip(10,1000), detalle_tip(11,900), detalle_tip(17,200))‏ ); INSERT INTO orden VALUES(200,SYSDATE+1, nest_detalle( detalle_tip(10,2000), detalle_tip(5,100), detalle_tip(13,220))‏ );

Seminario de Bases de Datos Selección: La selección es “normal”: SELECT * FROM orden; --Imprime cada orden acompañada de todos sus items… SELECT detalles, id_orden FROM orden; --Imprime el código de cada orden y sus detalles… ¿Qué pasa si se desea imprimir el código de cada orden sólo con el código de los productos de sus detalles? Ver más adelante

Seminario de Bases de Datos Para agregar más detalles a la orden # 100, se requiere usar el operador TABLE, para acceder a la tabla anidada así: INSERT INTO TABLE (SELECT detalles FROM orden WHERE id_orden=100) VALUES(31,330); INSERT INTO TABLE (SELECT detalles FROM orden WHERE id_orden=200) VALUES(32,30);

Seminario de Bases de Datos Considérese lo siguiente: DELETE orden; INSERT INTO orden VALUES(111,SYSDATE,NULL); --Y ahora: INSERT INTO TABLE (SELECT detalles FROM orden WHERE id_orden=111) VALUES(10,22); --Genera el error: ORA-22908: reference to NULL table value ¿Entonces cómo llenarla? Tabla anidada nula

Seminario de Bases de Datos Lo que se debe hacer es un update de la siguiente manera: UPDATE orden SET detalles = nest_detalle ( detalle_tip(10,1000), detalle_tip(11,1100), detalle_tip(12,1200))‏ WHERE id_orden = 111;

Seminario de Bases de Datos Supóngase que se realiza lo siguiente: DELETE FROM TABLE(SELECT detalles FROM orden WHERE id_orden=111); Para insertar los detalles de la orden 111, se puede proceder* así: INSERT INTO TABLE (SELECT detalles FROM orden WHERE id_orden=111) VALUES(10,2000); Conclusión: Tabla anidada átomicamente nula ≠ Tabla anidada vacía *En este caso el UPDATE también funciona

Seminario de Bases de Datos Sumar 5 unidades a la cantidad de la orden 111 en su item 10: UPDATE TABLE(SELECT detalles FROM orden WHERE id_orden=111) anidada SET anidada.cantidad=anidada.cantidad + 5 WHERE anidada.id_producto = 10; El alias es opcional… Borrar el item 10 a la orden 111: DELETE FROM TABLE(SELECT detalles FROM orden WHERE id_orden=111)‏ WHERE id_producto=10;

Seminario de Bases de Datos Selección de columnas de la tabla anidada con columnas de la tabla que la contiene: SELECT id_orden, t2.id_producto FROM orden t, TABLE(t.detalles) t2; Desanidamiento

Seminario de Bases de Datos gobernando a Star #name *age Planet #name *mass orbitando a gobernando a orbitando a Satellite #name *diameter

Seminario de Bases de Datos DROP TYPE satellite_t FORCE; CREATE OR REPLACE TYPE satellite_t AS OBJECT ( name VARCHAR2(20), diameter NUMBER(10)); / DROP TYPE nt_sat_t FORCE; CREATE TYPE nt_sat_t AS TABLE OF satellite_t; /

Seminario de Bases de Datos DROP TYPE planet_t FORCE; CREATE OR REPLACE TYPE planet_t AS OBJECT ( name VARCHAR2(20), mass NUMBER(10), satellites nt_sat_t); / DROP TYPE nt_pl_t FORCE; CREATE TYPE nt_pl_t AS TABLE OF planet_t; /

Seminario de Bases de Datos DROP TABLE star PURGE; CREATE TABLE star( name VARCHAR2(20), age NUMBER(10), planets nt_pl_t)‏ NESTED TABLE planets STORE AS planets_tab (NESTED TABLE satellites STORE AS satellites_tab);

Seminario de Bases de Datos INSERT INTO star VALUES ('Sun',23,nt_pl_t( planet_t('Neptune',10, nt_sat_t( satellite_t('Proteus',67), satellite_t('Triton',82)‏ )‏ ), planet_t('Jupiter',189, nt_sat_t( satellite_t('Callisto',97), satellite_t('Ganymede', 22)‏ )‏ )‏ )‏ ); ¿Qué implicaciones tendría manejar una entidad llamada cuerpo_celeste y manejar subtipos?

Seminario de Bases de Datos SELECT s.name sn, p.name pn,t.name tn FROM star s, TABLE(s.planets) p, TABLE(p.satellites) t; SN PN TN ------- ------------ --------------- Sun Neptune Proteus Sun Neptune Triton Sun Jupiter Callisto Sun Jupiter Ganymede

Características Objeto Relacionales en Oracle 10G*