我正在使用2个参数（_id和_operator）在mysql（8.0.18）中创建一个函数。哪里_id是需要处理的记录标识，_operator是要执行的操作。
这段代码工作正常。但是，它相当大且重复：
DELIMITER $$
DROP FUNCTION IF EXISTS `deleteSubscriber` $$
CREATE FUNCTION `deleteSubscriber`(_id INTEGER, _operator VARCHAR(2)) RETURNS CHAR
BEGIN
IF (_operator = '=' OR ISNULL(_operator) = TRUE) THEN
DELETE FROM `subscribers` WHERE `id` = _id;
DELETE FROM `subscriber_packages` WHERE `id` = _id;
...
ELSEIF (_operator = '>=') THEN
DELETE FROM `subscribers` WHERE `id` >= _id;
DELETE FROM `subscriber_packages` WHERE `id` >= _id;
...
ELSEIF (_operator = '<=') THEN
DELETE FROM `subscribers` WHERE `id` <= _id;
DELETE FROM `subscriber_packages` WHERE `id` <= _id;
...
ELSEIF (_operator = '<>' OR _operator = '!=') THEN
DELETE FROM `subscribers` WHERE `id` != _id;
DELETE FROM `subscriber_packages` WHERE `id` != _id;
...
END IF;
RETURN NULL;
END$$
DELIMITER ;
这是我正在尝试的代码。但是，出现错误：
DELIMITER $$
DROP FUNCTION IF EXISTS `deleteSubscriber` $$
CREATE FUNCTION `deleteSubscriber`(_id INTEGER, _operator VARCHAR(2)) RETURNS CHAR
BEGIN
DELETE FROM `subscribers` WHERE `id` _operator _id;
DELETE FROM `subscriber_packages` WHERE `id` _operator _id;
...
RETURN NULL;
END$$
DELIMITER ;
有没有一种方法可以将查询运算符作为值传递给此函数，以便删除重复的代码..？
用途Prepared Statement相同。
CREATE PROCEDURE `deleteSubscriberData`(IN `_id` INT, IN `_operator` VARCHAR(2))
BEGIN
SET @s = CONCAT('DELETE FROM subscribers WHERE id ', _operator, ' ', _id);
PREPARE stmt1 FROM @s;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
SET @s = CONCAT('DELETE FROM subscriber_packages WHERE id ', _operator, ' ', _id);
PREPARE stmt2 FROM @s;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;
END
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句