Objeto-relacional Oracle 10g 2e5165

M.I.T.I Ambrosio Cardoso Jiménez Enero 2010

Es una BD que permite que los datos sean grabados como objetos. Sin embargo, todos los metadatos y la información siguen utilizando el sistema de filas y columnas de tal manera que la información pueda ser accedida como en el modelo relacional. Así mismo, cuando los datos son recuperados de la Base de Datos se tiene la capacidad de reconstruir nuevamente los datos simples y transformarlos a objetos complejos

Tipos estructurados de datos. No sólo se permiten atributos atómicos, sino atributos construidos a partir de tipos atómicos y constructores tales como struct, set, bag. Operaciones especiales puedes ser definidas y aplicadas a valores de tipos de datos definidos por el . Identificadores de tuplas. En bases de datos objeto-relacional, las tuplas juegan el rol de objetos. Así pueden necesitar tener un identificador que las distingue de otras tuplas, incluso para aquellas tuplas que tienen el mismo valor en todos sus componentes. Este identificador puede ser invisible al .

Referencias. Permite referenciar o apuntar a otras tuplas.

Extensiones en los tipo de datos Tipos Definidos por el Tipos fila y referencia Tipos colección (set, bag, list y array) Tipos de Objetos Grandes, LOB (LargeObjects) Extensiones en el Control de la Semántica de datos Objeto-Relacionales: Triggers Procedimientos almacenados y funciones definidas por el Extensiones en las capacidades Consultivas Objeto-Relacionales: Consultas recursivas SQL:99 Incorpora al lenguaje XML y viceversa Otras Extensiones en las Bases de Datos Objeto-Relacionales: Extensiones a la ayuda en la toma de Decisiones: OLAP, etc. Intercambio de Objetos (OEM), Datos Multimedia, etc.

Introduce algunas características de XML, cambios en las funciones, estandarización del objeto sequence y de las columnas autonuméricas Multiset (Multiconjunto), Tabla anidada en Oracle 10g

UML

T_Empleado nombres: string direccion:string telefono: string fechaIngreso: date antigüedad(): int

SQL

DEFINE CLASS T_Empleado TYPE TUPLE [ nombres:string, direccion: string, telefono: string, fechaingreso: date] OPERATIONS antigüedad: integer

WNDS: No se permite al método modificar las tablas de la base de datos WNPS: No se permite al método modificar las variables del paquete PL/SQL RNDS: No se permite al método leer las tablas de la base de datos RNPS: No se permite al método leer las variables del paquete PL/SQL

ORACLE 10g

CREATE TYPE T_Empleado AS OBJECT (nombres VARCHAR2 (50), direccion VARCHAR2 (50), telefono VARCHAR2 (20), fechaingreso DATE , MEMBER FUNCTION antigüedad RETURN NUMBER, PRAGMA RESTRICT_REFERENCES (fechaingreso, WNDS));

Un tipo de dato define una estructura y un comportamiento común para un conjunto de datos de las aplicaciones. Los s de Oracle pueden definir sus propios tipos de datos mediante dos categorías: tipos de objetos (object type) y tipos para colecciones (collection type). Para construir los tipos de se utilizan los tipos básicos provistos por el sistema y otros tipos de previamente definidos. Un tipo de objeto representa una entidad del mundo real y se compone de los siguientes elementos: Su nombre que sirve para identificar el tipo de los objetos. Sus atributos que modelan la estructura y los valores de los datos de ese tipo. Cada atributo puede ser de un tipo de datos básico o de un tipo de . Sus métodos (procedimientos o funciones) escritos en lenguaje PL/SQL (almacenados en la BDOR), o escritos en C (almacenados externamente).

CREATE TYPE tipo AS OBJECT ( c1 tipoDato, c2 tipoDato, … cn tipoDato, MEMBER FUNCTION f1 RETURN tipoDato [,PRAGMA RESTRICT_REFERENCES (campoX, FORMA)] );

La implementación del método se hace por separado

La especificación de un método se hace junto a la creación de su tipo, y debe llevar siempre asociada una directiva de compilación (PRAGMA RESTRICT_REFERENCES), para evitar que los métodos manipulen la base de datos o las variables del paquete PL/SQL. Tienen el siguiente significado: WNDS: no se permite al método modificar las tablas de la base de datos WNPS: no se permite al método modificar las variables del paquete PL/SQL RNDS: no se permite al método leer las tablas de la base de datos RNPS: no se permite al método leer las variables del paquete PL/SQL

Los métodos se pueden ejecutar sobre los objetos de su mismo tipo. Si x es una variable PL/SQL que almacena objetos del tipo Empleado, entonces x.antigüedad() calcula la antigüedad del Empleado almacenado en x. La definición del cuerpo de un método en PL/SQL se hace de la siguiente manera:

CREATE OR REPLACE TYPE BODY T_Empleado AS MEMBER FUNCTION antigüedad RETURN NUMBER IS BEGIN RETURN ROUND ( SYSDATE-fechaIngreso ); /* regresa en dias */ END; END;

En Oracle, todos los tipos de objetos tienen asociado por defecto un método que construye nuevos objetos de ese tipo de acuerdo a la especificación del tipo. El nombre del método coincide con el nombre del tipo, y sus parámetros son los atributos del tipo. Vamos a realizar una ligera modifcación a nuestra clase T_Empleado haciendo que direccion sea un TDU un lugar de un VARCHAR

Creamos un objeto con la estructura de una dirección CREATE OR REPLACE TYPE t_Direccion AS OBJECT (calle VARCHAR2 (50), numero NUMBER, colonia VARCHAR2 (50), Estado VARCHAR (50), pais VARCHAR (50));

Modificamos el tipo T_Empleado CREATE OR REPLACE TYPE T_Empleado AS OBJECT (nombres VARCHAR2 (50), direccion t_Direccion, telefono VARCHAR2 (20));

CREATE TABLE Empleado OF T_Empleado; INSERT INTO Empleado VALUES (‘Juan Jimenez’,T_Direccion (‘Benito Juarez’, 121, ‘Las Ámericas’, ‘Oaxaca’,’México’), ‘9514567890’,’[email protected]’);

Una vez definidos los tipos, éstos pueden utilizarse para definir nuevos tipos, tablas que almacenen objetos de esos tipos, o para definir el tipo de los atributos de una tabla. Una tabla de objetos es una clase especial de tabla que almacena un objeto en cada fila y que facilita el a los atributos de esos objetos como si fueran columnas de la tabla. Por ejemplo, se puede definir una tabla para almacenar los Alumnos egresados y otra para almacenar los que están inscritos actualmente en algún semestre de la siguiente manera:

DROP TYPE t_Alumno FORCE; CREATE TYPE t_Alumno AS OBJECT (nocontrol VARCHAR (10), nombre VARCHAR (50), apellidos VARCHAR (50), carrera VARCHAR (50), semestreActual NUMBER ); 1. CREATE TABLE alumnos OF t_Alumno (PRIMARY KEY (nocontrol) ); 2. CREATE TABLE egresados (anioEgreso NUMBER, alumno t_Alumno );

La diferencia entre la primera y la segunda tabla es que la primera almacena objetos con su propia identidad (OID) y la segunda no es una tabla de objetos, sino una tabla con una columna con un tipo de datos de objeto. Es decir, la segunda tabla tiene una columna con un tipo de datos complejo pero sin identidad de objeto. Además de esto, Oracle permite considerar una tabla de objetos desde dos puntos de vista: Como una tabla con una sola columna cuyo tipo es el de un tipo de objetos. Como una tabla que tiene tantas columnas como atributos de los objetos que almacena.

La clave externa del modelo relacional expresa asociaciones uno-avarios con la clave primaria. El modelo de objetos de Oracle proporciona una forma más eficaz para expresar asociaciones uno-avarios. Oracle asigna un identificador único a cada objeto en una fila llamado OID. El OID hace posible que otros objetos referencien objetos filas existentes en la BD. Oracle guarda estas referencias en el tipo de dato llamado REF. Un atributo de tipo REF almacena una referencia a un objeto del tipo definido, e implementa una relación de asociación entre los dos tipos de objetos. Estas referencias se pueden utilizar para acceder a los objetos referenciados y para modificarlos; sin embargo, no es posible operar sobre ellas directamente. Para asignar o actualizar una referencia se debe utilizar siempre REF o NULL.

T_Empleado

Genera

1 nombres: string direccion:string telefono: string fechaIngreso: date

T_Nomina 0..* fechaPago: Date Periodo: string empleado: T_EMPLEADO importe: NUMBER (10,2)

DROP TYPE t_Empleado FORCE; CREATE OR REPLACE TYPE t_Empleado AS OBJECT (nombres VARCHAR2 (50), direccion VARCHAR2 (50), telefono VARCHAR2 (20), fechaIngreso DATE );

DROP TABLE empleados; CREATE TABLE empleados OF t_Empleado; CREATE TYPE t_Nomina AS OBJECT (fechaPago DATE, periodo VARCHAR2 (30), Empleado REF t_Empleado SCOPE IS empleados, importe NUMBER (10,2) ); Para la integridad CREATE TABLE nomina OF t_Nomina ; referencial

INSERT INTO empleados VALUES ('Ambrosio Cardoso','Laureles 121','9515706918','30/08/2009'); INSERT INTO empleados VALUES ('Gisela Vásquez','Buena Vista 102','95189456’,'01/01/2000'); INSERT INTO nomina (fechapago, periodo, Empleado, importe) SELECT '30/11/2009','15/11/2009-30/11/2009', REF (E), 3200 FROM Empleados E WHERE nombres='Ambrosio Cardoso‘; INSERT INTO nomina (fechapago, periodo, Empleado, importe) SELECT '30/12/2008','15/12/2008-30/12/2008', REF (E), 6200 FROM Empleados E WHERE nombres='Gisela Vásquez‘;

SELECT * FROM nomina; fechaPago

Periodo

Empleado

Importe

30/11/09

15/11/200930/11/2009

0000220208FC5E867B31A841D3827BC350C07AC2 8470401219E57C4740B27616E8D7AE5B52

3200

30/12/08

15/12/200830/12/2008

000022020860F0C29AA39D4B0C9F130A46C9E426 B170401219E57C4740B27616E8D7AE5B52

6200

Como se ha comentado anteriormente, en el campo Empleado, se almacena el OID (identificador de objeto) que está en la tabla empleados. Para poder ver el contenido de este campo, ORACLE dispone de una función DEREF esta función toma el OID, evalúa la referencia y devuelve un valor.

SELECT DEREF (n.Empleado), fechaPago, importe FROM nomina n; Empleado

fechaPago

Importe

T_EMPLEADO('Ambrosio Cardoso', 'Laureles 121', '9515706918', '30/08/09')

30/11/09

3200

T_EMPLEADO('Gisela Vásquez', 'Buena Vista 102', '95189456', '01/01/00')

30/12/08

6200

Un array es un conjunto ordenado de elementos del mismo tipo. Cada elemento tiene asociado un índice que indica su posición dentro del array. Oracle permite que los VARRAY sean de longitud variable, aunque es necesario especificar un tamaño máximo cuando se declara el tipo VARRAY. Las siguientes declaraciones crean un tipo para una lista ordenada de habilidades

CREATE TYPE t_Habilidades AS VARRAY (5) OF VARCHAR2 (50) ; Cuando se declara un tipo VARRAY no se reserva espacio. Si el espacio que requiere lo permite, se almacena junto con el resto de columnas de su tabla, pero si es demasiado largo (más de 4000 bytes) se almacena aparte de la tabla como un BLOB.

define TYPE t_Habilidades LIST (string); DEFINE CLASS T_Gente : tuple [nombre:string, apellidos: string, habilidad t_Habilidades];

CREATE TYPE t_Habilidades AS VARRAY (5) OF VARCHAR2(50) ; CREATE TYPE T_Gente AS OBJECT (nombre VARCHAR2 (50), apellidos VARCHAR2 (50), habilidad t_Habilidades ) ; CREATE TABLE gente OF T_Gente; INSERT INTO gente VALUES (‘Rosa Maria’,’Perez’, t_Habilidades (‘Música’,’Ajedrez’,’Futbol’,’Natación’) );

T_Persona nombres: string direccion:string telefono: string curp: string sexo: string

T_Profesor cedula: string nivelAcademico:string especialidad: string sueldo: float

T_Alumno nocontrol: string carrera:string semestre: Int grupo: string

DROP TYPE t_Persona FORCE; CREATE TYPE t_Persona AS OBJECT (nombres VARCHAR2 (50), direccion VARCHAR2 (50), telefono VARCHAR2 (20), curp VARCHAR2 (18), sexo CHAR (1) ) NOT FINAL ; DROP TYPE t_Profesor FORCE; CREATE TYPE t_Profesor UNDER t_Persona ( cedula VARCHAR2 (10), nivelAcademico VARCHAR2 (50), especialidad VARCHAR2 (50), Sueldo NUMBER (10,2)) ; DROP TYPE t_Alumno FORCE; CREATE TYPE t_Alumno UNDER t_Persona ( nocontrol VARCHAR2 (10), carrera VARCHAR2 (50), semestre NUMBER, grupo CHAR (1) );

T_Persona nombres: string direccion:string telefono: string curp: string sexo: string

CREATE TABLE Persona OF t_Persona; INSERT INTO Persona VALUES (‘Ambrosio Cardoso’, ‘Laureles 121’, ’9515706918’,’CAJA751207HOCRMM06’,’M’); INSERT INTO Persona VALUES ( t_Persona(‘Angela Mendez’, ‘Benito Juarez 121’, ’9515123456’,’MERA781209HTCRMM09’,’F’) );

T_Profesor cedula: string nivelAcademico:string especialidad: string sueldo: float

T_Alumno nocontrol: string carrera:string semestre: Int grupo: string

T_Persona NOTA: nombres: string direccion:string telefono: string curp: string sexo: string

T_Profesor cedula: string nivelAcademico:string especialidad: string sueldo: float

En Oracle los subtipos no pueden ser solapados.

T_Alumno nocontrol: string carrera:string semestre: Int grupo: string

INSERT INTO persona VALUES ( T_Profesor (‘Pablo Munguia’, ‘Alamos 11’,’9515123456’, ’MUGP600303HOCRMM06’, ’M’,’99999’,’Maestria’, ’Tec. Informacion’,10000.00 )); INSERT INTO persona VALUES ( T_Alumno (‘Wilches Ramiro’, ‘Emiliano Zapata 66’, ’95151654321’,’AMWL800303HACRXX86’, ’M’,’68456’, ’Maestria en Tec. Informacion’,5,’U’ ));

Primera Forma

SQL> select * from persona; NOMBRES Ambrosio cardoso Angela Mendez Pablo Munguia Wilches Ramiro

DIRECION TELEFONO CURP Laureles 121 9515706918 Benito Juarez 121 9515123456 Alamos 11 9515123456 Emiliano Zapata 66 95151654321

CAJA751207HOCRMM06 MERA781209HTCRMM09 MUGP600303HOCRMM06 AMWL800303HACRXX86

SEXO M F M M

Segunda Forma

SQL> select value (p) from Persona P; VALUE(P)(NOMBRES, DIRECCION, TELEFONO, CURP, SEXO) -------------------------------------------------------------------------------T_PERSONA('Ambrosio cardoso', 'Laureles 121', '9515706918', 'CAJA751207HOCRMM06', 'M') T_PERSONA('Angela Mendez', 'Benito Juarez 121', '9515123456', 'MERA781209HTCRMM09', 'F') T_PROFESOR('Pablo Munguia', 'Alamos 11', '9515123456', 'MUGP600303HOCRMM06', 'M', '99999','Maestria', 'Tec.Informacion', 10000) T_ALUMNO('Wilches Ramiro', 'Emiliano Zapata 66', '95151654321', 'AMWL800303HACRXX86', 'M', '68456', 'Maestria en Tec. Informacion', 5, 'U') La función VALUE sirve para obtener la misma estructura del tipo de objeto de un objeto tabla

SELECT VALUE (p) FROM persona p WHERE VALUE (p) IS OF (ONLY t_Alumno); VALUE(P)(NOMBRES, DIRECCION, TELEFONO, CURP, SEXO) --------------------------------------------------------------------------------

T_ALUMNO('Wilches Ramiro', 'Emiliano Zapata 66', '95151654321', 'AMWL800303HACRXX86', 'M', '68456', 'Maestria en Tec. Informacion', 5, 'U')

1. Insertar 2 objetos de Profesor y 2 para alumnos (tanto Masculino como Femenino <<M,F>>) 2. Mostrar únicamente cedula y nivelAcademico de los profesores 3. Mostrar nombres, nivelAcademico y sueldos de los profesores 4. Mostrar alumnos de sexo Femenino (F)

TREAT Permite descubrir los atributos propios de un tipo Devuelve nulo si el objeto no se puede convertir Tampoco convierte un subtipo a supertipo

2 SELECT TREAT (VALUE (P) AS T_Profesor ).cedula, TREAT (VALUE (P) AS T_Profesor ).nivelAcademico FROM persona p; 3 SELECT nombres, TREAT (VALUE (P) AS T_Profesor).nivelAcademico, TREAT (VALUE (P) AS T_Profesor ).sueldo FROM persona p WHERE VALUE (p) IS OF (ONLY t_Profesor);

4 SELECT VALUE (P) FROM persona p WHERE VALUE (p) IS OF (ONLY t_Alumno) AND sexo='F'; Adicionalmente pruebe estas instrucciones y comente los resultados

SELECT TREAT(VALUE(p) AS T_Profesor) FROM persona p;

UPDATE persona SET nombres='Ambrosio Cardoso Jiménez' WHERE nombres='Ambrosio cardoso';

UPDATE persona P SET VALUE(P) = T_ALUMNO('Wilches Ramiro Amado Miguel', 'Emiliano Zapata 66', '95151654321', 'AMWL800303HACRXX86', 'M', '68456', 'Maestria en Tec. Informacion', 5, ‘U') WHERE TREAT(VALUE(P) AS T_Alumno).nocontrol = '68456';

DELETE FROM persona p WHERE TREAT(VALUE(P) AS t_Alumno).nocontrol ='68456';

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 (típicamente) Incluso en el de otra tabla anidada (tablas anidadas de tablas anidadas, etc.) 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

T_Pedido

UML

idPedido: int fecha: date proveedor : string

T_DetallePedido

1..*

articulo: string cantidad: int precio : double

SQL T_Pedido idPedido: int fecha: date proveedor : string <<multiset>> detalle: {T_DetallePedido}

T_DetallePedido articulo: string cantidad: int precio : double

T_Pedido idPedido: int fecha: date proveedor : string <<multiset>> detalle: {T_DetallePedido}

T_DetallePedido articulo: string cantidad: int precio : double

Oracle 10g CREATE TYPE t_Detallepedidos AS OBJECT (articulo VARCHAR2 (50), cantidad NUMBER, precio NUMBER (10,2));

CREATE OR REPLACE TYPE detalle AS TABLE OF t_Detallepedidos; CREATE TYPE t_Pedidos AS OBJECT (idPedido number (10), fecha DATE, proveedor VARCHAR2 (50), detallepedidos detalle); CREATE TABLE pedidos OF t_Pedidos (PRIMARY KEY (idpedido)) NESTED TABLE detallepedidos STORE AS tbl_DP;

CREATE TYPE t_Pedidos AS OBJECT (idPedido number (10), fecha DATE, proveedor VARCHAR2 (50), detallepedidos detalle); CREATE TABLE pedidos OF t_Pedidos (PRIMARY KEY (idpedido)) NESTED TABLE detallepedidos STORE AS tbl_DP;

detallepedidos es el nombre de la columna y contiene para cada orden su tabla anidada de detalle. tbl_DP es el nombre físico del lugar (tabla) donde se almacenan todas las tablas anidadas de la columna detalle. Esta tabla no se puede accesar directamente, sólo a través de la columna detalle. Directamente es “intocable”, sólo se puede describir

INSERT INTO pedidos VALUES (1,to_date('01/11/2009'),'Proveedora escolar', detalle ( t_detallePedidos ('C# avanzado',1,1234), t_detallePedidos ('Oracle 10g Expert',2,790), t_detallePedidos ('ASP.NET paso apaso',1,99) ) ); INSERT INTO pedidos VALUES (2,to_date('10/11/2009'),'LA balanza', detalle ( t_detallePedidos ('Resistol 850',4,45), t_detallePedidos ('Borrador',1,15) ) );

idPedido

fecha

1

01/11/2009 Proveedora escolar

2

…

proveedor

10/11/2009 La Balanza

…

…

Detalle Articulo

cantidad

precio

C# avanzado

1

1234

Oracle 10 g Expert

2

790

ASP.NET paso a paso

1

999

Articulo

cantidad

precio

Resistol 870

4

45

Borrador

1

15

…

Mostrar todos los pedidos con sus detalles SELECT * FROM pedidos; Mostrar los idpedido con sus detalles SELECT idpedido, detallepedidos from pedidos; ¿Qué pasa si se desea imprimir el idpedido sólo con el artículo de sus detalles?. ¡Inténtelo!

Para agregar más detalles a la orden # 2, se requiere usar el operador THE, para acceder a la tabla anidada así: INSERT INTO THE (SELECT detallepedidos FROM pedidos WHERE idpedido=2) VALUES (‘Sacapuntas eléctrico‘,1,145);

Nota: THE debe recibir como parámetro una sola tabla anidada.

También se puede usar el operador TABLE: INSERT INTO TABLE (SELECT detallepedidos FROM pedidos WHERE idpedido=1) VALUES (‘POO‘,1,845); ¿Diferencias entre THE y TABLE?

Considere lo siguiente: INSERT INTO pedidos VALUES (3,SYSDATE,’Editorial trillas’,NULL); Intentamos agregar los elementos

Tabla anidada nula

INSERT INTO THE (SELECT detallepedidos FROM pedidos WHERE idpedido=3) VALUES(‘Oaxaca en pie de lucha’,1,322); Genera el error: ORA-22908: reference to NULL table value. ¿Entonces cómo llenarla?

Lo que se debe hacer es un update de la siguiente manera: UPDATE pedidos SET detallepedidos = detalle ( t_detallePedidos (‘Oaxaca en pie de lucha’,1,322) ) WHERE idpedido = 3;

Sumar 1 unidad a la cantidad del idpedido 3. UPDATE THE(SELECT detallepedidos FROM pedidos WHERE idpedido = 3) anidada SET anidada.cantidad=anidada.cantidad + 1 WHERE anidada.articulo = ‘Oaxaca en pie de lucha’; Agregar un articulo temporal a idpedido 3 Borrar el articulo temporal de idpedido 3 DELETE FROM THE(SELECT detallepedidos FROM pedidos WHERE idpedido=3) WHERE articulo=‘Temporal’;

Selección de columnas de la tabla anidada con columnas de la tabla que la contiene: SELECT p.idpedido, dp.articulo FROM pedidos p, TABLE(p.detallepedidos) dp;

Curiosamente aquí NO es válido el uso de THE: SELECT p.idpedido, dp.articulo FROM pedidos p, THE (p.detallepedidos) dp;

Diagrama UML para una Aplicación de facturación Francisco Charte Ojeda 2007