英文:
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 ;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论