Objetivos:
• Conocer más acerca de las diferentes Colecciones de PL/SQL (PL/SQL Collections).
• Ver los diferentes tipos disponibles:
---Matrices Asociativas(Associative Arrays)
---Tablas Anidadas(Nested Table)
---Los VARRAYs
• Exponer sus diferencias.
• Ver Ejemplos Prácticos.
• Ver los diferentes tipos disponibles:
---Matrices Asociativas(Associative Arrays)
---Tablas Anidadas(Nested Table)
---Los VARRAYs
• Exponer sus diferencias.
• Ver Ejemplos Prácticos.
NOTA: Usamos como ejemplo la Base de Datos: ORCL, la cual viene por defecto en cualquier versión de ORACLE.
____________________________________________________________________________________
Las Colecciones
Oracle provee dos Tipos de Datos Compuestos(Composite Data Types), estos con: Los Records y Las Colecciones (Ambos de PL/SQL). Como es evidente en esta entrega nos enfocaremos en los últimos.
Para tener una idea más clara podríamos ver las Colecciones de PL/SQL como los arregos(arrays) disponibles en otros Lenguajes de Programación como: C, C++ y Java. Por lo cual las podemos definir como un conjunto (o una estructura) de datos homogéneos almacenados en forma consecutiva en memoria.
Al decir que son un conjunto de datos homogéneos se entiende que todos los valores deben ser del mismo tipo de dato, pero es prudente tener presente que dicho tipo de dato puede ser un tipo de dato compuesto, o sea, una Colección de otra Colección o de un Record; Recordar que los Records son usados para tratar distintos tipos de datos (con cierta relación entre sí) como una unidad lógica, Eje: las columnas de la tabla: employees.
En síntesis, usamos las Colecciones de PL/SQL cuando queremos almacenar una lista de valores del mismo tipo de dato.
Eje: Una lista con los nombres (VARCHAR2) de los empleados de la tabla employees; De igual manera, Podríamos tener una lista con todos los campos de la tabla countries, en este último caso definiríamos un Tipo Record equivalente a countries@ROWTYPE y posteriormente una Colección de dicho tipo.
Al igual que los Tipos de Datos Escalares (VARCHAR2, NUMBER, etc), las Colecciones pueden ser usadas como parámetros de entrada i/o salida de Procedimientos y Funciones así como también pueden ser el valor de retorno de una función.
Nota: Esta publicación se puede considerar una continuación de: Tipos de Datos Compuestos. Click Aquí.
____________________________________________________________________________________
Las Colecciones y sus Métodos
Estos métodos son Funciones o Procedimientos incorporados en las Colecciones, los mismos facilitan su manejo y mantenimiento. Para hacer uso de ellos usamos la notación de punto.
Sintaxis:
collection_name.
{COUNT
| DELETE [ (index [, index]) ]
| EXISTS (index)
| EXTEND [ (number [, index] ]
| FIRST
| LAST
| LIMIT
| NEXT (index)
| PRIOR (index)
| TRIM [ (number) ]
}
En la Sintaxis:
collection_name: Es el nombre de la variable tipo Colección.
Método
| Descripción. |
COUNT
|
Devuelve el número de elementos que contiene la Colección, muy útil porque no siempre se conoce el tamaño actual de una Colección. Puede utilizar COUNT dondequiera que se permita una expresión numérica. En el caso de los VARRAYs, el valor de COUNT siempre es igual a LAST. Para las Tablas Anidadas y las Matrices Asociativas, COUNT es normalmente igual a LAST. A menos que se eliminen elementos del centro, si esto ocurre, COUNT sería menor que LAST.
|
DELETE
|
Solo puede ser usado con Tablas Anidadas y/o Matrices Asociativas.
Tiene Tres variantes:
DELETE elimina todos los elementos de una Colección.
DELETE(n) elimina el elemento n. Si n es nulo, DELETE(n) no hace nada.
DELETE(m, n) elimina todos los elementos del rango m..n. Si m es mayor que n o si m o n es nulo, DELETE(m, n) no hace nada.
|
EXISTS
|
EXISTS(n) devuelve TRUE si existe el elemento n de una Colección, de lo contrario, devuelve FALSE. Puede utilizar EXISTS para evitar una excepción al hacer referencia a un elemento inexistente. Cuando se le pasa un subíndice fuera de rango, EXISTS devuelve FALSE en lugar de lanzar la excepción SUBSCRIPT_OUTSIDE_LIMIT.
|
EXTEND
|
· Es un método exclusivo de las Tablas Anidadas y Los VARRAYs.
Este procedimiento tiene tres formas:
EXTEND agrega un elemento nulo.
EXTEND(n) agrega n elementos nulos.
EXTEND(n, i) agrega n copias del elemento i.
EXTEND opera sobre el tamaño interno de una Colección. Si EXTEND encuentra elementos eliminados, los incluye en su recuento.
|
FIRST
|
FIRST devuelve el primer (más pequeño) valor de subíndice en una colección. Los valores de subíndice suelen ser enteros, pero en el caso de las Matrices Asociativas también pueden ser cadenas. Si la Colección está vacía, FIRST devuelve NULL. Para Los VARRAYs, FIRST siempre devuelve 1.
|
LAST
|
LAST devuelve el último (más grande) valor de subíndice en una Colección. Al igual que FIRST, si la Colección está vacía, LAST devuelve NULL. Para Los VARRAYs, LAST siempre es igual a COUNT. Para las Tablas Anidadas y las Matrices Asociativas, LAST es normalmente igual a COUNT. A menos que se eliminen elementos del centro, si esto ocurre, LAST sería mayor que COUNT.
|
PRIOR(n)
|
Retorna el número de índice que antecede al índice n.
|
NEXT(n)
|
Retorna el número de índice que sigue(sucede) al índice n.
|
LIMIT
|
Para las Tablas Anidadas y las Matrices Asociativas (No tienen un tamaño máximo), LIMIT devuelve NULL. Para Los VARRAYs, LIMIT devuelve el número máximo de elementos que un VARRAY puede contener (Especificado en la definición de tipo).
|
TRIM
|
Es un método exclusivo de las Tablas Anidadas y Los
Este procedimiento tiene dos formas:
TRIM elimina un elemento del final de una Colección.
TRIM(n) elimina n elementos del final de una Colección. Si n es mayor que COUNT, TRIM(n) eleva la excepción SUBSCRIPT_BEYOND_COUNT. TRIM opera en el tamaño interno de una Colección. Si TRIM encuentra elementos eliminados, los incluye en su recuento.
|
Algunas Directrices:
• Los métodos de las Colecciones no se pueden ser invocados desde Sentencias SQL.
• Sólo es posible usar el método EXISTS en Colecciones nulas. Si aplica otro método a dichas Colecciones, se eleva la excepción: COLLECTION_IS_NULL.
• Los métodos EXISTS, COUNT, LIMIT, FIRST, LAST, PRIOR y NEXT son funciones; EXTEND, TRIM y DELETE son procedimientos.
____________________________________________________________________________________
Matrices Asociativas
Iniciamos presentando las Matrices Asociativas(Associative Arrays), también conocidas como INDEX BY Tables.
Características:
Las Matrices Asociativas:
• Son tablas exclusivas de PL/SQL, esto significa que pueden existir en estructuras de memoria de PL/SQL (Paquetes, Funciones, Procedimientos etc...) pero no pueden ser creadas como objectos/columnas de Base de Datos.
• Están compuestas de dos columnas a las cuales no es posible asignarles nombres:
---La primera columna es el indice (index).
---La segunda columna contiene el dato almacenado(value).
---El indice (index) es usado para localizar el dato almacenado en la segunda columna.
---Los valores del indice pueden ser tanto negativos como positivos y no necesariamente tienen que ser insertados de forma secuenciar o consecutiva, esto es, puede agregar el indice 4 antes que el 3.
• No son inicializadas al momento de su declaración.
• Tienen un tamaño dinámico, por lo cual pueden crecer tanto como sea necesario.
Nota: Los valores de índice para matrices asociativas deben ser únicos. Si se asigna un nuevo valor(columna value) a un indice previamente usado, el valor anterior es reemplazado. Esto es:
v_assoc_array(1) = 'Hola';
v_assoc_array(1) = 'PL/SQL';
/*Para el indice 1 de la Colección: v_assoc_array el valor contenido dejo de ser 'Hola' para convertirse en 'PL/SQL', esto a partir de la segunda asignación.*/
Tipos de Datos compatibles con las dos columnas:
Columna Index: Para esta columna el tipo de dato puede ser:
• Numérico: BINARY_INTEGER o PLS_INTEGER. Estos tipos requieren menos almacenamiento que el tipo NUMBER y con ellos las operaciones aritméticas resultan más rápidas.
• Carácter: VARCHAR2 o uno de sus subtipos.
Columna Value: El tipo de dato para esta columna de valores puede ser Escalar (Un valor por fila) o un tipo Record (Varios valores por fila).
Sintaxis:
--Primero creamos el Tipo Tabla:
TYPE type_name IS TABLE OF
{
column_type [NOT NULL] | variable%TYPE [NOT NULL]
| table.column%TYPE [NOT NULL]
| table%ROWTYPE }
INDEX BY { PLS_INTEGER | BINARY_INTEGER | VARCHAR2(<size>)
};
--Luego una variable de ese Tipo Tabla:
identifier type_name;
Ejemplos:
SET SERVEROUTPUT ON
DECLARE
--Definición del Tipo Tabla:
TYPE typ_assoc_array IS TABLE OF
VARCHAR2(25)
INDEX BY PLS_INTEGER;
--Declaramos una Variable del Tipo Tabla anterior:
v_assoc_array typ_assoc_array;
BEGIN
v_assoc_array(1) := 'Valor para el Indice 1';
v_assoc_array(2) := 'Valor para el Indice 2';
v_assoc_array(3) := 'Valor para el Indice 3';
DBMS_OUTPUT.PUT_LINE(v_assoc_array(1)||', '||v_assoc_array(2)||', '||v_assoc_array(3));
END;
/*Empezamos un este ejemplo básico: Notar como declaramos el Tipo Tabla: typ_assoc_array y posteriormente creamos la variable: v_assoc_array de ese Tipo. Ver como especificamos el indice al cual queremos agregar 'X' valor, de la misma forma que usamos el indice para hacer uso del valor.*/
---OUTPUT:
SET SERVEROUTPUT ON
DECLARE
--Definición del Tipo Tabla:
TYPE typ_assoc_array IS TABLE OF
VARCHAR2(25)
INDEX BY PLS_INTEGER;
--Declaramos una Variable del Tipo Tabla anterior:
v_assoc_array typ_assoc_array;
--CURSOR con los apellidos de todos los empleados de la tabla employees ordenados por salario de mayor a menor:
CURSOR c_emp_last_name IS
SELECT last_name
FROM hr.employees
ORDER BY salary DESC;
v_counter NUMBER(1) := 1;
BEGIN
FOR i IN c_emp_last_name LOOP
v_assoc_array(v_counter) := i.last_name;
EXIT WHEN c_emp_last_name%ROWCOUNT > 4; ---Limitamos el LOOP a solo 5 vueltas.
v_counter := v_counter+1;
END LOOP;
--Validamos que haya registros en nuestra Colección:
IF v_assoc_array.COUNT > 0 THEN
DBMS_OUTPUT.PUT_LINE('Los '||v_counter||' empleados que mas Ganan!!!');
--De no haber Registros el LOOP a continuación daría error.
--Esto porque los métodos FIRST y LAST tendrían valores nulos.
FOR i IN v_assoc_array.FIRST..v_assoc_array.LAST LOOP
DBMS_OUTPUT.PUT_LINE(v_assoc_array(i));
END LOOP;
END IF;
END;
/*Ahora modificamos el ejemplo anterior para hacerlo un poco mas complejo; En este usamos un CURSOR que busca los apellidos de todos los empleados de la tabla employes, la consulta la ordenados por salario de mayor a menor; luego usamos a un CURSOR FOR LOOP para iterar cada valor y así asignarlo a la variable: v_assoc_array(notar que limitamos la cantidad de LOOPs a solo 5); En el mismo ejemplo mostramos el uso de los métodos: COUNT, FIRST y LAST.*/
---OUTPUT:
SET SERVEROUTPUT ON
DECLARE
--CURSOR con los apellidos, salario, y salario promedio de su departamento
--de todos los empleados de la tabla employees:
CURSOR c_emp_name_sal IS
SELECT
e.last_name,
e.salary,
(
SELECT TRUNC(AVG(av.salary))
FROM hr.employees av
WHERE av.department_id = e.department_id
) AS avg_dept_sal
FROM hr.employees e
ORDER BY salary DESC;
--Definimos un Record con los campos similares a los del CURSOR: c_emp_name_sal
TYPE typ_emp_name_sal IS RECORD
(
name hr.employees.last_name%TYPE,
salary hr.employees.salary%TYPE,
avg_sal hr.employees.salary%TYPE
);
--Definición del Tipo Tabla:
TYPE typ_assoc_array IS TABLE OF
typ_emp_name_sal --typ_emp_name_sal es el Tipo Record antes creado.
INDEX BY PLS_INTEGER;
--Declaramos una Variable del tipo Tabla: typ_assoc_array
v_assoc_array typ_assoc_array;
v_counter NUMBER(3) := 0; ---Notar que ahora es 0
BEGIN
FOR i IN c_emp_name_sal LOOP --Primer LOOP
v_counter := v_counter+1;
--Notar a continuación como como el tipo: typ_assoc_array Hereda los campos de tipo: typ_emp_name_sal
v_assoc_array(v_counter).name := i.last_name;
v_assoc_array(v_counter).salary := i.salary;
v_assoc_array(v_counter).avg_sal := i.avg_dept_sal;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Colección: v_assoc_array. Después del Primer LOOP: ');
DBMS_OUTPUT.PUT_LINE('Primer Indice: '||v_assoc_array.FIRST);
DBMS_OUTPUT.PUT_LINE('Último Indice: '||v_assoc_array.LAST);
DBMS_OUTPUT.PUT_LINE('Cantidad de Registros en el Colección: '||v_assoc_array.COUNT);
--Validamos que haya registros en nuestra Colección:
IF v_assoc_array.COUNT > 0 THEN
--El siguiente LOOP es en reversa:
FOR i IN REVERSE v_assoc_array.FIRST..v_assoc_array.LAST LOOP --Segundo LOOP
--Con la siguiente validación aseguramos que la colección solo contenga los empleados con un salario
--menor al salario promedio de su departamento.
--Usamos el NLV ya que hay empleados sin departamento por lo cual
--v_assoc_array(v_counter).avg_sal seria NULL
IF v_assoc_array(v_counter).salary >= NVL(v_assoc_array(v_counter).avg_sal,0) THEN
v_assoc_array.DELETE(v_counter); --Elimina el indice equivalente al valor de v_counter
END IF;
v_counter := v_counter-1; --Le restamos para que esté acorde con el LOOP.
END LOOP;
DBMS_OUTPUT.PUT_LINE(CHR(10)||'Colección: v_assoc_array. Después del Segundo LOOP: ');
DBMS_OUTPUT.PUT_LINE('Primer Indice: '||v_assoc_array.FIRST);
DBMS_OUTPUT.PUT_LINE('Último Indice: '||v_assoc_array.LAST);
DBMS_OUTPUT.PUT_LINE('Cantidad de Registros en el Colección: '||v_assoc_array.COUNT);
DBMS_OUTPUT.PUT_LINE(CHR(10)||'Empleado || Salario || Promedio Dept || Indice');
--Como ahora nuestra Colección tiene huecos seria algo complicado recorrer
--cada valor mediante un LOOP FOR, es por ello que usamos un LOOP Simple:
v_counter := v_assoc_array.FIRST;
LOOP
DBMS_OUTPUT.PUT_LINE
(
RPAD(v_assoc_array(v_counter).name,14,' ')||
RPAD(v_assoc_array(v_counter).salary, 14,' ')||
RPAD(v_assoc_array(v_counter).avg_sal,19,' ')||
v_counter
);
v_counter := v_assoc_array.NEXT(v_counter); --Retorna el próximo Indice, NULL si no existe.
EXIT WHEN v_counter IS NULL;
END LOOP;
END IF;
END;
/*Como notan, explico el uso de cada sentencia a lo largo del código, por lo cual no dare muchos detalles aquí. Primero definimos un CURSOR que trae los empleados con sus salario y salario promedio de su departamento, Definimos un Tipo Record con campos equivalentes a los del CURSOR, posteriormente definimos un Tipo Colección con los campos del Tipo Record antes creado, por último definimos una variable del Tipo Colección antes definido. Ya en la sección de ejecución usamos un CURSOR FOR LOOP para asignar los valores del CURSOR a la variable Tipo Colección, luego usamos un LOOP FOR para eliminar los registros de la Colección que tengan un salario mayor o igual al salario promedio de su departamento. Notar el uso de los métodos: FIRST, LAST, COUNT, DELETE y NEXT.*/
---OUTPUT:
/*En la imagen se puede apreciar los cambios en la Colección a lo largo del SCRIPT.*/
---
SET SERVEROUTPUT ON
DECLARE
TYPE typ_string_assoc IS TABLE OF
VARCHAR2(15)
INDEX BY VARCHAR2(3);
v_string_assoc typ_string_assoc;
v_index VARCHAR2(3);
BEGIN
v_string_assoc('abc') := 'Primer Valor';
v_string_assoc('def') := 'Segundo Valor';
v_string_assoc('ao') := 'Tercer Valor';
v_string_assoc('xiz') := 'Último Valor';
v_index := v_string_assoc.FIRST;
WHILE(v_index IS NOT NULL) LOOP
DBMS_OUTPUT.PUT_LINE(v_string_assoc(v_index));
v_index := v_string_assoc.NEXT(v_index);
END LOOP;
END;
/*Finalmente mostramos un ejemplo de un una Matriz Asociativa con Indice VARCHAR2.*/
---OUTPUT:
____________________________________________________________________________________
Tablas Anidadas
Una Tabla Anidada(Nested Table) puede ser considerada como una tabla de una sola columna que puede ser alojada en memoria, aunque es prudente decir que también puede ser una columna en una tabla de Base de Datos.
Características:
Las Tablas Anidadas:
• Pueden ser declaradas en bloques de PL/SQL así como también en la Base de Datos.
• Al igual que las Matrices Asociativas(Associative Array), Las Tablas Anidadas tienen un tamaño dinámico y puede contener elementos vacíos, o sea, sus Indices no tiene que ser consecutivos.
• Deben ser Inicializadas antes de ser usadas. Una variable de Tabla Anidada no inicializada es una Colección nula.
• Para ser inicializadas es necesario hacer uso de su Constructor. Dicho Constructor es una función con el mismo nombre que el Tipo Colección, el cual devuelve una Colección de ese Tipo.
• A diferencia de las Matrices Asociativas, las Tablas Anidadas no pueden contener Indices negativos. Otro dato importante es que aunque se hace referencia a la primera columna como 'Indice', las Tablas Anidadas no tienen Indices, mas bien es una columna con números.
Sintaxis:
TYPE type_name IS TABLE OF
{column_type | variable%TYPE
| table.column%TYPE} [NOT NULL]
| table%ROWTYPE
Ejemplos:
Nota: Para fines de Comparación, presentaremos los mismos ejemplos que usamos para las Matrices Asociativas. Los OUTPUTS son los mismos, por tal razón no los presento a continuación.
SET SERVEROUTPUT ON
DECLARE
--Definición del Tipo Tabla:
TYPE typ_nest_tab IS TABLE OF
VARCHAR2(25);
--Declaramos una Variable del Tipo Tabla: typ_nest_tab
v_nest_tab typ_nest_tab;
BEGIN
--A continuación Inicializamos la variable Tipo Tabla: v_nest_tab, de esta forma podemos darle uso:
v_nest_tab := typ_nest_tab();
v_nest_tab.EXTEND; --EXTEND: Inserta un Registro Nulo a la Tabla.
v_nest_tab(1) := 'Valor para el Indice 1';
v_nest_tab.EXTEND;
v_nest_tab(2) := 'Valor para el Indice 2';
v_nest_tab.EXTEND;
v_nest_tab(3) := 'Valor para el Indice 3';
DBMS_OUTPUT.PUT_LINE(v_nest_tab(1)||', '||v_nest_tab(2)||', '||v_nest_tab(3));
END;
/*En este ejemplo se puede apreciar que las Matrices Asociativas y las Tablas Anidadas guardan bastante similitud en su implementación, la mayor diferencia esta en que las ultimas deben ser inicializadas antes de ser usadas y hay que invocar el método EXTEND para poder insertar nuevos registros a la Tabla.*/
---SET SERVEROUTPUT ON
DECLARE
--Definición del Tipo Tabla:
TYPE typ_nest_tab
IS TABLE OF VARCHAR2(25);
--Declaramos una Variable del Tipo Tabla:
v_nest_tab typ_nest_tab;
--CURSOR con los apellidos de todos los empleados de la tabla employees ordenados por salario de mayor a menor:
CURSOR c_emp_last_name IS
SELECT last_name
FROM hr.employees
ORDER BY salary DESC;
v_counter NUMBER(1) := 1;
BEGIN
--A continuación Inicializamos la variable Tipo Tabla: v_nest_tab, de esta forma podemos darle uso:
v_nest_tab := typ_nest_tab();
FOR i IN c_emp_last_name LOOP
v_nest_tab.EXTEND;
v_nest_tab(v_counter) := i.last_name;
EXIT WHEN c_emp_last_name%ROWCOUNT > 4; ---Limitamos el LOOP a solo 5 vueltas.
v_counter := v_counter+1;
END LOOP;
--Validamos que haya registros en nuestra Colección:
IF v_nest_tab.COUNT > 0 THEN
DBMS_OUTPUT.PUT_LINE('Los '||v_counter||' empleados que mas Ganan!!!');
--De no haber Registros el LOOP a continuación daría error.
--Esto porque los métodos FIRST y LAST tendrían valores nulos.
FOR i IN v_nest_tab.FIRST..v_nest_tab.LAST LOOP
DBMS_OUTPUT.PUT_LINE(v_nest_tab(i));
END LOOP;
END IF;
END;
/*Este ejemplo muestra una vez mas la implementación de las Tablas Anidadas.*/
---SET SERVEROUTPUT ON
DECLARE
--CURSOR con los apellidos, salario, y salario promedio de su departamento
--de todos los empleados de la tabla employees:
CURSOR c_emp_name_sal IS
SELECT
e.last_name,
e.salary,
(
SELECT TRUNC(AVG(av.salary))
FROM hr.employees av
WHERE av.department_id = e.department_id
) AS avg_dept_sal
FROM hr.employees e
ORDER BY salary DESC;
--Definimos un Record con los campos similares a los del CURSOR: c_emp_name_sal
TYPE typ_emp_name_sal IS RECORD
(
name hr.employees.last_name%TYPE,
salary hr.employees.salary%TYPE,
avg_sal hr.employees.salary%TYPE
);
--Definición del Tipo Tabla:
TYPE typ_nest_tab IS TABLE OF
typ_emp_name_sal; --typ_emp_name_sal es el Tipo Record antes creado.
--Declaramos una Variable del Tipo Tabla: typ_nest_tab
v_nest_tab typ_nest_tab;
v_counter NUMBER(3) := 0; ---Notar que ahora es 0
BEGIN
--A continuación Inicializamos la variable Tipo Tabla: v_nest_tab, de esta forma podemos darle uso:
v_nest_tab := typ_nest_tab();
FOR i IN c_emp_name_sal LOOP --Primer LOOP
v_counter := v_counter+1;
v_nest_tab.EXTEND;
--Notar a continuación como como el tipo: typ_nest_tab Hereda los campos de tipo: typ_emp_name_sal
v_nest_tab(v_counter).name := i.last_name;
v_nest_tab(v_counter).salary := i.salary;
v_nest_tab(v_counter).avg_sal := i.avg_dept_sal;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Colección: v_nest_tab. Después del Primer LOOP: ');
DBMS_OUTPUT.PUT_LINE('Primer Indice: '||v_nest_tab.FIRST);
DBMS_OUTPUT.PUT_LINE('Último Indice: '||v_nest_tab.LAST);
DBMS_OUTPUT.PUT_LINE('Cantidad de Registros en el Colección: '||v_nest_tab.COUNT);
--Validamos que haya registros en nuestra Colección:
IF v_nest_tab.COUNT> 0 THEN
--El siguiente LOOP es en reversa:
FOR i IN REVERSE v_nest_tab.FIRST..v_nest_tab.LAST LOOP --Segundo LOOP
--Con la siguiente validación aseguramos que la colección solo contenga los empleados con un salario
--menor al salario promedio de su departamento.
--Usamos el NLV ya que hay empleados sin departamento por lo cual
--v_nest_tab(v_counter).avg_sal seria NULL
IF v_nest_tab(v_counter).salary >= NVL(v_nest_tab(v_counter).avg_sal,0) THEN
v_nest_tab.DELETE(v_counter); --Elimina el indice equivalente al valor de v_counter
END IF;
v_counter := v_counter-1; --Le restamos para que esté acorde con el LOOP.
END LOOP;
DBMS_OUTPUT.PUT_LINE(CHR(10)||'Colección: v_nest_tab. Después del Segundo LOOP: ');
DBMS_OUTPUT.PUT_LINE('Primer Indice: '||v_nest_tab.FIRST);
DBMS_OUTPUT.PUT_LINE('Último Indice: '||v_nest_tab.LAST);
DBMS_OUTPUT.PUT_LINE('Cantidad de Registros en el Colección: '||v_nest_tab.COUNT);
DBMS_OUTPUT.PUT_LINE(CHR(10)||'Empleado || Salario || Promedio Dept || Indice');
--Como ahora nuestra Colección tiene huecos seria algo complicado recorrer
--cada valor mediante un LOOP FOR, es por ello que usamos un LOOP Simple:
v_counter := v_nest_tab.FIRST;
LOOP
DBMS_OUTPUT.PUT_LINE
(
RPAD(v_nest_tab(v_counter).name,14,' ')||
RPAD(v_nest_tab(v_counter).salary, 14,' ')||
RPAD(v_nest_tab(v_counter).avg_sal,19,' ')||
v_counter
);
v_counter := v_nest_tab.NEXT(v_counter); --Retorna el próximo Indice, NULL si no existe.
EXIT WHEN v_counter IS NULL;
END LOOP;
END IF;
END;
/*Como había señalado, los OUTPUTS de los ejemplos con las Tablas Anidadas son los mismos que con las Matrices Asociativas, por tal razón no los presento.*/
____________________________________________________________________________________Los VARRAYs
VARRAY significa Matriz de Tamaño Variable(Variable-Size Array). Los VARRAYs con colecciones que guardan mas similitud con las Tablas Anidadas que con las Matrices Asociativas. A continuación detallamos sus características:
Características:
Los VARRAYs:
• Al igual que las Tablas Anidadas pueden ser declaradas en bloques de PL/SQL así como también en la Base de Datos.
• A diferencia de las Matrices Asociativas y las Tablas Anidadas los VARRAYs no tienen un tamaño dinámico, a estos últimos hay que especificarles el limite máximo al momento de su declaración.
• Deben ser Inicializadas antes de ser usadas(Al igual que las Tablas Anidadas). Una variable Tipo VARRAY no inicializada es una Colección nula.
• Para ser inicializadas es necesario hacer uso de su Constructor. Dicho Constructor es una función con el mismo nombre que el Tipo Colección, el cual devuelve una Colección de ese Tipo.
• Los Indices siempre son secuenciales/consecutivos, esto significa que no es posible eliminar elementos del centro de un VARRAY, solo es posible eliminarlos del finar de la Colección (con el método TRIM). Los Indices no pueden ser negativos siendo 1 el limite inferior.
En Síntesis:
Un VARRAY es una Matriz cuyo número de elementos puede variar de cero (vacío) hasta el tamaño máximo establecido. Para acceder a los elementos de una variable VARRAY, utilice la sintaxis variable_name(index). El límite inferior del índice es 1; El límite superior es el número actual de elementos. El límite superior cambia a medida que agrega o elimina elementos, pero no puede exceder el tamaño máximo.
Ejemplos:
Nota: Los dos primeros ejemplos a continuación tienen la misma implemenacion y OUTPUTS que sus equivalentes presentados anteriormente para las Matrices Asociativas y las Tablas Anidadas, ya en el tercer ejemplo tuvimos que cambiar la lógica del SCRIPT, esto por lo ya explicado: como los VARRAYs no pueden tener huecos entre sus elementos, no es posible usar el método DELETE.
SET SERVEROUTPUT ON
DECLARE
--Definición del Tipo Tabla:
TYPE typ_varray IS VARRAY(3) OF --Este VARRAY puede contener máximo 3 registros.
VARCHAR2(25);
--Declaramos una Variable del Tipo Tabla: typ_varray
v_varray typ_varray;
BEGIN
--A continuación Inicializamos la variable Tipo Tabla: v_varray, de esta forma podemos darle uso:
v_varray := typ_varray();
v_varray.EXTEND; --EXTEND: Inserta un Registro Nulo a la Tabla.
v_varray(1) := 'Valor para el Indice 1';
v_varray.EXTEND;
v_varray(2) := 'Valor para el Indice 2';
v_varray.EXTEND;
v_varray(3) := 'Valor para el Indice 3';
DBMS_OUTPUT.PUT_LINE(v_varray(1)||', '||v_varray(2)||', '||v_varray(3));
END;
/
SET SERVEROUTPUT ON
DECLARE
--Definición del Tipo Tabla:
TYPE typ_varray
IS VARRAY(5) OF VARCHAR2(25); --Este VARRAY puede contener máximo 5 registros.
--Declaramos una Variable del Tipo Tabla:
v_varray typ_varray;
--CURSOR con los apellidos de todos los empleados de la tabla employees ordenados por salario de mayor a menor:
CURSOR c_emp_last_name IS
SELECT last_name
FROM hr.employees
ORDER BY salary DESC;
v_counter NUMBER(1) := 1;
BEGIN
--A continuación Inicializamos la variable Tipo Tabla: v_varray, de esta forma podemos darle uso:
v_varray := typ_varray();
FOR i IN c_emp_last_name LOOP
v_varray.EXTEND;
v_varray(v_counter) := i.last_name;
EXIT WHEN c_emp_last_name%ROWCOUNT > 4; ---Limitamos el LOOP a solo 5 vueltas.
v_counter := v_counter+1;
END LOOP;
--Validamos que haya registros en nuestra Colección:
IF v_varray.COUNT > 0 THEN
DBMS_OUTPUT.PUT_LINE('Los '||v_counter||' empleados que mas Ganan!!!');
--De no haber Registros el LOOP a continuación daría error.
--Esto porque los métodos FIRST y LAST tendrían valores nulos.
FOR i IN v_varray.FIRST..v_varray.LAST LOOP
DBMS_OUTPUT.PUT_LINE(v_varray(i));
END LOOP;
END IF;
END;
/
---OUTPUT:
En Síntesis:
Un VARRAY es una Matriz cuyo número de elementos puede variar de cero (vacío) hasta el tamaño máximo establecido. Para acceder a los elementos de una variable VARRAY, utilice la sintaxis variable_name(index). El límite inferior del índice es 1; El límite superior es el número actual de elementos. El límite superior cambia a medida que agrega o elimina elementos, pero no puede exceder el tamaño máximo.
Ejemplos:
Nota: Los dos primeros ejemplos a continuación tienen la misma implemenacion y OUTPUTS que sus equivalentes presentados anteriormente para las Matrices Asociativas y las Tablas Anidadas, ya en el tercer ejemplo tuvimos que cambiar la lógica del SCRIPT, esto por lo ya explicado: como los VARRAYs no pueden tener huecos entre sus elementos, no es posible usar el método DELETE.
SET SERVEROUTPUT ON
DECLARE
--Definición del Tipo Tabla:
TYPE typ_varray IS VARRAY(3) OF --Este VARRAY puede contener máximo 3 registros.
VARCHAR2(25);
--Declaramos una Variable del Tipo Tabla: typ_varray
v_varray typ_varray;
BEGIN
--A continuación Inicializamos la variable Tipo Tabla: v_varray, de esta forma podemos darle uso:
v_varray := typ_varray();
v_varray.EXTEND; --EXTEND: Inserta un Registro Nulo a la Tabla.
v_varray(1) := 'Valor para el Indice 1';
v_varray.EXTEND;
v_varray(2) := 'Valor para el Indice 2';
v_varray.EXTEND;
v_varray(3) := 'Valor para el Indice 3';
DBMS_OUTPUT.PUT_LINE(v_varray(1)||', '||v_varray(2)||', '||v_varray(3));
END;
/
SET SERVEROUTPUT ON
DECLARE
--Definición del Tipo Tabla:
TYPE typ_varray
IS VARRAY(5) OF VARCHAR2(25); --Este VARRAY puede contener máximo 5 registros.
--Declaramos una Variable del Tipo Tabla:
v_varray typ_varray;
--CURSOR con los apellidos de todos los empleados de la tabla employees ordenados por salario de mayor a menor:
CURSOR c_emp_last_name IS
SELECT last_name
FROM hr.employees
ORDER BY salary DESC;
v_counter NUMBER(1) := 1;
BEGIN
--A continuación Inicializamos la variable Tipo Tabla: v_varray, de esta forma podemos darle uso:
v_varray := typ_varray();
FOR i IN c_emp_last_name LOOP
v_varray.EXTEND;
v_varray(v_counter) := i.last_name;
EXIT WHEN c_emp_last_name%ROWCOUNT > 4; ---Limitamos el LOOP a solo 5 vueltas.
v_counter := v_counter+1;
END LOOP;
--Validamos que haya registros en nuestra Colección:
IF v_varray.COUNT > 0 THEN
DBMS_OUTPUT.PUT_LINE('Los '||v_counter||' empleados que mas Ganan!!!');
--De no haber Registros el LOOP a continuación daría error.
--Esto porque los métodos FIRST y LAST tendrían valores nulos.
FOR i IN v_varray.FIRST..v_varray.LAST LOOP
DBMS_OUTPUT.PUT_LINE(v_varray(i));
END LOOP;
END IF;
END;
/
SET SERVEROUTPUT ON
DECLARE
--CURSOR con los apellidos, salario, y salario promedio de su departamento
--de todos los empleados de la tabla employees:
CURSOR c_emp_name_sal IS
SELECT
e.last_name,
e.salary,
(
SELECT TRUNC(AVG(av.salary))
FROM hr.employees av
WHERE av.department_id = e.department_id
) AS avg_dept_sal
FROM hr.employees e
ORDER BY salary DESC;
--Definimos un Record con los campos similares a los del CURSOR: c_emp_name_sal.
TYPE typ_emp_name_sal IS RECORD
(
name hr.employees.last_name%TYPE,
salary hr.employees.salary%TYPE,
avg_sal hr.employees.salary%TYPE
);
--Definición del Tipo Tabla:
TYPE typ_varray IS VARRAY(300) OF
typ_emp_name_sal; --typ_emp_name_sal es el Tipo Record antes creado.
--Declaramos una Variable del Tipo Tabla: typ_varray
v_varray typ_varray;
v_counter NUMBER(3) := 0; ---Notar que ahora es 0
BEGIN
--A continuación Inicializamos la variable Tipo Tabla: v_varray, de esta forma podemos darle uso:
v_varray := typ_varray();
FOR i IN c_emp_name_sal LOOP
v_counter := v_counter+1;
v_varray.EXTEND;
--Notar a continuación como como el tipo: typ_varray Hereda los campos de tipo: typ_emp_name_sal
v_varray(v_counter).name := i.last_name;
v_varray(v_counter).salary := i.salary;
v_varray(v_counter).avg_sal := i.avg_dept_sal;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Limite del VARRAY: '||v_varray.LIMIT);
DBMS_OUTPUT.PUT_LINE('Primer Indice: '||v_varray.FIRST);
DBMS_OUTPUT.PUT_LINE('Último Indice: '||v_varray.LAST);
DBMS_OUTPUT.PUT_LINE('Cantidad de Registros en el Colección: '||v_varray.COUNT);
DBMS_OUTPUT.PUT_LINE(CHR(10)||'Empleado || Salario || Promedio Dept || Indice');
--Validamos que haya registros en nuestra Colección:
IF v_varray.COUNT > 0 THEN
--Ya el LOOP no es en reversa:
FOR i IN v_varray.FIRST..v_varray.LAST LOOP
--Con la siguiente validación mostramos solo los empleados con un salario
--menor al salario promedio de su departamento.
--Usamos el NLV ya que hay empleados sin departamento por lo cual
--v_varray(v_counter).avg_sal seria NULL
IF v_varray(i).salary < NVL(v_varray(i).avg_sal,0) THEN
DBMS_OUTPUT.PUT_LINE
(
RPAD(v_varray(i).name,14,' ')||
RPAD(v_varray(i).salary, 14,' ')||
RPAD(v_varray(i).avg_sal,19,' ')||
i
);
END IF;
END LOOP;
END IF;
END;
/*Como habíamos señalado, la condición de los VARRAYS nos obligo a tener que cambiar la lógica del SCRIPT; ahora en lugar de eliminar los registros con salario igual o mayor al salario promedio de su departamento, procedimos a omitirlos para que no se muestren por pantalla. Notar que el modo de implementacion es muy similar al de las Tablas Anidadas: es necesario invocar su Constructor y hacer uso del método EXTEND para asignar registros al mismo. En este ejemplo también vemos como usamos el método LIMIT, recuerden que si se trata de insertar mas registros del limite, se levantaría la Excepción: SUBSCRIPT_OUTSIDE_LIMIT.*/---OUTPUT:
Para no extender mas la publicación decidimos presentar mas ejemplos relacionados con en el tema en otra entrega. Click Aquí.
_____________________________________________________________________________________
