cursor
1. declare
eg: declare c1 cursor for ....(sql select salary from employees;)
declare <游标名> cursor for <select语句>;
2. open
eg: open c1;
open <游标名>;
3. operation
eg: loop
fetch c1 into a;
end loop;
fetch <游标名> into <变量1>,<变量2>...
4. close
eg: close c1;
close <游标名>;
举例说明:
delimiter //
create procedure p_11()
begin
declare c_total float(8,2);
declare c_name varchar(20);
declare c_salary float(8,2);
declare c_comm float(3,2);
declare c1 cursor for select name, salary, comm from employees;
declare exit handler for not found
begin close c1; end;
open c1;
loop
fetch c1 into c_name, c_salary, c_comm;
select ( c_salary * ifnull(c_comm,0) + c_salary ) into c_total from dual;
update salary set salary=c_total where name=c_name;
end loop;
close c1;
end
//
delimiter ;