MySQL create function时报错
mysql> delimiter $$
mysql> CREATE FUNCTION is_ChangeDescColumnExist (THE_VERSION_LEVEL_TABLE_NAME VARCHAR(35)) RETURN INT DETERMINISTIC
-> BEGIN
-> DECLARE nbr INT;
-> SET nbr =0;
-> /* DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN 0;*/
-> select count(*) into nbr from information_schema.columns where table_name = THE_VERSION_LEVEL_TABLE_NAME and
-> (column_name ='C_CHANGE_DESCRIPTION');
-> IF nbr = 1 THEN
-> RETURN 1;
-> ELSE
ELSE ELSEIF
-> ELSE
-> RETURN 0;
-> END IF;
-> END$$
ERROR 1064 (42000): 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 'RETURN INT DETERMINISTIC
BEGIN
DECLARE nbr INT;
SET nbr =0;
sel' at line 1
报错原因:
RETURN INT DETERMINISTIC语句中返回关键字应该是RETURNS
修改后,创建函数正常
delimiter $$
CREATE FUNCTION is_ChangeDescColumnExist (THE_VERSION_LEVEL_TABLE_NAME VARCHAR(35)) RETURNS INT DETERMINISTIC
BEGIN
DECLARE nbr INT;
SET nbr =0;
/* DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN 0;*/
select count(*) into nbr from information_schema.columns where table_name = THE_VERSION_LEVEL_TABLE_NAME and
(column_name ='C_CHANGE_DESCRIPTION');
IF nbr = 1 THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END$$
mysql> delimiter $$
mysql> CREATE FUNCTION is_ChangeDescColumnExist (THE_VERSION_LEVEL_TABLE_NAME VARCHAR(35)) RETURN INT DETERMINISTIC
-> BEGIN
-> DECLARE nbr INT;
-> SET nbr =0;
-> /* DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN 0;*/
-> select count(*) into nbr from information_schema.columns where table_name = THE_VERSION_LEVEL_TABLE_NAME and
-> (column_name ='C_CHANGE_DESCRIPTION');
-> IF nbr = 1 THEN
-> RETURN 1;
-> ELSE
ELSE ELSEIF
-> ELSE
-> RETURN 0;
-> END IF;
-> END$$
ERROR 1064 (42000): 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 'RETURN INT DETERMINISTIC
BEGIN
DECLARE nbr INT;
SET nbr =0;
sel' at line 1
报错原因:
RETURN INT DETERMINISTIC语句中返回关键字应该是RETURNS
修改后,创建函数正常
delimiter $$
CREATE FUNCTION is_ChangeDescColumnExist (THE_VERSION_LEVEL_TABLE_NAME VARCHAR(35)) RETURNS INT DETERMINISTIC
BEGIN
DECLARE nbr INT;
SET nbr =0;
/* DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN 0;*/
select count(*) into nbr from information_schema.columns where table_name = THE_VERSION_LEVEL_TABLE_NAME and
(column_name ='C_CHANGE_DESCRIPTION');
IF nbr = 1 THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END$$
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26506993/viewspace-2124694/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26506993/viewspace-2124694/