如果在 mysql 中的存储过程中

Question

大家好，我有点困惑。我只想问一下这是否是正确的语法。我尝试在其他帖子中存储 proc 查询，但它不起作用。

CREATE PROCEDURE `addBarberCommission`(IN `empID` INT, IN `ioIN` VARCHAR(11))
BEGIN
INSERT INTO `attendance_log` (`emp_id`, `io`) VALUES (`empID`, `ioIN`);
IF (SELECT e.position, e.status, a.io FROM employee e INNER JOIN
attendance_log a WHERE a.emp_id = `empID`
AND e.position = 'Barber'
AND e.status = 'Active'
AND a.io = 'Time In') THEN
INSERT INTO `barber_employee` (`emp_id`, `tot_sal`) VALUES (`empID`, 0);
END IF;
END #

该过程有效，但是当我调用该存储过程时，CALL addBarberCommission(1, 'Time In');它将产生一个

错误代码：1241。操作数应包含 1 列

我只是要求澄清。谢谢

Answer 1

你需要使用EXISTS：

CREATE PROCEDURE `addBarberCommission`(IN `empID` INT, IN `ioIN` VARCHAR(11))
BEGIN
INSERT INTO `attendance_log` (`emp_id`, `io`) VALUES (`empID`, `ioIN`);
IF EXISTS (SELECT e.position, e.status, a.io    --here
FROM employee e 
JOIN  attendance_log a 
WHERE a.emp_id = `empID`
AND e.position = 'Barber'
AND e.status = 'Active'
AND a.io = 'Time In') THEN
INSERT INTO `barber_employee` (`emp_id`, `tot_sal`) VALUES (`empID`, 0);
END IF;
END #

DBFiddle 演示