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;

PLSQL Básico1

Encontré varios ejemplos de hace varios años cuando aprendía plsql, allí les dejo algunos ejemplitos, ojalá les sirvan:

Ejemplo 1: Lo unico que hace es mostrar una linea en consola.

CREATE OR REPLACE PROCEDURE proc01 IS
BEGIN
  dbms_output.put_line('Hola mundo con los procedimientos de Oracle');
END;

Ejemplo 2: Igual que el anterior pero ahora la ultima línea finaliza con el nombre del procedimiento... buena practica!

CREATE OR REPLACE PROCEDURE proc02 IS
BEGIN
  dbms_output.put_line('Otro procedimiento pero ahora con otro texto');
END proc02;

Ejemplo 3: Uso de la clausula %TYPE y %ROWTYPE, la clausula %TYPE sirve para asignar los tipos de datos heredados de otras variables en tiempo de ejecución lo mismo para %ROWTYPE que actua a nivel de fila

CREATE OR REPLACE PROCEDURE proc03 IS
  v_fecha_sistema DATE;
  v_fecha_contratacion v_fecha_sistema%TYPE;
  v_salario employees.salary%TYPE;
  reg_empleados employees%ROWTYPE; 
BEGIN
  SELECT sysdate INTO v_fecha_sistema FROM dual;
  SELECT * INTO reg_empleados FROM employees WHERE employee_id=200;
  v_salario := reg_empleados.salary;
  v_fecha_contratacion := reg_empleados.hire_date;
  dbms_output.put_line('El nombre del empleado es:'||reg_empleados.first_name);
  dbms_output.put_line('El salario del empleado es:'||v_salario);
  dbms_output.put_line('La fecha de contratación del empleado es:'||v_fecha_contratacion);
  dbms_output.put_line('La fecha del sistema es:'||v_fecha_sistema);
END proc03;

Ejemplo 4: Muestra el uso de etiquetas para resolucion de nombres de variables, analiza el ámbito de la variable v_cantidad

CREATE OR REPLACE PROCEDURE proc04 IS
BEGIN
  <<ambito_externo>>
  DECLARE 
  v_cantidad NUMBER := 100;
  BEGIN
    DECLARE
    v_cantidad NUMBER := 150;
    BEGIN
    -- Inicio de un bloque de codigo interno
    dbms_output.put_line('Valor de la variable interna:'||v_cantidad);
    dbms_output.put_line('Valor de la variable externa:'||ambito_externo.v_cantidad);
    END;
    -- Aqui podemos continuar con los datos de la variable etiquetada externamente
    dbms_output.put_line('Valor de la variable:'||v_cantidad);
  END;
END proc04;

Ejemplo 5: Podemos crear funciones dentro del procedimiento y usarlas en el mismo procedimiento.

CREATE OR REPLACE PROCEDURE proc05 IS
  
  -- Funcion sumar:
  FUNCTION sumar(ope1 IN NUMBER,ope2 IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN ope1+ope2;
  END sumar;
  
  -- Funcion restar:
  FUNCTION restar(ope1 IN NUMBER,ope2 IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN ope1-ope2;
  END restar;
  
  -- Funcion multiplicar:
  FUNCTION multiplicar(ope1 IN NUMBER,ope2 IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN ope1*ope2;
  END multiplicar;
  
  -- Funcion dividir:
  FUNCTION dividir(ope1 IN NUMBER,ope2 IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN ope1/ope2;
  END dividir;
  
BEGIN
  dbms_output.put_line('Suma de 15 y 45: '||sumar(15,45));
  dbms_output.put_line('Resta de 15 y 45: '||restar(15,45));
  dbms_output.put_line('Multiplicacion de 15 y 45: '||multiplicar(15,45));
  dbms_output.put_line('Division de 15 y 45: '||dividir(15,45));
END proc05;

Ejemplo 6: Muestra el uso de las estructuras de control condicional IF-THEN e IF-THEN-ELSE

CREATE OR REPLACE
PROCEDURE proc06(vi_cantidad_minima IN INTEGER) IS
  v_sueldo_base NUMBER := 4000;
  v_cantidad_empleados INTEGER := 0;
BEGIN
  --Obtenemos la cantidad de empleados que tienen un sueldo mayor al sueldo base
  SELECT COUNT(employee_id) INTO v_cantidad_empleados FROM employees 
  WHERE salary > v_sueldo_base;
  
  -- Testeamos si la cantidad de empleados es mayor a una constante
  dbms_output.put_line('La cantidad de empleados es:'||v_cantidad_empleados); 
  dbms_output.put_line('-----------------------------------------------------'); 
  dbms_output.put_line('Uso de las estructura if-then'); 
  IF v_cantidad_empleados > vi_cantidad_minima THEN
    dbms_output.put_line('La cantidad de empleados:'||v_cantidad_empleados||', ES MAYOR a la minima');  
  END IF;
  
  dbms_output.put_line('Uso de las estructura if-then-else'); 
  IF v_cantidad_empleados > vi_cantidad_minima THEN
    dbms_output.put_line('La cantidad de empleados:'||v_cantidad_empleados||', ES MAYOR a la minima');  
  ELSE
    dbms_output.put_line('La cantidad de empleados:'||v_cantidad_empleados||', NO ES MAYOR a la minima');  
  END IF;
  
END proc06;

Ejemplo 7: Muestra el uso de las estructuras de control iterativas LOOP y EXIT

CREATE OR REPLACE
PROCEDURE proc07(vi_cantidad_iteraciones IN INTEGER) IS
  v_contador1 INTEGER := 0;
  v_contador2 INTEGER := 0;
BEGIN
  -- Primero con una declaración EXIT para salir de un bucle.
  dbms_output.put_line('Iteracion con clausula EXIT');
  LOOP
    IF v_contador1 = vi_cantidad_iteraciones THEN
      EXIT;
    ELSE
      dbms_output.put_line('Iterando con EXIT');    
    END IF;
    v_contador1 := v_contador1 + 1;
  END LOOP;
  
  --Ahora iteramos pero con la clausula EXIT WHEN
  v_contador1 := 0;
  dbms_output.put_line('Iteracion con la clausula EXIT WHEN');
  LOOP
  v_contador1 := v_contador1 + 1;
  dbms_output.put_line('Iterando con EXIT WHEN');    
    EXIT WHEN v_contador1 >= 20;
  END LOOP;
  
  --Ahora iteramos con la clausula EXIT WHEN con etiquetas
  v_contador1 := 0;
  v_contador2 := 0;
  dbms_output.put_line('Iteracion con la clausula EXIT WHEN CON ETIQUETAS');
  <<externo>>
  LOOP
    v_contador1 := v_contador1 + 1;
    dbms_output.put_line('Iterando con EXIT WHEN');    
    v_contador2 := 0;
    <<interno>>
    LOOP
     v_contador2 := v_contador2 + 1;
 dbms_output.put_line('Iterando con EXIT WHEN ANIDADO');
        EXIT externo WHEN (v_contador1 = 2 AND v_contador2 = 3);  
        EXIT interno WHEN v_contador2 >= 3;
    END LOOP;
    EXIT externo WHEN v_contador1 >= 3;
  END LOOP;
    
END proc07;

Ejemplo 8: Muestra el uso de estructuras de iteracion WHILE-LOOP-END LOOP con EXIT y etiquetas

CREATE OR REPLACE
PROCEDURE proc08(vi_cantidad_iteraciones IN INTEGER) IS
  v_contador1 INTEGER := 0;
  v_contador2 INTEGER := 0;
  v_contador3 INTEGER := 0;
BEGIN  
  --Ahora iteramos con la clausula EXIT WHEN con etiquetas
  v_contador1 := 0;
  v_contador2 := 0;
  dbms_output.put_line('Iteracion con la clausula EXIT WHEN CON ETIQUETAS');
  <<externo>>
  LOOP
    v_contador1 := v_contador1 + 1;
    dbms_output.put_line('Iterando con EXIT WHEN');    
    v_contador2 := 0;
    <<interno>>
    LOOP
     v_contador2 := v_contador2 + 1;
 dbms_output.put_line('Iterando con EXIT WHEN ANIDADO'); 
 v_contador3 := 0;
 WHILE (v_contador3 < 5 ) LOOP
   v_contador3 := v_contador3 + 1;
   dbms_output.put_line('Iterando con EXIT WHEN ANIDADO CON WHILE-LOOP-END LOOP');
 END LOOP;
        EXIT externo WHEN (v_contador1 = 2 AND v_contador2 = 3);  
        EXIT interno WHEN v_contador2 >= 3;
    END LOOP;
    EXIT externo WHEN v_contador1 >= 3;
  END LOOP;   
END proc08;

Pruebas de Software con TestNG Parte 1

TestNG es un framework de pruebas de software diseñado para simplificar un conjunto extenso de necesidades de prueba, desde pruebas unitarias(probar una clase java aislada de otras) hasta pruebas de integración(probar sistemas enteros implementados con muchas clases, muchos paquetes e incluso frameworks externos como servidores de aplicaciones). Escribir una prueba de software consiste tipicamente de un proceso en tres pasos:

1. Escribe la lógica de negocio de tu prueba e inserta anotaciones(por ejemplo @Test) de TestNG en tu código.
   
2. Agrega la información acerca de tu prueba( por ejemplo el nombre de la clase, los grupos que deseas ejecutar, etc...) en un archivo testng.xml o en un build.xml
   
3. Ejecuta tus pruebas de software.
   

Los conceptos usados por este framework son los que siguen:

1. Una conjunto de pruebas de software es representado por un archivo XML, este archivo contiene una o más pruebas(tests) y es definida por la etiqueta <suite>.
   
2. Una prueba es representada por una etiqueta <test> y puede contener una o más clases TestNG.
   
3. Una clase TestNG es una clase java que contiene al menos una anotación TestNG(@Test), es representada por la etiqueta <class> y puede contener uno o más métodos de prueba.
   
4. Un método de prueba es un método java anotado con @Test en tu código fuente.
   

Un ejemplo de lo anterior lo podemos ver en el siguiente archivo: