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