DELIMITER $$DROP FUNCTION IF EXISTS `test2`.`fn_level`$$CREATE FUNCTION `test2`.`fn_level`(id char(3))
RETURNS int
BEGIN
declare v_level int default 1;
declare total int default 0;
declare cnt int default 0;
create temporary table if not exists t_Level (ID char(3),`Level` int);
insert into t_Level select id,v_level;
select found_rows() into cnt;
while cnt>0
do
SET v_level = v_level+1;
insert into t_Level select tb.ID,v_level
from tb,t_Level where tb.PID=t_Level.ID
and t_Level.Level=v_level-1;
end while;
select count(*) from t_Level into total;
RETURN total;
END$$DELIMITER ;
RETURNS int
BEGIN
declare v_level int default 1;
declare total int default 0;
declare cnt int default 0;
create temporary table if not exists t_Level (ID char(3),`Level` int);
insert into t_Level select id,v_level;
select found_rows() into cnt;
while cnt>0
do
SET v_level = v_level+1;
insert into t_Level select tb.ID,v_level
from tb,t_Level where tb.PID=t_Level.ID
and t_Level.Level=v_level-1;
end while;
select count(*) from t_Level into total;
RETURN total;
END$$DELIMITER ;
经测试,结果和SQLSERVER的返回结果不一样的:(
BEGIN
declare v_level int default 1;
declare total int default 0;
create temporary table if not exists t_Level (ID char(3),`Level` int);
insert into t_Level select id,v_level;
while row_count()!=-1
do
SET v_level = v_level+1;
insert into t_Level select tb.ID,v_level
from tb,t_Level where tb.PID=t_Level.ID
and t_Level.Level=v_level-1;
end while;
select count(*) from t_Level into total;
RETURN total;
END$$DELIMITER ;