游标的使用

CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE a CHAR(16);
  DECLARE b,c INT;
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur1;
  OPEN cur2;

  REPEAT
    FETCH cur1 INTO a, b;
    FETCH cur2 INTO c;
    IF NOT done THEN
       IF b < c THEN
          INSERT INTO test.t3 VALUES (a,b);
       ELSE
          INSERT INTO test.t3 VALUES (a,c);
       END IF;
    END IF;
  UNTIL done END REPEAT;

  CLOSE cur1;
  CLOSE cur2;
END

 

begin

        declare done int default 0;
        declare _employeeid int;
        declare _grouptime  varchar(15);
        declare _monthplanjobnum  int;
        declare _finishrate float;
        declare num int default 0;
    DECLARE cur_1 CURSOR FOR
    select  com_employee._superior as superior,com_employee.id as employeeid,com_employee._name as employeename,count(com_assignment.id) as jobnum,round(sum(com_receipt._plan)/100/count(com_assignment.id),4) as finishrate, concat(date_format(_createtime, '%Y-%m'),"-01") as grouptime
     from com_assignment  right join  com_employee on com_assignment._employeeId=com_employee.id left join com_receipt on com_assignment.id=com_receipt._assignmentId
    group by com_employee.id, grouptime;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
        OPEN cur_1;
        loop1:loop
             FETCH cur_1 INTO _superid, _employeeid, _employeename,_jobnum,_finishrate,_grouptime;
             if done=1   then
                          leave loop1;
             end if;
        select _grouptime;
             select @num:= count(*)  from employeetaskprogress where employeename=_employeename and grouptime=_grouptime;
             if @num<1
        then
            insert into employeetaskprogress(superid,employeeid,employeename,jobnum,finishrate,grouptime) values(_superid,_employeeid,_employeename,_jobnum,_finishrate,_grouptime);
            else
                update employeetaskprogress  set superid=_superid,employeeid=_employeeid,grouptime=_grouptime,jobnum=_jobnum,finishrate=_finishrate where employeename=_employeename and grouptime=_grouptime;
        end if;
        end loop loop1;
       close cur_1;
       select num;
end