Oracle Colecciones PL/SQL: Varrays, anidadas e indexadas por tablas

¿Qué es una colección?

Una colección es un grupo ordenado de elementos de un tipo de dato específico. Puede ser una colección de un tipo de dato simple o de un tipo de dato complejo, como los tipos definidos por el usuario o los tipos de registro.

En una colección, cada elemento se identifica mediante un término llamado "subíndice." A cada elemento se le asigna un subíndice único, y los datos se pueden manipular u obtener haciendo referencia a ese subíndice único.

Las colecciones son más útiles cuando se necesita procesar o manipular una gran cantidad de datos del mismo tipo. Las colecciones se pueden llenar y manipular en su conjunto utilizando la opción 'BULK' en Oracle.

Las colecciones se clasifican según su estructura, subíndice y almacenamiento, como se muestra a continuación:

• Tablas indexadas (también conocidas como matrices asociativas)
• Tablas anidadas
• Varrays

En cualquier momento, los datos de una colección pueden ser referenciados mediante tres términos: nombre de la colección, subíndice y nombre del campo o columna, como “ ( ). Aprenderás sobre estas categorías de colección en las secciones siguientes.

Tipos de colecciones de un vistazo

Los tres tipos de colecciones presentan diferentes ventajas y desventajas. La siguiente tabla las compara antes de analizar cada una en detalle.

Varrays

Un Varray es una colección cuyo tamaño es fijo y no puede excederse. El subíndice de un Varray es un valor numérico. Los atributos de los Varrays son:

• El tamaño límite superior es fijo.
• Se rellenan secuencialmente comenzando con el subíndice '1'.
• Este tipo de colección siempre es densa; no podemos eliminar elementos individuales de la matriz. Un Varray se puede eliminar por completo o recortar desde el final.
• Debido a su densidad constante, tiene muy poca flexibilidad.
• Resulta más apropiado cuando se conoce el tamaño del array y se realizan actividades similares en todos los elementos.
• El subíndice y el número de elementos de la colección siempre permanecen estables.
• Debe inicializarse antes de su uso. Cualquier operación que no sea EXISTS en una colección no inicializada generará un error.
• Se puede crear como un objeto de base de datos visible en toda la base de datos, o dentro de un subprograma para su uso exclusivo en ese ámbito.

La siguiente figura explica la asignación de memoria de un Varray (denso).

Sintaxis de VARRAY:

TYPE <type_name> IS VARRAY (<SIZE>) OF <DATA_TYPE>;

• En la sintaxis anterior, type_name se declara como un VARRAY del tipo 'DATA_TYPE' para el límite de tamaño especificado. El tipo de dato puede ser simple o complejo.

Tablas anidadas

Una tabla anidada es una colección cuyo tamaño no es fijo. Tiene un tipo de índice numérico. Más información sobre el tipo de tabla anidada:

• La tabla anidada no tiene límite de tamaño superior.
• Dado que el límite superior no es fijo, es necesario ampliar la memoria cada vez antes de su uso, utilizando la palabra clave 'EXTEND'.
• Se rellenan secuencialmente comenzando con el subíndice '1'.
• Este tipo de colección puede ser ambos denso y escasoPodemos crearlo denso y también eliminar elementos individuales al azar, lo que lo hace disperso.
• Ofrece mayor flexibilidad para eliminar elementos de la matriz.
• Se almacena en una tabla de base de datos generada por el sistema y se puede utilizar en una consulta SELECT para obtener valores.
• El subíndice y el recuento pueden variar.
• Debe inicializarse antes de su uso. Cualquier operación que no sea EXISTS en una colección no inicializada generará un error.
• Se puede crear como un objeto de base de datos visible en toda la base de datos, o dentro de un subprograma para su uso exclusivo en ese ámbito.

La siguiente figura explica la asignación de memoria de una tabla anidada (densa y dispersa). Un espacio vacío indica un elemento disperso.

Sintaxis de tabla anidada:

TYPE <type_name> IS TABLE OF <DATA_TYPE>;

• En la sintaxis anterior, type_name se declara como una colección de tablas anidadas del tipo 'DATA_TYPE'. El tipo de datos puede ser simple o complejo.

Tabla de índices por

Una tabla indexada es una colección cuyo tamaño no es fijo. A diferencia de otros tipos de colecciones, el usuario puede definir el índice de una tabla indexada. Los atributos de una tabla indexada son:

• El subíndice puede ser un número entero o una cadena de texto. El tipo de subíndice debe especificarse al crear la colección.
• Estas colecciones no se almacenan secuencialmente.
• Siempre son escasos en la naturaleza.
• El tamaño de la matriz no es fijo.
• No se pueden almacenar en una columna de la base de datos. Se crean y utilizan dentro de una sesión específica.
• Ofrecen mayor flexibilidad para mantener el subíndice.
• Los subíndices pueden ser una secuencia negativa.
• Son más apropiadas para conjuntos de valores relativamente pequeños utilizados dentro del mismo subprograma.
• No es necesario inicializarlos antes de su uso.
• No se pueden crear como un objeto de base de datos; solo se crean dentro de un subprograma.
• La función BULK COLLECT no se puede utilizar con este tipo de recopilación, ya que el subíndice debe especificarse explícitamente para cada registro.

La siguiente figura explica la asignación de memoria de una tabla indexada (dispersa). Un espacio vacío indica un elemento disperso.

Sintaxis para la indexación por tabla:

TYPE <type_name> IS TABLE OF <DATA_TYPE> INDEX BY VARCHAR2 (10);

• En la sintaxis anterior, type_name se declara como una colección de tablas indexadas del tipo 'DATA_TYPE'. La variable de subíndice se especifica como de tipo VARCHAR2 con un tamaño máximo de 10.

Constructor y concepto de inicialización en colecciones.

Los constructores son funciones integradas proporcionadas por Oracle que tienen el mismo nombre que el objeto o la colección. Se ejecutan primero cuando se hace referencia a un objeto o colección por primera vez en una sesión. Detalles importantes de un constructor en el contexto de la colección:

• En el caso de las colecciones, estos constructores deben llamarse explícitamente para inicializar la colección.
• Tanto los arrays de tipo Varray como las tablas anidadas deben inicializarse mediante estos constructores antes de poder ser utilizados en el programa.
• Un constructor extiende implícitamente la asignación de memoria para una colección (excepto Varray), por lo que también puede asignar variables a la colección.
• Asignar valores mediante constructores nunca hace que la colección sea dispersa.

Métodos de recolección

Oracle Ofrece numerosas funciones para manipular y trabajar con colecciones. Estas funciones determinan y modifican los diferentes atributos de una colección. La siguiente tabla muestra las distintas funciones y sus descripciones.

Ejemplo 1: Tipo de registro a nivel de subprograma

En este ejemplo, vemos cómo llenar la colección usando 'COLECCIÓN A GRANEL'y cómo hacer referencia a los datos de la colección.

DECLARE
TYPE emp_det IS RECORD
(
EMP_NO NUMBER,
EMP_NAME VARCHAR2(150),
MANAGER NUMBER,
SALARY NUMBER
);
TYPE emp_det_tbl IS TABLE OF emp_det;
guru99_emp_rec emp_det_tbl:= emp_det_tbl();
BEGIN
INSERT INTO emp (emp_no,emp_name, salary, manager) VALUES (1000,'AAA',25000,1000);
INSERT INTO emp (emp_no,emp_name, salary, manager) VALUES (1001,'XXX',10000,1000);
INSERT INTO emp (emp_no, emp_name, salary, manager) VALUES (1002,'YYY',15000,1000);
INSERT INTO emp (emp_no,emp_name,salary, manager) VALUES (1003,'ZZZ',7500,1000);
COMMIT;
SELECT emp_no,emp_name,manager,salary BULK COLLECT INTO guru99_emp_rec
FROM emp;
dbms_output.put_line ('Employee Detail');
FOR i IN guru99_emp_rec.FIRST..guru99_emp_rec.LAST
LOOP
dbms_output.put_line ('Employee Number: '||guru99_emp_rec(i).emp_no);
dbms_output.put_line ('Employee Name: '||guru99_emp_rec(i).emp_name);
dbms_output.put_line ('Employee Salary:'|| guru99_emp_rec(i).salary);
dbms_output.put_line('Employee Manager Number:'||guru99_emp_rec(i).manager);
dbms_output.put_line('--------------------------------');
END LOOP;
END;
/

Code Explicación

• Code líneas 2-8: Tipo de registro 'emp_det' se declara con las columnas emp_no, emp_name, manager y salary de tipo de datos NUMBER, VARCHAR2, NUMBER y NUMBER.
• Code línea 9: Creando la colección 'emp_det_tbl' del elemento de tipo registro 'emp_det'.
• Code línea 10: Declarar la variable 'guru99_emp_rec' como de tipo 'emp_det_tbl' e inicializarla con un constructor nulo.
• Code líneas 12-15: Insertar los datos de muestra en la tabla 'emp'.
• Code línea 16: Confirmando la transacción de inserción.
• Code línea 17: Se recuperan los registros de la tabla 'emp' y se rellena la variable de colección de forma masiva mediante "BULK COLLECT". La variable 'guru99_emp_rec' ahora contiene todos los registros presentes en la tabla 'emp'.
• Code líneas 19-26: Configurar el bucle 'FOR' para imprimir todos los registros de la colección uno por uno. Los métodos de colección FIRST y LAST se utilizan como límites inferior y superior de la colección. loops.

Salida: Cuando se ejecuta el código anterior, se obtiene la siguiente salida.

Employee Detail
Employee Number: 1000
Employee Name: AAA
Employee Salary: 25000
Employee Manager Number: 1000
----------------------------------------------
Employee Number: 1001
Employee Name: XXX
Employee Salary: 10000
Employee Manager Number: 1000
----------------------------------------------
Employee Number: 1002
Employee Name: YYY
Employee Salary: 15000
Employee Manager Number: 1000
----------------------------------------------
Employee Number: 1003
Employee Name: ZZZ
Employee Salary: 7500
Employee Manager Number: 1000
----------------------------------------------

Preguntas Frecuentes