MySQL存储函数的正确if子句语法是什么? [英] What is the proper if clause syntax for a MySQL stored function?
问题描述
我是MySQL的新手，因此，如果其中的某些内容相对初级，我深表歉意.
I am brand new to MySQL, so I apologize if some of this is relatively elementary.
我正在尝试编写一个存储的函数，以将数字四舍五入到一定数量的有效数字.我有一个理论上应该起作用的功能-我咨询了另一个有用的StackOverflow以明确逻辑(
I'm trying to write a stored function to round a digit to a certain number of significant figures. I have a function which, in theory, should work - I consulted another helpful StackOverflow to nail down the logic (Round to n Significant Figures in SQL). However, numbers between 1 and -1 (excluding 0, because I threw in the edge case catch) fail to round appropriately; for instance, a call of sfround(.00123456789, 5), which should yield 0.0012345, instead yields 0.0012345999712124467.
为了解决此问题，我希望将1到-1(不包括0)之间的数字截断为平凡的零的数量加上必要的无花果的数量.在下面的代码中，IF子句旨在执行此截断.但是，我发现每当包含此IF子句时，都会导致错误:
In an attempt to fix this, I was hoping to truncate digits between 1 and -1 (excluding 0) to the number of trivial zeros PLUS the number of necessary sig figs. In the code below, the IF clause is intended to carry out this truncation. However, I find that whenever I include this IF clause, it results in an error:
'错误代码:1064.您的SQL语法有错误；检查与您的MySQL服务器版本相对应的手册，以在'DECLARE numDigits INT附近使用正确的语法； SET numDigits = FLOOR(LOG10(ABS(number))); #total d'，第5行).
'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 'DECLARE numDigits INT; SET numDigits = FLOOR(LOG10(ABS(number))); #total d' at line 5).
即使我尝试了一个非常简单的IF语句，例如
Even when I experimented with a very simple IF statement, such as
IF (5 = 5) THEN DECLARE dummyvar INT; END IF;
我在这一行上仍然遇到相同的错误，这使我认为我在IF子句的MySQL语法中缺少一些简单的东西.
I still got the same error at this line, which leads me to think that I am missing something simple in my MySQL syntax for an IF clause.
DELIMITER $$
USE `vg_summary1`$$
DROP FUNCTION IF EXISTS `sfround`$$
CREATE FUNCTION 'sfround`(number FLOAT, sf INT) RETURNS FLOAT
DETERMINISTIC
BEGIN
IF ((ABS(number) < 1) AND (ABS(number) > 0)) THEN
DECLARE numDigits INT;
SET numDigits = FLOOR(LOG10(ABS(number)));
DECLARE trivialDigits INT;
SET trivialDigits = 0;
DECLARE placeholder FLOAT;
SET placeholder = ABS(number);
WHILE placeholder < 1
BEGIN
placeholder = placeholder * 10;
IF (placeholder < 1) THEN trivialDigits = trivialDigits + 1;
END IF;
END;
DECLARE keptDigits = trivialDigits + sf;
DECLARE special_answer FLOAT;
SET special_answer = ROUND(number, keptDigits-1-FLOOR(LOG10(ABS(number))));
RETURN special_answer;
END IF;
DECLARE r FLOAT;
SET r = CASE WHEN number = 0 THEN 0 ELSE ROUND(number, sf-1-FLOOR(LOG10(ABS(number)))) END;
RETURN r;
END$$
DELIMITER ;
如果有人对语法错误或更合理的方法来解决原始错误有任何建议，我将不胜感激！非常感谢！
If anyone has any suggestions in terms of syntax errors or more logical ways to fix the original error, I would be very grateful to hear them! Thank you so much!
