我已尝试使用存储过程与递归调用.我需要根据leg1基于leg1显示相关名称
但它显示错误,例如#1414 – OUT或INOUT参数2用于例行sample.getVolume不是BEFORE触发器中的变量或NEW伪变量
这是我的代码,
DELIMITER $$
CREATE PROCEDURE getVolume( IN param_name VARCHAR(255),OUT result VARCHAR(255 ))
BEGIN
SELECT val INTO result FROM employee WHERE emp_name = param_name ;
IF result IS NULL THEN
select result;
ELSE
CALL getVolume(result,'');
END IF;
END $$
DELIMITER ;
SET @@GLOBAL.max_sp_recursion_depth = 255;
SET @@session.max_sp_recursion_depth = 255;
call getVolume('new',@result);
select @result;
最佳答案
@Shadow的答案是正确的.这是一个可以帮助您的实用示例：
DELIMITER //
DROP TABLE IF EXISTS `employee`//
DROP PROCEDURE IF EXISTS `getVolume`//
CREATE TABLE `employee` (
`emp_name` VARCHAR(255),`val` VARCHAR(255)
)//
INSERT INTO `employee` (`emp_name`,`val`)
VALUES
('demo','new'),('new','d.new'),('d.new','view'),('view','hello'),('hello',NULL)
//
CREATE PROCEDURE `getVolume`(
IN `param_name` VARCHAR(255),OUT `result` VARCHAR(255)
)
BEGIN
DECLARE `next_param_name` VARCHAR(255);
SELECT `val` INTO `next_param_name`
FROM `employee`
WHERE `emp_name` = `param_name`;
IF `next_param_name` IS NULL THEN
SET `result` := `param_name`;
ELSE
-- CALL `getVolume`(`result`,'');
CALL `getVolume`(`next_param_name`,`result`);
-- SET `result` := CONCAT(`param_name`,',IFNULL(`result`,''));
END IF;
END//
DELIMITER ;