刚用mySQL,运行不了,请问是什么问题,谢谢DELIMITER $$DROP PROCEDURE IF EXISTS `infwebbi_v105x_gm`.`SP_Sales_Report_By_Hour`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `SP_xxx`(Begin_Date date, End_Date date,OutLet varchar(50) )
BEGIN
CREATE TEMPORARY TABLE tmp_time (
FID INTEGER NOT NULL,
FSTART VARCHAR(10) NOT NULL,
FEND varchar(10)
); DECLARE iHour INT; SET @iHour=0; WHILE (iHour<24)
INSERT INTO tmp_time(FID,FSTART,FEND) Values(iHour,LPAD(trim(cast(iHour As CHAR)),2,'0'),':00',LPAD(trim(cast(iHour As CHAR)),2,'0'),':59');
END WHILE
select * from tmp_time;
END$$DELIMITER ;
BEGIN
CREATE TEMPORARY TABLE tmp_time (
FID INTEGER NOT NULL,
FSTART VARCHAR(10) NOT NULL,
FEND varchar(10)
); DECLARE iHour INT; SET @iHour=0; WHILE (iHour<24)
INSERT INTO tmp_time(FID,FSTART,FEND) Values(iHour,LPAD(trim(cast(iHour As CHAR)),2,'0'),':00',LPAD(trim(cast(iHour As CHAR)),2,'0'),':59');
END WHILE
select * from tmp_time;
END$$DELIMITER ;
BEGIN
CREATE TEMPORARY TABLE tmp_time (
FID INTEGER NOT NULL,
FSTART VARCHAR(10) NOT NULL,
FEND varchar(10)
); DECLARE iHour INT; SET iHour=0; WHILE (iHour <24)
INSERT INTO tmp_time(FID,FSTART,FEND) Values(iHour,LPAD(trim(cast(iHour As CHAR)),2,'0'),':00',LPAD(trim(cast(iHour As CHAR)),2,'0'),':59');
END WHILE
select * from tmp_time;
END$$ DELIMITER ;SQLyog运行时,提示
(0 row(s)affected)
(0 ms taken)Error Code : 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 iHour INT; SET iHour=0; WHILE (iHour<24)
INSERT INTO tmp_time' at line 9
(0 ms taken)Error Code : 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$$DELIMITER' at line 25
(0 ms taken)Error Code : 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$$
DELIMITER' at line 25
(0 ms taken)
INSERT INTO tmp_time(FID,FSTART,FEND) Values(iHour,LPAD(trim(cast(iHour As CHAR)),2,'0'),':00',LPAD(trim(cast(iHour As CHAR)),2,'0'),':59');
参数并不匹配。错得地方比较多。
DELIMITER $$DROP PROCEDURE IF EXISTS `SP_xxx`$$CREATE PROCEDURE `SP_xxx`(Begin_Date date, End_Date date,OutLet varchar(50) )
BEGIN
DECLARE iHour INT;
CREATE TEMPORARY TABLE tmp_time (
FID INTEGER NOT NULL,
FSTART VARCHAR(10) NOT NULL,
FEND varchar(10)
);
SET iHour=0;
WHILE (iHour <24) DO
INSERT INTO tmp_time(FID,FSTART,FEND) Values(
iHour,
right(concat('00',iHour,':00'),5),
right(concat('00',iHour,':59'),5));
END WHILE;
select * from tmp_time;
END$$DELIMITER ;