oracle递归排序，返回排序号函数

/* 根据参数ID递归查询上级部门ID并把上级部门排序号拼到前面，用于递归排序*/

CREATE OR REPLACE FUNCTION fun_depnosortby (f_depid IN VARCHAR2) --返回排序号

   RETURN VARCHAR2
AS
   f_sortbys     VARCHAR2 (300);
   f_tempdepid   VARCHAR2 (32);
BEGIN
   f_tempdepid := f_depid;

   WHILE f_tempdepid IS NOT NULL
   LOOP
      SELECT parentdepid, LPAD (sortby, 5, '0') || f_sortbys
        INTO f_tempdepid, f_sortbys
        FROM fw_department
       WHERE depid = f_tempdepid;
   END LOOP;

   IF f_sortbys IS NULL
   THEN
      SELECT sortby
        INTO f_sortbys
        FROM fw_department
       WHERE depid = f_tempdepid;
   END IF;

   RETURN f_sortbys;
END fun_depnosortby;
/