Post on 13-Apr-2018
7/25/2019 Objeto Relacional
1/27
1
SOPORTE OBJETO-RELACIONAL EN ORACLE 8
Modelos Avanzados de Bases de
Datos
Los tipos de datos compuestos enOracle 8
REGISTRO
TABLA PL/SQL TABLA ANIDADA
VARRAY
7/25/2019 Objeto Relacional
2/27
2
Registros PL/SQL
Grupo de datos relacionados
Los campos pueden ser inicializados ypueden ser definidos como NOT NULL
Los campos no inicializados explcitamente,se inicializarn automticamente a NULL
Pueden estar anidados
Registros PL/SQL
Declaracin del tipo:
TYPE nombre_tipo_reg IS RECORD
(declaracin_campo [,declaracin_campo]...);
Donde declaracin_campo tiene la forma:
nombre_campo
{tipo_campo | variable%TYPE |table.columns%TYPE | table%ROWTYPE }
[ [NOT NULL] {:= | DEFAULT} expresin]
7/25/2019 Objeto Relacional
3/27
3
Registros PL/SQL
Declaracin de una variable del tipo registro:
id_variable nombre_tipo_reg;
Acceso a un campo de un registro:
id_variable.nombre_campo
Asignacin de valores:
Directamente a un campo: Id.campo := 3;
Puede asignarse valor a todos los campos de unregistro utilizando una sentencia SELECT.
Puede asignarse un registro a otro siempre y cuandosean del mismo tipo
Registros PL/SQL
Declaracin de registros con el atributo %ROWTYPE
CREATE TABLE empleado(
id number,
nombre char(10),apellido char(20),
direccin char(30));
Puedo declarar una variable de tipo registro como:
DECLARE
reg_emp empleado%ROWTYPE;
7/25/2019 Objeto Relacional
4/27
4
Tablas PL/SQL
Es similar a un array
Tiene dos componentes: clave primaria(BINARY_INTEGER) que hace de ndice yuna columna de valores
Puede aumentar su tamao de formadinmica
Tablas PL/SQL
Declaracin del tipo
TYPE nombre_tipo_tabla IS TABLE OF{tipo_columna|varible%TYPE!tabla.columna%TYPE
}[NOT NULL]INDEX BY BINARY_INTEGER ;
Declaracin de una variable del tipo
nombre_var nombre_tipo_tabla;
No es posible inicializar las tablas en la inicializacin.
7/25/2019 Objeto Relacional
5/27
5
Tablas PL/SQL
Referenciar un elemento de la tabla
Sintaxis:
Pl/sql_nombre_table(valor_primary_key);
El rango de binary integer es 2147483647.. 2147483647,por lo tanto el ndice puede ser negativo, lo cual indica queel ndice del primer valor no tiene que ser necesariamenteel uno.
Tablas de registros
Es posible declarar elementos de una tablaPL/SQL como de tipo registro.
Para referenciar un elemento se har de lasiguiente forma:
nombre_tabla(ndice).nombre_campo
7/25/2019 Objeto Relacional
6/27
6
Funciones para el manejo detablas PL/SQL Funcin EXISTS(i). Utilizada para saber si en un cierto
ndice hay almacenado un valor. Devolver TRUE si en elndice i hay un valor.
Funcin COUNT. Devuelve el nmero de elementos de latabla PL/SQL.
Funcin FIRST. Devuelve el menor ndice de la tabla.NULL si est vaca.
Funcin LAST. Devuelve el mayor ndice de la tabla.NULL si est vaca.
Funciones para el manejo detablas PL/SQL
Funcin PRIOR (n). Devuelve el nmero del ndiceanterior a n en la tabla.
Funcin NEXT (n). Devuelve el nmero del ndice
posterior a n en la tabla. Funcin TRIM. Borra un elemento del final de la tabla
PL/SQL. TRIM(n) borra n elementos del final de la tablaPL/SQL.
Funcin DELETE. Borra todos los elementos de la tablaPL/SQL. DELETE(n) borra el correspondiente al ndice n.DELETE(m,n) borra los elementos entre m y n.
7/25/2019 Objeto Relacional
7/27
7
COLECCIONES
Un tipo coleccin es aquel que manejavarias variables como una unidad
La versin 8 de Oracle PL/SQL aade dostipos coleccin nuevos: las tablas anidadasy los varrays
TABLAS ANIDADAS Las tablas anidadas son muy parecidas a las
tablas PL/SQL (tambin llamadasindexadas).
Las tablas anidadas aaden funcionalidad alas indexadas al aadir mtodos decoleccin adicionales y la capacidad dealmacenar tablas anidadas en una tabla de labase de datos.
Tambin pueden manejarse directamenteutilizando rdenes SQL.
7/25/2019 Objeto Relacional
8/27
8
TABLAS ANIDADAS
Sintaxis:TYPE nombre_tabla IS TABLE OF tipo_tabla[NOT NULL];
La nica diferencia con la declaracin de
una tabla indexada es que ahora no aparecela clusula INDEX BYBINARY_INTEGER.
INICIALIZACIN DENested Tables
Para inicializar una tabla anidada hay queutilizar el constructor
Este constructor tendr el mismo nombreque la tabla anidada.
A continuacin podrn ponerse los valorescon los que queramos inicializar o nada (encuyo caso se inicializa a una tabla sinelementos, que es diferente que una tabla aNULL).
7/25/2019 Objeto Relacional
9/27
9
INICIALIZACIN DENested TablesDECLARE
TYPE tabla_numero IS TABLE OF NUMBER;
var_tabla_1 tabla_numero := tabla_numero(0);
var_tabla_2 tabla_numero := tabla_numero(1, 2, 3, 4);
var_tabla_3 tabla_numero := tabla_numero();
var_tabla_4 tabla_numero;
BEGIN
var_tabla_1(1):= 123;DBMS_OUTPUT.PUT_LINE(Valor 1 de var_1: || var_tabla_1(1));DBMS_OUTPUT.PUT_LINE(Valor 1 de var_2: || var_tabla_2(1));
INICIALIZACIN DENested Tables
IF var_tabla_3 IS NULL THENDBMS_OUTPUT.PUT_LINE(var_tabla_3 SI es NULL);
ELSEDBMS_OUTPUT.PUT_LINE(var_tabla_3 NO es NULL);
END IF;IF var_tabla_4 IS NULL THEN
DBMS_OUTPUT.PUT_LINE(var_tabla_4 SI es NULL);ELSE
DBMS_OUTPUT.PUT_LINE(var_tabla_4 NO es NULL);END IF;END;La salida ser:
Valor 1 de var_1: 123
Valor 1 de var_2: 1
var_tabla_3 NO es NULL
var_tabla_4 SI es NULL
7/25/2019 Objeto Relacional
10/27
10
CLAVES EN LAINICIALIZACIN Cuando se inicializa una tabla utilizando el
constructor, los elementos de la tabla senumeran secuencialmente desde 1 hasta elnmero de elementos especificado en lallamada al constructor
En caso de borrado en una tabla anidada dela base de datos, las claves se renumeranpara seguir siendo secuenciales
CLAVES EN LAINICIALIZACIN
DECLARE
TYPE tabla_numero IS TABLE OF NUMBER;
var_tabla_2 tabla_numero := tabla_numero(10, 20, 30, 40);
BEGINDBMS_OUTPUT.PUT_LINE(Nro elem de var_tabla_2: ||var_tabla_2.count);DBMS_OUTPUT.PUT_LINE(Primer elem: || var_tabla_2.first);
DBMS_OUTPUT.PUT_LINE(ltimo elem: || var_tabla_2.last);
END;
La salida ser:
Nro elem de var_tabla_2: 4
Primer elem: 1
ltimo elem: 4
7/25/2019 Objeto Relacional
11/27
11
ADICIN DE ELEMENTOS
Aunque las tablas no tienen un tamao fijo,no se puede asignar un valor a un elementoque todava no existe. Para poder aadirelementos hay que utilizar el mtodoEXTEND
ADICIN DE ELEMENTOS
DECLARE
TYPE tabla_numero IS TABLE OF NUMBER;
var_tabla_2 tabla_numero := tabla_numero(10, 20, 30, 40);
BEGINvar_tabla_2 (1) := 50;
DBMS_OUTPUT.PUT_LINE(Primer elemento de var_tabla_2: ||var_tabla_2(1) );
var_tabla_2 (5) := 60;
DBMS_OUTPUT.PUT_LINE(Quinto elemento de var_tabla_2: ||var_tabla_2(5));
END;
La primera asignacin es correcta pero la segunda dar un error
7/25/2019 Objeto Relacional
12/27
12
TABLAS ANIDADAS EN LABASE DE DATOS Una tabla anidada se puede almacenar como
una columna de una tabla. Para ello hay quedefinir la tabla anidada con la ordenCREATE TYPE para crear el tipo de latabla anidada en lugar de TYPE.
TABLAS ANIDADAS EN LABASE DE DATOS
EjemploCREATE TYPE inf_libro AS OBJECT(
titulo varchar2(40),
nombre_autor varchar2(40),isbn number);
CREATE TYPE isbn_libros AS TABLE OF inf_libro;
CREATE TABLE prestamo (
fecha_entrega date,
nro_socio number(10),
libros_prestados isbn_libros)
NESTED TABLE libros_prestados STORE AS prestados_tabla;
7/25/2019 Objeto Relacional
13/27
13
MANIPULACIN DE TABLASCOMPLETASInsercin
Para insertar una fila en una tabla anidadase utiliza la orden INSERT. Hay que teneren cuenta que la tabla se crea e inicializa enPL/SQL y despus se inserta en la base de
datos.
MANIPULACIN DE TABLASCOMPLETAS
Ejemplo insercin.DECLARE
libros isbn_libros := isbn_libros(inf_libro(La ruta no natural, Macario
Polo, 1234567));BEGIN
INSERT INTO prestamo VALUES (sysdate, 12,
isbn_libros( inf_libro(Mtricas para bases de datos, Coral Calero,234567),
inf_libro(La amigdalitis de Tarzn, Alfredo Bryce,3456)));
INSERT INTO prestamo VALUES (sysdate, 24, libros);
END;
/
7/25/2019 Objeto Relacional
14/27
14
MANIPULACIN DE TABLASCOMPLETAS
Modificacin
De forma similar, se utiliza UPDATE paramodificar la tabla almacenada
Ejemplo modificacinDECLARE
libros isbn_libros := isbn_libros(inf_libro(La ruta no natural, Macario Polo,
1234567), inf_libro(La amigdalitisTarz, Alfredo Bryce, 3456));BEGIN
UPDATE prestamo
SET libros_prestados = libros
WHERE nro_socio = 24;
END;
MANIPULACIN DE TABLASCOMPLETAS
Eliminacin
DELETE puede eliminar una fila que
contenga una tabla anidada.Ejemplo eliminacin
BEGIN
DELETE FROM prestamo WHEREnro_socio = 24;
END;
7/25/2019 Objeto Relacional
15/27
15
MANIPULACIN DE TABLASCOMPLETASSeleccin
Cuando se recupera una tabla anidada enuna variable PL/SQL, se asignan clavescomenzando por 1 hasta llegar al nmero deelementos que contiene la tabla. Este valor
puede determinarse mediante el mtodoCOUNT, el cual se describe ms adelante.Se trata de las mismas claves establecidaspor el constructor.
MANIPULACIN DE TABLASCOMPLETAS
Ejemplo seleccin
Set serveroutput on;
DECLARE
libros isbn_libros;
i NUMBER;
BEGINSELECT libros_prestados INTO libros FROM prestamo WHEREnro_socio=12;
FOR i IN 1 .. libros.count LOOP
DBMS_OUTPUT.PUT_LINE(Ttulo: || libros(i).titulo|| del elemento: || i);
END LOOP;
END;
Salida:
Ttulo: Mtricas para bases de datos del elemento: 1
Ttulo: La amigdalitis de Tarzn del elemento: 2
7/25/2019 Objeto Relacional
16/27
16
MANIPULACIN DE FILASINDIVIDUALES
Oracle 8 proporciona un operador para manipularuna tabla anidada mientras se almacena en unatabla en lugar de manipularla en PL/SQL.
Operador THE. Toma como argumento unasubconsulta y devuelve una tabla anidada. Lasubconsulta debe devolver una nica fila de la
tabla anidada.
UPDATE THE (SELECT libros_prestados FROM prestamo WHEREnro_socio=12)
SET isbn=isbn + 10;
VARRAYS Un varray se manipula de forma muy similar a las
tablas indexadas o anidadas pero se implementa deforma diferente. Los elementos en el varray sealmacenan comenzando en el ndice 1 hasta la
longitud mxima declarada en el tipo varray. El tipo_elementos puede especificarse utilizando
%TYPE. Sin embargo, no puede ser BOOLEAN,NCHAR, NCLOB, NVARCHAR(2), REFCURSOR, TABLE u otro VARRAY.
TYPE nombre_tipo IS VARRAY (tamao_maximo) OF tipo_elementos;
7/25/2019 Objeto Relacional
17/27
17
INICIALIZACINDe forma similar a las tablas, los VARRAY se
inicializan utilizando un constructor.Ejemplo
DECLARE
TYPE tipo_numeros IS VARRAY(20) OF NUMBER(3);
nros_uno tipo_numeros;
nros_dos tipo_numeros := tipo_numeros(1,2);
nros_tres tipo_numeros := tipo_numeros(NULL);
INICIALIZACINBEGIN
IF nros_uno IS NULL THEN DBMS_OUTPUT.PUT_LINE( nros_uno es NULL);
END IF;
IF nros_tres IS NULL THEN
DBMS_OUTPUT.PUT_LINE( nros_tres es NULL);
END IF;IF nros_tres(1) IS NULL THEN
DBMS_OUTPUT.PUT_LINE( nros_tres(1) es NULL);
END IF;
END;
Salida:
nros_uno es NULL
nros_tres(1) es NULL
7/25/2019 Objeto Relacional
18/27
18
MANIPULACIN DEELEMENTOS Como las tablas anidadas, el tamao inicial
de un VARRAY se establece mediante elnmero de elementos utilizados en elconstructor usado para declararlo. Si sehacen asignaciones a elementos que queden
fuera del rango se producir un error.
MANIPULACIN DEELEMENTOS
DECLARE
TYPE t_cadena IS VARRAY(5) OF
VARCHAR2(10);v_lista t_cadena:= (Paco, Pepe, Luis);
BEGIN
v_lista(2) := Lola;
v_lista(4) := Est mal;
END;
/
7/25/2019 Objeto Relacional
19/27
19
VARRAYS EN LA BASE DEDATOS
Los VARRAYS pueden almacenarse en lascolumnas de la base de datos. Sin embargo, unvarray slo puede manipularse en su integridad, nopudiendo modificarse sus elementos individualesde un varray almacenado (como se haca en lastablas anidadas con el operador THE).
EjemploCREATE OR REPLACE TYPE lista_libros AS VARRAY(10) OF
inf_libro;
CREATE TABLE ejemplo(
id number,
libros inf_libro);
VARRAYS Y TABLASANIDADAS
Aspectos similares: Ambos tipos permiten el acceso a elementos
individuales utilizando la notacin consubndices
Ambos tipos pueden almacenarse en la base dedatos
7/25/2019 Objeto Relacional
20/27
20
VARRAYS Y TABLASANIDADAS Diferencias:
Los varrays tienen un tamao mximo y las tablas anidadas no
Los varrays se almacenan junto con la tabla que los contienemientras que las tablas anidadas se almacenan en una tablaseparada, que puede tener diferentes caractersticas dealmacenamiento
Cuando estn almacenados en la base de datos, los varrays
mantienen el orden y los valores de los subndices para loselementos, mientras que las tablas anidadas no.
Los elementos individuales se pueden borrar de una tabla anidadausando el mtodo TRIM por lo que el tamao de la tabladisminuye. Un varray siempre tiene un tamao constante.
MTODOS DE COLECCIONES
EXISTS(n). Se usa para averiguar si enrealidad existe el elemento referenciado.
Donde n es una expresin entera. Si elelemento existe (incluso aunque sea NULL)devuelve TRUE.
COUNT. Devuelve un nmero enterocorrespondiente al nmero de elementosque tiene actualmente una coleccin.
7/25/2019 Objeto Relacional
21/27
21
MTODOS DE COLECCIONES LIMIT. Devuelve el nmero mximo
actual de elementos de una coleccin.Siempre devuelve NULL cuando se aplica auna tabla anidada (ya que no tienen tamaomximo). Para los VARRAY devolver elmximo valor definido en la declaracin.
FIRST y LAST. FIRST devuelve el ndicedel primer elemento de la coleccin y LASTel ltimo. En un VARRAY, FIRST siempredevuelve 1 y LAST siempre devuelve elvalor de COUNT.
MTODOS DE COLECCIONES
NEXT y PRIOR. Se utilizan paraincrementar o decrementar la clave de unacoleccin.
NEXT(n) Clave del elementoinmediatamente posterior a n.
PRIOR(n) Clave del elementoinmediatamente anterior a n.
Si no existe (el posterior o anterior)devuelve NULL.
7/25/2019 Objeto Relacional
22/27
22
MTODOS DE COLECCIONES EXTEND. Se usa para aadir elementos al
final de una tabla anidada. Tiene tresformatos: EXTEND Aade un elemento NULL con
ndice LAST +1 al final de la tabla
EXTEND(n) Aade n elementos con valorNULL al final de la tabla.
EXTEND(n,i) Aade n copias del elemento ial final de la tabla.
Si la tabla se ha creado con restriccionesNOT NULL slo el ltimo formato.
MTODOS DE COLECCIONES
DELETE. Elimina uno o ms elementos deuna tabla anidada. Tiene tres formatos: DELETE Elimina la tabla completa
DELETE(n) Elimina el elemento con elndice n
DELETE(m,n) Elimina todos los elementosentre m y n.
Si un elemento que se va a borrar no existe,DELETE no da error y lo salta
7/25/2019 Objeto Relacional
23/27
23
MTODOS DE COLECCIONES TRIM. Elimina elementos del final de una
tabla anidada. TRIM no tiene efecto cuandose usa sobre un varray.Tiene dos formatos: TRIM Elimina el elemento del final de la
coleccin
TRIM (n) Elimina n elementos.
Si n>COUNT se genera una excepcin. TRIMopera sobre el tamao interno de la coleccin(incluyendo cualquier elemento eliminado con unDELETE)
MTODOS DE COLECCIONES
...
Numeros NumTab := NumTab(-3, -2, -1, 0, 1, 2, 3);
BEGIN
Numeros.DELETE(6);
Numeros.TRIM(3);
END;Elementos
Posicin 1: -3Posicin 2: -2Posicin 3: -1Posicin 4: 0
Elementos
Posicin 1: -3Posicin 2: -2Posicin 3: -1
Posicin 4: 0Posicin 5: 1Posicin 6:
Posicin 7: 3
7/25/2019 Objeto Relacional
24/27
24
Paquetes y resolucin delproblema de las tablas mutantes
Una tabla es mutante slo para losdisparadores a nivel de fila
No se puede usar, sin ms, un disparador anivel de orden, porque, por lo general,
necesitamos acceder a valores que han sidomodificados (de ah el problema de las tablasmutantes)
La solucin consiste en crear dosdisparadores, uno a nivel de fila y otro a nivelde orden
Paquetes y tablas mutantes
7/25/2019 Objeto Relacional
25/27
25
En el disparador con nivel de fila almacenamos(en una estructura de datos apropiada) los datosque queremos consultar (los que provocan elerror de tabla mutante)
En el disparador con nivel de orden realizamos laconsulta (pero sobre los datos almacenados enlugar de sobre la tabla)
La mejor forma de almacenar los valores es enuna tabla PL/SQL y aunar todas las operacionesdescritas dentro de un paquete
Paquetes y tablas mutantes
CREATE OR REPLACE TRIGGER limite_especialidad(
BEFORE INSERT OR UPDATE OF especialidad ON estudiantes
FOR EACH ROW
DECLARE
maxEstudiantes CONSTANT NUMBER:=5;
EstudiantesActuales NUMBER;
BEGIN
SELECT COUNT(*) INTO EstudiantesActualesFROM estudiantes
WHERE especialidad = :new.especialidad;
IF EstudiantesActuales+1>maxEstudiantes THEN
RAISE_APPLICATION_ERROR(-20000, Demasiadosestudiantes en la especialidad: ||:new.especialidad);
END IF;
END limite_especialidad;
Paquetes y tablas mutantes
7/25/2019 Objeto Relacional
26/27
26
Si ejecutamos
UPDATE estudiantes
SET especialidad = Historia
WHERE id=1003;
Nos da un error de tabla mutante
Para arreglarlo definimos el siguiente paquete:
Paquetes y tablas mutantes
Creamos el paquete:
CREATE OR REPLACE PACKAGE DatosEstudiantes AS
TYPE TipoEspecialidad IS TABLE OFestudiantes.especialidad%TYPE INDEX BYBINARY_INTEGER;
TYPE TipoIdentificador IS TABLE OFestudiantes.id%TYPE INDEX BY BINARY_INTEGER;
EspecEst TipoEspecialidad;
IdEst TipoIdentificador;
NumEntradas BINARY_INTEGER:=0;
END DatosEstudiantes;
Paquetes y tablas mutantes
7/25/2019 Objeto Relacional
27/27
Creamos el disparador a nivel de fila para almacenarlos nuevos datos:
CREATE OR REPLACE TRIGGER FilaLimiteEspecialidad
BEFORE INSERT OR UPDATE OF especialidad ON estudiantes
FOR EACH ROW
BEGIN
DatosEstudiantes.NumEntradas :=DatosEstudiantes.NumEntradas + 1;
DatosEstudiantes.EspecEst(DatosEstudiantes.NumEntradas)
:= :new.especialidad;DatosEstudiantes.IdEst(DatosEstudiantes.NumEntradas) :=
:new.id;
END FilaLimiteEspecialidad;
Paquetes y tablas mutantes
Creamos el disparador a nivel de orden para dar lafuncionalidad que queramos:
CREATE OR REPLACE TRIGGER OrdenLimiteEspecialidadAFTER INSERT OR UPDATE OF especialidad ON estudiantesDECLAREmaxEstudiantes CONSTANT NUMBER:=5;EstudiantesActuales NUMBER;EstudianteId estudiantes.id%TYPE;LaEspecialidad estudiantes.especialidad%TYPE;BEGIN
FOR indice IN 1..DatosEstudiantes.NumEntradas LOOPEstudianteId := DatosEstudiantes.IdEst(indice);LaEspecialidad := DatosEstudiantes.EspecEst(indice);SELECT COUNT(*) INTO EstudiantesActualesFROM estudiantesWHERE especialidad = LaEspecialidad;
IF EstudiantesActuales+1>maxEstudiantes THENRAISE_APPLICATION_ERROR(-20000, Demasiados estudiantes en laespecialidad: || LaEspecialidad);
END IF;END LOOP;DatosEstudiantes.NumEntradas := 0;END OrdenLimiteEspecialidad;
Paquetes y tablas mutantes