Connect by prior

Connect by prior start with is often used when a table has a recursive relationship. For example, we often store a complex directory tree in a table. You can also store some departments in a table, and these departments are affiliated with each other. In this case, you will use connect by prior start.

The typical method is:
Select * from Table connect by prior cur_id = parent_id start with cur_id = ???
For example:
A B
1 0
2 1
3 1
4 2
5 3

If you want to search for a = 2 and all the data below it, then:
Select * from Table connect by prior A = B start with a = 2
A B
2 1
4 2

These are just the basics. In fact, as long as you flexibly construct query statements. Unexpected results can be obtained. For example, every path in the tree is generated.
However, the tree composed of these records must be normal. If there is a parent-child relationship, a loop error occurs!

 

Select * From tb_cus_area_cde

-- Child takes the parent
Select * From tb_cus_area_cde
Connect by prior A. c_snr_area = A. c_area_cde start with A. c_area_cde = '201312'

-- Parent fetch child
Select * From tb_cus_area_cde
Connect by prior A. c_area_cde = A. c_snr_area start with A. c_snr_area is null

 

 

Note: When using this function, the statement parameter must use resultset. type_scroll_insensitive instead of resultset. type_scroll_sensitive. Here we will talk about the differences between the two:

1. type_forword_only, which can be rolled forward only;

2. type_scroll_insensitive: two-way scrolling, but not timely updating. If the data in the database has been modified, it is not reflected in the resultset.

3. type_scroll_sensitive, two-way scrolling, and timely tracking of database updates, in order to change the data in the resultset