Características Objeto Relacionales en Oracle

Características Objeto Relacionales en Oracle Francisco Moreno Universidad Nacional

Métodos Son de dos tipos: • Procedimientos • Funciones • Los procedimientos y funciones se pueden invocar desde otros procedimientos o desde otras funciones. • Las funciones también se pueden invocar desde operaciones DML (pero los procedimientos no).

Sea LIBRO idlibro título cantidad precio_base imp_base género Atributos impuesto() descuento(tiquete) Métodos

Ejemplo: Para determinar el precio final de un libro se considera un impuesto y un descuento . El impuesto se obtiene del precio base del libro así: • (imp_base+ 5%) si hay menos de 100 unidades del libro • (imp_base+ 10%) si hay más de 99 unidades del libro Estos datos se guardan en una tabla así:

CREATE TABLE rangoimp( liminf NUMBER(8) PRIMARY KEY, limsup NUMBER(8) UNIQUE NOT NULL, impto NUMBER(8), CHECK (liminf < limsup) ); INSERT INTO rangoimp VALUES(0,99,5); INSERT INTO rangoimp VALUES(100,1000000,10);

El descuento se obtiene del precio base del libro a partir de un tiquete que el cliente puede presentar, así: • Tiquete tipo 1  descuento del 5% • Tiquete tipo 2  descuento del 7.5% • Tiquete tipo 3  descuento del 10% • Sin tiquete  descuento del 0% Estos datos se guardan en una tabla así:

DROP TABLE dtiq; CREATE TABLE dtiq( cod NUMBER(1) PRIMARY KEY, val NUMBER(3,1) NOT NULL ); INSERT INTO dtiq VALUES(1,5); INSERT INTO dtiq VALUES(2,7.5); INSERT INTO dtiq VALUES(3,10);

CREATE OR REPLACE TYPE libro_tip AS OBJECT( --Atributos idlibro NUMBER(5), titulo VARCHAR2(30), cantidad NUMBER(4), precio_base NUMBER(6), imp_base NUMBER(3), genero VARCHAR2(20), --Métodos: MEMBER FUNCTION impuestoRETURN NUMBER, MEMBER FUNCTION descuento (tiquete IN NUMBER) RETURN NUMBER ); / Notas: Los nombres de los métodos deben ser diferentes a los nombres de los atributos. Se permite sobrecarga de métodos y constructores de usuario, se ven más adelante

CREATE OR REPLACE TYPE BODY libro_tip AS MEMBER FUNCTION impuestoRETURN NUMBER IS i NUMBER(8); BEGIN SELECT impto INTO i FROM rangoimp WHERE cantidad BETWEEN liminf AND limsup; RETURN ((imp_base + i) * precio_base)/100; EXCEPTION WHEN OTHERS THEN RETURN ((imp_base) * precio_base)/100; END impuesto; Nota: El body continúa en la próxima diapositiva ¡Si hay error solo se aplica el impuesto base!

Atributo del objeto MEMBER FUNCTION descuento (tiquete IN NUMBER) RETURN NUMBER IS d NUMBER(8,2); BEGIN SELECT (precio_base * val)/100 INTO d FROM dtiq WHERE cod = tiquete; RETURN d; EXCEPTION WHEN OTHERS THEN RETURN 0; END descuento; END; --Fin del BODY / Columna de dtiq Parámetro

Tabla tipada de libros: CREATE TABLE libro OF libro_tip(idlibro PRIMARY KEY); INSERT INTO libro VALUES (libro_tip(785,'Billy el Mico',200,5000,5,'Novela')); INSERT INTO libro VALUES (libro_tip(795,'Beauty Disrupted',100,50000,5,'Biografía')); Al insertar en una tabla tipada no es necesario invocar al constructor explícitamente: INSERT INTO libro VALUES(523,'Azul',25,10000,10,'Poesía'); INSERT INTO libro VALUES(655,'Versos',70,15000,10,'Poesía'); INSERT INTO libro VALUES(625,'Dragón',700,5000,8,'Novela');

Ahora ya se pueden formular consultas como: Imprimir el código de cada libro, su precio base, su impuesto y su descuento con tiquete 1: SELECT idlibro, titulo, precio_base, l.descuento(1) AS descuento, l.impuesto() AS impuesto FROM libro l;

¿Qué hacen las siguientes consultas? a) SELECT idlibro, titulo, precio_base, (l.precio_base - l.descuento(1) + l.impuesto()) AS precio_final FROM libro l;

b) SELECT idlibro, titulo FROM libro l WHERE (l.precio_base - l.descuento(2) + l.impuesto()) <= 10000;

c) SELECT genero, COUNT(*) AS cuantos, SUM(precio_base) AS total_basico, SUM(precio_base - l.descuento(0) + l.impuesto()) AS total_final FROM libro l GROUP BY genero HAVING COUNT(*) > 1;

Supóngase que el precio final de un libro se requiere constantemente… • Se puede crear una función llamada precio_finalla cual invoca a las otras dos funciones para obtener el valor final del libro

ALTER TYPE libro_tip ADD MEMBER FUNCTION precio_final (tiquete IN NUMBER) RETURN NUMBER CASCADE; Ver otras opciones de modificación de tipos más adelante  Evolución de tipos

Se debe crear de nuevo todo el BODY agregando el siguiente código: MEMBER FUNCTION precio_final(tiquete IN NUMBER) RETURN NUMBER IS BEGIN RETURN (precio_base - descuento(tiquete) + impuesto); END precio_final; Acá no requiere ()

Ahora ya es posible: SELECT titulo, l.precio_final(1) AS pfinal FROM libro l WHERE l.precio_final(1) > 10000; Notas: • Todavía se pueden invocar a las funciones descuento e impuesto y cada una se puede alterar independientemente … • La función precio_finaloculta todo el proceso del descuento y del impuesto

Un ejemplo con recursividad:Árboles Binarios CREATE OR REPLACE TYPE nodo_tipo AS OBJECT( izq REF nodo_tipo, dato NUMBER(5), der REF nodo_tipo, MEMBER FUNCTION inorden RETURN VARCHAR); / CREATE TABLE nodo OF nodo_tipo;

CREATE OR REPLACE TYPE BODY nodo_tipo AS MEMBER FUNCTION inorden RETURN VARCHAR AS in_izq VARCHAR2(100); in_der VARCHAR2(100); BEGIN IF izq IS NOT NULL THEN SELECT n.inorden() INTO in_izq FROM nodo n WHERE REF(n) = SELF.izq; END IF; IF der IS NOT NULL THEN SELECT n.inorden() INTO in_der FROM nodo n WHERE REF(n) = SELF.der; END IF; RETURN in_izq || ' ' || dato || ' ' || in_der; END; END; / Desafortunadamente, lo siguiente es inválido en PL/SQL: in_izq := izq.inorden();o in_izq := SELF.izq.inorden(); Por eso se requiere el SELECT … INTO…que debería ser innecesario…

Sea el árbol: 7 72 73 721 722 731

Primero se insertan las hojas 721, 722 y 731: INSERT INTO nodo VALUES(NULL, 721, NULL); INSERT INTO nodo VALUES(NULL, 722, NULL); INSERT INTO nodo VALUES(NULL, 731, NULL); Ahora las subraíces 72 y 73: INSERT INTO nodo VALUES((SELECT REF(n) FROM nodo n WHERE dato = 721), 72, (SELECT REF(n) FROM nodo n WHERE dato = 722)); INSERT INTO nodo VALUES(NULL, 73, (SELECT REF(n) FROM nodo n WHERE dato = 731));

--Ahora la raíz: INSERT INTO nodo VALUES((SELECT REF(n) FROM nodo n WHERE dato = 72), 7, (SELECT REF(n) FROM nodo n WHERE dato = 73)); Finalmente, la consulta: SELECT n.dato, n.inorden() AS rec_inorden FROM nodo n;

¿Qué pasa si se borra la tupla apuntada por un puntero? • DELETE FROM nodo WHERE dato = 731; • SELECT n.der.dato FROM nodo n WHERE dato = 73; • SELECT dato FROM nodo WHERE der IS DANGLING;

Características Objeto Relacionales en Oracle