Oracle Study Notes (1) -- oracle recursive query
First of all, we mentioned that connect by is used for Recursive queries. It is to traverse the link of the recursive application designed by the table structure in the database to meet our requirements. Connect by is followed by recursive conditions. In this condition sentence, the keyword prior is often used. This keyword indicates the previous record, such as connect by prior id = parentid, it indicates that the recursive condition rule is that the id of the previous record is the parentid of the next record, so that the id of the next record is the parentid of the next record, until the conditions in connect by are no longer met, it indicates that the leaf of the tree has been reached. In recursion, another condition often uses start with, which indicates the starting condition of traversal. For example, start with parentid = 0 or parentid is null. In this way, we can determine the conditions to start traversing. with the combination of start with... connect by, we can get the desired tree data in a targeted manner. // Check the sub-product directory list with the level field and whether the sub-product directory is leaf.
12 SELECT pc. *, LEVEL, CONNECT_BY_ISLEAF ISLEAF3 FROM qd_product_category pc4 WHERE pc. enabled = 1 and pc. deleted = 0 and pc. parent_id is not null5 start with CATEGORY_NAME =? And parent_id is null6 connect by prior pc. PK_ID = pc. PARENT_ID7