mysql 存储过程退出时报错

Leslie-M Leslie-M 墨衍会员 · AI 创作全网分发 墨衍智能分发 通过墨衍一键同步至各平台 可同步平台 CSDN微信公众号微博知乎掘金百家号博客园抖音小红书 你的文章也可以这样分发 墨衍会员 ¥399起/年,写一次发全网 我也要 + 加入社区
错误:
[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的循环插入, 插入结束退出时就报上述错误
...全文

相关推荐

cmd进入MySQL数据库[代码]

本文详细介绍了如何通过cmd命令进入MySQL数据库的操作步骤。首先需要打开cmd,然后输入MySQL安装的盘路径和完整的mysql.exe安装路径。接着输入包含主机名、用户名和密码的连接命令,注意每个-前需有空格。成功连接后,可以执行如show databases;等语句查看现有数据库,或通过create database demo;创建新数据库。最后,输入exit;即可退出操作。整个过程简洁明了,适合初学者快速掌握。

放屁带闪电

MySQL命令行使用问题[源码]

文章解释了在DOS窗口中使用MySQL时,输入命令后只出现箭头符号的原因,即未使用分号作为命令结束符。同时,文章提供了MySQL的基本规则,如关键字与函数名建议大写、数据库和表名称小写、SQL语句必须以分号结尾等。此外,还列举了一些基本的MySQL命令,包括创建和删除数据库、创建和删除新表等操作,为初学者提供了实用的参考信息。

moss5

MySQL备份工具mysqldump详解[源码]

本文详细介绍了MySQL数据库备份工具mysqldump的常用方法及参数说明。主要内容包括:导出整个数据库(含数据)、仅导出数据库结构、导出单张数据表(含数据或仅结构)等基础操作命令。文章还列举了--all-databases、--no-tablespaces、--add-drop-table等20余个常用参数的具体用法及注意事项。针对大数据量备份场景,特别说明了-q参数的重要性,指出其可避免内存溢出风险。最后提醒备份时需注意DDL操作可能导致的元数据锁冲突问题,建议调整备份窗口时间或手动重试。全文系统性地整理了mysqldump的实用技巧,适合数据库管理员参考使用。

l1k9j8h7g6

Mysql通过存储过程分割字符串为数组

分割字符串为数组需要用到 三个mysql 的函数 : REVERSE(str) 返回颠倒字符顺序的字符串str。 SUBSTRING_INDEX(str,delim,count) 返回从字符串str的第count个出现的分隔符delim之后的子串。如果count是正数,返回最后的分隔符到左边(从左边数) 的所有字符。如果count是负数,返回最后的分隔符到右边的所有字符(从右边数)。 REPLACE(str,from_str,to_str) 返回字符串str,其字符串from_str的所有出现由字符串to_str代替。 通过三个函数的组合使用做到分割字符串为数组的功能。 首先写两个函数 1

weixin_38617297

mysql存储过程之错误处理实例详解

主要介绍了mysql存储过程之错误处理,结合实例形式详细分析了mysql存储过程错误处理相关原理、操作技巧与注意事项,需要的朋友可以参考下

weixin_38699302
评论 5
收藏 已收藏
分享
返回顶部
Leslie-M
MySQL 57,062 成员 56,759 内容