oracle存储过程的递归调用

原创于 2012-12-20 15:00:04 发布 · 1k 阅读

CC 4.0 BY-SA版权

本文介绍了一个用于查找特定等级的父地址的递归函数实现。该函数通过比较当前地址等级与目标等级来确定是否返回当前地址ID或继续递归查找更高级别的父地址。

一个查找父地址的过程.

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;