FAQ's de MySQL

[gnzsoloyo]

Pregunta: ¿Cómo recorrer iterativamente el resultado de una consulta dentro de un procedimiento almacenado?
Respuesta: Cursor, Handlers, variables locales.

El tema se resuelve por medio de:
1. Trazado de la lectura por medio de CURSOR y FETCH.
2. Manejo de HANDLER, para controlar los eventos de tabla.
3. Acciones repetitivas por medio de funciones de control de flujo: REPEAT ... END REPEAT y LOOP ... END LOOP, etc.

El manual de referencia tiene un capítulo entero dedicado al tema, pero lo esencial se puede resumir así:

1. Primero tienes que declarar todas las variables que vayas a usar para contener los datos de cada registro que leas. es necesario porque no puedes leer el registro en sí, sino que debes volcar los datos seleccionados dentro de variables y manipularlos en esas variables.
Esto es lo primero a definir, apenas después del BEGIN del store procedure.

2. Luego tienes que definir el CURSOR. Podemos decir que un cursor en un puntero que contiene la posición del registro de la consulta a una tabla según una sentencia de SELECT asociada al mismo. La sintaxis habitual es una sentencia select cualquiera, por ejemplo:

Código MySQL:

DECLARE traza1 CURSOR FOR 
SELECT  A, B, C, D, E 
FROM tabla1 
WHERE A = 23;
 

3. Luego tienes que ser capaz de saber cuándo has llegado al fin de la lectura de la tabla. Eso sucede cuando el puntero del cursor se intenta mover más allá del último regitro. En ese momento se produce un SQLSTATE cuyo valor indica el tipo de problema. El número de cada SQLSTATE está asociado a un error en la base de datos y están listados en un capítulo aparte.
El handler define no solamente la captura del error sino qué acción se realizará (nada, salir, deshacer) y la sintaxis en este caso será:

Código MySQL:

DECLARE CONTINUE HANDLER FOR SQLSTATE '20000' SET accion = 1;
 

La variable accion es también una variable que debes definir previamente y darle el valor cero (0):

Código MySQL:

DECLARE accion INT DEFAULT 0;
 

4. Finalmente, puedes escribir el cuerpo del procedimiento, hasta el momento que ya tengas la tabla temporal. Obviamente el cursor es un select dirigido a ella.
Lo primero es abrir el cursor, luego iniciar el ciclo de lectura y finalmente cerrarlo.

Código MySQL:

OPEN traza1;
REPEAT
   FETCH traza1 INTO A1, B1, C1, D1, E1;
...
Cuerpo de los procesos a realizar con los valores
...
UNTIL accion END REPEAT;
 
CLOSE traza1;
 

FETCH lo que hace es tomar la resultante de la consulta del cursor, registro a registro y volcarla a las variables. Obviamente la cantidad de variables debe ser igual a la cantidad de campos leídos en la consulta, así como también sus tipos de datos deben ser de la misma clase.
Cuando llega al final de la tabla resultado, e intenta leer de más, se produce el SQLSTATE 20000, la variable se pone en 1, y como es la condición de salida, sale del loop.
Dos últimos detalles:
1. El proceso es siempre de lectura hacia adelante. No puedes retroceder. En todo caso deberás abrir de nuevo el cursor (nueva ejecución), y rehacer el barrido.
2. No se pueden definir variables en el SP con posterioridad al CURSOR, ni se puede definir un HANDLER antes del CURSOR. Te daría un error de compilación.