//一张表中父编号 = 字编号情况,
CREATE OR REPLACE FUNCTION nademo.GET_DEPTNAME(pId_param IN NUMBER)
RETURN VARCHAR2-- 返回值的数据类型
IS
out_connNAME VARCHAR2(500);
BEGIN
--得到给定部门所有上级的数据集
FOR K IN (
SELECT deptID
,deptNAME
FROM Dept
START WITH deptID=pId_param CONNECT BY PRIOR PARENTID = deptID ORDER BY deptID) LOOP --把所有上级的名称连接起来 out_connNAME:=out_connNAME||K.deptNAME; END LOOP; --返回 RETURN out_connNAME; EXCEPTION WHEN OTHERS THEN --返回'' RETURN ''; END; /