More than one cursor in the Mysql database

Question

More than one cursor in the Mysql database

Asked 6 years, 11 months ago

Viewed 468 times

0

Do I need to run more than one cursor on the same precedent? This is 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

I don’t know if it’s right, but he never runs both cursors

– Andre Cabral

2017/12/04 at 13:05
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 usuarios
set saldo = v_saldo - v_valor
where v_id = id and v_dia = dayofweek(now());

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

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

END IF;
UNTIL done END REPEAT; CLOSE Sunday;

– Andre Cabral

2017/12/04 at 13:07
Em seguida eu colo isso 
OPEN segunda;
REPEAT
FETCH segunda INTO v_saldo, v_hora,v_flag, v_valor, v_dia, v_rotina, v_id ;
IF NOT done THEN

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

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

update rotina
set flag = 1
where hora <= curtime() and v_dia = dayofweek(now()) and v_id = id_usuario; END IF; UNTIL done END REPEAT; CLOSE second;

– Andre Cabral

2017/12/04 at 13:08
ready post in question

– Andre Cabral

2017/12/04 at 17:18

1 answer

Browser other questions tagged mysql stored-procedures

You are not signed in. Login or sign up in order to post.

by arllondias • **2,492** points · Answer 1 · 2017-12-04T20:27:55+00:00

it is possible but you have to work differently the loop, not just checking the done variable, because when the first cursor is executed it will not run the second, then we will work in another way to set whether it is complete or not we will check how many records to go through and we will set a counter and when to go through all the lines we set the variables done1 and done2, getting like this:

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