PLSQL Básico2

Más ejemplos sobre PLSQL, en este post veamos como podemos crear procedimientos que utilicen arrays,collections y estructuras de datos derivadas de estos

Ejemplo 1: Muestra el uso de arrays asociativos
Definimos un tipo de la forma: TYPE miHashType IS TABLE OF NUMBER INDEX BY VARCHAR2(6);
La clausula TYPE especifica que vamos a definir un nuevo tipo.
El código miHashType es el nombre del nuevo tipo, es como una nueva clase
La clausula IS TABLE OF NUMBER indica que el arreglo asociativo va a tener valores de tipo NUMBER
La clausula BY VARCHAR2(5) indica que los valores de los indices van a ser del tipo VARCHAR2(6)

CREATE OR REPLACE PROCEDURE collection01 IS 

TYPE miHashType IS TABLE OF NUMBER INDEX BY VARCHAR2(6);
arreglo1 miHashType;
arreglo2 miHashType;

TYPE miEmployeeType IS TABLE OF employees%rowtype INDEX BY PLS_INTEGER;
empleadosArray miEmployeeType;

-- Los tipos anidados pueden almacenar un número ilimitado de elementos
TYPE miTableType IS TABLE OF VARCHAR2(20);
arregloV1 miTableType;
arregloV2 miTableType;

-- Los tipos varray tienen un número máximo de elementos
TYPE miArregloType IS VARRAY(4) OF VARCHAR2(10);
varreglo1 miArregloType;
varreglo2 miArregloType;

BEGIN
  -- Agregamos 3 entradas al primer arreglo:
  arreglo1('clave1') := 666;
  arreglo1('clave2') := 667;
  arreglo1('clave3') := 668;
  
  -- Agregamos 3 entradas al primer arreglo:
  arreglo2('clave1') := 100;
  arreglo2('clave2') := 101;
  arreglo2('clave3') := 102;
  arreglo2('clave4') := 103;
  arreglo2('clave5') := 104;
  arreglo2('clave6') := 105;
  
  -- Ahora mostramos los valores deacuerdo a su clave:
  dbms_output.put_line('El valor del arreglo asociativo arreglo2(''clave2''):'||arreglo2('clave2'));
  
  -- Obtenemos los datos de un empleado:
  SELECT * INTO empleadosArray(100) FROM employees WHERE employee_id = 100;
  dbms_output.put_line('Nombre del empleado: '||empleadosArray(100).first_name);
  dbms_output.put_line('Apellidos del empleado: '||empleadosArray(100).last_name);
  dbms_output.put_line('Email del empleado: '||empleadosArray(100).email);
  
  -- Ahora asignamos los valores a los arreglos:
  arregloV1 := miTableType('carlos','alberto','cacique','yupanqui','certificado','en','java','y','oracle');
  arregloV2 := miTableType('zaida','angelica');
  varreglo1 := miArregloType('uno','dos');
  -- Con el metodo EXTEND extendemos el arreglo dinamico, el primer argument 
  -- especifica cuantos elementos más creamos y el segundo el valor que se va a 
  -- copiar de los elementos existentes a los elementos nuevos
  varreglo1.EXTEND(2,1);
  --varreglo1(3) := 'tres';
  --varreglo1(4) := 'cuatro';
  varreglo2 := miArregloType('primero','segundo','tercero','cuarto');
  
  -- Ahora mostramos los valores de los tipos anidados;
  FOR i IN arregloV1.FIRST..arregloV1.LAST LOOP
   dbms_output.put_line('arregloV1('||i||'):'||arregloV1(i));
  END LOOP;
  FOR i IN arregloV2.FIRST..arregloV2.LAST LOOP
     dbms_output.put_line('arregloV2('||i||'):'||arregloV2(i));
  END LOOP;
  
  
  -- Ahora mostramos los valores de los tipos varrays
  FOR i IN varreglo1.FIRST..varreglo1.LAST LOOP
     dbms_output.put_line('varreglo1('||i||'):'||varreglo1(i));
  END LOOP;
  FOR i IN varreglo2.FIRST..varreglo2.LAST LOOP
       dbms_output.put_line('varreglo2('||i||'):'||varreglo2(i));
  END LOOP;
  
END collection01;

Ejemplo 2:
Ahora veremos como declarar un tipo complejo con TYPE... IS RECORD, despues creamos un tipo anidado con el tipo creado anteriormente, una vez creado el tipo anidado, creamos una variable del tipo anidado, todo esto para poder mantener los datos que más tarde nos retornará un cursor.
Después declaramos un tipo cursor y creamos una variable, más adelante en el cuerpo del procedimiento abrimos el cursor para un SELECT específico y cargamos la variable coleccion con los datos del cursor por medio de la clausula FETCH <cursor> BULK COLLECTION INTO <coleccion>, al final del procedimiento solo mostramos los datos pero desde la colección.

CREATE OR REPLACE PROCEDURE collection02 IS

  -- Declaramos un tipo de dato complejo
  TYPE emp_name_rec IS RECORD (
    firstname    employees.first_name%TYPE,
    lastname     employees.last_name%TYPE,
    hiredate     employees.hire_date%TYPE
  );
    
  -- Tipo Tabla que puede mantener información acerca de los empleados
  TYPE EmpList_tab IS TABLE OF emp_name_rec;
  SeniorSalespeople EmpList_tab;   
   
  -- Declaramos una referencia a un cursor para seleccionar un subconjunto de columnas.
  EndCounter NUMBER := 10;
  TYPE EmpCurTyp IS REF CURSOR;
  emp_cv EmpCurTyp; 
   
BEGIN
  -- Abrimos el cursor con la sentencia SELECT indicada
  OPEN emp_cv FOR SELECT first_name, last_name, hire_date FROM employees 
  WHERE job_id = 'SA_REP' ORDER BY hire_date;

  -- Sacamos los datos del cursor y los colocamos dentro de la coleccion
  FETCH emp_cv BULK COLLECT INTO SeniorSalespeople;
  
  -- Cerramos el cursor
  CLOSE emp_cv;

  -- Para este ejemplo mostramos un máximo de 10 empleados
  IF SeniorSalespeople.LAST > 0 THEN
    IF SeniorSalespeople.LAST < 10 THEN EndCounter := SeniorSalespeople.LAST; 
    END IF;
    FOR i in 1..EndCounter LOOP
      DBMS_OUTPUT.PUT_LINE(SeniorSalespeople(i).lastname || ', ' 
      || SeniorSalespeople(i).firstname || ', ' || SeniorSalespeople(i).hiredate);
    END LOOP;
  END IF;
END collection02;

Ejemplo 3: En este ejemplo se muestra la eficiencia de la clausula FORALL a diferencia de la clausula FOR LOOP con tipos anidados:

CREATE OR REPLACE PROCEDURE COLLECTION03 IS
  -- Definimos des tipos de datos anidados
  TYPE NumTab IS TABLE OF parts1.pnum%TYPE INDEX BY PLS_INTEGER;
  TYPE NameTab IS TABLE OF parts1.pname%TYPE INDEX BY PLS_INTEGER;
  -- Declaramos variables de los tipos definidos anteriormente
  pnums  NumTab;
  pnames NameTab;
  -- Cuantas veces vamos a iterar
  iterations CONSTANT PLS_INTEGER := 10000;
  -- Variables que nos van a ayudar a medir el tiempo
  t1 INTEGER;
  t2 INTEGER;
  t3 INTEGER;
BEGIN
  FOR j IN 1..iterations LOOP  -- load index-by tables
     pnums(j) := j;
     pnames(j) := 'Part No. ' || TO_CHAR(j);
  END LOOP;
  t1 := DBMS_UTILITY.get_time;
  FOR i IN 1..iterations LOOP  -- use FOR loop
     INSERT INTO parts1 VALUES (pnums(i), pnames(i));
  END LOOP;
  t2 := DBMS_UTILITY.get_time;
  FORALL i IN 1..iterations  -- use FORALL statement
     INSERT INTO parts2 VALUES (pnums(i), pnames(i));
  t3 := DBMS_UTILITY.get_time;
  DBMS_OUTPUT.PUT_LINE('Tiempo de ejecución en segundos');
  DBMS_OUTPUT.PUT_LINE('---------------------');
  DBMS_OUTPUT.PUT_LINE('FOR LOOP: ' || TO_CHAR((t2 - t1)));
  DBMS_OUTPUT.PUT_LINE('FORALL:   ' || TO_CHAR((t3 - t2)));
  COMMIT;
END COLLECTION03;

Ejemplo 4: Ejemplo de uso de operaciones de comparacion con operadores SET:

CREATE OR REPLACE PROCEDURE COLLECTION04 IS

  TYPE nested_typ IS TABLE OF NUMBER;
    nt1 nested_typ := nested_typ(1,2,3);
    nt2 nested_typ := nested_typ(3,2,1);
    nt3 nested_typ := nested_typ(2,3,1,3);
    nt4 nested_typ := nested_typ(1,2,4);
    respuesta BOOLEAN;
    cantidad NUMBER;
    
    -- Definimos un procedimiento que mostrará una cadena de caracteres y una cantidad
    PROCEDURE imprimir(truth BOOLEAN DEFAULT NULL, quantity NUMBER DEFAULT NULL) IS
    BEGIN
      IF truth IS NOT NULL THEN
        DBMS_OUTPUT.PUT_LINE(CASE truth WHEN TRUE THEN 'True' WHEN FALSE THEN 'False' END);
      END IF;
      IF quantity IS NOT NULL THEN
          DBMS_OUTPUT.PUT_LINE(quantity);
      END IF;
    END;
  BEGIN
    -- La variable anidada nt1 se encuentra en el subconjunto especificado por IN?
    -- Sí, nt1 coincide con nt2 que se encuentra en la clausula IN
    respuesta := nt1 IN (nt2,nt3,nt4); 
    imprimir(truth => respuesta);
    
    -- La variable anidada nt1 es un subconjunto de la variable nt3
    -- Sí, todos los elementos coinciden
    respuesta := nt1 SUBMULTISET OF nt3; 
    imprimir(truth => respuesta);
    
    -- La variable anidada no es un subconjunto de la variable anidada nt4?
    -- Sí, la variable NO lo es.
    respuesta := nt1 NOT SUBMULTISET OF nt4;
    imprimir(truth => respuesta);
    
    -- CARDINALITY retorna el numero de elementos de la variable anidada nt3
    cantidad := CARDINALITY(nt3);
    imprimir(quantity => cantidad);
    
    -- CARDINALITY(SET(?)) muestra el número de elementos distintos en la variable
    cantidad := CARDINALITY(SET(nt3));
    imprimir(quantity => cantidad);
    
    -- El elemento con valor 4 es un miembro de la variable nt1?
    -- No, ese elemento no existe en la variable anidada.
    respuesta := 4 MEMBER OF nt1;
    imprimir(truth => respuesta);
    
    -- La variable nt3 es un SET, es decir no tiene duplicados?
    -- NO, la variable tiene duplicados, por lo tanto no es un SET
    respuesta := nt3 IS A SET;
    imprimir(truth => respuesta);
    
    -- La variable nt3 no es un SET?
    -- Sí dicha variable no es un SET porque contiene elementos duplicados.
    respuesta := nt3 IS NOT A SET;
    imprimir(truth => respuesta);
    
    -- La variable nt1 no tiene elementos?
    -- falso, dicha variable si tiene elementos.
    respuesta := nt1 IS EMPTY;
    imprimir(truth => respuesta);
   
END COLLECTION05;

Ejemplo 5: Operaciones con los diferentes métodos implícitos de la colecciones.

• lista.EXIST(n), lista, es una variable de tipo anidada o varray y n es el indice de un elemento en la lista
  
• lista.COUNT, retorna el número de elementos que la lista actualmente contiene.
  
• lista.LIMIT, retorna el tamaño máximo de la tabla anidada.
  
• lista.TRIM, remueve un elemento del final de la lista, si le especificasmo un argumentoentonces removerá la cantidad especificada.
  

CREATE OR REPLACE PROCEDURE COLLECTION05 IS

  -- Definimos un tipo anidado de enteros
  TYPE listaNumerosType IS TABLE OF INTEGER;
  lista listaNumerosType := listaNumerosType(1,3,5,7);
   
  BEGIN
   dbms_output.put_line('Cantidad de elementos de la lista 1:'||lista.COUNT);
   -- Borramos el segundo elemento:
   lista.DELETE(2); 
   dbms_output.put_line('Cantidad de elementos de la lista 2:'||lista.COUNT);
   
   -- Si en la lista existe el elemento con indice 1 entonces hacer:
   IF lista.EXISTS(1) THEN
      DBMS_OUTPUT.PUT_LINE('El element #1 existe.');
   END IF;
   -- Si en la lista no existe el elemento con indice 2 entonces hacer
   IF lista.EXISTS(2) = FALSE THEN
      DBMS_OUTPUT.PUT_LINE('El elemento #2 ha sido borrado.');
   END IF;
   
   -- Agregamos un par de elementos más a la lista y volvemos a hacer COUNT
   lista.EXTEND(5);
   lista(5) := 9;
   dbms_output.put_line('Cantidad de elementos de la lista 3:'||lista.COUNT);
   dbms_output.put_line('Limite de elementos de la lista :'||lista.LIMIT);
   -- Removera 2 elementos del final de la coleccion, ojo que TRIM solo removerá uno
   lista.TRIM(2);
   dbms_output.put_line('Cantidad de elementos de la lista despues de TRIM:'||lista.COUNT);
   dbms_output.put_line('Limite de elementos de la lista despues de TRIM:'||lista.LIMIT);
   
   -- Usando los metodos first, prior, next y last
   dbms_output.put_line('lista.FIRST:'||lista.FIRST);
   dbms_output.put_line('lista.LAST:'||lista.LAST);
   dbms_output.put_line('lista.PRIOR(2):'||lista.PRIOR(2));
   dbms_output.put_line('lista.NEXT(2):'||lista.NEXT(2));
   
END COLLECTION05;