Revisió del 20:22, 17 maig 2018

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 ARRAY 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 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', '1-800-555-1243'))

• 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.dep_no, p.*
FROM dep_phone_list d, TABLE(d.phone_list) p;               FROM dep_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                    1-800-555-1243

• La expresión de TABLE puede referirse al alias de la tabla que aparece a la izquierda – left correlation.