MySQL存儲過程例子,不能在if else裡面用begin end否則會報錯Error Code:1064解決 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 報錯是因為mysql的procedure裡面if else語句裡面, 用了begin end語句,去掉了就OK了. [sql] DELIMITER $$ USE `sportgbmj`$$ DROP PROCEDURE IF EXISTS `sp_web_addmiddayrank`$$ CREATE DEFINER=`root`@`%` PROCEDURE `sp_web_addmiddayrank`() BEGIN /* 修訂記錄: ------------------------------------------------------------------- 版本 修訂人 修訂日期 修訂描述 ------------------------------------------------------------------- 1.0.0 2013-06-15 查詢當前中午12:30-13:30比賽排名 該sp 當前比賽結束後執行 排名先決條件: 1:在一場內必須完滿10(含)局以上 ------------------------------------------------------------------- */ DECLARE i_date DATE ; DECLARE i_starttime TIME; DECLARE i_endtime TIME; SET i_date = NOW(); SET i_endtime = NOW(); IF (i_endtime > '22:30') THEN <strong><span style="color:#ff0000;">BEGIN</span></strong> /* 夜間賽場 */ SET i_starttime = '21:30'; SET i_endtime = '22:30'; <strong><span style="color:#ff0000;">END</span></strong> ELSE <span style="color:#ff0000;"><strong>BEGIN</strong></span> /* 中午場 */ SET i_starttime = '12:30'; SET i_endtime = '13:30'; <span style="color:#ff0000;"><strong>END</strong></span> END IF; CREATE TEMPORARY TABLE tmp_rank( id INT NOT NULL AUTO_INCREMENT -- 自增 ,userid INT -- 用戶標識 ,beans INT -- bean 匯總 ,rounds TINYINT -- 局數 匯總 ,posttime TIME -- 時間 (該玩家本場比賽最後的時間) ) ENGINE = MYISAM; INSERT INTO tmp_rank (userid,beans,rounds,posttime) SELECT userid ,SUM(CASE WHEN consume > 0 THEN consume ELSE 0 END) beans ,COUNT(userid) rounds ,MAX(posttime) AS posttime FROM score AS S INNER JOIN scorelist AS SL ON S.pid = SL.pid WHERE roomid IN ('1001') AND (postdate = i_date AND posttime >='12:30:00' AND posttime <='13:30:00') GROUP BY userid HAVING (rounds >= 10) ORDER BY beans DESC ,maxtime ASC LIMIT 500; /* 插入排名表 */ INSERT INTO hf_playranklist (userid,rank,beans,rounds,postdate,posttime) SELECT userid,beans,rounds,i_date,posttime FROM tmp_rank; /* 插入獎品表 */ INSERT INTO hf_prizelist (userid,STATUS,prizeid,receivetime,postdate) SELECT userid ,0 ,(CASE WHEN id >= 11 THEN 4 WHEN (id >= 3 AND id <= 10) THEN 3 WHEN id = 2 THEN 2 WHEN id = 1 THEN 1 END) AS prizeid ,beans ,rounds ,posttime FROM tmp_rank AS R WHERE rank >= 30; END$$ DELIMITER ;