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