5.3. Cursores

Un cursor es un puntero a una zona privada de SQL que almacena información sobre el proceso de una sentencia SELECT o DML ( INSERT, UPDATE, DELETE o MERGE)

No obstante de que Oracle Database se encarga de la gestión de cursores de las sentencias DML. PL / SQL ofrece varias maneras de definir y manipular los cursores para ejecutar instrucciones SELECT. Logrando que sea tan fácil y eficiente como sea posible la consulta y manipulación del contenido de las tablas de una base de datos.

Podemos distinguir dos tipos de cursores:

Un cursor admite el uso de parámetros. Los parámetros deben declararse junto con el cursor.

El siguiente diagrama representa como se procesa una instrucción SQL a través de un cursor.

Fases de un cursor ORACLE

Cursores implícitos

Se utilizan cuando la sentencia SELECT devuelve un solo registro. En cada cursor implícito debe existir la palabra reservada INTO.

Las variables que reciben los datos devueltos por el cursor deben tener el mismo tipo de dato que las columnas de la tabla.

También es posible usar cursores dentro de un procedimiento almacenado.

Ejemplo: Salario máximo de los jugadores en la NBA 2015 - 2016


CREATE OR REPLACE PROCEDURE mejorPagado IS
  maximo nbaSalarios.salario%TYPE;
 BEGIN 
   SELECT MAX(salario) INTO maximo
   FROM nbaSalarios;

   DBMS_OUTPUT.PUT_LINE(TO_CHAR(maximo,'$999,999,999'));
END;
/

Ejecutar

EXECUTE mejorPagado;

Excepciones asociadas a los cursores implícitos.

Los cursores implícitos sólo pueden devolver una fila, por lo que pueden producirse determinadas excepciones. Las más comunes que se pueden encontrar son NO_DATA_FOUND y TOO_MANY_ROWS.

Cuando un SELECT INTO hace referencia a una función de grupo nunca se levantará la excepción NO_DATA_FOUND, Y SQL%FOUND siempre será verdadero. Esto se debe a que las funciones de grupo siempre retornan algún valor (aunque sea NULL).

Ejemplo: Retomando el problema anterior buscaremos el salario máximo y el o los jugadores que lo tienen

CREATE OR REPLACE PROCEDURE mejorPagado IS
  o_nombre   nbaSalarios.nombre%TYPE;
  o_equipo   nbaSalarios.equipo%TYPE;
  o_salario  nbaSalarios.salario%TYPE;
  o_cadena   VARCHAR(70);
 BEGIN 
  SELECT nombre, salario, equipo INTO o_nombre, o_salario, o_equipo
     FROM nbaSalarios WHERE salario = (SELECT MAX(salario)
                               FROM nbaSalarios);
  
  o_cadena:= TRIM(o_nombre) || ' de ' || TRIM(o_equipo) || ' con un salario de ';
  DBMS_OUTPUT.PUT_LINE(o_cadena || TO_CHAR(o_salario,'$999,999,999'));
END;
/

Ejecutar

Ahora si me pregustas que este código es eficiente. La respuesta es NO. Observa la razón

INSERT INTO nbaSalarios VALUES(500,'Astro 1','SG','Astros',30000000);
INSERT INTO nbaSalarios VALUES(501,'Astro 2','SG','Estrellas',30000000);

Si ejecutamos el cursor obtendremos un error

Si aun así deseamos usar un cursor ímplicito la solución es fácil. Usaremos una sentencia FOR ... IN ... LOOP.

Aquí 'jugadores' es una variable de tipo de registro local con campos definidos de forma automática (ROWTYPE)para que coincida con los campos en los datos de filas resultado de la instrucción DML

CREATE OR REPLACE PROCEDURE mejorPagado IS
  jugadores nbaSalarios%ROWTYPE;
  o_cadena   VARCHAR(100);
 BEGIN
   FOR jugadores IN  (SELECT * 
     FROM nbaSalarios WHERE salario = (SELECT MAX(salario)
                               FROM nbaSalarios)) LOOP
     o_cadena:= TRIM(jugadores.nombre) || ' de ' || TRIM(jugadores.equipo) || ' con un salario de ';
     DBMS_OUTPUT.PUT_LINE(o_cadena || TO_CHAR(jugadores.salario,'$999,999,999'));
   END LOOP;
 END;
 /

Y ejecutamos nuevamente nuestro procedimiento


Cursores explícitos:

Se utilizan cuando la sentencia SELECT puede devolver varios registros. También se pueden utilizar en consultas que devuelvan un solo registro por razones de eficiencia con respecto a los cursores implícitos, eficiencia que mejorará especialmente si el cursor explícito se tiene que ejecutar varias veces dentro del bloque de código.

Un cursor explícito tiene que ser definido previamente como cualquier otra variable. Admiten el uso de parámetros. Los parámetros deben declararse junto con el cursor.

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

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

Ejemplo: Encontrar todos los municipios cuyo nombre inicia con la subcadena 'Paso' y el estado donde se ubica dicho municipio

El código funcional propuesto es el siguiente

CREATE OR REPLACE PROCEDURE municipiosEstado AS
  CURSOR recordSet IS
    SELECT E.estado, M.municipio 
    FROM estados E, municipios M
    WHERE E.idEstado = M.idEstado AND M.municipio LIKE 'Paso%';
  o_Estado    VARCHAR(40);
  o_Municipio VARCHAR(85);
  o_cadena    VARCHAR(125);
BEGIN
  OPEN recordSet;
    LOOP
      FETCH  recordSet INTO o_Estado, o_Municipio;
      EXIT WHEN recordSet%NOTFOUND;
      o_cadena:= TRIM(o_Estado) || ' ' || TRIM(o_Municipio);
      DBMS_OUTPUT.PUT_LINE(o_cadena);   
    END LOOP;
  CLOSE recordSet;
END;
/

El código funciona, sin embargo no es óptimo dado que si queremos buscar otras subcadenas o variamos la posición de la subcadena este deja de funcionar. La solución propuesta es usar parámetros y cambiar de sentencia de ciclo. Por ejemplo FOR.

Uso de FOR en cursores

CREATE OR REPLACE PROCEDURE municipiosEstado(in_buscar VARCHAR2) AS
 
  CURSOR recordSet (in_buscar VARCHAR2) IS
    SELECT E.estado, M.municipio 
    FROM estados E, municipios M
    WHERE E.idEstado = M.idEstado AND M.municipio LIKE '%' || in_buscar || '%';
  o_cadena    VARCHAR(125);
BEGIN
  FOR  registro IN recordSet (in_buscar) LOOP  
      o_cadena:= TRIM(registro.Municipio) || ', ' || TRIM(registro.estado);
      DBMS_OUTPUT.PUT_LINE(o_cadena);   
  END LOOP;
END;
/

Ejemplo: Campeones y número de veces que lo han logrado en la NBA. Usaremos una tabla ya desarrollada previamente cuya estructura es la siguiente

CREATE OR REPLACE PROCEDURE campeones AS
CURSOR cCampeones IS SELECT campeon, COUNT(*) n          
                     FROM campeonesNBA
                     GROUP BY campeon
                     ORDER BY n DESC, campeon;
BEGIN 
  FOR  registro IN cCampeones LOOP
     DBMS_OUTPUT.PUT_LINE( registro.campeon || registro.n);
 END LOOP;
END;
/