[求助]MYSQL存储过程，实在无法，求解释？？_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛

总帖数

每页帖数

1/1页

返回列表

发起投票

查看: 2242 | 回复: 0

主题： [求助]MYSQL存储过程，实在无法，求解释？？

oraclexie

注册用户

等级：新兵
经验：36
发帖：67
精华：0
注册：2011-8-21
状态：离线
发送短消息息给oraclexie

加好友发送短消息息给oraclexie

发消息

发表于：

2015-1-6 10:02:03 | [全部帖] [楼主帖]

楼主

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大神们看下哪有问题吧！！自己机子上执行好好的，到别人机子上一直报错：[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版本

--转自