/* 根据参数ID递归查询上级部门ID并把上级部门排序号拼到前面,用于递归排序*/
CREATE OR REPLACE FUNCTION fun_depnosortby (f_depid IN VARCHAR2) --返回排序号
RETURN VARCHAR2AS
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;
/