mysql存储过程例子

DELIMITER $$


DROP PROCEDURE IF EXISTS `lim`.`fatatopsum_proc`$$


CREATE DEFINER=`root`@`%` PROCEDURE `fatatopsum_proc`()
BEGIN
         DECLARE host_id int(11);
         DECLARE hostname_ varchar(256);
         DECLARE triggerid_ int(11);
         DECLARE triggername_ varchar(256);
         DECLARE eventid_ int(11);
         DECLARE clock_ int(11);
         DECLARE priority_ int(11);
           /********************week**********************/
BEGIN
    DECLARE Done1 INT DEFAULT 0;
    DECLARE cur_item cursor for SELECT q.hostid_v2 as hostid_v,q.description_v1 as description_v,q.tid as triggerid_v,e.eventid as eventid_v,
q.ffhost_v2 as  host_v ,e.clock as clock_v  ,q.priority_v2 as priority_v /*,count(*) as ttr*/ FROM events e ,
(SELECT t.triggerid as tid,t.description as description_v1, t.priority as priority_v2,ifh.ffhost as ffhost_v2,ifh.ffhostid as hostid_v2 
FROM triggers t,(SELECT ff.triggerid as fftid,ih.host_v1 as ffhost,ih.hostid_v1 as ffhostid FROM functions ff,
( SELECT ii.itemid as itemid_v,hh.host as host_v1,hh.hostid as hostid_v1 FROM items ii,hosts hh WHERE  hh.hostid=ii.hostid /*AND ( ii.status=0 and hh.status=0 )*/ ) ih
WHERE ih.itemid_v=ff.itemid) ifh where t.triggerid=ifh.fftid AND t.priority>1 and t.status=0 /*AND t.value IN ('1')*/ )  q 
WHERE (e.objectid IN (q.tid)) /* and DATE_SUB(CURDATE(), INTERVAL  1 year) < date(FROM_UNIXTIME(e.clock))*/ AND ( (e.object IN ('0')) /*AND (e.value IN ('1'))*/ AND (e.value_changed IN ('1')) );
/*group by triggerid_v order by ttr desc*/
             DECLARE continue handler for not found set Done1 = 1;
    OPEN cur_item;
    FETCH   NEXT   FROM  cur_item  INTO  host_id,triggername_,triggerid_,eventid_,hostname_,clock_,priority_;

REPEAT
   IF NOT Done1 THEN
     
     insert into report_top(id,hostid,hostname,triggerid,triggername,eventid,clock,priority)  values('', host_id,hostname_,triggerid_,triggername_,eventid_,clock_,priority_);
   END IF;
    FETCH NEXT FROM cur_item INTO host_id,triggername_,triggerid_,eventid_,hostname_,clock_,priority_;
    UNTIL Done1 END REPEAT;
    CLOSE cur_item;
    END;
    END$$


DELIMITER ;