存储过程未创建

huangapple go评论73阅读模式
英文:

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 ;

huangapple
  • 本文由 发表于 2023年6月26日 20:57:47
  • 转载请务必保留本文链接:https://go.coder-hub.com/76556923.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定