Características Objeto Relacionales en Oracle 10G



Descargar 16,92 Kb.
Fecha de conversión24.09.2017
Tamaño16,92 Kb.

Características Objeto Relacionales en Oracle 10G*

Colecciones: VARRAYS (ARRAYs Variables)

  • Los VARRAYs son el equivalente a los ARRAYs de SQL:2003
  • Todos los elementos del VARRAY son del mismo tipo
  • Al igual que las tablas anidadas sus elementos pueden pertenecer a un tipo:
  • - Primitivo
  • - Definido por el usuario
  • - Incluso el de otro VARRAY (VARRAY de
  • VARRAYs etc.)‏

VARRAYS

  • Los VARRAYs pueden verse como tablas anidadas limitadas (soportan un número límite de elementos)‏
  • El total de elementos del VARRAY está determinado por el número de elementos activos (existentes) en el VARRAY (no puede sobrepasar el máximo que se defina)‏

VARRAYS

  • Similarmente a las tablas anidadas, se debe definir el tipo para el VARRAY
  • Ejemplo:
  • DROP TYPE emails FORCE;
  • CREATE TYPE emails AS VARRAY(10) OF
  • VARCHAR2(20);
  • /
  • Ya con este tipo se pueden declarar columnas, parámetros, valores de retorno etc.

VARRAYS

  • Se puede cambiar el tipo de los elementos que componen un VARRAY así*:
  • ALTER TYPE emails MODIFY ELEMENT TYPE VARCHAR2(30)
  • CASCADE;
  • Es posible cambiar el tamaño* de un VARRAY así:
  • ALTER TYPE emails MODIFY LIMIT 15 CASCADE;
  • * Aplican restricciones…(tamaño mayor tanto en el límite como el tipo de datos)‏

VARRAYs

  • Veamos un ejemplo en el cual se va a crear una tabla de departamentos y a cada departamento se le colocará un VARRAY de REFs correspondiente a los empleados que trabajan en dicho dpto.
  • Es decir un ¡VARRAY de punteros!

Ejemplo

  • Modelo Entidad Relación
  • EMPLEADO
  • # cédula
  • * nombre
  • adscrito
  • a
  • conformado
  • por
  • Relacional: 3 Tablas
  • DPTO
  • #código
  • *nombre
  • *conmutador
  • EMPLEADO
  • #cédula
  • *nombre
  • Objeto relacional
  • Primera forma con REFs:
  • -Crear los tipos para dpto, empleado y dptoxemp
  • -Crear las tablas tipadas correspondientes
  • -En el tipo dptoxemp las dos claves foráneas se convierten
  • en REFs.
  • DPTOxEMP
  • #cédula (cf)‏
  • #código (cf)‏

  • Objeto relacional:
  • Segunda forma con VARRAYs
  • Se crea un tipo VARRAY para manejar los punteros a los empleados
  • Se debe colocar un máximo de empleados por departamento
  • ¿Por qué un VARRAY de punteros a empleados en vez de un VARRAY de objetos empleados?

Gráficamente:

  • 3390
  • 2132
  • 2103
  • conmutador
  • Vacío
  • Aseo
  • 38
  • Cont.
  • 20
  • Ventas
  • 11
  • mis_emps
  • nombre
  • código
  • 543
  • BC1
  • AE1
  • FE2
  • DPTO
  • EMP
  • cédula
  • nombre
  • oid
  • 10
  • Ana
  • FE2…
  • 20
  • Beto
  • AE1…
  • 40
  • Rolli
  • 43E…
  • 90
  • Lalo
  • BC1…
  • 80
  • Sara
  • 543…
  • 543
  • 43E
  • Punteros

Implementación

  • Se crea el tipo y la tabla para los empleados:
  • DROP TYPE emp_tip FORCE;
  • CREATE OR REPLACE TYPE emp_tip AS OBJECT(
  • cedula VARCHAR2(8),
  • nombre VARCHAR2(15)‏
  • );
  • /
  • DROP TABLE EMPLEADO;
  • CREATE TABLE empleado OF emp_tip
  • (cedula PRIMARY KEY);
  • INSERT INTO empleado VALUES('10','Ana');
  • INSERT INTO empleado VALUES('20','Beto');
  • INSERT INTO empleado VALUES('40','Rolli');
  • INSERT INTO empleado VALUES('90','Lalo');
  • INSERT INTO empleado VALUES('80','Sara');
  • Ahora se crea un tipo VARRAY de 10 posiciones de apuntadores a empleados:
  • DROP TYPE emp_varray FORCE;
  • CREATE OR REPLACE TYPE emp_varray AS
  • VARRAY(10) OF REF emp_tip;
  • /
  • Se crea la tabla de departamentos: a cada
  • departamento se le coloca su vector de punteros a
  • empleados:
  • DROP TABLE dep;
  • CREATE TABLE dep(
  • codigo NUMBER(3) PRIMARY KEY,
  • nombre VARCHAR2(10),
  • conmutador NUMBER(10),
  • mis_emps emp_varray
  • );
  • Por supuesto dep podría ser una tabla tipada…

Inserción

  • INSERT INTO dep VALUES
  • (11,'Ventas', 2103,
  • emp_varray( (SELECT REF(e) FROM empleado e
  • WHERE cedula ='10'),
  • (SELECT REF(e) FROM empleado e
  • WHERE cedula ='20'),
  • (SELECT REF(e) FROM empleado e
  • WHERE cedula ='90'),
  • (SELECT REF(e) FROM empleado e
  • WHERE cedula ='80')‏
  • )‏
  • );
  • INSERT INTO dep VALUES
  • (20, 'Cont', 2132,
  • emp_varray( ( SELECT REF(e) FROM empleado e
  • WHERE cedula='40'),
  • ( SELECT REF(e) FROM empleado e
  • WHERE cedula='80')‏
  • )‏
  • );
  • INSERT INTO dep VALUES
  • (38, 'Aseo', 3390,
  • emp_varray()
  • );
  • Vacío, también puede ser NULL…
  • (aunque no son equivalentes)

Selección

  • La siguiente consulta imprime cada dpto con el REF de sus empleados:
  • SELECT * FROM dep;
  • Si se desea imprimir cada dpto junto con la información de cada uno de sus empleados se debe proceder así:
  • 1. Observar el resultado de:
  • SELECT nombre, e.*
  • FROM dep, TABLE(mis_emps) e;
  • ¿De dónde surge la columna: COLUMN_VALUE?
  • Nota: Se acude al operador TABLE para producir el
  • desanidamiento de la colección al igual que en las tablas
  • anidadas
  • 2. Por lo tanto es posible:
  • SELECT nombre, e.COLUMN_VALUE
  • FROM dep, TABLE(mis_emps) e;
  • 3. Ahora se aplica el dereferenciamiento:
  • SELECT nombre, DEREF(e.COLUMN_VALUE)‏
  • from dep, TABLE(mis_emps) e;
  • Nota: El alias ”e” es opcional en este caso.
  • Ahora supóngase que se desea sólo un
  • atributo del empleado:
  • SELECT nombre,
  • e.COLUMN_VALUE.cedula AS ced
  • FROM dep, TABLE(mis_emps) e;
  • En este caso el alias “e“ es indispensable…
  • No existe soporte en el SQL de Oracle para
  • inserción, actualización o borrado de los
  • elementos de un VARRAY.
  • Entonces ¿Cómo realizar estas acciones?
  •  Se debe acudir a PL/SQL.
  • Veamos un ejemplo:
  • Inserción de un empleado a un departamento.
  • Se creará un procedimiento para realizarlo.
  • CREATE OR REPLACE PROCEDURE
  • insemp(midep IN NUMBER, miemp IN VARCHAR) AS
  • varr emp_varray; /* Se declara un VARRAY de
  • punteros a empleados */
  • refmiemp REF emp_tip; -- Puntero a un empleado
  • BEGIN
  • --Se obtiene el vector de empleados del dpto
  • --enviado:
  • SELECT mis_emps INTO varr
  • FROM dep c
  • WHERE c.codigo = midep;
  • Continúa…
  • Cédula del empleado
  • --Se comprueba que no se está en el límite del
  • --vector
  • IF varr.COUNT = 10 THEN
  • RAISE_APPLICATION_ERROR(-20564, 'No hay sitio para otro emp. en el dpto '||midep);
  • END IF;
  • --Se obtiene el REF del empleado enviado
  • SELECT REF(e) INTO refmiemp
  • FROM empleado e
  • WHERE cedula = miemp;
  • Continúa…
  • --Comprobar que el emp. no esté repetido
  • FOR contador IN 1..varr.COUNT LOOP
  • IF varr(contador) = refmiemp THEN
  • RAISE_APPLICATION_ERROR(-20764, 'El emp '|| miemp|| ' ya trabaja en el dpto ' ||midep);
  • END IF;
  • END LOOP;
  • Continúa…
  • Nótese como se accesa un
  • elemento del VARRAY.
  • --Prepara el VARRAY para un nuevo elemento
  • varr.EXTEND;
  • varr(varr.COUNT) := refmiemp; --Asigna el elemento
  • --¡Actualizar en la base de datos!
  • UPDATE dep c SET mis_emps= varr
  • WHERE c.codigo = midep;
  • EXCEPTION
  • WHEN NO_DATA_FOUND THEN
  • RAISE_APPLICATION_ERROR(-20111,'Dato no existe');
  • END;
  • /
  • Los atributos EXTEND, COUNT, DELETE y otros
  • funcionan también con tablas anidadas en
  • PL/SQL.
  • En PL/SQL una tabla anidada se puede manipular igual que un VARRAY.
  • Invocación del procedimiento:
  • EXECUTE insemp('38','10');
  • Ejercicio:
  • Realizar un procedimiento para eliminar un
  • empleado de un dpto.
  • Aseo
  • Ana
  • Considere el siguiente caso:
  • En una institución se pueden formar comités
  • Cada comité tiene un nombre, una descripción y máximo 5 miembros
  • Cada miembro puede ser un profesor, un estudiante o un empleado (no docente)‏
  • Sin embargo todo comité debe tener un director y ese director tiene que ser un profesor (el director es un miembro aparte de los otros 5 miembros)‏
  • --Se crea el tipo persona abstracto y no final:
  • DROP TYPE person_type FORCE;
  • CREATE TYPE person_type AS OBJECT(
  • ced NUMBER(8), name VARCHAR2(10)‏
  • )NOT INSTANTIABLE NOT FINAL;
  • /
  • --Se crean los subtipos:
  • DROP TYPE teacher_type FORCE;
  • CREATE TYPE teacher_type UNDER person_type( registro NUMBER(5));
  • /
  • DROP TYPE student_type FORCE;
  • CREATE TYPE student_type UNDER person_type( nro_creditos NUMBER(3));
  • /
  • DROP TYPE emp_type FORCE;
  • CREATE TYPE emp_type UNDER person_type( salario NUMBER(5));
  • /
  • --Se crean las tablas tipadas:
  • DROP TABLE profesor;
  • CREATE TABLE profesor OF teacher_type;
  • INSERT INTO profesor VALUES(15,'Lisa',10);
  • INSERT INTO profesor VALUES (23,'Paddy',20);
  • INSERT INTO profesor VALUES (39,'George',30);
  • DROP TABLE estudiante;
  • CREATE TABLE estudiante OF student_type;
  • INSERT INTO estudiante VALUES (42,'Belinda',4);
  • INSERT INTO estudiante VALUES (55,'Mariah',5);
  • INSERT INTO estudiante VALUES (69,'Cathy',6);
  • DROP TABLE empleado;
  • CREATE TABLE empleado OF emp_type;
  • INSERT INTO empleado VALUES (76,'Dido',700);
  • INSERT INTO empleado VALUES (83,'Kylie',800);
  • INSERT INTO empleado VALUES (91,'Kirsty',900);
  • --Se crea el tipo VARRAY de punteros a miembros:
  • DROP TYPE per_var FORCE;
  • CREATE TYPE per_var AS VARRAY(5) OF REF person_type;
  • /
  • DROP TABLE comite;
  • CREATE TABLE comite(
  • nomcomite VARCHAR2(10),
  • miembros per_var,
  • director REF teacher_type NOT NULL
  • );
  • INSERT INTO comite VALUES('Juego',
  • per_var( (SELECT REF(e) FROM estudiante e WHERE ced=42),
  • (SELECT REF(p) FROM profesor p WHERE ced=15),
  • (SELECT REF(e) FROM empleado e WHERE ced=76)‏
  • ),
  • (SELECT REF(p) FROM profesor p WHERE ced=39)‏
  • );
  • SELECT nomcomite,
  • DEREF(COLUMN_VALUE)‏
  • FROM comite c, TABLE(c.miembros);
  • SELECT nomcomite, DEREF(director),
  • DEREF(COLUMN_VALUE)‏
  • FROM comite c, TABLE(c.miembros);
  • INSERT INTO comite VALUES('Aseo',
  • per_var( (SELECT REF(e) FROM estudiante e WHERE ced=42),
  • (SELECT REF(p) FROM profesor p WHERE ced=15),
  • (SELECT REF(e) FROM empleado e WHERE ced=76)‏
  • ),
  • (SELECT REF(p) FROM estudiante p WHERE ced=55)‏
  • );
  • Se rechaza porque
  • ¡los punteros también tiene tipo!
  • Ejercicio: Realizar un trigger que controle que todos los miembros de un
  • comité sean todos del mismo tipo, es decir todos estudiantes o todos
  • empleados etc. El trigger debe validar además que el director no sea
  • uno de los miembros del VARRAY.

Tablas anidadas VS. VARRAYs

  • Almacenadas out of line
  • Se pueden indexar
  • No limitadas en número de elementos
  • Soporte para INSERT, DELETE, UPDATE directos
  • Apropiada para grandes conjuntos de elementos
  • Almacenados in-line (si < 4k)‏
  • No soportan índices
  • Límite en el número de elementos  ampliarlo es “incómodo”
  • Requiere PL/SQL para insertar, borrar y actualizar
  • Útil para conjuntos “pequeños” y que posean un límite conocido y estable  su tiempo de respuesta es muy bueno
  • Tablas Anidadas
  • VARRAYS


La base de datos está protegida por derechos de autor ©absta.info 2016
enviar mensaje

    Página principal