oracle_树形递归（oracle _ 树形递归）.doc

oracle_树形递归（oracle _ 树形递归） Oracle tree recursive query Tree shape table structure: Node ID the name of the higher ID node Two formula: Select node ID, node name, level From surface Connect by prior node ID= higher node ID Start with higher node ID= node value Explain： 1, the common tree structure for companies, organizations, regions...... 2, for more than ID node structure, or above the structure, the node ID= higher node ID about the order change. 3, Level is the special field of Oracle, which means layer. The next level of the current node ID is 1. -- Test SQL: Note 1. Request all sub nodes and levels below 002 (or above) (dynamic: always start at 1), but not yourself Note 2, if more than 002 of the node, then connect by prior topno=departno, = on both sides of the conditions can be transposed. Select, departno, departname, level From Dept Connect by prior departno=topno Start with topno=002; - test data Create, table, Dept DepartNO VARCHAR2 (10), DepartName VARCHAR2 (20), TopNo VARCHAR2 (10); Insert, into, Dept, values (001, board of directors,0); Insert, into, Dept, values (002, CEO ,001); Insert, into, Dept, values (003, finance department,001); Insert, into, Dept, values (004, marketing department,002); Insert, into, Dept, values (005, public relations,002); Insert, into, Dept, values (006, sales department,002); Insert, into, Dept, values (007, distributor ,006); Insert, into, Dept, values (008, business development division ,004); Insert, into, Dept, values (009, sales division,007); Look ahead, for example Select, distinct, departno, departname, level From Dept Connect by prior topno=departno Start with Departno=005; Well, actually check out 005 yourself and your superiors, So the result is 005002001 ============================================================================================ The most critical thing to write about recursion is to define recursive functions. The recursive function is the key to define its parameters and its return value We d