MYSQL – Cursores en procedimientos almacenados.

En  MySQL, los cursores son de solo lectura y pueden recorrerse únicamente en una dirección. A pesar de estas limitantes su uso en procedimientos almacenados puede ser muy útil y para ejemplificarlo vamos a crear un procedimiento almacenado que nos devuelva una lista de correos separada por punto y coma (;). Su uso, claro, nos podría servir para aquellas aplicaciones que envían correos a determinadas listas de distribución.  Para este ejemplo usaremos la base de datos Sakila que puedes descargar del siguiente enlace, si es que no la tienes instalada.

De acuerdo a la documentación de MySQL, para poder hacer uso de cursores en nuestros procedimientos almacenados será necesario de lo siguiente:

  • Declaración del cursor y la sentencia SQL cuyo resultado recorreremos.
  • Un Handler que nos alerte cuando hayamos llegado al último registro.
  • Un Loop que nos permita iterar a través del ResultSet devuelto por el cursor.

Procedamos entonces a crear nuestro procedimiento almacenado, así que vamos a declararlo de la siguiente manera:

 

/****************************************************************************
--* File Name    : sp_damelistacorreo.sql
--* Author       : Edgar Ramírez
--* Description  : Procedimiento almacenado que devuelve en una cadena separada
--*                por ; los correos de clientes de una tienda en particular 
*****************************************************************************/
CREATE PROCEDURE sp_damelistacorreo(pi_idstore INTEGER(10))
    NOT DETERMINISTIC
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN

END;

Ahora vamos a declarar las variables que usaremos, el cursor y el handler necesario para detener las iteraciones. Es muy importante que el orden de las declaraciones sea el siguiente:

  1. Variables
  2. Cursor
  3. Handler

De no seguir el orden indicado, mysql lanzará un error y no podrá ser compilado nuestro procedimiento almacenado. Con esto en mente codificamos lo siguiente:

  -- Termina cambiará a True cuando lleguemos al último registro del Resultset
  -- y VV_Listadecorreos será usado para almacenar los correos 
  DECLARE vb_termina BOOL DEFAULT FALSE;
  DECLARE VV_LISTACORREOS VARCHAR(21800);
  DECLARE VV_TmpEmail varchar(250) default '';

  -- Se usará la tabla customer de la base de datos sakila. filtrada por la tienda
   DECLARE CR_Correos CURSOR 
       FOR  select email                                
              from sakila.customer          
             where store_id = pi_idstore;

   -- El handler establece a true la variable termina, que usaremos a modo de bandera
   -- para saber cuando termine de recorrer el cursor             
   DECLARE CONTINUE HANDLER 
       FOR SQLSTATE '02000'
       SET vb_termina = TRUE;

Una vez hechas las declaraciones necesarias. Vamos a usar LOOP para recorrer los registros devueltos al abrir el cursor:

OPEN CR_Correos;
   Recorre_Cursor: LOOP
        FETCH CR_Correos INTO VV_TmpEmail;

        IF vb_termina THEN
            LEAVE Recorre_Cursor;
        END IF;

        set VV_LISTACORREOS = concat(VV_TmpEmail,';',VV_LISTACORREOS);

    END LOOP;
  CLOSE CR_Correos;

Y por último daremos salida a la variable haciendo uso de la cláusula SELECT. Esta no es la única manera de darle salida, pues podría también hacerse a través de un parámetro de salida.

  SELECT VV_LISTACORREOS CORREOS;

Al ejecutar el procedimiento almacenado obtendremos una cadena con la lista de correos lista para ser usada por nuestras aplicaciones:

ejecuta_sp_con_cursor

y para terminar aquí está el código completo del procedimiento almacenado.

 

/****************************************************************************
--* File Name    : sp_damelistacorreo.sql
--* Author       : Edgar Ramírez
--* Description  : Procedimiento almacenado que devuelve en una cadena separada
                   por ; los correos de clientes de una tienda en particular 
*****************************************************************************/
DROP procedure if exists sp_damelistacorreo;

CREATE PROCEDURE sp_damelistacorreo(pi_idstore INTEGER(10))
    NOT DETERMINISTIC
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN

  -- Termina cambiará a True cuando lleguemos al último registro del Resultset
  -- y VV_Listadecorreos será usado para almacenar los correos 
  DECLARE vb_termina BOOL DEFAULT FALSE;
  DECLARE VV_LISTACORREOS VARCHAR(21800) default '';
  DECLARE VV_TmpEmail varchar(250) default '';

  -- Se usará la tabla customer de la base de datos sakila. filtrada por la tienda
   DECLARE CR_Correos CURSOR 
       FOR  select email                                
              from sakila.customer          
             where store_id = pi_idstore;

   -- El handler establece a true la variable termina, que usaremos a modo de bandera
   -- para saber cuando termine de recorrer el cursor             
   DECLARE CONTINUE HANDLER 
       FOR SQLSTATE '02000'
       SET vb_termina = TRUE;

  OPEN CR_Correos;
   Recorre_Cursor: LOOP
        FETCH CR_Correos INTO VV_TmpEmail;

        IF vb_termina THEN
            LEAVE Recorre_Cursor;
        END IF;

        set VV_LISTACORREOS = concat(VV_TmpEmail,';',VV_LISTACORREOS);

    END LOOP;
  CLOSE CR_Correos;

  SELECT VV_LISTACORREOS CORREOS;  

END;

12 Comments

  1. Buenas, lo felicito por su excelente articulo, esta muy entendible, quisiera pedirle un favor, estoy tratando de realizar un procedimiento que retorne true si una consulta trajo algo tome su codigo le quite algunas cosas y pense que funcionaria:

    CREATE PROCEDURE trajo(pi_idstore varchar(20))
    NOT DETERMINISTIC
    SQL SECURITY DEFINER
    COMMENT ”
    BEGIN
    DECLARE vb_termina BOOL DEFAULT FALSE;
    DECLARE CR_Cuentas CURSOR
    FOR select Cuentas
    from salud.Cuentas
    where Cuentas = pi_idstore;
    DECLARE CONTINUE HANDLER
    FOR SQLSTATE ‘02000’
    SET vb_termina = TRUE;
    OPEN CR_Cuentas;
    Recorre_Cursor: LOOP

    IF vb_termina THEN
    LEAVE Recorre_Cursor;
    END IF;

    END LOOP;
    CLOSE CR_Cuentas;
    SELECT vb_termina CORREOS;
    END;

    Cuando llamo a dicho procedimiento se bloq

    • hola que tal, en clase me dejaron realizar un procedimiento almacenado al cual se le puedan realizar diferentes funciones,como agregar usuarios, cambiar, eliminar…etc, y al final me regrese la base de datos con la que comencé a trabajar, el profesor nos dijo que usáramos cursores, pero la verdad apenas le estoy agarrando la onda a las bases de datos, así que me gustaría saber su opinión de que es lo que puedo realizar… que tengas una excelente noche 😀

      • Para modificar, eliminar o agregar usuarios no es necesario usar cursores a menos claro que debas recolectar más información de otras tablas.

        Para agregarle bloques específicos a tu procedimiento puedes hacer uso de una sentencia de control IF y un parámetro de entrada que indique la operación a realizar.

        Saludos.

  2. Hola. Quiero felicitarlos por el post públicado, ya que esta bastante entendible. Pero tengo una duda. (La cual explicaré tomando como base el ejemplo del post)
    Si en ves de consultar solo el correo se consultaran mas datos (estado_correo, tipo_correo), y al momento de recorrerlos (en el cursor) hacer una condicional con el tipo_correo. Es decir si es tipo 1. que haga tal cosa, si es tipo 2 que haga otra accion. Espero repuesta Gracias!.

    • Hola Leo. La situación es sencilla de resolver. Primero debes obtener con el fetch los campos que deseas evaluar. Luego con un if campo= “condicion” then reailzar la acción que deseas adicionar. Ello te permitirá solucionar tu requerimiento.

      Saludos cordiales.

  3. Muy buen articulo, pero tengo una duda y no se si me pudieras ayudar, si lo que necesito hacer es tomar por ejemplo el nombre de una empresa (el cual esta en una tabla pej: Empresas) y debo agregar una columna a otra tabla por cada nombre de empresa, como puedo realizarlo?, agradecería mucho tu ayuda, saludos

  4. Hola realice un procedure y compila bien pero el select que esta dentro del recorrido del cursor no me trae nada en absoluto deseo saber que estoy haciendo mal.

    DROP PROCEDURE IF EXISTS Proc_CosteoProduccion;

    DELIMITER $$

    CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `serpi`.`Proc_CosteoProduccion`(IN item INT, IN fechaini DATE,IN fechafin DATE )
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT ‘string’*/
    BEGIN
    DECLARE CONTEO INT;
    DECLARE ARTICULO INT;
    DECLARE TIPO INT;
    DECLARE NUMERO INT;
    DECLARE CATEGORIA VARCHAR(5);
    DECLARE FECHA DATE;
    DECLARE E_S CHAR(1);
    DECLARE CANTIDAD DECIMAL(18,5);
    DECLARE VALUNIT DECIMAL(18,5);
    DECLARE PRIORIDAD INT;
    DECLARE COSTOP DECIMAL(18,5);
    DECLARE done INT DEFAULT 0;
    DECLARE totens DECIMAL(18,5);
    DECLARE PivotU DECIMAL(18,5);
    DECLARE PivotC DECIMAL(18,5);

    DECLARE cur CURSOR FOR
    SELECT a.gdtmovinventid,a.gdttipodocid,a.numero,a.fecha,a.gdtarticuloid,
    a.cantidad, a.costounit, a.E_S,b.categoria,
    CASE b.categoria
    WHEN “ENS” THEN IF (a.E_S=”E”,1,2)
    WHEN “FC” THEN 1
    WHEN “FV” THEN 2
    WHEN “DV” THEN 2
    WHEN “AI” THEN 2
    ELSE 2
    END AS prioridad
    FROM gdtmovinvent a
    INNER JOIN gdttipodoc b ON (a.gdttipodocid=b.gdttipodocid)
    WHERE a.gdtarticuloid=item
    AND a.fecha between fechaini and fechafin ORDER BY prioridad;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    SELECT SUM(IF (E_S=”S”,a.cantidad*-1,a.cantidad)) AS saldoU,
    SUM(IF (E_S=”S”,a.cantidad*-1,a.cantidad) * a.costounit) SaldoM FROM gdtmovinvent a
    WHERE a.gdtarticuloid = item
    AND a.fecha 0) THEN
    SET COSTOP = (PivotC+(COSTOP*CANTIDAD))/(PivotU+CANTIDAD);
    ELSE
    SET COSTOP = COSTOP;
    END IF;

    SET PivotU = PivotU+CANTIDAD;
    SET PivotC = PivotC+(CANTIDAD * COSTOP);

    END IF;

    IF PRIORIDAD = 2 THEN

    UPDATE gdtmovinvent SET costounit = COSTOP
    WHERE gdtmovinventid = conteo;

    SET PivotU = PivotU-CANTIDAD;
    SET PivotC = PivotC-(CANTIDAD * COSTOP);

    END IF;
    END IF;

    IF CATEGORIA = “EA” THEN
    /* TOMAR SALDO MOVIMIENTO / SALDO DE UNIDADES */

    IF (PivotU > 0) THEN
    SET COSTOP = (PivotC+(VALUNIT*CANTIDAD))/(PivotU+CANTIDAD);
    ELSE
    SET COSTOP = VALUNIT;
    END IF;

    SET PivotU = PivotU+CANTIDAD;
    SET PivotC = PivotC+(CANTIDAD * COSTOP);

    END IF;

    IF CATEGORIA “ENS” AND PRIORIDAD = 2 THEN
    UPDATE gdtmovinvent SET costounit = COSTOP
    WHERE gdtmovinventid = conteo;

    SET PivotU = PivotU-CANTIDAD;
    SET PivotC = PivotC-(CANTIDAD * COSTOP);

    END IF;

    UNTIL done = 1 END REPEAT;
    CLOSE cur;

    SELECT ARTICULO,FECHA,TIPO;

    END$$

    DELIMITER ;

1 Trackback / Pingback

  1. MYSQL – Sentencias SQL dinámicas en procedimientos almacenados | Blog del Poli

Deja un comentario