Anidar iteraciones con cursores MySQL

Lo normal al usar procedimientos almacenados en MySQL es trabajar con un solo CURSOR:

BEGIN
  DECLARE v_foo INT;                        
  DECLARE no_more_rows BOOLEAN:= FALSE;     -- variable de control del loop
  DECLARE cursor1 CURSOR FOR                -- definimos el cursor 
    SELECT col1 FROM table;

  DECLARE CONTINUE HANDLER FOR NOT FOUND    -- Capturamos el final del cursor y cambiamos la variable de control
    set no_more_rows := TRUE;

  OPEN cursor1;                             -- Ejecutamos la query del cursor
  LOOP1: loop

    fetch cursor1 into v_foo;               -- Mapeamos la actual tupla en variables                             
      if no_more_rows then
        close cursor1;
        leave LOOP1;                    
      end if;

      -- 
      -- Codigo del procedimiento
      -- 
  end loop LOOP1;
end;

El CURSOR se procesa iterando con un LOOP. Para cada iteración, guardamos los valores retornados por la query en variables. Cuando el cursor llega al final de la query, no devuelve mas resultados, el manejador(handler) de NOT FOUND salta y cambia la variable de control del LOOP, rompiendo la iteración.

El problema viene cuando necesitamos tener dos iteraciones sobre cursores diferentes y que estén anidadas. Por la definición de handler, éstos solo pueden ser definidos DESPUES de haber declarado TODOS los cursores, esto quiere decir que un manejador no está relacionado con un cursor en particular. ¿Como controlar entonces dos cursores?:

BEGIN
  DECLARE no_more_rows BOOLEAN DEFAULT false;
  DECLARE v_col1 INT;
  DECLARE v_col2 INT;
  DECLARE cursor1 CURSOR FOR
    SELECT col1 FROM table;

  DECLARE cursor2 CURSOR FOR
    SELECT col2 FROM table2;
  
  DECLARE CONTINUE HANDLER FOR NOT FOUND 
    SET v_done := true;
    
  OPEN cursor1;
  LOOP1: LOOP
    FETCH cursor1 INTO v_col1;
    IF v_done THEN
      CLOSE cursor1;
      LEAVE LOOP1;
    END IF;
     
    OPEN cursor2;
    LOOP2: LOOP
      FETCH cursor2 INTO v_col2;
        IF v_done THEN
          SET v_done := false;
          CLOSE cursor2;
          LEAVE LOOP2;
        END IF;
    END LOOP LOOP2;
  END LOOP LOOP1;
END;

La iteración exterior, sigue siendo una iteración normal con un sistema de control normal. El cambio está en el control de la iteración interior, ya que cuando se cumple, además de cerrar el cursor y salir del loop, volvemos a cambiar la variable de estado v_done, para que el loop exterior siga con su iteración.


Un ejemplo practico que calcula el total pedido de un fichero de lineas de pedido y lo inserta en  un archivo de totales de pedido:

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `totalespedido`()

BEGIN

  DECLARE a,b,c INT;

  DECLARE pre1 decimal(7,2);

  DECLARE var_done INT DEFAULT 0;

  DECLARE codigo1 int;

  declare total decimal(8,2);

  

  DECLARE cur1 CURSOR FOR SELECT pedidos.id_pedido, pedidos.id_producto, pedidos.cantidad FROM pedidos WHERE pedidos.id_pedido=codigo1;

  declare cur2 cursor for select DISTINCT pedidos.id_pedido from pedidos;

  

  

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_done=1;

  open cur2;

 get_b:loop

 fetch cur2 into codigo1;

  

 if var_done=1 THEN leave get_b;

    end IF;

 set total = 0;

   OPEN cur1;

 get_a:loop

    FETCH cur1 INTO a, b, c;

    if var_done=1 THEN 

    set var_done=0;

    close cur1;

    

    leave get_a;

    end IF;

    

    select precio into pre1 from articulos WHERE articulos.id_articulo=b;

    set total=total+(pre1*c);

      

   END LOOP get_a;

    update totalpedido set cantidad=total where id_pedido=codigo1;

  

   end loop get_b;

  close cur2;

  

END$$

DELIMITER ;

Fuente :http://www.thegameofcode.com/2012/08/anidar-iteraciones-con-cursores-mysql.html