sql__procedure 练习

DROP PROCEDUREIF EXISTS pro_event;CREATE PROCEDURE pro_event ()BEGINDECLARE userid INT;DECLARE msgid INT;DECLARE s INT DEFAULT 0;DECLARE user_id CURSOR FOR SELECTu_idFROM`user`;DECLARE msg_id CURSOR FOR SELECTm_idFROMmessageWHEREm_id NOT IN (SELECTm_idFROMlogWHEREsendtime >= DATE_SUB(NOW(), INTERVAL 6 MINUTE));DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'SET s = 1;OPEN msg_id;FETCH msg_id INTO msgid;WHILE s <> 1 DOOPEN user_id;FETCH user_id INTO userid;WHILE s <> 1 DOINSERT INTO log(u_id, m_id, sendtime)VALUES(userid, msgid, SYSDATE());FETCH user_id INTO userid;ENDWHILE;SET s = 1;FETCH msg_id INTO msgid;ENDWHILE;CLOSE user_id;CLOSE msg_id;END;CALL pro_event ();CREATE EVENTIF NOT EXISTS e_test ON SCHEDULE EVERY 1 MINUTE ON COMPLETION PRESERVE DOCALL pro_event ();