DB2中实现等级查询二

//再建一个函数，用于外部程序调用，返回一个树形结构
CREATE FUNCTION F_GETRECCHILDS (ROOT VARCHAR(20))
  RETURNS TABLE (ID VARCHAR(20))
  LANGUAGE SQL
  DETERMINISTIC
  READS SQL DATA
  STATIC DISPATCH
  NO EXTERNAL ACTION
  INHERIT SPECIAL REGISTERS
  RETURN 
   WITH rec(id) AS    //这句是循环查询结果集
   (
    values(root)
    UNION ALL SELECT t.id FROM rec,
    TABLE(f_getdirectchilds(rec.id)) AS T
   )
   SELECT id FROM rec

//最终程序中执行的SQL语句
SELECT T.id folderid, parentid ,name FROM TABLE(F_GETRECCHILDS('ROOT')) AS T, folder a where T.id = a.folderid