Caractersticas Objeto Relacionales en Oracle 10G

1
Características Objeto Relacionales en Oracle 10G
Tomado del curso de Francisco Moreno
2
ColeccionesVARRAYS (ARRAYs Variables)

• Los VARRAYs son el equivalente a los ARRAYs de
  SQL2003
• 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.)?

3
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)?

4
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.

5
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)?

6
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!

7
Ejemplo
Modelo Entidad Relación
conformado por
DPTO código nombre conmutador
EMPLEADO cédula nombre
adscrito a
8
Relacional 3 Tablas
DPTOxEMP cédula (cf)? código (cf)?
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.
9

• 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?

10
Gráficamente
DPTO
EMP
cédula
nombre
oid
10
Ana
FE2
Punteros
20
Beto
AE1
40
Rolli
43E
90
Lalo
BC1
80
Sara
543
11
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)

12

• 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
• /

13

• 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

14
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')?
• )?
• )

15

• 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)
16
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í

17

• 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

18

• 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.

19

• 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

20

• 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.

21

• 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

Cédula del empleado
Continúa
22

• --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
23

• --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

Nótese como se accesa un elemento del VARRAY.
Continúa
24

• --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
• /

25

• 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.

Ana
Aseo
26

• 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)?

27

• --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))
• /

28

• --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)

29

• --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
• )

30

• INSERT INTO comite VALUES('Juego',
• per_var( (SELECT REF(e) FROM estudiante e
  WHERE ced42),
• (SELECT REF(p) FROM profesor p WHERE
  ced15),
• (SELECT REF(e) FROM empleado e
  WHERE ced76)?
• ),
• (SELECT REF(p) FROM profesor p
  WHERE ced39)?
• )
• SELECT nomcomite,
• DEREF(COLUMN_VALUE)?
• FROM comite c, TABLE(c.miembros)
• SELECT nomcomite, DEREF(director),
• DEREF(COLUMN_VALUE)?

31

• INSERT INTO comite VALUES('Aseo',
• per_var( (SELECT REF(e) FROM estudiante e
  WHERE ced42),
• (SELECT REF(p) FROM profesor p WHERE
  ced15),
• (SELECT REF(e) FROM empleado e
  WHERE ced76)?
• ),
• (SELECT REF(p) FROM estudiante p
  WHERE ced55)?
• )

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.
32
Tablas anidadas VS. VARRAYs
VARRAYS
Tablas Anidadas

• 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 lt 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