Why does my procedure have error when I'm trying to do loop?-CodePudding

i try to do loop to load data in MySQL Workbench 8.0. but it keep error and i cannot figure it out.

here is my code

DROP TABLE IF EXISTS Cars;
CREATE TABLE Cars (
Id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
Name CHAR (50) NOT NULL,
Price INT
);

DROP PROCEDURE IF EXISTS load_car;

DELIMITER $$
CREATE PROCEDURE load_car()
    BEGIN
        DECLARE counter int;
        SET counter = 1;
        START transaction;
    WHILE counter <= 10 DO
    BEGIN
        INSERT INTO Cars 
        VALUES 
        ('Car-' CAST(counter as varchar), counter*100);
        SET counter = counter   1;
    END;
    END WHILE;
END
$$
DELIMITER ;

CodePudding user response：

You cant use VARCHAR() in CAST(). You need to change that to CHAR() and with length defined. Try this:

DROP TABLE IF EXISTS Cars;
CREATE TABLE Cars (
Id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
Name CHAR (50) NOT NULL,
Price INT
);

DROP PROCEDURE IF EXISTS load_car;

DELIMITER $$
CREATE PROCEDURE load_car()
    BEGIN
        DECLARE counter int;
        SET counter = 1;
        START transaction;
    WHILE counter <= 10 DO
    BEGIN
        INSERT INTO Cars 
        VALUES 
        ('Car-' CAST(counter AS CHAR(255)), counter*100);
        SET counter = counter   1;
    END;
    END WHILE;
END
$$
DELIMITER ;

CodePudding user response：

Your table Cars has three columns, but you insert two; you need to provide a column list:

  INSERT INTO Cars (Name, Price)
        VALUES 
        ('Car-' CAST(counter as varchar(20)), counter*100)

Also, it may not be right to cast to varchar without specifying length, be specific and give the max length.

If these comments don't resolve your problem please specify what error you get.

CodePudding user response：

It's the INSERT INTO Cars VALUES ('Car-' CAST(counter as varchar), counter*100); to blame. First of all , you table Cars has three columns but your insert statement without defining specific columns only provides for the last two,missing the id column . Secondly, the cast function does not support the varchar type. Try the following: INSERT INTO Cars VALUES (default,concat('Car-',counter), counter*100);