CREATE PROCEDURE `test`.`new_procedure` ()BEGIN  -- 需要定义接收游标数据的变量   DECLARE a CHAR(16);  -- 声明游标的结束标志  DECLARE done INT DEFAULT FALSE;  -- 将所需数据赋予游标，游标必须定义在变量/条件后，handler前；否则会报错。
　-- ERROR 1337 (42000): Variable or condition declaration after cursor or handler declaration
  DECLARE cur CURSOR FOR SELECT i FROM test.t;  -- 将结束标志绑定到游标，如果捕获到not found异常时就会将变量done设置为TRUE，done=TRUE可以当作循环跳出条件  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;  -- 打开游标  OPEN cur;    -- 开始循环  read_loop: LOOP    -- 提取游标里的数据，这里只有一个，也可以有多个，例如fetch <游标名> into <变量1>,<变量2>    FETCH cur INTO a;    -- 声明结束的时候    IF done THEN      LEAVE read_loop;    END IF;    -- 这里做你想做的循环的事件    sql;  END LOOP;  -- 关闭游标  CLOSE cur;END

drop procedure if exists test_proce2;  create procedure test_proce2()  begin      declare temp_id int(11);      declare temp_time datetime;      declare isFinished boolean default false;      declare test_cursor cursor for select id,time from test;      declare continue handler for not found set isFinished=true;      open test_cursor;      repeat          fetch test_cursor into temp_id,temp_time;          if not isFinished then              select concat(concat(temp_id,":"),temp_time);          end if;      until isFinished end repeat;     close test_cursor;    end  

 drop procedure if exists test_proce3;  create procedure test_proce3()  begin      declare temp_id int(11);      declare temp_time datetime;      declare isFinished boolean default false;      declare test_cursor cursor for select id,time from test;      declare continue handler for not found set isFinished=true;      open test_cursor;      test_loop:loop          fetch test_cursor into temp_id,temp_time;          if isFinished then              leave test_loop;          end if;          //若该if语句放在fetch后面，该循环为while型；若该if语句紧接在end loop前该循环为do while型。          select concat(concat(temp_id,":"),temp_time);      end loop test_loop;    close test_cursor;   end  

DELIMITER $$CREATE EVENT `report`.`monitor_user4cx` ON SCHEDULE EVERY 15 MINUTE  DO BEGINDECLARE cx_id INT(10);DECLARE t_query VARCHAR(500);DECLARE done INT DEFAULT FALSE;DECLARE cur CURSOR FOR SELECT id FROM information_schema.PROCESSLIST WHERE `USER`=‘cx‘ AND `TIME` > 600 AND `Command`=‘Query‘;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;OPEN cur;read_loop:LOOPFETCH cur INTO cx_id;IF done THENLEAVE read_loop;END IF;SELECT t.trx_query,t.trx_started,p.`USER` FROM information_schema.PROCESSLIST p INNER JOIN information_schema.innodb_trx t ON p.id=t.trx_mysql_thread_id WHERE p.id=cx_id INTO @t_query,@t_time,@p_user;INSERT INTO test.monitor_user4cx(`p_id`,`start_time`,`user`,`time`,`query`) VALUES (cx_id,@t_time,@p_user,NOW(),@t_query);KILL cx_id;END LOOP;CLOSE cur;END$$DELIMITER ;