--去掉else,测试一下 BEGIN DECLARE Done INT DEFAULT 0 ; DECLARE tablename VARCHAR(255); DECLARE rs CURSOR FOR SELECT id,`level`,`exp` FROM role; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1; OPEN rs; FETCH NEXT FROM rs INTO tablename; REPEAT IF NOT Done THEN CASE WHEN `level` = 1 AND `exp`>5 THEN UPDATE role SET `exp` = 5; WHEN `level` = 2 AND `exp`>10 THEN UPDATE role SET `exp` = 10;
END CASE; END IF; FETCH NEXT FROM rs INTO tablename; UNTIL Done END REPEAT; DEALLOCATE PREPARE S1; CLOSE rs; COMMIT;
去掉else后报错。。 错误代码: 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 'IF; FETCH NEXT FROM rs INTO tablename; UNTIL Done END REPEAT; DEALLO' at line 14执行耗时 : 0 sec 传送时间 : 0 sec 总耗时 : 0.002 sec
mysql 5.5下测试通过 DELIMITER $$ CREATE PROCEDURE dd1() BEGIN DECLARE Done INT DEFAULT 0 ; DECLARE tablename VARCHAR(255); DECLARE rs CURSOR FOR SELECT id,`level`,`exp` FROM role; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1; OPEN rs; FETCH NEXT FROM rs INTO tablename; REPEAT IF NOT Done THEN CASE WHEN `level` = 1 AND `exp`>5 THEN UPDATE role SET `exp` = 5; WHEN `level` = 2 AND `exp`>10 THEN UPDATE role SET `exp` = 10;
END CASE; END IF; FETCH NEXT FROM rs INTO tablename; UNTIL Done END REPEAT; DEALLOCATE PREPARE S1; CLOSE rs; COMMIT; END$$ DELIMITER ;
DECLARE Done INT DEFAULT 0 ; DECLARE tmp_id INT(11); DECLARE tmp_level INT(11); DECLARE tmp_exp INT(11); DECLARE rs CURSOR FOR SELECT id FROM role ; DECLARE CONTINUE HANDLER FOR SQLSTATE '06000' SET Done = 1; OPEN rs; FETCH NEXT FROM rs INTO tmp_id; REPEAT IF NOT Done THEN SELECT `level`,`exp` INTO tmp_level,tmp_exp FROM role WHERE id = tmp_id; CASE WHEN tmp_level = 1 THEN UPDATE role SET `exp` = 1000 WHERE id = tmp_id; WHEN tmp_level = 2 THEN UPDATE role SET `exp` = 500 WHERE id = tmp_id; END CASE; END IF; COMMIT; FETCH NEXT FROM rs INTO tmp_id; UNTIL Done END REPEAT; CLOSE rs; END$$ 编译都能过,问题就是执行的时候。。这里提示 错误代码: 1339 Case not found for CASE statement case写错了吗?
If no when_value or search_condition matches the value tested and the CASE statement contains no ELSE clause, a Case not found for CASE statement error results. ----《MySQL手册》 你得写else~~~~ 比如: CASE v WHEN 2 THEN SELECT v; WHEN 3 THEN SELECT 0; ELSE BEGIN END; END CASE;
BEGIN
DECLARE Done INT DEFAULT 0 ;
DECLARE tablename VARCHAR(255);
DECLARE rs CURSOR FOR SELECT id,`level`,`exp` FROM role;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;
OPEN rs;
FETCH NEXT FROM rs INTO tablename;
REPEAT
IF NOT Done THEN
CASE
WHEN `level` = 1 AND `exp`>5
THEN
UPDATE role SET `exp` = 5;
WHEN `level` = 2 AND `exp`>10
THEN
UPDATE role SET `exp` = 10;
END CASE;
END IF;
FETCH NEXT FROM rs INTO tablename;
UNTIL Done END REPEAT;
DEALLOCATE PREPARE S1;
CLOSE rs;
COMMIT;
错误代码: 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 'IF;
FETCH NEXT FROM rs INTO tablename;
UNTIL Done END REPEAT;
DEALLO' at line 14执行耗时 : 0 sec
传送时间 : 0 sec
总耗时 : 0.002 sec
DELIMITER $$
CREATE PROCEDURE dd1()
BEGIN
DECLARE Done INT DEFAULT 0 ;
DECLARE tablename VARCHAR(255);
DECLARE rs CURSOR FOR SELECT id,`level`,`exp` FROM role;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;
OPEN rs;
FETCH NEXT FROM rs INTO tablename;
REPEAT
IF NOT Done THEN
CASE
WHEN `level` = 1 AND `exp`>5
THEN
UPDATE role SET `exp` = 5;
WHEN `level` = 2 AND `exp`>10
THEN
UPDATE role SET `exp` = 10;
END CASE;
END IF;
FETCH NEXT FROM rs INTO tablename;
UNTIL Done END REPEAT;
DEALLOCATE PREPARE S1;
CLOSE rs;
COMMIT;
END$$
DELIMITER ;
DECLARE tmp_id INT(11);
DECLARE tmp_level INT(11);
DECLARE tmp_exp INT(11);
DECLARE rs CURSOR FOR SELECT id FROM role ;
DECLARE CONTINUE HANDLER FOR SQLSTATE '06000' SET Done = 1;
OPEN rs;
FETCH NEXT FROM rs INTO tmp_id;
REPEAT
IF NOT Done THEN
SELECT `level`,`exp` INTO tmp_level,tmp_exp FROM role WHERE id = tmp_id;
CASE
WHEN tmp_level = 1 THEN
UPDATE role SET `exp` = 1000 WHERE id = tmp_id;
WHEN tmp_level = 2 THEN
UPDATE role SET `exp` = 500 WHERE id = tmp_id;
END CASE;
END IF;
COMMIT;
FETCH NEXT FROM rs INTO tmp_id;
UNTIL Done END REPEAT;
CLOSE rs;
END$$
编译都能过,问题就是执行的时候。。这里提示
错误代码: 1339
Case not found for CASE statement
case写错了吗?
----《MySQL手册》
你得写else~~~~
比如:
CASE v
WHEN 2 THEN SELECT v;
WHEN 3 THEN SELECT 0;
ELSE
BEGIN
END;
END CASE;