当我试图在存储过程中使用IF Else条件时,我得到语法错误
根据文档,语法是你有一个错误的SQL语法;检查与您的MariaDB服务器版本对应的手册,以便在'sort = 1 THEN附近使用正确的语法
IF condition THEN
statements;
ELSE
else-statements;
END IF;
My Stored Procedure:
DELIMITER $$
CREATE PROCEDURE test(IN _code INT, IN testformat SMALLINT(5), IN testcurrency CHAR, IN testdate Date, IN tesval CHAR(1), IN sortby INT)
BEGIN
SELECT value1, valu2, value3, value4
FROM shop
WHERE CODE1 = _code
ORDER BY
IF sortby = 1 THEN
value2, value3 ASC;
ELSE
value2, value3 DESC;
END;
END$$
DELIMITER ;
我找不到问题。谢谢你的帮助。
IF/THEN/ELSE
结构体本身就是一个语句。它不是可以放在查询的ORDER BY
子句中的表达式。
但是您不能使用IF(condition, expr1, expr2)
函数,因为该函数必须返回一个标量,而不是列列表和SQL关键字。
你的问题的答案是:
DELIMITER $$
CREATE PROCEDURE test(
IN _code INT,
IN testformat SMALLINT(5),
IN testcurrency CHAR,
IN testdate Date,
IN tesval CHAR(1),
IN sortby INT)
BEGIN
IF sortby = 1 THEN
SELECT value1, valu2, value3, value4
FROM shop
WHERE CODE1 = _code
ORDER BY value2, value3 ASC;
ELSE
SELECT value1, valu2, value3, value4
FROM shop
WHERE CODE1 = _code
ORDER BY value2, value3 DESC;
END IF;
END$$
DELIMITER ;
由于唯一的区别是一个SQL关键字(而不是一个值),因此缩短它的唯一方法是使用动态SQL:
DELIMITER $$
CREATE PROCEDURE test(
IN _code INT,
IN testformat SMALLINT(5),
IN testcurrency CHAR,
IN testdate Date,
IN tesval CHAR(1),
IN sortby INT)
BEGIN
SET @query = CONCAT('
SELECT value1, valu2, value3, value4
FROM shop
WHERE CODE1 = _code
ORDER BY value2, value3 ', IF(sortby=1, 'ASC', 'DESC'));
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;