Lunes, 08 Junio 2020 01:41

Las Colecciones de PL/SQL

Rate this item
(0 votes)

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.

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: CC++ 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 (VARCHAR2NUMBER, etc), las Colecciones pueden ser usadas como parámetros de entrada i/o salida de Procedimientos 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 CompuestosClick 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 AsociativasCOUNT 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 es nulo, DELETE(n) no hace nada.

DELETE(mn) elimina todos los elementos del rango m..n. Si es mayor que n o si es nulo, DELETE(mn) no hace nada.

EXISTS

     EXISTS(n) devuelve TRUE si existe el elemento 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 elementos nulos.

EXTEND(ni) agrega 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 VARRAYsFIRST 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 VARRAYsLAST siempre es igual a COUNT. Para las Tablas Anidadas y las Matrices AsociativasLAST 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 VARRAYsLIMIT 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 VARRAYs

Este procedimiento tiene dos formas:

TRIM elimina un elemento del final de una Colección.

TRIM(n) elimina elementos del final de una Colección. Si es mayor que COUNTTRIM(n) eleva la excepción SUBSCRIPT_BEYOND_COUNTTRIM 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 lasColecciones no se pueden ser invocados desde Sentencias SQL.
  • Sólo es posible usar el métodoEXISTS en Colecciones nulas. Si aplica otro método a dichas Colecciones, se eleva la excepción: COLLECTION_IS_NULL.
  • Los métodos EXISTS,COUNTLIMITFIRSTLASTPRIOR NEXT son funciones; EXTENDTRIM 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 (PaquetesFuncionesProcedimientos 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ónv_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 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 definimos 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:

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 Tablatyp_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:

Simple Associative Array 1

 

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: COUNTFIRST LAST.*/

---OUTPUT:

Simple Associative Array 2

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:

Associative Array 1

/*En la imagen se puede apreciar los cambios en la Colección a lo largo del SCRIPT.*/

 

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:

TYPE typ string assoc


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 dePL/SQL así como también en la Base de Datos.
  • Al igual que lasMatrices Asociativas(Associative Array), Las Tablas Anidadastienen 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 unaColección nula.
  • Para ser inicializadas es necesario hacer uso de suConstructor.  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.

 

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.*/

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.*/

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 Anidadaspueden ser declaradas en bloques de PL/SQL así como también en la Base de Datos.
  • A diferencia de las Matrices Asociativasy las Tablas Anidadas losVARRAYs 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 Constructores 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 unVARRAY, 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.

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;

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;
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:

VARRAY

Para no extender mas la publicación decidimos presentar mas ejemplos relacionados con en el tema en otra entrega. Click Aquí.


Fuentes: Oracle Database: PL/SQL Fundamentals
https://docs.oracle.com/cloud/latest/db112/LNPLS/composites.htm#LNPLS005
https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/collections.htm#i27396
https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/collection_method.htm

Read 3513 times Last modified on Martes, 11 Agosto 2020 15:42

Leave a comment

Make sure you enter all the required information, indicated by an asterisk (*). HTML code is not allowed.

Magic PL/SQL

Blog orientado al desarrollo de PL / SQL en el "Maravilloso Mundo ORACLE". Cursos Online y Tutoriales Gratis de Manejo de Base de Datos. Aprende a programar, a crear aplicaciones para empresas y negocios y gana dinero. Hosting y Tecnología.