我已尝试使用存储过程与递归调用.我需要根据leg1基于leg1显示相关名称
但它显示错误,例如#1414 – OUT或INOUT参数2用于例行sample.getVolume不是BEFORE触发器中的变量或NEW伪变量
这是我的代码,
DELIMITER $$CREATE PROCEDURE getVolume( IN param_name VARCHAR(255),OUT result VARCHAR(255 ))BEGINSELECT val INTO result FROM employee WHERE emp_name = param_name ;IF result IS NULL THENselect result;ELSECALL 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;@H_403_13@
最佳答案
@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 ;@H_403_13@