Colecciones de Oracle PL / SQL: Varrays, anidadas y amp; Índice por tablas

¿Qué es la colección?

Una colección es un grupo ordenado de elementos de tipos de datos particulares. Puede ser una colección de tipos de datos simples o complejos (como tipos de registro o definidos por el usuario).

En la colección, cada elemento se identifica mediante un término llamado "subíndice". A cada elemento de la colección se le asigna un subíndice único. Los datos de esa colección se pueden manipular o recuperar haciendo referencia a ese subíndice único.

Las colecciones son cosas más útiles cuando es necesario procesar o manipular grandes datos del mismo tipo. Las colecciones se pueden completar y manipular como un todo usando la opción 'BULK' en Oracle.

En este tutorial, aprenderá:

• ¿Qué es la colección?
• Varrays
• Tablas anidadas
• Índice por tabla
• Constructor y concepto de inicialización en colecciones
• Métodos de recolección

Las colecciones se clasifican según la estructura, el subíndice y el almacenamiento, como se muestra a continuación.

• Index-by-tables (también conocido como Associative Array)
• Mesas anidadas
• Varrays

En cualquier momento, los datos de la colección pueden denominarse mediante tres términos: Nombre de la colección, Subíndice, Campo / Nombre de columna como " (). ". Aprenderá más sobre estas categorías de colección mencionadas anteriormente en la siguiente sección.

Varrays

Varray es un método de recopilación en el que se fija el tamaño de la matriz. El tamaño de la matriz no se puede exceder de su valor fijo. El subíndice de Varray tiene un valor numérico. A continuación se muestran los atributos de Varrays.

• El tamaño del límite superior es fijo
• Completado secuencialmente comenzando con el subíndice '1'
• Este tipo de colección es siempre denso, es decir, no podemos eliminar ningún elemento de la matriz. Varray se puede eliminar como un todo o se puede recortar desde el final.
• Dado que siempre es de naturaleza densa, tiene muy menos flexibilidad.
• Es más apropiado usarlo cuando se conoce el tamaño de la matriz y realizar actividades similares en todos los elementos de la matriz.
• El subíndice y la secuencia siempre permanecen estables, es decir, el subíndice y el recuento de la colección son siempre los mismos.
• Deben inicializarse antes de usarlos en programas. Cualquier operación (excepto la operación EXISTS) en una colección no inicializada arrojará un error.
• Se puede crear como un objeto de base de datos, que es visible en toda la base de datos o dentro del subprograma, que solo se puede usar en ese subprograma.

La siguiente figura explicará la asignación de memoria de Varray (denso) en forma de diagrama.

Subíndice	1	2	3	4	5	6	7
Valor	Xyz	Dfv	Sde	Cxs	VBC	Nhu	Qwe

Sintaxis de VARRAY:

TYPE  IS VARRAY () OF ;

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

Tablas anidadas

Una tabla anidada es una colección en la que el tamaño de la matriz no es fijo. Tiene el tipo de subíndice numérico. A continuación se muestran más descripciones sobre el tipo de tabla anidada.

• La tabla anidada no tiene límite de tamaño superior.
• Dado que el límite de tamaño superior no es fijo, la colección y la memoria deben ampliarse cada vez que la usemos. Podemos extender la colección usando la palabra clave 'EXTEND'.
• Se rellena secuencialmente comenzando con el subíndice '1'.
• Este tipo de colección puede ser tanto denso como disperso , es decir, podemos crear la colección como densa, y también podemos eliminar el elemento individual de la matriz al azar, lo que lo convierte en escaso.
• Ofrece más flexibilidad para eliminar el elemento de la matriz.
• Se almacena en la tabla de la base de datos generada por el sistema y se puede utilizar en la consulta de selección para obtener los valores.
• El subíndice y la secuencia no son estables, es decir, el subíndice y el recuento del elemento de la matriz pueden variar.
• Deben inicializarse antes de usarlos en programas. Cualquier operación (excepto la operación EXISTS) en la colección no inicializada arrojará un error.
• Se puede crear como un objeto de base de datos, que es visible en toda la base de datos o dentro del subprograma, que solo se puede usar en ese subprograma.

La siguiente figura explicará la asignación de memoria de la tabla anidada (densa y dispersa) en forma de diagrama. El espacio del elemento de color negro denota el elemento vacío en una colección, es decir, escaso.

Subíndice	1	2	3	4	5	6	7
Valor (denso)	Xyz	Dfv	Sde	Cxs	VBC	Nhu	Qwe
Valor (escaso)	Qwe		Asd	Afg		Asd	Nosotros somos

Sintaxis de la tabla anidada:

TYPE  IS TABLE OF ;

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

Índice por tabla

Index-by-table es una colección en la que el tamaño de la matriz no es fijo. A diferencia de los otros tipos de colección, en la colección de índice por tabla, el subíndice puede consistir en ser definido por el usuario. A continuación se muestran los atributos de índice por tabla.

• El subíndice puede tener números enteros o cadenas. Al momento de crear la colección, se debe mencionar el tipo de subíndice.
• Estas colecciones no se almacenan secuencialmente.
• Siempre son escasos por naturaleza.
• El tamaño de la matriz no es fijo.
• No se pueden almacenar en la columna de la base de datos. Se crearán y utilizarán en cualquier programa de esa sesión en particular.
• Dan más flexibilidad en términos de mantener el subíndice.
• Los subíndices también pueden ser de secuencia de subíndices negativos.
• Son más apropiados para usar con valores colectivos relativamente más pequeños en los que la colección se puede inicializar y usar dentro de los mismos subprogramas.
• No es necesario inicializarlos antes de empezar a utilizarlos.
• No se puede crear como un objeto de base de datos. Solo se puede crear dentro del subprograma, que solo se puede usar en ese subprograma.
• BULK COLLECT no se puede utilizar en este tipo de colección, ya que el subíndice debe proporcionarse explícitamente para cada registro de la colección.

La siguiente figura explicará la asignación de memoria de la tabla anidada (dispersa) en forma de diagrama. El espacio del elemento de color negro denota el elemento vacío en una colección, es decir, escaso.

Subíndice (varchar)	PRIMERO	SEGUNDO	TERCERA	CUATRO	QUINTO	SEXTO	SÉPTIMO
Valor (escaso)	Qwe		Asd	Afg		Asd	Nosotros somos

Sintaxis para índice por tabla

TYPE  IS TABLE OF  INDEX BY VARCHAR2 (10);

• En la sintaxis anterior, type_name se declara como una colección de índice por tabla del tipo 'DATA_TYPE'. El tipo de datos puede ser simple o complejo. La variable subsciprt / index se da como tipo VARCHAR2 con un tamaño máximo de 10.

Constructor y concepto de inicialización en colecciones

Los constructores son la función incorporada proporcionada por el oráculo que tiene el mismo nombre que el objeto o las colecciones. Se ejecutan primero cada vez que se hace referencia a objetos o colecciones por primera vez en una sesión. A continuación se muestran los detalles importantes del constructor en el contexto de la colección:

• Para las colecciones, estos constructores deben llamarse explícitamente para inicializarlo.
• Tanto las tablas Varray como las anidadas deben inicializarse a través de estos constructores antes de ser referidas al programa.
• El constructor extiende implícitamente la asignación de memoria para una colección (excepto Varray), por lo tanto, el constructor también puede asignar las variables a las colecciones.
• La asignación de valores a la colección a través de constructores nunca hará que la colección sea escasa.

Métodos de recolección

Oracle proporciona muchas funciones para manipular y trabajar con las colecciones. Estas funciones son muy útiles en el programa para determinar y modificar los diferentes atributos de las colecciones. La siguiente tabla dará las diferentes funciones y su descripción.

Método	Descripción	SINTAXIS
EXISTE (n)	Este método devolverá resultados booleanos. Devolverá 'TRUE' si el n- ésimo elemento existe en esa colección, de lo contrario, devolverá FALSE. Solo las funciones EXISTS se pueden usar en una colección no inicializada	.EXISTS (posición_elemento)
CONTAR	Da el recuento total de los elementos presentes en una colección.	.COUNT
LÍMITE	Devuelve el tamaño máximo de la colección. Para Varray, devolverá el tamaño fijo que se ha definido. Para la tabla anidada y el índice por tabla, da NULL	.LIMIT
PRIMERO	Devuelve el valor de la primera variable de índice (subíndice) de las colecciones.	.FIRST
ÚLTIMO	Devuelve el valor de la última variable de índice (subíndice) de las colecciones.	.LAST
ANTERIOR (n)	Devuelve precede a la variable de índice en una colección del enésimo elemento. Si no hay ningún valor de índice anterior, se devuelve NULL	.PRIOR (n)
SIGUIENTE (n)	Devuelve la variable de índice correcta en una colección del n- ésimo elemento. Si no hay un índice de éxito, se devuelve el valor NULL	.NEXT (n)
AMPLIAR	Extiende un elemento de una colección al final	.EXTEND
EXTENDER (n)	Extiende n elementos al final de una colección	.EXTEND (n)
EXTENDER (n, i)	Extiende n copias del i- ésimo elemento al final de la colección	.EXTEND (n, i)
PODAR	Elimina un elemento del final de la colección.	.TRIM
TRIM (n)	Elimina n elementos del final de la colección	.TRIM (n)
ELIMINAR	Elimina todos los elementos de la colección. Vacía la colección	.DELETE
BORRAR (n)	Elimina el enésimo elemento de la colección. Si el n- ésimo elemento es NULL, entonces esto no hará nada	.DELETE (n)
BORRAR (m, n)	Elimina el elemento en el intervalo m º a n º en la colección	.DELETE (m, n)

Ejemplo 1: Tipo de registro a nivel de subprograma

En este ejemplo, veremos cómo poblar la colección usando 'BULK COLLECT' y cómo referir los datos de la colección.

DECLARETYPE 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();BEGININSERT 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_recFROM emp;dbms_output.put_line (‘Employee Detail');FOR i IN guru99_emp_rec.FIRST… guru99_emp_rec.LASTLOOPdbms_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;/

Explicación del código:

• Línea de código 2-8 : El tipo de registro 'emp_det' se declara con las columnas emp_no, emp_name, salario y administrador del tipo de datos NUMBER, VARCHAR2, NUMBER, NUMBER.
• Línea de código 9: Creación de la colección 'emp_det_tbl' del elemento de tipo de registro 'emp_det'
• Línea de código 10: Declarando la variable 'guru99_emp_rec' como tipo 'emp_det_tbl' e inicializada con un constructor nulo.
• Línea de código 12-15: Insertar los datos de muestra en la tabla 'emp'.
• Línea de código 16: Confirmación de la transacción de inserción.
• Línea de código 17: Obteniendo los registros de la tabla 'emp' y llenando la variable de colección de forma masiva usando el comando "BULK COLLECT". Ahora la variable 'guru99_emp_rec' contiene todos los registros que están presentes en la tabla 'emp'.
• Línea de código 19-26: Configuración del bucle 'FOR' utilizando para imprimir todos los registros de la colección uno por uno. El método de recolección FIRST y LAST se utiliza como límite inferior y superior del bucle.

Salida : como puede ver en la captura de pantalla anterior, cuando se ejecuta el código anterior, obtendrá la siguiente salida

Employee DetailEmployee Number: 1000Employee Name: AAAEmployee Salary: 25000Employee Manager Number: 1000----------------------------------------------Employee Number: 1001Employee Name: XXXEmployee Salary: 10000Employee Manager Number: 1000----------------------------------------------Employee Number: 1002Employee Name: YYYEmployee Salary: 15000Employee Manager Number: 1000----------------------------------------------Employee Number: 1003Employee Name: ZZZEmployee Salary: 7500Employee Manager Number: 1000----------------------------------------------