MySQLでカーソルを使用する方法
1.カーソルを作成
DECLARE calc_bonus_startnews24 CURSOR FOR SELECT id, salary, commission FROM employees;
2.カーソルを開く
OPEN calc_bonus_startnews24;
3.カーソルを使用
FETCH calc_bonus_startnews24 INTO re_id, re_salary, re_comm;
4.カーソルをクローズ
CLOSE calc_bonus_startnews24;
5.サンプルコード：
DELIMITER //
CREATE PROCEDURE calculate_bonus_startnews24()
BEGIN
DECLARE emp_id INT;
DECLARE sal DECIMAL(8, 2);
DECLARE comm DECIMAL(3, 2);
DECLARE done INT;
DECLARE calc_bonus_startnews24 CURSOR FOR SELECT id, salary, commission FROM employees;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN calc_bonus_startnews24;
BEGIN_calc: LOOP
FETCH calc_bonus_startnews24 INTO emp_id, sal, comm;
IF done THEN
LEAVE begin_calc;
IF sal > 60000 THEN
IF comm > 0.05 THEM
UPDATE employees SET bonus_startnews24 = sal * comm WHERE id = emp_id;
ELSEIF comm <= 0.05 THEN
UPDATE employees SET bonus_startnews24 = sal * 0.03 WHERE id = emp_id;
END IF;
ELSE
UPDATE employees SET bonus_startnews24 = sal * 0.07 WHERE id = emp_id;
END IF;
END LOOP begin_calc;
CLOSE calc_bonus_startnews24;
END//
DELIMITER ;