oracle和mysql的递归查询

oracle:

1.查询一个机构下所辖机构：(start with)

select * from t00_organ t start with t.organkey=#uporgankey# connect by prior t.organkey=t.uporgankey ;

2.例子（with 查询）

with emps (employee_id, name, job_id, salary, lvl) as (  

  select employee_id, first_name || ', '|| last_name name, job_id, salary, 1 as lvl  
  from employees  
  where manager_id is null  
  union all  
  select emp.employee_id, emp.first_name || ', '|| emp.last_name, emp.job_id, emp.salary, root.lvl + 1  
  from employees emp, emps root  
  where emp.manager_id=root.employee_id  
)  

select * from emps;

MYSQL：

CREATE FUNCTION `fcGetTreeList`(rid INT) 
 RETURNS varchar(1000) 
 BEGIN 
 DECLARE sTemp VARCHAR(1000); 
 DECLARE sTempChd VARCHAR(1000); 

 SET sTemp = '$'; 
 SET sTempChd =cast(rid as CHAR); 

 WHILE sTempChd is not null DO 
   SET sTemp = concat(sTemp,',',sTempChd); 
   SELECT group_concat(id) INTO sTempChd FROM treeNodes where FIND_IN_SET(pid,sTempChd)>0; 
 END WHILE; 
 RETURN sTemp; 
 END 

-- 调用
SELECT * from test where FIND_IN_SET(id,fcGetTreeList(1));