Oracle Colecciones PL/SQL: Varrays, anidadas e indexadas por tablas
fiona marrón
¿Qué es la colección?
Una colección es un grupo ordenado de elementos de tipos de datos específicos. Puede ser una colección de tipos de datos simples o complejos (como tipos de datos definidos por el usuario o de registro).
En la colección, cada elemento está identificado por 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 muy útiles cuando es necesario procesar o manipular una gran cantidad de datos del mismo tipo. Las colecciones se pueden completar y manipular en su totalidad usando la opción 'BULK' en Oracle.
Las colecciones se clasifican según la estructura, el subíndice y el almacenamiento como se muestra a continuación.
- Índice por tablas (también conocido como matriz asociativa)
- Tablas anidadas
- Varrays
En cualquier momento, se puede hacer referencia a los datos de la colección mediante tres términos Nombre de colección, Subíndice, Nombre de campo/columna como “().”. Aprenderá más sobre estas categorías de colecciones mencionadas anteriormente en la siguiente sección.
Varrays
Varray es un método de recopilación en el que el tamaño de la matriz es fijo. El tamaño de la matriz no puede superar su valor fijo. El subíndice de Varray es 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 siempre es densa, es decir, no podemos eliminar ningún elemento de la matriz. Varray se puede eliminar en su totalidad o se puede recortar desde el final.
- Como 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.
- Es necesario inicializarlos antes de usarlos en programas. Cualquier operación (excepto la operación EXISTS) sobre una colección no inicializada generará un error.
- Puede crearse como un objeto de base de datos, que es visible en toda la base de datos o dentro del subprograma, que solo puede usarse en ese subprograma.
La siguiente figura explicará esquemáticamente la asignación de memoria de Varray (densa).
| subíndice | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| Valor | Xyz | dfv | editores | cxs | vbc | Nhu | qwe |
Sintaxis de VARRAY:
TYPE <type_name> IS VARRAY (<SIZE>) OF <DATA_TYPE>;
- En la sintaxis anterior, type_name se declara como VARRAY del tipo 'DATA_TYPE' para el límite de tamaño indicado. 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 memoria de la colección debe ampliarse cada vez antes de usarla. Podemos ampliar la colección usando la palabra clave 'EXTEND'.
- Se completa secuencialmente comenzando con el subíndice '1'.
- Este tipo de colección puede ser de ambos denso y escaso, es decir, podemos crear la colección como densa y también podemos eliminar el elemento de la matriz individual al azar, lo que la hace escasa.
- Proporciona más flexibilidad con respecto a la eliminación del elemento de la matriz.
- Se almacena en la tabla de base de datos generada por el sistema y se puede utilizar en la consulta de selección para recuperar 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.
- Es necesario inicializarlos antes de usarlos en programas. Cualquier operación (excepto la operación EXISTS) sobre la colección no inicializada generará un error.
- Puede crearse como un objeto de base de datos, que es visible en toda la base de datos o dentro del subprograma, que solo puede usarse en ese subprograma.
La siguiente figura explicará esquemáticamente la asignación de memoria de la tabla anidada (densa y dispersa). El espacio del elemento de color negro indica el elemento vacío en una colección, es decir, escaso.
| subíndice | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| Valor (denso) | Xyz | dfv | editores | cxs | vbc | Nhu | qwe |
| Valor (escaso) | qwe | Asd | afg | Asd | ¿Quién |
Sintaxis de tabla anidada:
TYPE <tvpe 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.
Índice por tabla
Índice por tabla es una colección en la que el tamaño de la matriz no es fijo. A diferencia de otros tipos de colección, en la colección índice por tabla el subíndice puede ser definido por el usuario. A continuación se muestran los atributos de índice por tabla.
- El subíndice puede ser un número entero 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 en la 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 tener una secuencia de subíndices negativos.
- Son más apropiados para valores colectivos relativamente más pequeños en los que la colección se puede inicializar y utilizar 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. Sólo se puede crear dentro del subprograma, que sólo se puede utilizar 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á esquemáticamente la asignación de memoria de la tabla anidada (escasa). El espacio del elemento de color negro indica el elemento vacío en una colección, es decir, escaso.
| Subíndice (varchar) | PRIMERO | SEGUNDO | TERCER | CUARTO | QUINTO | SEXTO | SÉPTIMO |
| Valor (escaso) | qwe | Asd | afg | Asd | ¿Quién |
Sintaxis de índice 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 indexada por tabla del tipo 'DATA_TYPE'. El tipo de datos puede ser simple o complejo. La variable subsciprt/index se proporciona como tipo VARCHAR2 con un tamaño máximo de 10.
Constructor y concepto de inicialización en colecciones.
Los constructores son funciones integradas que proporciona el oráculo y que tienen el mismo nombre que el objeto o la colección. Se ejecutan primero cuando se hace referencia al objeto o la colección 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 inicializarlas.
- Tanto las tablas Varray como las anidadas deben inicializarse a través de estos constructores antes de ser remitidas al programa.
- El constructor extiende implícitamente la asignación de memoria para una colección (excepto Varray), por lo que el constructor también puede asignar las variables a las colecciones.
- Asignar 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 muestra las diferentes funciones y su descripción.
| Método | Descripción | SINTAXIS |
|---|---|---|
| EXISTE (n) | Este método devolverá resultados booleanos. Devolverá 'VERDADERO' si el nth El elemento existe en esa colección; de lo contrario, devolverá FALSO. Solo se pueden usar funciones EXISTS en una colección no inicializada | .EXISTS(posición_elemento) |
| COUNT | Da el recuento total de los elementos presentes en una colección. | .COUNT |
| LIMITE LAS | Devuelve el tamaño máximo de la colección. Para Varray, devolverá el tamaño fijo que se ha definido. Para tabla anidada e í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) | Los retornos preceden a la variable de índice en una colección de nth elemento. Si no hay ningún valor de índice anterior, se devuelve NULL | .PRIOR(n) |
| SIGUIENTE (n) | Devoluciones suceden a la variable de índice en una colección de nth elemento. Si no hay ningún valor de índice exitoso, se devuelve 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 de ith elemento al final de la colección | .EXTEND(n,i) |
| TRIM | Elimina un elemento del final de la colección. | .TRIM |
| RECORTAR (n) | Elimina n elementos del final de la colección. | .TRIM (n) |
| BORRAR | Elimina todos los elementos de la colección. Hace que la colección esté vacía. | .BORRAR |
| BORRAR (n) | Elimina el enésimo elemento de la colección. si el norteth elemento es NULL, entonces esto no hará nada | .DELETE(n) |
| BORRAR (m,n) | Elimina el elemento en el rango m.th a nth en la colección | .DELETE(m,n) |
Ejemplo 1: Tipo de registro a nivel de subprograma
En este ejemplo, veremos cómo completar la colección usando 'COLECCIÓN A GRANEL' y cómo referir los datos de la recopilació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;
/
Explicación del código:
- Línea de código 2-8: Tipo de registro 'emp_det' se declara con las columnas emp_no, emp_name, salario y administrador de tipo de datos NUMBER, VARCHAR2, NUMBER, NUMBER.
- Línea de código 9: Creando la colección 'emp_det_tbl' del elemento de tipo de registro 'emp_det'
- Línea de código 10: Declarar 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: Confirmando la transacción de inserción.
- Línea de código 17: Obtener los registros de la tabla 'emp' y completar la variable de colección de forma masiva utilizando 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: Configurar el bucle 'FOR' para imprimir todos los registros de la colección uno por uno. El método de recopilación PRIMERO y ÚLTIMO se utiliza como límite inferior y superior del loops.
Salida:Como puede ver en la captura de pantalla anterior, cuando se ejecuta el código anterior, obtendrá el siguiente resultado
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 ----------------------------------------------
