mysql 存储过程退出时报错

[Leslie-M]

错误:
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/*100,3), '%') AS `Percentage` FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=' at line 1

存储过程如下：

DECLARE count INT DEFAULT 0;
declare synMaxDate date default '';
declare epgMaxDate date default '';

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, (select max(epg_date) as synMaxDate from statistics.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;
-- DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-- DECLARE EXIT 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)
);


select t1.MaxDate into synMaxDate from (select max(epg_date) AS MaxDate from statistics.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 = '' and synMaxDate = '' THEN
set a_oiRet = 1; -- 两张表均为空， 结束
leave over;
end if;


if synMaxDate is not null THEN
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
-- SET count = count + 1;
if epgprogramid2 != '0' then
insert into statistics.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 statistics.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;
else
set a_oiRet = 0;
set done = 0;
open cur2;
REPEAT
FETCH cur2 into epgname, epgstime, epgpropertyid, epgdate, epgprogramid1, epgprogramid2, epgchannleid, epgetime;
IF NOT done THEN
-- SET count = count + 1;
if epgprogramid2 != '0' then
insert into statistics.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 statistics.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;
-- SELECT count, epgname, epgstime, epgpropertyid, epgdate, epgprogramid1, epgprogramid2, epgchannleid, epgetime;
-- SELECT count;
close cur2;
leave over;
end if;


每次我实际执行的是cur2的循环插入， 插入结束退出时就报上述错误

[Leslie-M]

有没有哪位帮我看看啊

[知道就是你]

我把你的脚本执行（保存存储过程）了一下，首先发现 “ a_oiRet ” 没有定义，如果是临时是变量需要前面加上“@” 如 @a_oiRet ，不然需要以declare方式声明变量 。 你说是存储过程执行结束后报错 ？没有相关表和数据，真没有人知道导致出错的原因 ！

[Leslie-M]

@JenMinZhang @a_oiRet 是我在外部传入的参数OUT a_oiRet int， 用于存储过程的返回值，

[rucypli]

奇怪 为什么你语法报错的语句没有在存储过程里搜出来呢

[Leslie-M]

是在游标cur2 循环取值判断插入， 结束时报的这个错， 这个错每次必现， 插入40多万条时报的这个错