我创建了一个返回值的函数,该值在周期表中选择如下:
CREATE TABLE `periods` (
`period` DATE NOT NULL,
`threshold` DOUBLE NOT NULL,
PRIMARY KEY (`period`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;
+------------------------+
| periods |
+------------+-----------+
| period | threshold |
+------------+-----------+
| 2013-11-01 | 5 |
+------------+-----------+
| 2013-12-01 | 1 |
+------------+-----------+
| 2014-01-01 | 5 |
+------------+-----------+
| 2014-02-01 | 5 |
+------------+-----------+
和函数创建:
DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `GET_THRESHOLD`(`PERIOD` VARCHAR(10))
RETURNS double
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
SQL SECURITY DEFINER
COMMENT 'RETURN THRESHOLD'
BEGIN
DECLARE RESULT DOUBLE;
SELECT threshold INTO RESULT FROM periods WHERE period = PERIOD LIMIT 1;
RETURN RESULT;
END
$$
DELIMITER ;
但该函数返回的值为 Wrong
mysql>SELECT GET_THRESHOLD('2013-12-01')
-> 5
有人可以帮助我吗?
U 参数与列同名。 这是不行的。 试试这个:
DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `GET_THRESHOLD`(`V_PERIOD` VARCHAR(10))
RETURNS double
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
SQL SECURITY DEFINER
COMMENT 'RETURN THRESHOLD'
BEGIN
DECLARE RESULT DOUBLE;
SELECT threshold INTO RESULT FROM periods WHERE period = V_PERIOD LIMIT 1;
RETURN RESULT;
END
$$
DELIMITER
声明:
WHERE period = PERIOD
将列值与自身进行比较。 因此,它选择具有非 NULL 列值的所有行。 不是很有趣。
最好始终在变量和参数前面加上一些东西,以将它们与表中的列区分开来。