Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to use Oracle recursive query start with connect by prior, the article introduces in great detail, has a certain reference value, interested friends must read it!

1. Basic grammar

The basic syntax of connect by recursive query is:

Select 1 from form start with... Connect by prior id = pId

Start with: indicates what is the root node. You can write 1: 1 without restriction. If you want to use the node with id as the root node, write start with id = 123.

Connect by:connect by is necessary, some cases of start with can be omitted, or there are no restrictions on start with 1.

The prior:prior keyword can be placed before or after the equal sign to express different meanings. For example, prior id = pid means that pid is the root node of the record.

Second, business scenarios

For example, write a SQL:

T_user (user table), t_unit_info (unit table), which means to find out the user information of the unit and its subunits with the unit code of "15803" as the root node.

Select us.user_code, us.user_name from t_user us where exists (select 1 from t_unit_info uinfo where us.unit_code = uinfo.unit_code start with uinfo.unit_code = '15803' connect by prior uinfo.unit_code = uinfo.para_unit_code)

Then change the position of the prior, and find that only the unit code is "15803" corresponding to the unit, not the child unit.

Select us.user_code, us.user_name from t_user us where exists (select 1 from t_unit_info uinfo where us.unit_code = uinfo.unit_code start with uinfo.unit_code = '15803' connect by uinfo.unit_code = prior uinfo.para_unit_code)

Oracle recursive query start with connect by prior usage and knowledge is not only that, this blog is just a brief record of what I have encountered

The above is all the contents of the article "Oracle Recursive query how to use start with connect by prior". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.