MYSQL : CURSORES

CURSORES EN MYSQL

Ejemplo de un procedimiento almacenado con uso de cursor :

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
/*
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.

*/

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
/*
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 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 saltemos a una fila en particular para ahorrarnos tiempo, debemos recorrer
obligatoriamente uno a uno los resultados.

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.
A continuación, se utiliza la sentencia FETCH para recuperar la siguiente fila señalado por el cursor
 y mover el cursor al siguiente registro. Si no existen más registros disponibles, ocurrirá una condición
 de Sin Datos con el valor SQLSTATE 02000.


 Cuando se trabaja con el cursor de MySQL, también debe declarar un controlador NOT FOUND para manejar
 la situacion cuando el cursor no pudo encontrar ninguna fila. Debido a que cada vez que se llama a
 la instrucción FETCH, el cursor se intenta leer la siguiente fila del conjunto de resultados.
 Cuando el cursor llega al final del conjunto de resultados, no va a ser capaz de obtener los datos
 y provoca un evento. El controlador se utiliza para manejar esta condición.

Para declarar el evento NOT FOUND , se utiliza la siguiente sintaxis:



DECLARE CONTINUE HANDLER FOR NOT FOUND SET terminado = 1;


Donde terminado es una variable para indicar que el cursor ha alcanzado el final del conjunto de
resultados. Observe que la declaración del controlador debe aparecer después de variable y
declaración de cursor dentro de los procedimientos almacenados.

Ejemplos :

/*Table structure for table `tabla` */
drop table if exists `tabla`;
create table `tabla` (
  `campo1` smallint(6) default null,
  `campo2` char(1) default null,
  `campo3` decimal(10,0) default null,
  `fecha` datetime default null
) engine=innodb default charset=latin1;

/*Data for the table `tabla` */
insert  into `tabla`(`campo1`,`campo2`,`campo3`,`fecha`)
values
(1,'A','1000','2011-01-18 00:56:56'),
(2,'B','2000','2011-01-18 00:57:02'),
(3,'C','3000','2011-01-18 00:57:08');

delimiter $$
drop procedure if exists `proc_para_probar`$$
create procedure `proc_para_probar`()
begin
-- declaracion de variables locales
declare pcampo1 smallint(6);
declare pcampo2 char(1);
declare pcampo3 decimal(10,0);
declare no_hay_mas_registros int default 0;
declare elCursor cursor for
select campo1, campo2, campo3 from tabla where fecha between '2011-01-18' and '2011-01-19';
declare continue handler for not found set no_hay_mas_registros = 1;
-- tabla temporal
drop temporary table if exists tablatemporal;
create temporary table tablatemporal (campo1 smallint(6), campo2 char(1), campo3 decimal(10,0));
-- se abre el cursor
open elCursor;
-- se van tomando los datos hasta que el cursor llegue al final
bucle: loop
 fetch elCursor into pcampo1, pcampo2, pcampo3;
 if (no_hay_mas_registros = 1) then
  leave bucle;
 end if;
 -- se cargan los datos en la tabla temporal
 insert tablatemporal(campo1, campo2, campo3) values (pcampo1, pcampo2, pcampo3);
end loop bucle;
close elCursor;
select * from tablatemporal order by campo1, campo2, campo3;
drop temporary table if exists tablatemporal;
end$$
delimiter ;

EJEMPLO 01 :

DELIMITER $$

USE `asterisk`$$

DROP PROCEDURE IF EXISTS `usp_Main_Insertar_Telefonos_Referencia_Scotiabank_NOCUSTOM`$$

CREATE DEFINER=`jorge`@`%` PROCEDURE `usp_Main_Insertar_Telefonos_Referencia_Scotiabank_NOCUSTOM`(IN IDCartera VARCHAR(40),IN IDLista VARCHAR(40))
BEGIN
--  IN filetxt VARCHAR(200),
--  SET @carga = CONTACT("LOAD DATA LOCAL INFILE '",filetxt,"'INTO TABLE TELEFONO_CONSOLIDADO FIELDS TERMINATED BY ','");
--  PREPARE sentencia FROM @carga;
--  EXECUTE sentencia;
--  DEALLOCATE PREPARE sentencia;
  
 CREATE TABLE IF NOT EXISTS tmpTelefonosReferencia_Scotiabank(
 codigo VARCHAR (100) DEFAULT NULL
 ,telefonos VARCHAR (20) DEFAULT NULL
 ,lead_id INT(9) NOT NULL); 
 
 TRUNCATE TABLE tmpTelefonosReferencia_Scotiabank; 
 
 SET @consulta = CONCAT("INSERT INTO tmpTelefonosReferencia_Scotiabank 
   SELECT
   DISTINCT
 TT.CODIGO
 ,TT.TELEFONO
 ,WW.lead_id
 FROM 
 (
  SELECT 
  a.Id AS CODIGO 
  ,a.Telefono AS TELEFONO
  ,a.Prioridad AS Origen 
  FROM scotiabank.master_telefonos a
  WHERE a.Telefono 
  NOT IN  
  (
  SELECT 
  DISTINCT phone_number 
  FROM asterisk.vicidial_list
  WHERE list_id = '",IDLista,"' AND address2 LIKE '%-",IDCartera,"'
  )
 ) AS TT
 INNER JOIN asterisk.vicidial_list AS WW ON  TT.CODIGO = WW.address2  -- AND a.Telefono <> b.phone_number
 WHERE  WW.list_id = '",IDLista,"'  AND address2 LIKE '%-",IDCartera,"' ORDER BY CODIGO ASC");
 
 PREPARE sentencia FROM @consulta;
 EXECUTE sentencia;
 DEALLOCATE PREPARE sentencia;
 
 
 CALL usp_Insertar_Telefonos_Referencia_Scotiabank();
END$$

DELIMITER ;

--------------------------------------------------------------------------

DELIMITER $$

USE `asterisk`$$

DROP PROCEDURE IF EXISTS `usp_Insertar_Telefonos_Referencia`$$

CREATE DEFINER=`jorge`@`%` PROCEDURE `usp_Insertar_Telefonos_Referencia`()
BEGIN
   
     
   -- Declaración de variables
   DECLARE vlead_id VARCHAR(30);
   DECLARE vcodcent VARCHAR(30);
   
   -- Declaracion del primer cursor
   DECLARE custom_cursor CURSOR FOR SELECT DISTINCT lead_id,codcent FROM tmpTelefonosReferencia;
   
   -- Declaracion del segundo cursor
   -- DECLARE telefonos_cursor CURSOR FOR 
   -- SELECT a.COD_LUNA AS cod_luna,a.TELEFONO_F AS telefonos
 -- FROM CSL_TELEFONOS_CONSOLIDADO a 
 -- LEFT JOIN custom_5310 b ON a.COD_LUNA = b.cod_luna
 -- WHERE b.cod_luna IS NULL;
   
   -- Declaración de un manejador de error tipo NOT FOUND
   /* 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.*/
      
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET @estado = TRUE;
   DROP TEMPORARY TABLE IF EXISTS tablatemporal;
   CREATE TEMPORARY TABLE tablatemporal (
  lead_id INT(9)
  ,cod_cent VARCHAR(20)
  ,phone_number VARCHAR(20)
  ,alt_phone_count VARCHAR(5));
  
   -- DROP TEMPORARY TABLE IF EXISTS telefonos_cursor;
   -- CREATE TEMPORARY TABLE telefonos_cursor (
 -- SELECT a.COD_LUNA AS cod_luna,a.TELEFONO_F AS telefonos
 -- FROM CSL_TELEFONOS_CONSOLIDADO a 
 -- LEFT JOIN custom_5310 b ON a.COD_LUNA = b.cod_luna
 -- WHERE b.cod_luna IS NULL);
 
   -- Abrimos el cursor
   OPEN custom_cursor;
   -- Comenzamos nuestro bucle de lectura
   loop1: LOOP
       -- Obtenemos la primera fila en la variables correspondientes
       FETCH custom_cursor INTO vlead_id,vcodcent;
       -- Si el cursor se quedó sin elementos, entonces nos salimos del bucle
       IF @estado THEN
          LEAVE loop1;
       END IF;
       -- CALL debug(idtabla);
       
       -- Insertamos 
       SET @numero = 0;
       INSERT INTO tablatemporal (SELECT vlead_id AS lead_id,codcent AS cod_cent, telefonos AS phone_number,@numero:=@numero+1 AS alt_phone_count 
       FROM tmpTelefonosReferencia WHERE codcent = vcodcent);
     
   END LOOP loop1;
   -- Cerramos el cursor
   CLOSE custom_cursor;
 
  INSERT INTO vicidial_list_alt_phones 
 (lead_id,phone_code,phone_number,alt_phone_count,active) 
  SELECT lead_id,'1' AS phone_code,phone_number,alt_phone_count,'Y' AS active 
  FROM tablatemporal;
 
 DROP TEMPORARY TABLE IF EXISTS tablatemporal;
END$$

DELIMITER ;

REFERENCIA:

http://www.hermosaprogramacion.com/2014/06/mysql-cursores/
http://blog.zeion.net/2013/03/21/como-usar-variables-en-mysql-dentro-de-sentencias-update-insert/
https://www.codejobs.biz/es/blog/2014/07/09/como-hacer-un-procedimiento-almacenado-en-mysql-sin-morir-en-el-intento

https://totaki.com/poesiabinaria/2013/12/bucles-y-cursores-en-mysql-con-ejemplos/