M2 - Bases de dades / Apunts UF4 : BDOR3
De wikiserver
Colecciones
- Oracle incluye dos tipos de colecciones:
- - Varray
- - Nested table
- Las colecciones pueden ser usadas en cualquier lugar donde se necesita definir tipo (atributo, variable, parámetro).
- Se puede definir el tipo de objeto representando la colección y después usarlo en la definición de la tabla.
Varrays
- Cada elemento tiene una posición que corresponde a la posición del elemento dentro de la colección.
- El tamaño del arreglo indica el número máximo de elementos, pero Oracle almacena tamaño variable (de acuerdo a la cantidad real de elementos que existen).
- - Por esto los arreglos se llaman varrays.
- Cada elemento es del mismo tipo.
- La creación de una colección tipo varray requiere la cláusula CREATE TYPE ... AS ARRAY OF...
CREATE TYPE e_mails_array_typ AS varray(3) OF VARCHAR2(20);- La creación de tipo (incluyendo varray) no asigna espacio.
- Este tipo se define para usarlo como:
- - Columna de tabla relacional.
- - Atributo de UDT.
- - Variable o parámetro de la función o procedimiento.
CREATE TABLE employee_tab (
id NUMBER(5),
name VARCHAR2(35),
e_mails e_mails_array_typ);- - Para insertar datos se ocupa invocar constructor:
INSERT INTO employee_tab VALUES
(100, 'Elka', e_mails_array_typ('algo@algo.com', 'otro@otro.org'));- Se puede crear objetos de más niveles de anidación
CREATE TYPE phone_typ AS OBJECT (
country_code VARCHAR2(2),
area_code VARCHAR2(3),
ph_number VARCHAR2(7));
CREATE TYPE phone_varray_typ AS VARRAY(5) OF phone_typ;
CREATE TABLE dept_phone_list (
dept_no NUMBER(5),
phone_list phone_varray_typ);- Para insertar datos se ocupa invocar constructores correspondientes:
INSERT INTO dept_phone_list VALUES (100, phone_varray_typ( phone_typ ('01', '650',
'5061111'), phone_typ ('01', '650', '5062222'), phone_typ ('01', '650', '5062525')));- El almacenamiento de varray depende solo del número de elementos presentes en la colección.
- Se guardan en columna de la misma tabla o como LOB, si su tamaño excede 4000 bytes.
- Varrays tienen mejor desempeño cuando se accede toda la colección porque se guardan en forma comprimida y no requieren ninguna operación join.
Nested table
- Permite almacenar la colección no ordenada de datos sin especificar el límite de elementos.
- Todos los elementos son del mismo tipo pre-definido o como tipo objeto creado por usuario.
- Los elementos de la tabla anidada se guardan físicamente en otra tabla.
- La tabla anidada contiene la columna NESTED_TABLE_ID creada por el sistema que permite identificar la tupla en la tabla padre, al cual cada elemento pertenece.
- Para definir la tabla anidada se requiere la cláusula CREATE TYPE ... AS TABLE OF ...:
CREATE OR REPLACE TYPE CourseList AS TABLE OF VARCHAR2(64);- Parecido a varray se puede usar como:
- - Columna de tabla relacional.
- - Atributo de UDT.
- - Variable o parámetro de la función o procedimiento.
- Ejemplo:
CREATE TABLE department (
name VARCHAR2(20),
director VARCHAR2(20),
office VARCHAR2(20),
courses CourseList) --> Nested table
NESTED TABLE courses STORE AS courses_tab;- Para el atributo tipo tabla anidada se especifica el nombre de la tabla que físicamente guarda los valores.
- Para insertar los valores se llama el constructor:
INSERT INTO department (name, director, office, courses)
VALUES ('English', 'Lynn Saunders', 'Breakstone Hall 205', CourseList('Expository Writing', 'Film and Literature', 'Modern Science Fiction'));- Se puede crear objetos de más niveles de anidación:
CREATE TYPE person_typ AS OBJECT (
idno NUMBER,
name VARCHAR2(20),
phone VARCHAR2(20));
CREATE TYPE people_typ AS TABLE OF person_typ;
CREATE TABLE department_persons (
dept_no NUMBER PRIMARY KEY,
dept_name CHAR(20),
dept_mgr person_typ,
dept_emps people_typ)
NESTED TABLE dept_emps STORE AS dept_emps_tab;
INSERT INTO department_persons VALUES ( 101, 'Physical Sciences', person_typ(65,'Vrinda Mills', '1-800-555-4412'), people_typ(person_typ(1, 'John Smith', '1-800-555-1212'), person_typ(2, 'Diane Smith', NULL) ));
INSERT INTO department_persons VALUES ( 104, 'Life Sciences', person_typ(70,'James Hall', '1-800-555-4621'), people_typ());Operaciones sobre colecciones
- Oracle provee dos formas de consultar las tablas que tienen la columna o atributo de tipo colección:
- - Devuelve la colección anidada dentro de la fila.
- - Distribuye (unnest) la colección de la forma que cada elemento de la colección aparece en la tupla separada (aplana la relación).
- Usando la primera opción tanto para varray como para nested tables:
SELECT * FROM employee_tab;
Resultado: 100, Elka, e_mails_array_typ(‘algo@algo.com’, ‘otro@otro.org’)
SELECT e.name, e.e_mails FROM employee_tab e;
Resultado: Elka, e_mails_array_typ(‘algo@algo.com’, ‘otro@otro.org’)
SELECT d.phone_list people_column FROM dept_phone_list d;
Resultado: phone_varray_typ(phone_typ ('01', '650', '5061111'),
phone_typ ('01', '650', '5062222'),
phone_typ ('01', '650', '5062525'))
SELECT d.courses FROM department d;
Resultado: CourseList('Expository Writing', 'Film and Literature', 'Modern Science Fiction')
SELECT d.dept_emps FROM department_persons d;
Resultado: PEOPLE_TYP(PERSON_TYP(1, 'John Smith', '1-800-555-1212'),
PERSON_TYP(2, 'Diane Smith', NULL))- Para obtener resultado en la forma de la tabla plana se usa la expresión TABLE con la colección:
- - Para varray:
SELECT p.* SELECT d.dept_no, p.*
FROM dept_phone_list d, TABLE(d.phone_list) p; FROM dept_phone_list d, TABLE(d.phone_list) p;
Resultado:
country_code arrea_code ph_number
-------------------------------------------------------
01 650 5061111
01 650 5062222
01 650 5062525- - Para nested table:
SELECT e.*
FROM department_persons d, TABLE(d.dept_emps) e;
Resultado:
idno name phone
---------- ------------------------------ ---------------
1 John Smith 1-800-555-1212
2 Diane Smith (null)- La expresión de TABLE puede referirse al alias de la tabla que aparece a la izquierda – left correlation.
- La expresión TABLE puede también tener a sub-consulta que devuelve la colección.
- Ejemplo de la misma consulta que antes:
SELECT *
FROM TABLE(SELECT d.phone_list
FROM dept_phone_list d);Existen tres restricciones para usar TABLE en sub-consultas
- - La sub-consulta tiene que devolver la colección.
- - La sentencia SELECT puede tener solo un elemento.
- - La colección que devuelve la sub-consulta tiene que ser simple no anidada.
- Oracle permite modificar colecciones enteras (varrays y nested table).
- - Ejemplo de modificación entera de atributo tipo array:
UPDATE employee_tab e
SET e. e_mails = e_mails_array_typ('modificado@gmail.com','otro@otro.org');- - Ejemplo de modificación entera de atributo tipo tablas anidadas:
UPDATE department_persons d
SET dept_emps = people_typ(person_typ(1, 'Juan Morales', '1-650-111-2222'), person_typ(5, 'Maria Castro', NULL))
WHERE d.dept_no=101;- Sin embargo, solo se puede realizar modificaciones parciales para la colección si ésta está definida como nested table.
- Las modificaciones parciales para nested table se hacen usando la expresíón TABLE:
UPDATE TABLE( SELECT d.dept_emps
FROM department_persons d
WHERE d.dept_no = 101) e
SET VALUE(e) = person_typ(5, 'Kevin Taylor', '1-800-555-6233')
WHERE e.idno = 5;
DELETE FROM TABLE( SELECT d.dept_emps
FROM department_persons d
WHERE d.dept_no = 101) e
WHERE e.idno = 5;- Para colecciones representadas como nested table Oracle incluye varios operadores de comparación, ej., =, <>, IN, MEMBER OF, etc.
- Adicionalmente, define varias operciones que se pueden realizar con nested tables, como CARDINALITY, COLLECT, MULTISET EXCEPT (diferencia de conjuntos), MULTISET INTERSECTION, UNION, etc.
- Estos operadores y operaciones no se pueden usar con las colecciones tipo varray.
