存储过程示例3

over:BEGIN
 #Routine body goes here...

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

 declare epgname varchar(500) 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(500) 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 where a.epg_date not in (select DISTINCT epg_date from statistics.epg_program_syn) and LOCATE('55',a.epg_propertyid)>0;
 

   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
--  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
--  DECLARE CONTINUE HANDLER FOR SQLWARNING,NOT FOUND,SQLEXCEPTION set done = 1;
 
 
  create table if not EXISTS epg_program_syn (
  epg_id int(10) not null AUTO_increment,
  epg_name varchar(500),
  epg_date date,
  epg_stime datetime,
  epg_etime datetime,
  epg_channleid varchar(20),
  epg_programid varchar(20), 
  epg_propertyid varchar(500),
  PRIMARY key(epg_id),
  index(epg_channleid)
 );
 
 
   set a_oiRet = 0;
   set done = 0;
   open cur1;   
   REPEAT
    FETCH cur1 into epgname, epgstime, epgpropertyid, epgdate, epgprogramid1, epgprogramid2, epgchannleid, epgetime;
    IF NOT done THEN       
      if epgprogramid2 != '0'  then
        insert into `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 `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 if;
   UNTIL done END REPEAT;
   close cur1;
   leave over;

END

 

 

参数：OUT a_oiRet int