我有一个存储过程和一个单独创建的视图。我试图从存储过程到我的视图调用minVal和maxVal,但是我不知道为什么不能使用MySql工作台调用它
存储过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `getMinMaxVal`(
IN age int,
OUT minVal double(8,2),
OUT maxVal double(8,2))
BEGIN
SET minVal = 45.64;
SET maxVal = 55.97;
ENDMySql View
CREATE
ALGORITHM = UNDEFINED
DEFINER = `root`@`localhost`
SQL SECURITY DEFINER
VIEW `test_view` AS
SELECT
getMinMaxVal(`user_data`.`age`,minVal) AS `min_range`,
getMinMaxVal(`user_data`.`age`,maxVal) AS `max_range`,
FROM
(`reports`
JOIN `user_data` ON (`reports`.`uhd` = `user_data`.`id`))
WHERE
`reports`.`active` = 1
ORDER BY `reports`.`created_at` DESC发布于 2021-08-02 22:13:17
您需要使用存储的函数,每个值一个。
CREATE FUNCTION getMinVal(IN age INT) RETURN FLOAT
RETURN 45.64;
CREATE FUNCTION getMaxVal(IN age INT) RETURN FLOAT
RETURN 55.97;
CREATE
ALGORITHM = UNDEFINED
DEFINER = `root`@`localhost`
SQL SECURITY DEFINER
VIEW `test_view` AS
SELECT
getMinVal(`user_data`.`age`) AS `min_range`,
getMaxVal(`user_data`.`age`) AS `max_range`,
FROM
(`reports`
JOIN `user_data` ON (`reports`.`uhd` = `user_data`.`id`))
WHERE
`reports`.`active` = 1
ORDER BY `reports`.`created_at` DESChttps://stackoverflow.com/questions/68627946
复制相似问题