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$$
