详解oracle递归（详解oracle递归）.doc

详解oracle递归（详解oracle递归） I. grammar General wording: select * from some_table [where condition 1] connect by [condition 2] start with [condition 3]; The order in which the connect, by, and start with statements are placed does not affect the results of the query, and the [where condition 1] may not be required. [where conditions 1], [condition 2]] [condition 3]] each function is not the same range: The [where condition 1] is filtered in the record selected under the connect by [conditional 2] start with [conditional 3]], filtering for a single record, without considering the tree structure; [condition 2] specifies the condition of the constructed tree and the filter condition for the branch of the tree, where the filtered filter filters the qualified records and all the child nodes under them; [condition 3] defines the condition as the starting point of the search, and if the top-down search is qualified as the root node condition, if the bottom-up search is qualified as a leaf node condition; Example: If there is a list of the following structures: some_table (ID, p_id, name), where p_id holds the ID of the parent record. Select * from, some_table, where, t.id, =123, connect, prior, t.p_id=t.id, and, t.p_id, =321, start, by, with, t, t.p_id=33, or, t.p_id=66; Description of prior: Prior exists in condition 2], you can not, no, you can only find records that conform to the start with [condition 3] , and are not looking for the child nodes of these records.. To a time when there are two way: connect by prior t.p_id=t.id by t.p_id=prior or connect t.id, the first sign said with a top-down search method (first find the parent node and child nodes, after looking for) a style of representation using a bottom-up search method (first to find the leaf node and then find the parent node). Two. Execution principle The implementation principles of connect by... Start with... Can be explained by the execution of the following program and the tuning of the stored procedure RECURSE