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 create function時報錯

相關文章