无法识别的语句接近IF - usage IF with Exists

Question

如果date_in和date_out都为空，我想更新roomstatus表中的行，其中room_id = @id。否则，我需要插入新行，比如where room_id = @id，date_in = @dateIn，date_out = @dateIN

IF ((SELECT date_in FROM roomstatus WHERE room_id = @id)= NULL AND (SELECT date_out FROM roomstatus WHERE room_id = @id)= NULL ) THEN 
    UPDATE roomstatus SET date_in = @dateIN, date_out = @dateOut WHERE roomid = @id
ELSE
    INSERT INTO roomstatus (room_id,date_in,date_out) VALUES (@id,@dateIn,@dateOut)
END IF;

MySQL说：

> #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 'IF ((SELECT date_in FROM roomstatus WHERE room_id = @id)= NULL
> AND (SELECT date_' at line 1

Answer 1

你的过程有一些错误1)每条语句都需要一个结束符2) MYSQL不能区分列名和参数名，例如room_id = roomID - (mysql会将列与自身进行比较)你必须提供该信息3)结束begin的结尾需要有结束分隔符。下面的语法是正确的

DROP PROCEDURE IF EXISTS P;

DELIMITER // 
CREATE PROCEDURE P(IN INdateIn DATETIME(6) , IN INdateOut DATETIME(6), IN INroomID INT(11)) 
BEGIN 
    IF EXISTS (SELECT room_id FROM roomstatus WHERE room_id = INroomID AND date_in = NULL AND date_out = NULL ) THEN
     UPDATE roomstatus 
        SET date_in = INdateIn , date_out = INdateOut 
         WHERE room_id = INroomID ;
    ELSE 
        INSERT INTO roomstatus (room_id,date_in,date_out) VALUES (INroomID,INdateIn,INdateOut); 
    END IF;
END //
DELIMITER ; 

在功能上我不是很确定，没有错误检查来确保你已经设置了参数来实现你想要做的事情。例如，您没有测试indateout，因此代码可能会进入更新逻辑，将indateout设置为null，将indatein设置为有效值-下次调用过程时，您可能会进入插入逻辑，并可能以重复结束。