MySQL create function时报错

末蓝、 2024-04-18 01:18 137阅读 0赞

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2124694/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2124694/