CODEWITHSUNDEEP
mysqlsqldatabase
Víctor
Víctor

Reputation: 15

Error with cursor in MySQL

I have the next error in the next Script with cursor in MySQL. "Script line: 4 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'declare c_salari cursor for select salario from empleados;

open c_salari;

' at line 10 "

DELIMITER $$

DROP FUNCTION IF EXISTS `empresa`.`EmpleatsRang` $$
CREATE FUNCTION `empresa`.`EmpleatsRang` (valor_inicial int, valor_final int) RETURNS INT
BEGIN
declare i int default 0;
declare resultat int default 0;
declare totalemp int;
declare v_salario int;
declare c_salari cursor for select salario from empleados;

select count(*) into totalemp from empleados;

open c_salari;

while i<totalemp do
  fetch c_salari into v_salario;
  if salari >= valor_inicial and salari <= valor_final then
    resultat=resultat+1;
  end if;
  i=i+1;
end while;

close c_salari;
return resultat;
END $$

DELIMITER ;

Upvotes: 0

Views: 112

Answers (4)

Ritesh Fondke
Ritesh Fondke

Reputation: 122

please use semicolon after end while

Upvotes: 0

V&#237;ctor
V&#237;ctor

Reputation: 15

It was because of i don't put set into variable "i" and variable "resultat"

while i<totalemp do
  fetch c_salari into v_salario;
  if salari >= valor_inicial and salari <= valor_final then
    set resultat=resultat+1; /*Need set*/
  end if;
  set i=i+1; /*Need set*/
end while;

Upvotes: 1

Ritesh Fondke
Ritesh Fondke

Reputation: 122

All declare statement should come after begin itself and before any select . so write 

Declare c_salari cursor for select salario from empleados;

before 

select count(*) into totalemp from empleados;

Upvotes: 2

Radim Bača
Radim Bača

Reputation: 10701

Do not use a cursor for such simple task. Use SELECT instead.

select count(salario)
from empleados
where salari >= valor_inicial and salari <= valor_final

The rule of thumb say: if it is possible, use one SQL statement. If not, use more SQL statements. The cursor is the last option and it kills DBMS performance.

Upvotes: 0

Related Questions