PL/MySQL - No data - zero rows fetched, selected, or processed

noislude

Hi. When I try to run the following code, I get the error I wrote in the subject.

delimiter //
drop procedure if exists example_cursor;
create procedure example_cursor() reads sql data
begin
-- declare variables
declare var_wage numeric(8,2);
declare var_employee_id int;
declare var_department_id int;

-- declarar done state
declare done int default 0;

-- create cursor for table
declare cur1 cursor for
select employee\_id, salary, department\_id from employees;

-- set done to 1 if data wasn't found
declare continue handler for not found set done = 1;

-- open cursor
open cur1;
-- start loop
emp\_loop:loop
   -- get data from cursor
   fetch cur1 into var\_employee\_id, var\_wage, var\_department\_id;
if(done = 1) then
   leave emp\_loop;
 end if;
end loop emp\_loop;
-- close cursor
close cur1;

end;
//

CREATE TABLE `employees` (
`EMPLOYEE_ID` int(11) NOT NULL auto_increment,
`SURNAME` varchar(40) NOT NULL,
`FIRSTNAME` varchar(40) NOT NULL,
`ADDRESS1` varchar(40) NOT NULL,
`ADDRESS2` varchar(40) NOT NULL,
`ZIPCODE` varchar(6) NOT NULL,
`DATE_OF_BIRTH` datetime NOT NULL,
`PHONENO` varchar(12) NOT NULL,
`MANAGER_ID` decimal(8,0) NOT NULL,
`SALARY` decimal(8,0) NOT NULL,
`STATUS` varchar(9) NOT NULL,
`DEPARTMENT_ID` decimal(8,0) default NULL,
`COMMENT_ID` decimal(8,0) default NULL,
`DEPARTMENT_NAME` varchar(40) default NULL,
`ADJUSTED_SALARY` decimal(8,0) default NULL,
`HIRE_DATE` datetime default NULL,
PRIMARY KEY (`EMPLOYEE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

GuyThiebaut

Have a look at this PL/SQL cursor example[^]. The example towards the bottom may help(Cursor with a While Loop).

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

noislude

thanks for the attention GuyThiebaut, I solved the problem.

noislude

Thanks GuyThiebaut. I Solved the problem even with the warning: I just printed the content with concat.

delimiter //
drop procedure if exists sp_exemplo_cursor;

create procedure sp_exemplo_cursor() reads sql data
begin
declare var_salario numeric(8,2);
declare var_empregado_id int;
declare var_departamento_id int;
declare flag_nao_encontrado int default 0;

declare csr cursor for
select employee_id, salary, department_id from employees;

declare continue handler for not found set flag_nao_encontrado = 1;

open csr;
exemplo_loop:loop
fetch csr into var_empregado_id, var_salario, var_departamento_id;
if flag_nao_encontrado = 1 then
leave exemplo_loop;
end if;
select concat(var_empregado_id, ' ', var_salario, ' ', var_departamento_id);
end loop exemplo_loop;
close csr;
end //