plsql实现递归删除或者更新

本文为姜友瑶原创作品 非商业转载请注明作译者、出处，并保留本文的原始链接。否则追究法律责任！商业转载请联系作者！

更多作品请访问我的个人网站 www.jyymatrix.cc

 

　　
 --================================================删除
  PROCEDURE DEL_FUNCTION(P_ID INTEGER) IS
  BEGIN
    UPDATE EX_SYS_FUNCTION A SET A.YXBZ = 0 WHERE A.ID = P_ID;
    DEL_CHILDREN_FUNCTION(P_ID);
    --处理异常
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
  END;

  --================================================删除子节点
  PROCEDURE DEL_CHILDREN_FUNCTION(FUNCTION_ID INTEGER) IS
    IDCOUNT INTEGER;
  BEGIN
    FOR ITEM IN (SELECT ID
                   FROM EX_SYS_FUNCTION
                  WHERE PARENT_ID = FUNCTION_ID) LOOP
      SELECT COUNT(*)
        INTO IDCOUNT
        FROM EX_SYS_FUNCTION
       WHERE PARENT_ID = ITEM.ID;
      IF IDCOUNT > 0 THEN
        DEL_FUNCTION(ITEM.ID);
      END IF;
      IF IDCOUNT = 0 THEN
        UPDATE EX_SYS_FUNCTION A SET A.YXBZ = 0 WHERE ID = ITEM.ID;
      END IF;
    END LOOP;
    COMMIT;
  END;