RRcoder
10/4/2018 - 1:41 PM

mysql how to loop a cursor for select query in a stored function

show how to make a select query inside a stored function and loop through a cursor

stored_function_with_cursor
content_copyfile_download
drop function TodasTareasCerradas;
delimiter $$
create function TodasTareasCerradas(n_nroorden int)
returns integer
BEGIN
declare nregs int default 0;
declare ncerr int default 0;
declare a1 int;
declare a2 int;
declare a3 int;
declare a4 int;
DECLARE accion INT DEFAULT FALSE;
DECLARE cur1 CURSOR FOR select nroorden, service, tarea, (select count(*) from operserv o where o.nroorden= o1.nroorden and o.service=o1.service and o.tarea=o1.tarea and estado='C' ) as cant_cerr from ordserv1 o1 where o1.nroorden=n_nroorden;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET accion = TRUE;
OPEN cur1;
my_loop: REPEAT
    FETCH cur1 INTO a1,a2,a3,a4;
    set ncerr=ncerr + a4; 
    set nregs=nregs + 1;
until accion
END REPEAT my_loop;
CLOSE cur1;
if ncerr<>nregs then
    return 0;
else
    return 1;
end if; 

END
$$
delimiter ;