Creación de Cursores en MySQL

Un cursor es un objeto que apunta a las filas retornadas de una consulta. Esta característica permite manipular los datos de cada fila de forma individual. MySQL usa la palabra reservada CURSOR para declarar estos espacios de lectura.

¿Qué son los Cursores en MySQL?

Recuerda que cuando consultábamos tablas con SELECT, MySQL arrojaba rápidamente los registros en pantalla de un solo tiro y nosotros eramos felices. Pero hay momentos donde necesitarémos acceder a cada registro de forma individual.

Lo que quiere decir que un cursor permite acceder en tiempo real a los datos de cada fila de una consulta. Este mecanismo es de gran utilidad cuando vayamos a comunicar MySQL con aplicativos o realizar consultas complejas.

¿Como usar Cursores en MySQL?

Para implementar un cursor debemos tener en cuenta 4 fases de su funcionamiento:

1. Declaración
2. Apertura
3. Lectura
4. Cierre

1. DECLARACION

Al igual que una una variable, los cursores se declaran con la sentencia DECLARE. Debemos declararlos después de nuestras variables corrientes, de lo contrario MySQL, generará un error. Veamos la sintaxis:

DECLARE nombre_cursor CURSOR FOR ;

Este sería un ejemplo:

DECLARE cursor_edad CURSOR FOR 
SELECT EDAD FROM CLIENTE
WHERE NOMBRE LIKE 'a%';

No significa que el objeto cursor_edad vaya a guardar los datos de la consulta a la cual esta referenciando. Lo que hace es apuntar a la dirección de memoria del primer resultado de dicha consulta. Si tienes conocimientos en C++ se te hará mas fácil comprender esta interpretación.

2. APERTURA

En la fase de declaración la consulta a la que hace referencia el cursor, aun no se ha ejecutado. Para ejecutarla usaremos el comando OPEN. Sin esta apertura los resultados del cursor no pueden ser leídos por MySQL, por lo tanto se producirá un error.

Debes tener en cuenta que al abrir el cursor este sitúa un puntero a la primera fila arrojada por la consulta.

OPEN nombre_cursor;

3. LECTURA

La lectura de los resultados de un cursor se hace con el comando FETCH. Este nos permite acceder a la primer fila generada por la consulta. Si se vuelve a usar el cursor pasa a apuntar a la segunda fila, luego a la tercer y así sucesivamente hasta que el cursor no tenga resultados que referenciar.

FETCH nombre_cursor INTO variable1,variable2,...

Es importante tener variables declaradas para almacenar temporalmente los datos de las columnas de cada fila, generadas por la consulta. Estas variables lógicamente deben tener el mismo tipo de dato que el valor de la columna a almacenar, y luego relacionarlas con la sentencia INTO.

Por ejemplo, si quisiéramos almacenar el id, nombre y apellido del primer empleado de la tabla EMPLEADO,  hacemos lo siguiente:

-- Declaración de variables para el cursor
DECLARE ID INT;
DECLARE NOMBRE VARCHAR(100);
DECLARE APELLIDO VARCHAR(100);
DECLARE cursor_cliente CURSOR 
FOR SELECT ID, NOMBRE, APELLIDO FROM CLIENTE;
OPEN cursor_cliente;
FETCH cursor_cliente INTO ID,NOMBRE,APELLIDO;
CLOSE cursor_cliente;

Me imagino que intuyes que si queremos recorrer todas las filas de la consulta, necesitaremos de alguna estructura repetitiva, ¿no es cierto?, claro!, incluir el comando FETCH dentro de un bucle permite leer todos los resultados de un cursor. Cuando el cursor llegue al final de los resultados de la consulta, entonces el bucle termina. Pero terminar un bucle de este tipo necesita una condición de parada especial en MySQL.

Existen manejadores de errores en MySQL para esta tarea, aunque por el momento no los hemos estudiado es necesario saber lo siguiente:

Cuando usamos FETCH en el cursor, pero ya no hay mas filas por retornar, MySQL arroja un error llamado "02000 NO DATA FECH". Así que lo que debemos hacer es crear un manejador para indicar que cuando suceda ese error, el programa no termine, pero que si termine el bucle. Veamos:

-- Declaración de un manejador de error tipo NOT FOUND
DECLARE CONTINUE HANDLER FOR NOT FOUND SET @hecho = TRUE;

Aquí indicamos que si ocurre un error tipoNOT FOUND, entonces asignemos a la variable @hecho el valor de TRUE. Con esa variable podremos manejar la terminación de nuestro bucle mas adelante.

4. CIERRE

Una vez leído todos los resultados del cursor, procedemos a cerrar y limpiar espacios de memoria con CLOSE.

CLOSE nombre_cursor;

Ejemplo de un Procedimiento con un Cursor

El siguiente es un ejemplo construido con fines educativos:

Cree un procedimiento en MySQL que imprima el código y el total acumulado de ventas del vendedor que mas facturó. Tenga en cuenta que el procedimiento debe recibir la fecha inicial y la fecha final, para estimar el lapso de tiempo en el que se calculará el acumulado.

El anterior requerimiento se crea a partir de una minibase de datos para un sistema de facturación que tiene el siguiente diagrama entidad-relación:

Diagrama entidad relación de un sistema de facturación.
Diseñador de phpMyAdmin

Antes de desarrollar el procedimiento, especificaremos el flujo en las entradas, procesos y salidas del programa:

Entradas

• Las variables fecha_inicio y fecha_final

Procesos

1. Consultar las facturas que liquidó el vendedor en las fechas estipuladas como entrada.
2. Encontrar los detalles de cada factura asociada al vendedor.
3. Multiplicar el precio por la cantidad de cada detalle y guardarlo en la variable acumulado_ventas.
4. Comparar el acumulado_ventas de cada vendedor mediante un bucle, para obtener el mejor vendedor.

Salida

• Mostrar en la pantalla el código y la variable acumulado_ventas del mejor vendedor.

Una vez comprendido estos pasos, solo queda implementar el código. Veamos:

DELIMITER //
CREATE PROCEDURE mejor_vendedor(fecha_inicio DATE, fecha_final DATE)
BEGIN
-- Declaración de variables
DECLARE ID_VENDEDOR INT;
DECLARE ACUMULADO_VENTAS INT;
DECLARE TEMPV INT DEFAULT 0;
DECLARE TEMPID INT DEFAULT 0;
-- Definición de la consulta
DECLARE mejor_vendedor_cursor CURSOR FOR
SELECT V.IDVENDEDOR,SUM(DF.UNIDADES*DF.PRECIO)
FROM VENDEDOR AS V INNER JOIN FACTURA AS F
ON V.IDVENDEDOR = F.IDVENDEDOR  AND (F.FECHA BETWEEN fecha_inicio AND fecha_final)
INNER JOIN DETALLEFACTURA AS DF
ON F.IDFACTURA = DF.IDFACTURA
GROUP BY V.IDVENDEDOR;
-- Declaración de un manejador de error tipo NOT FOUND
DECLARE CONTINUE HANDLER FOR NOT FOUND SET @hecho = TRUE;
-- Abrimos el cursor
OPEN mejor_vendedor_cursor;
-- Comenzamos nuestro bucle de lectura
loop1: LOOP
-- Obtenemos la primera fila en la variables correspondientes
FETCH mejor_vendedor_cursor INTO ID_VENDEDOR, ACUMULADO_VENTAS;
-- Si el cursor se quedó sin elementos,
-- entonces nos salimos del bucle
IF @hecho THEN
LEAVE loop1;
END IF;
-- Guardamos el acumulado de ventas y el código
-- si el vendedor actual tiene mejores resultados
IF ACUMULADO_VENTAS>=TEMPV THEN
SET TEMPV = ACUMULADO_VENTAS;
SET TEMPID = ID_VENDEDOR;
END IF;
END LOOP loop1;
-- Cerramos el cursor
CLOSE mejor_vendedor_cursor;
-- Imprimimos el código y total acumulado de ventas del vendedor
SELECT  TEMPID AS CODIGO_VENDEDOR, TEMPV AS TOTAL_VENTAS;
END//
DELIMITER ;

MySQL no permite que los cursores lean los resultados de una consulta desde el ultimo elemento hasta el primero. Usa ORDER BY para organizar tu mismo la información.

MySQL no permite que saltemos a una fila en particular para ahorrarnos tiempo, debemos recorrer obligatoriamente uno a uno los resultados.

James Revelo Urrea - Desarrollador independiente http://hermosaprogramacion.blogspot.com