Recursive Algorithms of Oralce

Typical Oracle recursive algorithms are used. For example, under a multi-level department, a sub-department cannot be the parent department of the parent department.

For example, the father department has a son department and B son department. The father Department cannot be a parent department of the father department.

This is a classic demand for multi-level classification of many management systems.

Okay. The Department table structure-Department.

1. NameType NullableDefaultComments
2. ----------------------------------------------
3. Id number (12)-- ID
4. DEPT_NO VARCHAR2 (32) Y-- Department ID
5. COMPANY_NO VARCHAR2 (4) Y-- Region
6. NAMEVARCHAR2 (128) Y-- Name
7. PARENT_NO VARCHAR2 (32) Y-- Parent department ID

Determine whether the sub-department can become the parent department of the father department and use recursion. SQL:

1. SELECT COUNT(*)FROM(SELECTD. DEPT_NO, D. PARENT_NOFROMDepartment D
2. CONNECT BY PRIORD. DEPT_NO = D. PARENT_NO
3. STARTWITHD. DEPT_NO = # deptNo #) tWhereT. DEPT_NO = # parentNo #

Connect by prior... start with... is recursion. # DeptNo # And # parentNo # Represent the sub-department and the father department respectively.

If the return value is equal to 1, the sub-department cannot be the parent department of the father department; if the return value is not equal to 1, the opposite is true.