Download La parte objeto relacional en Oracle 10G

Características Objeto
Relacionales en Oracle 10G*
* Tomado del curso de Francisco Moreno
01/12/07
Seminario de Bases de Datos
1
Colecciones
Las colecciones en Oracle son de 2 Tipos:
 Tablas Anidadas (Nested Tables)
 Varrays (Variable Arrays )
01/12/07
Seminario de Bases de Datos
2
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.
01/12/07
Seminario de Bases de Datos
3


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.)
01/12/07
Seminario de Bases de Datos
4



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
01/12/07
Seminario de Bases de Datos
5
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…
01/12/07
Seminario de Bases de Datos
6
Relacional: 2 tablas y manejo de clave foránea
DETALLE
#id_producto
#Id_orden (cf)
*cantidad
ORDEN
# id_orden
*fecha
Objeto relacional: Primera forma con REFs:
-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
01/12/07
Seminario de Bases de Datos
7
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
01/12/07
Seminario de Bases de Datos
8
Gráficamente:
id_orden
fecha
detalles
id_producto
11
34
78
Julio 13 de 2003
1
100
2
90
Mayo 2 de 2003
Junio 23 de 2002
cantidad
Vacía
id_producto
1
cantidad
150
Tabla de Órdenes
01/12/07
Seminario de Bases de Datos
9
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));
/
01/12/07
Seminario de Bases de Datos
10

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);
/
01/12/07
Seminario de Bases de Datos
11

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?
01/12/07
Seminario de Bases de Datos
12


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…
01/12/07
Seminario de Bases de Datos
13
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))
);
01/12/07
Seminario de Bases de Datos
14
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
01/12/07
Seminario de Bases de Datos
15

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);
01/12/07
Seminario de Bases de Datos
16

Considérese lo siguiente:
DELETE orden;
INSERT INTO orden VALUES(111,SYSDATE,NULL);
Tabla anidada
nula
--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?
01/12/07
Seminario de Bases de Datos
17
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;
01/12/07
Seminario de Bases de Datos
18

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
01/12/07
Seminario de Bases de Datos
19

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;
01/12/07
Seminario de Bases de Datos
20
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
01/12/07
Seminario de Bases de Datos
21
gobernando
a
Star
#name
*age
orbitando
a
Planet
#name
*mass
gobernando
a
orbitando
a
Satellite
#name
*diameter
01/12/07
Seminario de Bases de Datos
22
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;
/
01/12/07
Seminario de Bases de Datos
23
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;
/
01/12/07
Seminario de Bases de Datos
24
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);
01/12/07
Seminario de Bases de Datos
25
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?
01/12/07
Seminario de Bases de Datos
26
SELECT s.name sn, p.name pn,t.name tn
FROM star s,
TABLE(s.planets) p,
TABLE(p.satellites) t;
SN
------Sun
Sun
Sun
Sun
PN
-----------Neptune
Neptune
Jupiter
Jupiter
01/12/07
TN
--------------Proteus
Triton
Callisto
Ganymede
Seminario de Bases de Datos
27