More than one cursor in procedure Mysql

0

Do I need to execute more than one cursor in the same procedure? Is this possible?

begin
DECLARE done INT DEFAULT 0;
declare v_dia, v_rotina int;
declare v_hora time;
declare v_saldo, v_valor decimal(5,2);
declare v_flag tinyint;
declare v_id varchar (100);

DECLARE sexta CURSOR FOR (
select saldo, hora, flag, valor, sexta, id_rotina, id
from tbl_cartao
inner join tbl_dias_uso
on tbl_dias_uso.id_usuario = tbl_cartao.id_usuario
inner join tbl_rotina
on tbl_rotina.id_usuario = tbl_cartao.id_usuario and tbl_rotina.tipo = tbl_dias_uso.tipo
inner join tbl_usuarios
on tbl_usuarios.id = tbl_cartao.id_usuario
where dayofweek(now()) = sexta and  hora <=  curtime() and flag = 0 
);

DECLARE domingo CURSOR FOR (
select saldo, hora, flag, valor, domingo, id_rotina, id
from tbl_cartao
inner join tbl_dias_uso
on tbl_dias_uso.id_usuario = tbl_cartao.id_usuario
inner join tbl_rotina
on tbl_rotina.id_usuario = tbl_cartao.id_usuario and tbl_rotina.tipo = tbl_dias_uso.tipo
inner join tbl_usuarios
on tbl_usuarios.id = tbl_cartao.id_usuario
where dayofweek(now()) = domingo and hora <=  curtime() and flag = 0 
);


DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;


OPEN sexta;
REPEAT
FETCH sexta INTO v_saldo,v_hora,v_flag, v_valor, v_dia,v_rotina, v_id;
IF NOT done THEN



update tbl_cartao
set saldo = v_saldo - v_valor
where v_id = id_usuario and v_dia = 
dayofweek(now());

insert into tbl_desconto_usuarios (id_usuario,saldo_anterior, saldo_apos, data_desconto,hora_desconto)
values(v_id, v_saldo, v_saldo - v_valor, CURRENT_DATE(),CURRENT_TIME() );

update tbl_rotina
set flag = 1
where hora <= curtime() and v_dia = dayofweek(now()) and v_id = id_usuario;


END IF;
UNTIL done END REPEAT;
CLOSE sexta;

OPEN domingo;
REPEAT
FETCH domingo INTO v_saldo,v_hora,v_flag, v_valor, v_dia,v_rotina, v_id;
IF NOT done THEN



update tbl_cartao
set saldo = v_saldo - v_valor
where v_id = id_usuario and v_dia = 
dayofweek(now());

insert into tbl_desconto_usuarios (id_usuario,saldo_anterior, saldo_apos, data_desconto,hora_desconto)
values(v_id, v_saldo, v_saldo - v_valor, CURRENT_DATE(),CURRENT_TIME() );

update tbl_rotina
set flag = 1
where hora <= curtime() and v_dia = dayofweek(now()) and v_id = id_usuario;


END IF;
UNTIL done END REPEAT;
CLOSE domingo;


END
    
asked by anonymous 04.12.2017 / 13:36

1 answer

0

It is possible however you have to work in a different way the loop, not just checking the done variable, because when the first cursor is executed it will not execute the second one, then we will work another way to see if it is complete or not verifies how many records to go through and we set a counter and how much to go through all the lines let the variables done1 and done2, thus:

begin
    DECLARE done INT DEFAULT 0;
    DECLARE done2 INT DEFAULT 0;
    DECLARE total_cur1 INT DEFAULT 0;
    DECLARE total_cur2 INT DEFAULT 0;
    DECLARE qtd_cur_1 INT DEFAULT 0;
    DECLARE qtd_cur_2 INT DEFAULT 0;
    declare v_dia, v_rotina int;
    declare v_hora time;
    declare v_saldo, v_valor decimal(5,2);
    declare v_flag tinyint;
    declare v_id varchar (100);

    DECLARE sexta CURSOR FOR (
                                                select saldo, hora, flag, valor, sexta, id_rotina, id
                                                from tbl_cartao
                                                inner join tbl_dias_uso
                                                on tbl_dias_uso.id_usuario = tbl_cartao.id_usuario
                                                inner join tbl_rotina
                                                on tbl_rotina.id_usuario = tbl_cartao.id_usuario and tbl_rotina.tipo = tbl_dias_uso.tipo
                                                inner join tbl_usuarios
                                                on tbl_usuarios.id = tbl_cartao.id_usuario
                                                where dayofweek(now()) = sexta and  hora <=  curtime() and flag = 0 
    );

    DECLARE domingo CURSOR FOR (
                                                        select saldo, hora, flag, valor, domingo, id_rotina, id
                                                        from tbl_cartao
                                                        inner join tbl_dias_uso
                                                        on tbl_dias_uso.id_usuario = tbl_cartao.id_usuario
                                                        inner join tbl_rotina
                                                        on tbl_rotina.id_usuario = tbl_cartao.id_usuario and tbl_rotina.tipo = tbl_dias_uso.tipo
                                                        inner join tbl_usuarios
                                                        on tbl_usuarios.id = tbl_cartao.id_usuario
                                                        where dayofweek(now()) = domingo and hora <=  curtime() and flag = 0 
    );


    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    SELECT
            COUNT(*)
    INTO total_cur1
    FROM tbl_cartao
    inner join tbl_dias_uso
    on tbl_dias_uso.id_usuario = tbl_cartao.id_usuario
    inner join tbl_rotina
    on tbl_rotina.id_usuario = tbl_cartao.id_usuario and tbl_rotina.tipo = tbl_dias_uso.tipo
    inner join tbl_usuarios
    on tbl_usuarios.id = tbl_cartao.id_usuario
    where dayofweek(now()) = sexta and  hora <=  curtime() and flag = 0 ;

    SELECT
                COUNT(*)
    INTO total_cur2
    FROM tbl_cartao
    inner join tbl_dias_uso
    on tbl_dias_uso.id_usuario = tbl_cartao.id_usuario
    inner join tbl_rotina
    on tbl_rotina.id_usuario = tbl_cartao.id_usuario and tbl_rotina.tipo = tbl_dias_uso.tipo
    inner join tbl_usuarios
    on tbl_usuarios.id = tbl_cartao.id_usuario
    where dayofweek(now()) = domingo and hora <=  curtime() and flag = 0 ;


    OPEN sexta;
        REPEAT
            FETCH sexta INTO v_saldo,v_hora,v_flag, v_valor, v_dia,v_rotina, v_id;
            IF NOT done THEN

                update tbl_cartao
                set saldo = v_saldo - v_valor
                where v_id = id_usuario and v_dia = 
                dayofweek(now());

                insert into tbl_desconto_usuarios (id_usuario,saldo_anterior, saldo_apos, data_desconto,hora_desconto)
                values(v_id, v_saldo, v_saldo - v_valor, CURRENT_DATE(),CURRENT_TIME() );

                update tbl_rotina
                set flag = 1
                where hora <= curtime() and v_dia = dayofweek(now()) and v_id = id_usuario;

                IF total_cur1 = qtd_cur_1 THEN
                    SET done = 1;
                END IF;

            END IF;

            SET qtd_cur_1 = qtd_cur_1 + 1;
        UNTIL done END REPEAT;
    CLOSE sexta;

    OPEN domingo;
        REPEAT
            FETCH domingo INTO v_saldo,v_hora,v_flag, v_valor, v_dia,v_rotina, v_id;
            IF NOT done2 THEN

                update tbl_cartao
                set saldo = v_saldo - v_valor
                where v_id = id_usuario and v_dia = 
                dayofweek(now());

                insert into tbl_desconto_usuarios (id_usuario,saldo_anterior, saldo_apos, data_desconto,hora_desconto)
                values(v_id, v_saldo, v_saldo - v_valor, CURRENT_DATE(),CURRENT_TIME() );

                update tbl_rotina
                set flag = 1
                where hora <= curtime() and v_dia = dayofweek(now()) and v_id = id_usuario;

                IF total_cur2 = qtd_cur_2 THEN
                    SET done2 = 1;
                END IF;

                SET qtd_cur_2 = qtd_cur_1 + 2;
            END IF;
        UNTIL done2 END REPEAT;
    CLOSE domingo;

END
    
04.12.2017 / 21:27