navicat运行循环插入数据一直报错

Question

drop procedure if EXISTS proc_insert_J;
DELIMITER $$
CREATE PROCEDURE proe_insert_J(in args int)
BEGIN
    DECLARE i INT DEFAULT 0:
        start transaction;
        REPEAT
            set i=i+1;
            insert into j(jno,jname,city) values (CONCAT('j',i),CONCAT('项目',i),'济南');
        UNTIL i>=args
        END REPEAT:
    commit;
END
$$
DELIMITER;

call proc_insert_J(100000);

错误

drop procedure if EXISTS proc_insert_J
> OK
> 时间: 0.001s


CREATE PROCEDURE proe_insert_J(in args int)
BEGIN
    DECLARE i INT DEFAULT 0:
        start transaction;
        REPEAT
            set i=i+1;
            insert into j(jno,jname,city) values (CONCAT('j',i),CONCAT('项目',i),'济南');
        UNTIL i>=args
        END REPEAT:
    commit;
END
> 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 ':
        start transaction;
        REPEAT
            set i=i+1;
            insert into j(jno,jname,cit' at line 3
> 时间: 0s

我想知道错误出在哪里，望告知

Answer 1

你声明变量i符号应该用分号、开启事务也是用分号，下面是代码

DROP PROCEDURE IF EXISTS proc_insert_J;
CREATE PROCEDURE proc_insert_J(in args int)
BEGIN
    DECLARE i INT DEFAULT 0;
    START TRANSACTION;
    REPEAT
        SET i=i+1;
        INSERT INTO j(jno,jname,city) VALUES (CONCAT('j',i),CONCAT('项目',i),'济南');
    UNTIL i>=args
    END REPEAT;
    COMMIT;
END;

有问题及时反馈哦