英文:
The stored procedure doesn't get created
问题
I'm trying to create a stored procedure in MySql but I just get an error. Can somebody help me?
CREATE PROCEDURE colorAddOrEdit (
in _id int,
in _descripcion varchar(45)
)
BEGIN
If _id = 0 then
insert into color (Descripcion_col)
values (_descripcion);
set _id = last_insert_id();
else
update color
set
Descripcion_col = _descripcion
where idColor_col = _id;
end if;
select _id = idColor_col;
end
The table is:
CREATE TABLE color
(
idColor_col int NOT NULL,
Descripcion_col varchar(45) DEFAULT NULL,
PRIMARY KEY (idColor_col)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
The error message:
> 08:52:19 CREATE PROCEDURE colorAddOrEdit ( in _id int, in _descripcion varchar(45) ) BEGIN If _id = 0 then insert into color (Descripcion_col) values (_descripcion) 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 '' at line 8 0.000 sec
英文:
I'm trying to create a stored procedure in MySql but I just get an error. Can somebody help me?
CREATE PROCEDURE colorAddOrEdit (
	in _id int,
    in _descripcion varchar(45)
    )
BEGIN
	If _id = 0 then
		insert into color (Descripcion_col)
        values (_descripcion);
        
		set _id = last_insert_id();
	else 
		update color 
        set 
		Descripcion_col = _descripcion
		where idColor_col = _id;
	end if;
    
    select _id = idColor_col;
end
The table is:
CREATE TABLE `color` 
(
    `idColor_col` int NOT NULL,
    `Descripcion_col` varchar(45) DEFAULT NULL,
    PRIMARY KEY (`idColor_col`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
The error message:
> 08:52:19 CREATE PROCEDURE colorAddOrEdit ( in _id int, in _descripcion varchar(45) ) BEGIN If _id = 0 then insert into color (Descripcion_col) values (_descripcion) 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 '' at line 8 0.000 sec
答案1
得分: 0
DELIMITER //
CREATE PROCEDURE colorAddOrEdit (
    IN _id INT,
    IN _descripcion VARCHAR(45)
)
BEGIN
    IF _id = 0 THEN
        INSERT INTO color (Descripcion_col)
        VALUES (_descripcion);
        SET _id = LAST_INSERT_ID();
    ELSE
        UPDATE color 
        SET Descripcion_col = _descripcion
        WHERE idColor_col = _id;
    END IF;
    SELECT _id AS idColor_col;
END //
DELIMITER ;
英文:
Try this
DELIMITER //
CREATE PROCEDURE colorAddOrEdit (
    IN _id INT,
    IN _descripcion VARCHAR(45)
)
BEGIN
    IF _id = 0 THEN
        INSERT INTO color (Descripcion_col)
        VALUES (_descripcion);
        SET _id = LAST_INSERT_ID();
    ELSE
        UPDATE color 
        SET Descripcion_col = _descripcion
        WHERE idColor_col = _id;
    END IF;
    SELECT _id AS idColor_col;
END //
DELIMITER ;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。


评论