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();

直接在命令行工具上试。排除一下你的图形工具的影响。

相关文章：

你感兴趣的文章：

标签云：