MYSQL存储过程，着实无法，求解释？

MYSQL存储过程，实在无法，求解释？？

SQL code

BEGIN        DECLARE Pmax INT; -- 记录统计表中最大ID     DECLARE Pmin INT; -- 记录统计表中最小ID     DECLARE PID SMALLINT(6);-- 记录当前统计Pack_ID        DELETE FROM packcollectionrank;-- 清空表    INSERT INTO packcollectionrank(Pack_ID) SELECT DISTINCT Pack_ID FROM products ;-- 插入所有Pack_ID到统计表packcollectionrank    SELECT MAX(ID) INTO Pmax FROM packcollectionrank;    SELECT MIN(ID) INTO Pmin FROM packcollectionrank;    WHILE Pmin<=Pmax DO-- 插入所有Pack_ID的人气，没订阅默认为0       SELECT Pack_ID INTO PID FROM packcollectionrank WHERE ID=Pmin;       SET @sql1=CONCAT("UPDATE packcollectionrank SET Collection_count=(SELECT COUNT(*) FROM userservicerelation WHERE Pack_ID=",PID," AND Relation_Status=6) WHERE Pack_ID=",PID,";");         PREPARE rankpack1 FROM @sql1;       EXECUTE rankpack1;          SET Pmin=Pmin+1;    END WHILE;    -- 插入END

大神们看下哪有问题吧！！自己机子上执行好好的，到别人机子上一直报错：

SQL code

[Err] 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 'declare Pmax int; 

我用的是Navicat for MySQL对方用的是mysql-pront.

[解决办法]
DELIMITER $$
CREATE PROCEDURE ff1()
BEGIN

DECLARE Pmax INT; -- 记录统计表中最大ID 
DECLARE Pmin INT; -- 记录统计表中最小ID 
DECLARE PID SMALLINT(6);-- 记录当前统计Pack_ID

DELETE FROM packcollectionrank;-- 清空表
INSERT INTO packcollectionrank(Pack_ID) SELECT DISTINCT Pack_ID FROM products ;-- 插入所有Pack_ID到统计表packcollectionrank

SELECT MAX(ID) INTO Pmax FROM packcollectionrank;
SELECT MIN(ID) INTO Pmin FROM packcollectionrank;

WHILE Pmin<=Pmax DO-- 插入所有Pack_ID的人气，没订阅默认为0
SELECT Pack_ID INTO PID FROM packcollectionrank WHERE ID=Pmin;

SET @sql1=CONCAT("UPDATE packcollectionrank SET Collection_count=(SELECT COUNT(*) FROM userservicerelation WHERE Pack_ID=",PID," AND Relation_Status=6) WHERE Pack_ID=",PID,";");
PREPARE rankpack1 FROM @sql1;
EXECUTE rankpack1;

SET Pmin=Pmin+1;
END WHILE;
-- 插入
END $$
DELIMITER ;

检查一下MYSQL版本
[解决办法]
语法没有错误关键是服务器的版本不统一造成的。
[解决办法]
select version();
[解决办法]
show version();
[解决办法]
直接在命令行工具上试。排除一下你的图形工具的影响。

查看更多 下一篇