Características Características Objeto-Relacionales Objeto-Relacionales en Oracleen Oracle
Francisco Moreno
Universidad Nacional
TiposCREATE TYPE nom_tipo AS OBJECTAS OBJECT( atributos...,atributos..., métodos...métodos...) [cláusula de instanciabilidad]*
[cláusula de herencia]**;/
* Valor predeterminado: INSTANTIABLE,ver luego.
** Nota: El valor predeterminado es FINAL,ver luego.
Un tipo NO es una tabla: en un tipo no se insertan datos
Ejemplo:
DROP TYPE ubicacion_tip FORCE;
CREATE TYPE ubicacion_tip AS OBJECT(
dir VARCHAR2(20), ciudad VARCHAR2(20), pais VARCHAR2(20));/
Tipo
DROP TABLE area;CREATE TABLE area( id NUMBER(3) PRIMARY KEY, nom VARCHAR2(10), ubicacion ubicacion_tip);
Columna tipada
INSERT INTO area VALUES(29, 'Progr', ubicacion_tip('Cl 20','Med','Col'));
INSERT INTO area VALUES(13, 'Ventas', ubicacion_tip('Cl 20','Med','Col'));
INSERT INTO area VALUES (35, 'Mercadeo', ubicacion_tip('Av 5', 'Mia','USA'));
INSERT INTO area VALUES (99, 'Public', ubicacion_tip('Cl 20','Med','Col'));
INSERT INTO area VALUES (11, 'Prod', ubicacion_tip('Cl 1','Med','Col'));
Constructor
Selección:
SELECT nom, ubicacion FROM area;
SELECT nom, ubicacion.pais FROM area;
¡No funciona!
Se requiere alias para acceder los atributos
de una columna tipada:
SELECT nom, a.ubicacion.pais FROM area a;
SELECT nom, ubicacion FROM area a WHERE a.ubicacion.dir LIKE 'Cl%';
Ejemplos con PL/SQL:
DECLAREu ubicacion_tip;BEGIN SELECT ubicacion INTO u FROM area WHERE id = 29; DBMS_OUTPUT.PUT_LINE(u.pais);END;/
BEGIN FOR mi_e IN (SELECT ubicacion FROM area)LOOP DBMS_OUTPUT.PUT_LINE(mi_e.ubicacion.pais); END LOOP;END;/
Ejemplo: Seleccionar el id de las áreas que tienen la misma ubicación que el área 13 (es decir, que tengan el mismo dir, ciudad y pais)
Primera solución
SELECT id FROM area a1 WHERE a1.ubicacion.pais = (SELECT a2.ubicacion.pais FROM area a2 WHERE id = 13) AND a1.ubicacion.ciudad = (SELECT
a2.ubicacion.ciudad FROM area a2 WHERE id = 13) AND a1.ubicacion.dir = (SELECT a2.ubicacion.dir FROM area a2 WHERE id = 13);
Es un poco largo e incómodo…Sin embargo, se puede mejorar:
Segunda soluciónSELECT id FROM area a1WHERE (a1.ubicacion.pais, a1.ubicacion.ciudad, a1.ubicacion.dir) = (SELECT a2.ubicacion.pais, a2.ubicacion.ciudad, a2.ubicacion.dir FROM area a2
WHERE id = 13 );
Se puede mejorar más:
Tercera solución SELECT id FROM area WHERE ubicacion = (SELECT ubicacion FROM area
WHERE id = 13);
Ejemplo: Imprimir todas las parejas de ids de las áreas que están situadas en la misma ubicación.
Solución:
SELECT a1.id, a2.idFROM area a1, area a2WHERE a1.ubicacion = a2.ubicacionAND a1.id < a2.id;
¿Qué sucede con esta consulta?:
SELECT ubicacion, COUNT(*)FROM areaGROUP BY ubicacion;
Sin embargo, es posible:
SELECT a.ubicacion.pais, a.ubicacion.ciudad, a.ubicacion.dir,COUNT(*)FROM area aGROUP BY a.ubicacion.pais,a.ubicacion.ciudad, a.ubicacion.dir;
Luego se verá como simplificarlo un poco...
Un problema similar sucede con XMLTYPE:
SELECT d, COUNT(*)FROM bodegaGROUP BY d;
Tampoco soporta igualdad
para XMLTYPE:SELECT *FROM bodega WHERE d = d;
DROP TABLE bodega;CREATE TABLE bodega(cod NUMBER(8) PRIMARY KEY,d XMLTYPE);
Tampoco funciona en este caso:CREATE TYPE x_tip AS OBJECT( doc XMLTYPE);/DROP TABLE bodega;CREATE TABLE bodega(d x_tip);
SELECT *FROM bodega WHERE d = d;
Algunas instrucciones útiles:
Instrucciones para que un atributo de una columna tipada:
a) Tenga un índiceb) Sea obligatorioc) Sea o haga parte de la clave primariad) Sea o haga parte de una clave alternativa
a)CREATE INDEX index_ciudad ON
area(ubicacion.ciudad);
b)DROP TABLE area;CREATE TABLE area(id NUMBER(3) PRIMARY KEY, nom VARCHAR2(10), ubicacion ubicacion_tip,
CHECK(ubicacion.pais IS NOT NULL) );
Nota: Confrontar con:
ubicacion ubicacion_tip NOT NULL
c)ALTER TABLE area DROP PRIMARY KEY; ALTER TABLE area ADD PRIMARY KEY(ubicacion.pais);
d)ALTER TABLE area ADD UNIQUE(ubicacion.dir);
* La opción MODIFY no funciona.
Supóngase que no puede haberdos áreas en el mismo país. Si ya hay países repetidos en la tabla área, lainstrucción fallará.
*
También es posible hacerlo en el CREATE sin ALTER:
DROP TABLE area;CREATE TABLE area(id NUMBER(3) UNIQUE, nom VARCHAR2(10), ubicacion ubicacion_tip, PRIMARY KEY(ubicacion.pais), UNIQUE(ubicacion.dir) );
CREATE TABLE empleado( code number(8) PRIMARY KEY, pais VARCHAR2(20));
¿Qué hace la siguiente instrucción?
ALTER TABLE empleado ADDFOREIGN KEY(pais) REFERENCES area(ubicacion.pais);
¿Qué característica debe cumplir el atributo pais en ubicacion en la tabla area?
Sea:
DROP TABLE t_auditoria;CREATE TABLE t_auditoria(id_area NUMBER(3),fecha DATE,ant_loc ubicacion_tip,estacion VARCHAR2(30),usuario VARCHAR2(30));
Analizar el siguiente trigger:
1. Destruir las tablas empleado y area
2. Crear la tabla area (ver diapositiva 4)
3. Insertar las cinco filas de la diapositiva 5
4. Crear el siguiente trigger
CREATE OR REPLACE TRIGGER cambio_locBEFORE UPDATE OF ubicacion ON areaFOR EACH ROWWHEN (OLD.ubicacion.ciudad <>
NEW.ubicacion.ciudad OR OLD.ubicacion.pais <> NEW.ubicacion.pais)BEGIN INSERT INTO t_auditoria VALUES(:NEW.id, SYSDATE, :OLD.ubicacion, SYS_CONTEXT('USERENV','TERMINAL'), SYS_CONTEXT('USERENV','SESSION_USER') );END;/
SYS_CONTEXT es más reciente y tiene más opciones que la función USERENV.
Ver acá.
Ejecutar el siguiente UPDATE:
UPDATE areaSET ubicacion = ubicacion_tip('AV 8','Mia','USA');
Y luego:
UPDATE areaSET ubicacion = ubicacion_tip('AV 3','Mia','USA');