心里知道有这么回事,但很少在实践中用到,下面是一个查找父地址的过程。
CREATE OR REPLACE FUNCTION F_CALLPARENTARDADDRESS(V_ARDADDRESSID IN NUMBER,
V_GRADE IN NUMBER)
RETURN NUMBER IS
V_SELFGRADE NUMBER;
V_PARENT_GRADE NUMBER;
V_PARENT_ADDRESSID NUMBER;
BEGIN
BEGIN
SELECT TELEARDADDRESS.GRADE, PARENTADDRESS.GRADE, PARENTADDRESS.ID
INTO V_SELFGRADE, V_PARENT_GRADE, V_PARENT_ADDRESSID
FROM TELEARDADDRESS, TELEARDADDRESS PARENTADDRESS
WHERE TELEARDADDRESS.ID = V_ARDADDRESSID
AND TELEARDADDRESS.PARENTADDRESSID = PARENTADDRESS.ID(+);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0;
END;
IF V_GRADE = V_SELFGRADE THEN
RETURN V_ARDADDRESSID;
ELSIF (V_PARENT_GRADE IS NULL) OR (V_PARENT_ADDRESSID IS NULL) OR
(V_GRADE > V_PARENT_GRADE) THEN
RETURN 0;
ELSIF (V_GRADE = V_PARENT_GRADE) THEN
RETURN V_PARENT_ADDRESSID;
--递归调用
ELSE
RETURN F_CALLPARENTARDADDRESS(V_PARENT_ADDRESSID, V_GRADE);
END IF;
END F_CALLPARENTARDADDRESS;