在mysql bigint变量声明内部自定义nextval函数中出现错误 [英] Error in mysql bigint variable declaration inside custom nextval function
问题描述
我忘了put delimiter指令,一些分号,并使用tsl语法,如[select variable = field],在mysql中无效。
当您使用tsl语法时,Mysql错误是[不允许返回从函数的结果集]和不帮助很多。
@AndreKR指向我,谢谢。
Im使用mysqlworkbench 5.2.30 CE。
工作函数成为:
分隔符//
CREATE FUNCTION nextval(seq_name varchar(100))
RETURNS bigint(20)
READS SQL DATA
NOT DETERMINISTIC
BEGIN
DECLARE workval bigint(20);
SELECT count(1)into workval
FROM tip_sequence
WHERE sequencename = seq_name;
IF workval<> 1 THEN
DELETE
FROM tip_sequence
WHERE sequencename = seq_name;
INSERT
INTO tip_sequence(sequencename,sequenceval,sequencestep)
VALUES(seq_name,1,1);
END IF;
SELECT sequenceval into workval
FROM tip_sequence
WHERE sequencename = seq_name;
UPDATE tip_sequence
SET sequenceval = sequenceval + sequencestep
WHERE sequencename = seq_name;
RETURN workval;
END //
delimiter;
解决方案由于 DECLARE workval bigint (20);
行是第一个带有分号的末尾,我怀疑您在输入功能代码之前忘记更改分隔符(虽然这取决于您使用的客户端)。
尝试将您的代码更改为:
DELIMITER#
CREATE FUNCTION nextval(seq_name varchar(100))
...
END#
I forget put delimiter directive, some semicolon and was using tsl syntax like [select variable = field] that is not valid in mysql.
Mysql error when you use tsl syntax is [not allowed to return a result set from a function] and dont help much.
@AndreKR point all of it to me, thanks.
Im using mysqlworkbench 5.2.30 CE.
The work function become:
delimiter //
CREATE FUNCTION nextval (seq_name varchar(100))
RETURNS bigint(20)
READS SQL DATA
NOT DETERMINISTIC
BEGIN
DECLARE workval bigint(20);
SELECT count(1) into workval
FROM tip_sequence
WHERE sequencename = seq_name;
IF workval <> 1 THEN
DELETE
FROM tip_sequence
WHERE sequencename = seq_name;
INSERT
INTO tip_sequence (sequencename, sequenceval, sequencestep)
VALUES (seq_name, 1, 1);
END IF;
SELECT sequenceval into workval
FROM tip_sequence
WHERE sequencename = seq_name;
UPDATE tip_sequence
SET sequenceval = sequenceval + sequencestep
WHERE sequencename = seq_name;
RETURN workval;
END//
delimiter ;
解决方案 Since the DECLARE workval bigint(20);
line is the first one with a semicolon at the end, I suspect you forgot to change the delimiter before inputting the function code (though this depends on the client you're using).
Try changing your code to:
DELIMITER #
CREATE FUNCTION nextval (seq_name varchar(100))
...
END#
这篇关于在mysql bigint变量声明内部自定义nextval函数中出现错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!