mysql存储过程，不知道哪的语法错了？
DELIMITER $$
USE `mstgwnew`$$
DROP PROCEDURE IF EXISTS `UPDATE_RANKING_MIDDLE_BYDAY`$$
CREATE DEFINER=`root`@`%` PROCEDURE `UPDATE_RANKING_MIDDLE_BYDAY`(
#传入的前多少条数据
IN topFlag INT(10)
)
BEGIN
DECLARE NOWTIMEFORMAT VARCHAR(20);
DECLARENOWHOUR VARCHAR(10);
DECLARE stmt VARCHAR(2000);
DECLARE maxCnt INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE websiteidV VARCHAR(300);
DECLARE parentidV VARCHAR(300);
DECLARE timesV DATETIME;
DECLARE urlV LONGTEXT;
DECLARE countnumV INT(10);
DECLARE isfirstpage CHAR(1);#是否为第一页1标识第一页
DECLARE titleV VARCHAR(500);
DECLARE stmt2 VARCHAR(2000);
#取当前时间YYMMDD
SET NOWTIMEFORMAT = CURDATE()+0;
#取当前小时HH
SET NOWHOUR = HOUR(NOW());
#创建日志临时表
DROP TABLE IF EXISTS `temp_apache`;
CREATE TEMPORARY TABLE `temp_apache` (
`id` INT NOT NULL AUTO_INCREMENT,
`website_id` VARCHAR(300) NOT NULL,
`parent_id` VARCHAR(300) NULL,
`times` DATETIME NOT NULL,
`url` LONGTEXT NOT NULL,
`countnum` INT(10) NOT NULL,
PRIMARY KEY (`id`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;
SET @sqlstring=CONCAT('INSERT INTO temp_apache (website_id,parent_id,times,url,countnum) SELECT APACHE.WEBSITE_ID WEBSITE_ID,APACHE.WEBROOT_ID PARENT_ID,APACHE.ACCESS_TIME TIMES,APACHE.URL URL,COUNT(APACHE.URL) COUNTNUM FROM apache_',NOWTIMEFORMAT,'_',NOWHOUR,' APACHE WHERE APACHE.STATU_CODE=200 GROUP BY APACHE.URL) ');
PREPARE stmt FROM @sqlstring;
EXECUTE stmt;
SELECT MIN(`id`) INTO i FROM temp_apache;
SELECT MAX(`id`) INTO maxCnt FROM temp_apache;
#创建排行榜中间临时表
DROP TABLE IF EXISTS `temp_ranking_middle`;
CREATE TEMPORARY TABLE `temp_ranking_middle` (
`title` VARCHAR(500) NULL,
`website_id` VARCHAR(300) NOT NULL,
`parent_id` VARCHAR(300) NULL,
`times` DATETIME NOT NULL,
`url` LONGTEXT NOT NULL,
`countvisit` INT(10) NOT NULL
)
#循环
WHILE i <= maxCnt DO
SELECT website_id,parent_id,times,url,countnum INTO websiteidV,parentidV,timesV,urlV,countnumV FROM temp_apache WHERE id=i;
SET @sqlstr=CONCAT(' SELECT DATA.IS_FIRST_PAGE into @isfirstpage,DATA.TITLE into @titleV FROM DATA_',parentidV,' DATA WHERE DATA.URL = ',urlV);
PREPARE stmt2 FROM @sqlstr;
EXECUTE stmt2;
#如果是第一页则插入到排行榜中间临时表中
IF isfirstpage <> 0 THEN
BEGIN
INSERT INTO temp_ranking_middle(titile,website_id,parent_id,times,url,countvisit) VALUES(titleV,websiteidV,parentidV,timesV,urlV,countnumV);
END;
SET i = i + 1;
END WHILE;
INSERT INTO RANKING_MIDDLE(TITLE,WEBSITE_ID,PARENT_ID,TIMES,URL,`COUNT`) SELECT temp.title title,temp.website_id websiteid,temp.parent_id parent_id,temp.times times,temp.url url,temp.countvisit countnum
FROM temp_ranking_middle temp ORDER BY countnum DESC LIMIT topFlag;
END$$
DELIMITER ;
错误码: 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 i <= maxCnt DO
SELECT website_id,parent_id,times,url,countnum INTO web' at line 64
[解决办法]
DELIMITER $$
USE `mstgwnew`$$
DROP PROCEDURE IF EXISTS `UPDATE_RANKING_MIDDLE_BYDAY`$$
CREATE DEFINER=`root`@`%` PROCEDURE `UPDATE_RANKING_MIDDLE_BYDAY`(
#传入的前多少条数据
IN topFlag INT(10)
)
BEGIN
DECLARE NOWTIMEFORMAT VARCHAR(20);
DECLARE NOWHOUR VARCHAR(10);
DECLARE stmt VARCHAR(2000);
DECLARE maxCnt INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE websiteidV VARCHAR(300);
DECLARE parentidV VARCHAR(300);
DECLARE timesV DATETIME;
DECLARE urlV LONGTEXT;
DECLARE countnumV INT(10);
DECLARE isfirstpage CHAR(1);#是否为第一页1标识第一页
DECLARE titleV VARCHAR(500);
DECLARE stmt2 VARCHAR(2000);
#取当前时间YYMMDD
SET NOWTIMEFORMAT = CURDATE()+0;
#取当前小时HH
SET NOWHOUR = HOUR(NOW());
#创建日志临时表
DROP TABLE IF EXISTS `temp_apache`;
CREATE TEMPORARY TABLE `temp_apache` (
`id` INT NOT NULL AUTO_INCREMENT,
`website_id` VARCHAR(300) NOT NULL,
`parent_id` VARCHAR(300) NULL,
`times` DATETIME NOT NULL,
`url` LONGTEXT NOT NULL,
`countnum` INT(10) NOT NULL,
PRIMARY KEY (`id`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;
SET @sqlstring=CONCAT('INSERT INTO temp_apache (website_id,parent_id,times,url,countnum) SELECT APACHE.WEBSITE_ID WEBSITE_ID,APACHE.WEBROOT_ID PARENT_ID,APACHE.ACCESS_TIME TIMES,APACHE.URL URL,COUNT(APACHE.URL) COUNTNUM FROM apache_',NOWTIMEFORMAT,'_',NOWHOUR,' APACHE WHERE APACHE.STATU_CODE=200 GROUP BY APACHE.URL) ');
PREPARE stmt FROM @sqlstring;
EXECUTE stmt;
SELECT MIN(`id`) INTO i FROM temp_apache;
SELECT MAX(`id`) INTO maxCnt FROM temp_apache;
#创建排行榜中间临时表
DROP TABLE IF EXISTS `temp_ranking_middle`;
CREATE TEMPORARY TABLE `temp_ranking_middle` (
`title` VARCHAR(500) NULL,
`website_id` VARCHAR(300) NOT NULL,
`parent_id` VARCHAR(300) NULL,
`times` DATETIME NOT NULL,
`url` LONGTEXT NOT NULL,
`countvisit` INT(10) NOT NULL
) ;
#循环
WHILE i <= maxCnt DO
SELECT website_id,parent_id,times,url,countnum INTO websiteidV,parentidV,timesV,urlV,countnumV FROM temp_apache WHERE id=i;
SET @sqlstr=CONCAT(' SELECT DATA.IS_FIRST_PAGE into @isfirstpage,DATA.TITLE into @titleV FROM DATA_',parentidV,' DATA WHERE DATA.URL = ',urlV);
PREPARE stmt2 FROM @sqlstr;
EXECUTE stmt2;
#如果是第一页则插入到排行榜中间临时表中
IF isfirstpage <> 0 THEN
INSERT INTO temp_ranking_middle(titile,website_id,parent_id,times,url,countvisit) VALUES(titleV,websiteidV,parentidV,timesV,urlV,countnumV);
END IF;
SET i = i + 1;
END WHILE;
INSERT INTO RANKING_MIDDLE(TITLE,WEBSITE_ID,PARENT_ID,TIMES,URL,`COUNT`) SELECT temp.title title,temp.website_id websiteid,temp.parent_id parent_id,temp.times times,temp.url url,temp.countvisit countnum
FROM temp_ranking_middle temp ORDER BY countnum DESC LIMIT topFlag;
END$$
DELIMITER ;
