startwith的用法(国外英文资料).docVIP

startwith的用法(国外英文资料) Oracle start with... Connect by prior clause Connect by is used in structured queries. The basic syntax is: The select... From tablename start with condition 1 Connect by condition 2 The where condition 3; Ex. : Start with... The Connect By clause recursive query is typically used for a table to maintain the tree structure. Create a sample table: The CREATE TABLE TBL_TEST ( ID NUMBER, NAME VARCHAR2 (100 BYTE), PID NUMBER DEFAULT zero ); Insert test data: INSERT INTO TBL_TEST (ID, NAME, PID) VALUES ( 1 , 10, 0); INSERT INTO TBL_TEST (ID, NAME, PID) VALUES ( 2 , 11, 1); INSERT INTO TBL_TEST (ID, NAME, PID) VALUES ( 3 , 20, 0); INSERT INTO TBL_TEST (ID, NAME, PID) VALUES ( 4 , 12, 1); INSERT INTO TBL_TEST (ID, NAME, PID) VALUES ( 5 , 121, 2); From Root to tree tip recursion Select * from TBL_TEST Start with id = 1 Connect by prior id = pid From the end to the tree ROOT Select * from TBL_TEST Start with id = 5 Connect by prior pid = id Simply put a tree structure in a table, such as two fields in a table: So org_id, parent_id so by representing the parent of each record,You can form a tree structure. All records of this tree can be obtained using the query above. Among them: Condition 1 is a qualified statement of the root, which, of course, can be relaxed to get multiple root nodes, which is actually a tree. Condition 2 is connected, which expressed in the PRIOR on a record, such as CONNECT BY the PRIOR org_id = parent_id org_id in on a record is the parent_id records in this article, namely the records father is on a record. Condition 3 is the filter condition that is used to filter all records returned. Heres a brief introduction: When you scan the tree structure table, you need to access every node of the tree structure, and one node can only access once, and the steps are as follows: Step 1: start with the root node; Step 2: access the node; Step 3: determine have not been to visit the child nodes of the node, if any, is not access to its most