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.
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.
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版本
时间: 0.250msProcedure executed successfully
受影响的行: 1在另外机子上执行:
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;
这个很无解
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' at line 1
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 Pmin int' at line 2
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 PID smallint(6)' at line 2
Database changed
Query OK, 124 rows affected
Records: 124 Duplicates: 0 Warnings: 01327 - Undeclared variable: Pmax
1327 - Undeclared variable: Pmin
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 'while Pmin<=Pmax do-- 鎻掑叆鎵€鏈塒ack_ID鐨勪汉姘旓紝娌¤闃' at line 1
1054 - Unknown column 'PID' in 'field list'
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 'NULL' at line 1
1243 - Unknown prepared statement handler (rankpack1) given to EXECUTE
1193 - Unknown system variable 'Pmin'
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 'end while' at line 1
->
这个执行应该算是成功的吧...
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' at line 3
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 Pmin INT' at line 2
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 PID SMALLINT(6)' at line 2
Query OK, 124 rows affectedQuery OK, 124 rows affected
Records: 124 Duplicates: 0 Warnings: 01327 - Undeclared variable: Pmax
1327 - Undeclared variable: Pmin
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 'WHILE Pmin<=Pmax DO-- 鎻掑叆鎵€鏈塒ack_ID鐨勪汉姘旓紝娌¤闃' at line 1
1054 - Unknown column 'PID' in 'field list'
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 'NULL' at line 1
1243 - Unknown prepared statement handler (rankpack1) given to EXECUTE
1193 - Unknown system variable 'Pmin'
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 'END WHILE' at line 1
mysql> DELIMITER $$
mysql> 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
roducts ;-- 插入所有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=(SE
CT COUNT(*) FROM userservicerelation WHERE Pack_ID=",PID," AND Relation_Status
) WHERE Pack_ID=",PID,";");
-> PREPARE rankpack1 FROM @sql1;
-> EXECUTE rankpack1;
->
-> SET Pmin=Pmin+1;
-> END WHILE;
-> -- 插入
-> END $$
Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ;
mysql>
这样的错呢??费解???
BEGIN
DECLARE SID int(11) DEFAULT 0; #定义变量,默认值为0
DECLARE n int(11) DEFAULT 0; #定义变量,默认值为0 SELECT User_ID INTO SID FROM user WHERE Client_ID = ServicerID;
IF SID = 0 THEN
ROLLBACK;#回滚
END IF;
SELECT count(Servicer_ID) INTO n FROM user_clientfwgx WHERE Servicer_ID=SID AND Client_ID=ClientID;
IF n!=0 THEN #相同
UPDATE user_clientfwgx SET Belong_Department=BranchID WHERE Servicer_ID=SID AND Client_ID=ClientID;
ELSE#不同
INSERT INTO user_clientfwgx(Servicer_ID,Client_ID,Belong_Department) VALUES(SID,ClientID,BranchID);
END IF;
COMMIT;#事物提交,释放锁
END
对方机子是可以执行的...又是嘛原因??纠结!
delimiter //
?!
DELIMITER $$
CREATE PROCEDURE ff1()
....DELIMITER ;在你的代码 中有没有?
MYSQL不支持匿名块