DELIMITER $$
DROP PROCEDURE IF EXITS cursor_example$$
CREATE PROCEDURE cursor_example()
READS SQL DATA
BEGIN
DECLARE l_employee_id INT;
DECLARE l_salary NUMERIC(8,2);
DECLARE l_department_id INT;
DECLARE done INT DEFAULT 0;
DECLARE cur1 CURSOR FOR SELECT employee_id FROM employees;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
OPEN cur1;
emp_loop: LOOP
FETCH cur1 INTO l_employee_id, l_salary, l_department_id;
IF done=1 THEN
LEAVE emp_loop;
else
select ch.curstore into l_laststore from checkstore ch where ...
insert into checkstore(medId,checkmon,laststore)
values(lmedid,currmonth,l_laststore) ;
END IF;
END LOOP emp_loop;
CLOSE cur1;
END$$
DELIMITER ; 程序问题就在这里面如果我在里面加上" select ch.curstore into l_laststore from checkstore ch where ..."语句时只能插入一条语句,如果没有这条查询语句就能执行循环,
IF done=1 THEN
LEAVE emp_loop;
else
select ch.curstore into l_laststore from checkstore ch where ...
insert into checkstore(medId,checkmon,laststore)
values(lmedid,currmonth,l_laststore) ;
我现在是想得到得到对应的ID之后要做汇总,汇总数据分别通过不同的查询得到,然后到插入到一张汇总表,可这样实现就有问题了,望高手指点。
DROP PROCEDURE IF EXITS cursor_example$$
CREATE PROCEDURE cursor_example()
READS SQL DATA
BEGIN
DECLARE l_employee_id INT;
DECLARE l_salary NUMERIC(8,2);
DECLARE l_department_id INT;
DECLARE done INT DEFAULT 0;
DECLARE cur1 CURSOR FOR SELECT employee_id FROM employees;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
OPEN cur1;
emp_loop: LOOP
FETCH cur1 INTO l_employee_id, l_salary, l_department_id;
IF done=1 THEN
LEAVE emp_loop;
else
select ch.curstore into l_laststore from checkstore ch where ...
insert into checkstore(medId,checkmon,laststore)
values(lmedid,currmonth,l_laststore) ;
END IF;
END LOOP emp_loop;
CLOSE cur1;
END$$
DELIMITER ; 程序问题就在这里面如果我在里面加上" select ch.curstore into l_laststore from checkstore ch where ..."语句时只能插入一条语句,如果没有这条查询语句就能执行循环,
IF done=1 THEN
LEAVE emp_loop;
else
select ch.curstore into l_laststore from checkstore ch where ...
insert into checkstore(medId,checkmon,laststore)
values(lmedid,currmonth,l_laststore) ;
我现在是想得到得到对应的ID之后要做汇总,汇总数据分别通过不同的查询得到,然后到插入到一张汇总表,可这样实现就有问题了,望高手指点。
SET done=0;
...