mysql 存储过程示例1

over:BEGIN
 #Routine body goes here...

 -- 同步备份epg_program表
 -- AUTHORS ：zm
 -- 2013年8月7日11:29:02

-- 参数OUT a_oiRet int
 
 DECLARE count INT DEFAULT 0;
 declare synMaxDate date default '';
 declare epgMaxDate date default '';

 declare epgname varchar(300) default '';
 declare epgdate date default '';
 declare epgstime datetime default '';
 declare epgetime datetime default '';
 declare epgchannleid varchar(20) default '';
 declare epgprogramid1 varchar(20) default '';
 declare epgprogramid2 varchar(20) default '';
 declare epgpropertyid varchar(300) default '';

 DECLARE done INT DEFAULT 0; 
 
 DECLARE cur1 CURSOR FOR
 select a.epg_name, a.epg_time, a.epg_propertyid, a.epg_date, a.epg_fid, a.epg_fid2, a.epg_channleid, a.epg_etime
 from haierdb.epg_program a, (select max(epg_date) as synMaxDate from haierdb.epg_program_syn) as t1,
 (select max(epg_date) as epgMaxDate from haierdb.epg_program ) as t2 where a.epg_date > t1.synMaxDate
 and a.epg_date <= t2.epgMaxDate and LOCATE('55',a.epg_propertyid)>0;
 
 DECLARE cur2 CURSOR FOR
 select a.epg_name, a.epg_time, a.epg_propertyid, a.epg_date, a.epg_fid, a.epg_fid2, a.epg_channleid, a.epg_etime
 from haierdb.epg_program a, (select min(epg_date) as epgMinDate from haierdb.epg_program) as t1,
 (select max(epg_date) as epgMaxDate from haierdb.epg_program ) as t2 where a.epg_date >= t1.epgMinDate
 and a.epg_date <= t2.epgMaxDate and LOCATE('55',a.epg_propertyid)>0;

 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  create table if not EXISTS epg_program_syn (
  epg_id int(10) not null AUTO_increment,
  epg_name varchar(300),
  epg_date date,
  epg_stime datetime,
  epg_etime datetime,
  epg_channleid varchar(20),
  epg_programid varchar(20), 
  epg_propertyid varchar(300),
  PRIMARY key(epg_id),
  index(epg_channleid)
 );

 select t1.MaxDate into synMaxDate from (select max(epg_date) AS MaxDate from haierdb.epg_program_syn ) as t1;
 select t2.MaxDate into epgMaxDate from (select max(epg_date) AS MaxDate from haierdb.epg_program ) as t2;

 if epgMaxDate is null and synMaxDate is null THEN
   set a_oiRet = 1;  -- 两张表均为空， 结束
   leave over;
 end if;
 
 if synMaxDate is not null THEN
   set a_oiRet = 0;
   open cur1;
   WHILE done <> 1 do
    SET count = count + 1;
    SET done = 0;
    FETCH cur1 into epgname, epgstime, epgpropertyid, epgdate, epgprogramid1, epgprogramid2, epgchannleid, epgetime;
      if epgprogramid2 != '0'  then
        insert into haierdb.epg_program_syn(epg_name, epg_date, epg_stime, epg_etime, epg_channleid, epg_programid, epg_propertyid)
        values(epgname, epgdate, epgstime, epgetime, epgchannleid, epgprogramid2, epgpropertyid);
      elseif epgprogramid1 != '0' then
        insert into haierdb.epg_program_syn(epg_name, epg_date, epg_stime, epg_etime, epg_channleid, epg_programid, epg_propertyid)
        values(epgname, epgdate, epgstime, epgetime, epgchannleid, epgprogramid1, epgpropertyid);
    end if; 

   END WHILE;
   close cur1;
   leave over;
 else
   set a_oiRet = 0;
   open cur2;
   WHILE done <> 1 do
    SET count = count + 1;
    SET done = 0;
    FETCH cur2 into epgname, epgstime, epgpropertyid, epgdate, epgprogramid1, epgprogramid2, epgchannleid, epgetime;
      if epgprogramid2 != '0'  then
        insert into haierdb.epg_program_syn(epg_name, epg_date, epg_stime, epg_etime, epg_channleid, epg_programid, epg_propertyid)
        values(epgname, epgdate, epgstime, epgetime, epgchannleid, epgprogramid2, epgpropertyid);
      elseif epgprogramid1 != '0' then
        insert into haierdb.epg_program_syn(epg_name, epg_date, epg_stime, epg_etime, epg_channleid, epg_programid, epg_propertyid)
        values(epgname, epgdate, epgstime, epgetime, epgchannleid, epgprogramid1, epgpropertyid);
      end if;  
      
   END WHILE;
   -- SELECT count, epgname, epgstime, epgpropertyid, epgdate, epgprogramid1, epgprogramid2, epgchannleid, epgetime;
   close cur2;
   leave over;
 end if;

END