mysql函数递归问题请教
在mysql里写递归函数 如下:
use semis;
drop function if exists f_get_category_full_name;
delimiter //
create function f_get_category_full_name(
vCategoryID varchar(32))
returns varchar(300)
language sql
deterministic
reads sql data
sql security invoker
comment '获取部职别全称(包括上级节点名称)'
begin
declare vCategoryFullName varchar(300);
set max_sp_recursion_depth = 10;
select ParentID, CategoryName into @ParentID, @CategoryName
from t_category
where CategoryID = vCategoryID;
set vCategoryFullName = @CategoryName;
if @ParentID is null then
return(vCategoryFullName);
else
set vCategoryFullName = concat(f_get_category_full_name(@ParentID), '/', @vCategoryFullName);
end if;
return(@vCategoryFullName);
end;
//
delimiter ;
select f_get_category_full_name('000001');
报错:
Recursive stored functions and triggers are not allowed.
该函数在mssql中ok 但是在mysql中一直报错
还请指教为什么?
set max_sp_recursion_depth = 10;这个设置深度也已经设置了
还是如此。