Mysql 创建函数不起作用

Question

我尝试在我的 mysql server 5.5 中创建这个函数，我从 mysql 中得到标准错误:

1064 - 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 '' at line 5

CREATE FUNCTION isInArea(p POINT, poly POLYGON)
    RETURNS INT(1)
    DETERMINISTIC
    BEGIN
        DECLARE n INT DEFAULT 0;
        DECLARE pX DECIMAL(9, 6);
        DECLARE pY DECIMAL(9, 6);
        DECLARE ls LINESTRING;
        DECLARE poly1 POINT;
        DECLARE poly1X DECIMAL(9, 6);
        DECLARE poly1Y DECIMAL(9, 6);
        DECLARE poly2 POINT;
        DECLARE poly2X DECIMAL(9, 6);
        DECLARE poly2Y DECIMAL(9, 6);
        DECLARE i INT DEFAULT 0;
        DECLARE result INT(1) DEFAULT 0; 
        SET 
            pX = X(p); 
        SET 
            pY = Y(p); 
        SET 
            ls = ExteriorRing(poly); 
        SET 
            poly2 = EndPoint(ls); 
        SET 
            poly2X = X(poly2); 
        SET 
            poly2Y = Y(poly2); 
        SET 
            n = NumPoints(ls); WHILE i < n DO 
        SET 
            poly1 = PointN(
                ls, 
                (i + 1)
            ); 
        SET 
            poly1X = X(poly1); 
        SET 
            poly1Y = Y(poly1); IF (
                (
                    (
                        (poly1X <= pX) && (pX < poly2X)
                    ) || (
                        (poly2X <= pX) && (pX < poly1X)
                    )
                ) && (
                    pY > (poly2Y - poly1Y) * (pX - poly1X) / (poly2X - poly1X) + poly1Y
                )
            ) THEN 
        SET 
            result = ! result; END IF; 
        SET 
            poly2X = poly1X; 
        SET 
            poly2Y = poly1Y; 
        SET 
            i = i + 1;
        END WHILE;
        RETURN result;
    End;

有人知道这个问题吗？

Answer 1

Add delimiter :

Each stored program contains a body that consists of an SQL statement. This statement may be a compound statement made up of several statements separated by semicolon (;) characters.

If you use the mysql client program to define a stored program containing semicolon characters, a problem arises. By default, mysql itself recognizes the semicolon as a statement delimiter, so you must redefine the delimiter temporarily to cause mysql to pass the entire stored program definition to the server.

DELIMITER //
CREATE FUNCTION isInArea(p POINT, poly POLYGON)
    RETURNS INT(1)
    DETERMINISTIC
    BEGIN
        DECLARE n INT DEFAULT 0;
        DECLARE pX DECIMAL(9, 6);
        DECLARE pY DECIMAL(9, 6);
        DECLARE ls LINESTRING;
        DECLARE poly1 POINT;
        DECLARE poly1X DECIMAL(9, 6);
        DECLARE poly1Y DECIMAL(9, 6);
        DECLARE poly2 POINT;
        DECLARE poly2X DECIMAL(9, 6);
        DECLARE poly2Y DECIMAL(9, 6);
        DECLARE i INT DEFAULT 0;
        DECLARE result INT(1) DEFAULT 0; 
        SET 
            pX = X(p); 
        SET 
            pY = Y(p); 
        SET 
            ls = ExteriorRing(poly); 
        SET 
            poly2 = EndPoint(ls); 
        SET 
            poly2X = X(poly2); 
        SET 
            poly2Y = Y(poly2); 
        SET 
            n = NumPoints(ls); WHILE i < n DO 
        SET 
            poly1 = PointN(
                ls, 
                (i + 1)
            ); 
        SET 
            poly1X = X(poly1); 
        SET 
            poly1Y = Y(poly1); IF (
                (
                    (
                        (poly1X <= pX) && (pX < poly2X)
                    ) || (
                        (poly2X <= pX) && (pX < poly1X)
                    )
                ) && (
                    pY > (poly2Y - poly1Y) * (pX - poly1X) / (poly2X - poly1X) + poly1Y
                )
            ) THEN 
        SET 
            result = ! result; END IF; 
        SET 
            poly2X = poly1X; 
        SET 
            poly2Y = poly1Y; 
        SET 
            i = i + 1;
        END WHILE;
        RETURN result;
    End//


DELIMITER ;