MySQL存储过程错误意外字符“:"; [英] MySQL Stored Procedure Error Unexpected character ":"
问题描述
我具有以下语句来创建存储过程,但是我不断收到位置835处的意外字符':'"错误.语句中唯一的冒号在start_loop:循环中.为什么会出现此错误,我该如何解决?
I have the following statement to create a stored procedure, but I keep getting an "Unexpected character ':' at position 835" error. The only colon in the statement is in the start_loop: loop. Why am I getting this error and how can I fix?
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_gauge_values` (in maxdate timestamp, in tagid int)
BEGIN
declare finished boolean;
declare line_timestamp timestamp;
declare line_tagid int;
declare line_name varchar(50);
declare line_value varchar(50);
-- CREATE OUR TEMP TABLE
DROP TABLE IF EXISTS GAUGE_VALUES_TEMP;
CREATE TABLE GAUGE_VALUES_TEMP (
LINE_NAME varchar(255),
LINE_VALUE varchar(50)
);
-- CREATE OUR CURSOR
DECLARE cid CURSOR FOR
SELECT MAX(hd.timestamp), hd.tag_id
FROM dashboard_lines dl
JOIN historical_data hd ON hd.tag_id = dl.gauge_tag_id
WHERE (hd.timestamp is null OR hd.timestamp <= maxdate)
AND (dl.gauge_tag_id is null OR dl.gauge_tag_id = tagid);
set finished=false;
declare continue handler for not found set finished=true;
open cid;
start_loop: loop
fetch cid into line_timestamp, line_tagid;
if finished <> false then
leave start_loop;
else
insert into gauge_values_temp (line_name, line_value)
select ol.name, hd.value
from dashboard_lines dl
join operation_lines ol on ol.line_id = dl.line_id
join historical_data hd on hd.tag_id = dl.gauge_tag_id
where dl.gauge_tag_id = line_tagid;
end if;
end loop;
close cid;
select * from gauge_values_temp;
END
$$
推荐答案
与MySQL语法非常相关的一个烦人的问题:过程中某个地方出现了TAB导致此解析问题:仅对任何缩进或对齐使用空格.
It is a very annoying MySQL syntax related issue: There is a TAB somewhere in you procedure leading to this parse issue: Use spaces only for any indentation or alignment.
我的版本(MariaDB 10.x)也不喜欢以DECLARE
顺序开始您的过程,因此我也对此进行了更新.看看它是否仍然适合您在该位置的目的.
My version (MariaDB 10.x) also does not like the start of your procedure with the DECLARE
order, so I also updated that. See if it still fits your purpose at that location.
我的数据库创建的调整后的过程没有语法错误:
The adjusted procedure which my database creates without syntax errors:
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_gauge_values` (in maxdate timestamp, in tagid int)
BEGIN
declare finished boolean;
declare line_timestamp timestamp;
declare line_tagid int;
declare line_name varchar(50);
declare line_value varchar(50);
DECLARE cid CURSOR FOR
SELECT MAX(hd.timestamp), hd.tag_id
FROM dashboard_lines dl
JOIN historical_data hd ON hd.tag_id = dl.gauge_tag_id
WHERE (hd.timestamp is null OR hd.timestamp <= maxdate)
AND (dl.gauge_tag_id is null OR dl.gauge_tag_id = tagid);
declare continue handler for not found set finished=true;
set finished=false;
DROP TABLE IF EXISTS GAUGE_VALUES_TEMP;
CREATE TABLE GAUGE_VALUES_TEMP (
LINE_NAME varchar(255),
LINE_VALUE varchar(50)
);
open cid;
start_loop: loop
fetch cid into line_timestamp, line_tagid;
if finished <> false then
leave start_loop;
else
insert into gauge_values_temp (line_name, line_value)
select ol.name, hd.value
from dashboard_lines dl
join operation_lines ol on ol.line_id = dl.line_id
join historical_data hd on hd.tag_id = dl.gauge_tag_id
where dl.gauge_tag_id = line_tagid;
end if;
end loop;
close cid;
select * from gauge_values_temp;
END
$$
这篇关于MySQL存储过程错误意外字符“:";的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!