MySQL存储来自SELECT语句的过程变量

I'm trying to create a stored procedure. Here's what I have so far (not working):

我正在尝试创建一个存储过程。这是我到目前为止(不工作):

DELIMITER |
CREATE PROCEDURE getNearestCities(IN cityID INT)
    BEGIN
        DECLARE cityLat FLOAT;
        DECLARE cityLng FLOAT;
        SET cityLat = SELECT cities.lat FROM cities WHERE cities.id = cityID;
        SET cityLng = SELECT cities.lng FROM cities WHERE cities.id = cityID;
        SELECT *, HAVERSINE(cityLat,cityLng, cities.lat, cities.lng) AS dist FROM cities ORDER BY dist LIMIT 10;
    END |

HAVERSINE is a function I created which works fine. As you can see I'm trying to take the id of a city from the cities table and then set cityLat and cityLng to some other values of that record. I'm obviously doing this wrong here by using SELECTs.

HAVERSINE是我创建的一个功能很好的功能。正如您所看到的,我正在尝试从cities表中获取城市的id,然后将cityLat和cityLng设置为该记录的其他值。我在这里使用SELECTs显然做错了。

Is this even possible. It seems it should be. Any help whatsoever will be greatly appreciated.

这是否可能。它似乎应该是。任何帮助将不胜感激。

2 个解决方案

#1

Corrected a few things and added an alternative select - delete as appropriate.

纠正了一些事情,并添加了另一种选择 - 适当删除。

DELIMITER |

CREATE PROCEDURE getNearestCities
(
IN p_cityID INT -- should this be int unsigned ?
)
BEGIN

DECLARE cityLat FLOAT; -- should these be decimals ?
DECLARE cityLng FLOAT;

    -- method 1
    SELECT lat,lng into cityLat, cityLng FROM cities WHERE cities.cityID = p_cityID;

    SELECT 
     b.*, 
     HAVERSINE(cityLat,cityLng, b.lat, b.lng) AS dist 
    FROM 
     cities b 
    ORDER BY 
     dist 
    LIMIT 10;

    -- method 2
    SELECT   
      b.*, 
      HAVERSINE(a.lat, a.lng, b.lat, b.lng) AS dist
    FROM     
      cities AS a
    JOIN cities AS b on a.cityID = p_cityID
    ORDER BY 
      dist
    LIMIT 10;

END |

delimiter ;

#2

You simply need to enclose your SELECT statements in parentheses to indicate that they are subqueries:

您只需将SELECT语句括在括号中以表明它们是子查询:

SET cityLat = (SELECT cities.lat FROM cities WHERE cities.id = cityID);

Alternatively, you can use MySQL's SELECT ... INTO syntax. One advantage of this approach is that both cityLat and cityLng can be assigned from a single table-access:

或者,您可以使用MySQL的SELECT ... INTO语法。这种方法的一个优点是可以从单个表访问分配cityLat和cityLng:

SELECT lat, lng INTO cityLat, cityLng FROM cities WHERE id = cityID;

However, the entire procedure can be replaced with a single self-joined SELECT statement:

但是,整个过程可以用单个自连接的SELECT语句替换:

SELECT   b.*, HAVERSINE(a.lat, a.lng, b.lat, b.lng) AS dist
FROM     cities AS a, cities AS b
WHERE    a.id = cityID
ORDER BY dist
LIMIT    10;

#1