MySQL procedure SET autocommit=0; Error Code: 1064DELIMITER $$

mchdba發表於2013-02-27

DROP PROCEDURE IF EXISTS test.t1 $$
DELIMITER $$
CREATE PROCEDURE t1()
    BEGIN
SET autocommit=0;
      DECLARE cnt INT DEFAULT 0;     -- it is wrong order
       WHILE cnt <= 10000 DO
        INSERT INTO t1 VALUES (cnt);
        SET cnt = cnt +1;
       END WHILE;
       COMMIT;
    END$$
DELIMITER ;

and when run the sql block; there was error info , follow this:
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 cnt INT DEFAULT 0;     
       WHILE cnt <= 10000 DO
        INSERT INTO' at line 4

[solved way]:  declare must be set before autocommit;

DELIMITER $$
DROP PROCEDURE IF EXISTS test.t1 $$
DELIMITER $$
CREATE PROCEDURE t1()
BEGIN
DECLARE cnt INT DEFAULT 0; -- it is right order
SET autocommit=0;
WHILE cnt <= 10000 DO
INSERT INTO t1 VALUES (cnt);
SET cnt = cnt +1;
END WHILE;
COMMIT;
END$$
DELIMITER ;

Execution Time : 0.005 sec
Transfer Time  : 0.097 sec
Total Time     : 0.102 sec

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26230597/viewspace-754877/,如需轉載,請註明出處,否則將追究法律責任。

相關文章