为什么在Django中使用callproc()时出现PROCEDURE不存在？

Question

我正在尝试通过游标和callproc()来使用一个存储过程，但我总是遇到错误：

OperationalError: (1305, 'PROCEDURE myapp.LatLonDistance does not exist')

这是我应用中抛出错误的那段代码：

cursor = connection.cursor()
result = cursor.callproc("myapp.LatLonDistance", (lat1, lon1, lat2, lon2))
cursor.close()

这是我可以在数据库上直接运行的查询，它运行得很好：

SELECT id,myapp.LatLonDistance(lat1, lon1, lat2, lon2) AS distance FROM myapp.users_userprofile;

这是我用来将存储过程写入数据库的脚本：

delimiter //
CREATE FUNCTION airrun.LatLonDistance (lat1 double, lon1 double, lat2 double, lon2 double)
RETURNS double
DETERMINISTIC
READS SQL DATA
BEGIN

        DECLARE theta double;
        DECLARE dist double;
        DECLARE miles double;
        SET theta = lon1 - lon2; 
        SET dist = SIN(RADIANS(lat1)) * SIN(RADIANS(lat2)) + COS(RADIANS(lat1)) * COS(RADIANS(lat2)) * COS(RADIANS(theta)); 
        SET dist = ACOS(dist); 
        SET dist = DEGREES(dist); 
        SET miles = dist * 60 * 1.1515;


        RETURN miles;
END
//
delimiter ;

我使用的是MySQL数据库。有什么想法吗？

Answer 1

因为那是一个函数，而不是一个存储过程，所以你可以用 cursor.execute 来调用它：

cursor = connection.cursor()
result = cursor.execute("SELECT id,myapp.LatLonDistance(%s, %s, %s, %s) AS distance FROM myapp.users_userprofile", (lat1, lon1, lat2, lon2))
cursor.close()