www.devjoker.com

Cursores Explicitos en PL/SQL

Declaración de cursores explicitos

    Los cursores explicitos se emplean para realizar consultas SELECT que pueden devolver cero filas, o más de una fila.

    Para trabajar con un cursor explicito necesitamos realizar las siguientes tareas:

  • Declarar el cursor.
  • Abrir el cursor con la instrucción OPEN.
  • Leer los datos del cursor con la instrucción FETCH.
  • Cerrar el cursor y liberar los recursos con la instrucción CLOSE.

    Para declarar un cursor debemos emplear la siguiente sintaxis:


    CURSOR nombre_cursor IS
     instrucción_SELECT

    También debemos declarar los posibles parametros que requiera el cursor:


    CURSOR nombre_cursor(param1 tipo1, ..., paramN tipoN) IS
     instrucción_SELECT

    Para abrir el cursor


     OPEN nombre_cursor;
     o bien (en el caso de un cursor con parámetros)
     OPEN nombre_cursor(valor1, valor2, ..., valorN);

    Para recuperar los datos en variables PL/SQL.


  
FETCH nombre_cursor INTO lista_variables;
-- o bien ...
FETCH nombre_cursor INTO registro_PL/SQL;

    Para cerrar el cursor:


  CLOSE nombre_cursor;

    El siguiente ejemplo ilustra el trabajo con un cursor explicito. Hay que tener en cuenta que al leer los datos del cursor debemos hacerlo sobre variables del mismo tipo de datos de la tabla (o tablas) que trata el cursor.


DECLARE 
  CURSOR cpaises 
  IS
  SELECT CO_PAIS, DESCRIPCION, CONTINENTE 
  FROM PAISES;
 


  co_pais VARCHAR2(3);
  descripcion VARCHAR2(50);
  continente  VARCHAR2(25);
BEGIN
  OPEN cpaises;
  FETCH cpaises INTO co_pais,descripcion,continente;
  CLOSE cpaises;
END;

    Podemos simplificar el ejemplo utilizando el atributo de tipo %ROWTYPE sobre el cursor.


DECLARE 
  CURSOR cpaises 
  IS
  SELECT CO_PAIS, DESCRIPCION, CONTINENTE 
  FROM PAISES;
  
  registro cpaises%ROWTYPE;
BEGIN
  OPEN cpaises;
  FETCH cpaises INTO registro;
  CLOSE cpaises;
END;

    El mismo ejemplo, pero utilizando parámetros:


DECLARE 
  CURSOR cpaises (p_continente VARCHAR2) 
  IS
  SELECT CO_PAIS, DESCRIPCION, CONTINENTE 
  FROM PAISES
  WHERE CONTINENTE = p_continente;
  
  registro cpaises%ROWTYPE;
BEGIN
  OPEN cpaises('EUROPA');
  FETCH cpaises INTO registro;
  CLOSE cpaises;
END;

     Cuando trabajamos con cursores debemos considerar:

  • Cuando un cursor está cerrado, no se puede leer.
  • Cuando leemos un cursor debemos comprobar el resultado de la lectura utilizando los atributos de los cursores.
  • Cuando se cierra el cursor, es ilegal tratar de usarlo.
  • Es ilegal tratar de cerrar un cursor que ya está cerrado o no ha sido abierto
Atributos de cursores 
   
   Toman los valores TRUE, FALSE o NULL dependiendo de la situación: 
 
AtributoAntes de abrirAl abrirDurante la recuperaciónAl finalizar la recuperaciónDespués de cerrar
%NOTFOUNDORA-1001NULLFALSETRUEORA-1001
%FOUNDORA-1001NULLTRUEFALSEORA-1001
%ISOPENFALSETRUETRUETRUEFALSE
%ROWCOUNTORA-10010***ORA-1001
 
    * Número de registros que ha recuperado hasta el momento
    ** Número de total de registros

Manejo del cursor

    Por medio de ciclo LOOP podemos iterar a través del cursor. Debe tenerse cuidado de agregar una condición para salir del bucle:

    Vamos a ver varias formas de iterar a través de un cursor. La primera es utilizando un bucle LOOP con una sentencia EXIT condicionada:

    
    OPEN nombre_cursor;
    LOOP
        FETCH nombre_cursor INTO lista_variables;
        EXIT WHEN nombre_cursor%NOTFOUND;
        /* Procesamiento de los registros recuperados */
    END LOOP;
    CLOSE nombre_cursor;

    Aplicada a nuestro ejemplo anterior:


DECLARE 
    CURSOR cpaises 
    IS
    SELECT CO_PAIS, DESCRIPCION, CONTINENTE 
    FROM  PAISES;
 
    co_pais VARCHAR2(3);
    descripcion VARCHAR2(50);
    continente VARCHAR2(25);
BEGIN
    OPEN cpaises;
    LOOP
        FETCH cpaises INTO co_pais,descripcion,continente;
        EXIT WHEN cpaises%NOTFOUND;
        dbms_output.put_line(descripcion);
    END LOOP; 
    CLOSE cpaises;
END;
     

    Otra forma es por medio de un bucle WHILE LOOP. La instrucción FECTH aparece dos veces.

    
    OPEN nombre_cursor;
    FETCH nombre_cursor INTO lista_variables;
    WHILE nombre_cursor%FOUND 
    LOOP
        /* Procesamiento de los registros recuperados */
        FETCH nombre_cursor INTO lista_variables;
    END LOOP;
    CLOSE nombre_cursor;

 

 
DECLARE 
    CURSOR cpaises 
    IS
    SELECT CO_PAIS, DESCRIPCION, CONTINENTE 
    FROM PAISES;
 
    co_pais VARCHAR2(3);
    descripcion VARCHAR2(50);
    continente VARCHAR2(25);
BEGIN
    OPEN cpaises;
    FETCH cpaises INTO co_pais,descripcion,continente;
    WHILE cpaises%found
    LOOP 
        dbms_output.put_line(descripcion);
        FETCH cpaises INTO co_pais,descripcion,continente;
    END LOOP; 
    CLOSE cpaises;
END;

   Por último podemos usar un bucle FOR LOOP. Es la forma más corta ya que el cursor es implicitamente se ejecutan las instrucciones OPEN, FECTH y CLOSE.


    FOR variable IN nombre_cursor LOOP
        /* Procesamiento de los registros recuperados */
    END LOOP;


 

BEGIN
    FOR REG IN (SELECT * FROM PAISES)
    LOOP
        dbms_output.put_line(reg.descripcion);
    END LOOP;
END;