这是一个名为code的表.
p_code列是父代码
并且存储过程应该返回一组由p_code组合的记录,如下所示;
除了第一次(重新连接MYSQL之后,它返回null!)之外,该过程运行良好.它出什么问题了?
这是存储过程.
BEGIN
DECLARE _CODE bigint(20);
DECLARE _P_CODE bigint(20);
DECLARE _SORT bigint(20);
DECLARE _pre_P_CODE bigint(20);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET @CODE = NULL;
SET _P_CODE = @CODE;
SET _CODE = '';
SET _SORT = 0;
SET _pre_P_CODE = '';
IF @CODE IS NULL THEN
RETURN NULL;
END IF;
LOOP
SELECT CODE, P_CODE, SORT, CODE_NAME, CODE_LEVEL
INTO @CODE, @P_CODE, @SORT, @CODE_NAME, @CODE_LEVEL
FROM CODE
WHERE LANGUAGE = @LANGUAGE
AND P_CODE = _P_CODE
AND SORT > _SORT
ORDER BY SORT limit 1;
IF @CODE IS NOT NULL OR _P_CODE = @start_with THEN
SET @level = @level + 1;
RETURN @CODE;
END IF;
SET @level := @level - 1;
SELECT CODE, P_CODE, SORT
INTO _CODE, _P_CODE, _SORT
FROM CODE
WHERE CODE = _P_CODE;
END LOOP;
END
这个程序就像这样.这个SQL将返回上面的第二张图片.
SELECT
menu_connect_by_p_code() AS CODE,
@level as level,
@P_CODE as p_code,
@SORT as sort,
@CODE_NAME as CODE_NAME,
@CODE_LEVEL as CODE_LEVEL
FROM (
SELECT
@start_with := 6001,@LANGUAGE := 'en' ,
@CODE := @start_with,
@level := 0
) vars, code
WHERE @CODE IS NOT NULL
我不明白为什么它第一次返回null. MYSQL的程序或错误有什么问题?
——————————-编辑—————— ——————
RolandoMySQLDBA,rsanchez,我试过你的选择,但保持不变.它返回多行,一些列为null.
——————————- edit2 —————— ——————