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