Oracle "connect by" is a hierarchical query clause, which is generally used for tree or hierarchical query of result sets. Its syntax is:
[ START WITHcondition ]CONNECT BY [ NOCYCLE ] condition
The start with .. connect by clause can be used to select data that has a hierarchical relationship (usually some sort of parent-> child (boss-> employee or thing-> parts ).
Note:
1. Start with: Tell the system which node is used as the root node to start searching and constructing the result set. This node is the highest node in the returned record.
2. When there are parent and child nodes in the hierarchical query, the ORA-01436 error is returned. In this case, you need to add the nocycle keyword after connect. At the same time, you can use the connect_by_iscycle pseudo column to locate specific nodes with parent-child loops. Connect_by_iscycle must be used with the keyword "nocycle ".
Next, we will use some examples to illustrate the usage of "connect.
Example 1
Create a department table, which has three fields, corresponding to the department ID, Department name, and parent department ID.
-- Create table create table DEP ( DEPID number(10)notnull, DEPNAME varchar2(256), UPPERDEPID number(10)) ;
Initialize some data
SQL> insert into Dep (depid, depname, upperdepid) values (0, 'General authorization', null); 1 row inserted SQL> insert into Dep (depid, depname, upperdepid) values (1, 'dation', 0); 1 row inserted SQL> insert into Dep (depid, depname, upperdepid) values (2, 'test part', 0 ); 1 row inserted SQL> insert into Dep (depid, depname, upperdepid) values (3, 'sever demo', 1); 1 row inserted SQL> insert into Dep (depid, depname, upperdepid) values (4, 'client development process', 1); 1 row inserted SQL> insert into Dep (depid, depname, upperdepid) values (5, 'ta test part ', 2); 1 row inserted SQL> insert into Dep (depid, depname, upperdepid) values (6, 'Project test part', 2); 1 row inserted SQL> commit; Commit complete
SQL> SELECT * FROM DEP; DEPID DEPNAME UPPERDEPID----------- -------------------------------------------------------------------------------- ----------- 0 General Deparment 1 Development 0 2 QA 0 3 Server Development 1 4 Client Development 1 5 TA 2 6 Porject QA 2 7 rowsselected
Now I want to implement tree-like query results based on "connect ".
SQL> SELECT RPAD(' ', 2*(LEVEL-1),'-') || DEPNAME "DEPNAME",CONNECT_BY_ROOT DEPNAME "ROOT",CONNECT_BY_ISLEAF "ISLEAF", LEVEL , SYS_CONNECT_BY_PATH(DEPNAME,'/')"PATH"FROM DEP START WITH UPPERDEPID IS NULLCONNECT BY PRIOR DEPID = UPPERDEPID; DEPNAME ROOT ISLEAF LEVELPATH------------------------------ ------------------- ---------- ---------- --------------------------------------------------------------------------------General Deparment General Deparment 0 1 /General Deparment -Development General Deparment 0 2 /General Deparment/Development ---Server Development General Deparment 1 3 /General Deparment/Development/Server Development ---Client Development General Deparment 1 3 /General Deparment/Development/Client Development -QA General Deparment 0 2 /General Deparment/QA ---TA General Deparment 1 3 /General Deparment/QA/TA ---Porject QA General Deparment 1 3 /General Deparment/QA/Porject QA 7 rowsselected
1. connect_by_root returns the top node of the current node
2. connect_by_isleaf determines whether it is a leaf node. If there is a subnode under this node, it is not a leaf node.
3. Level pseudo column indicates node depth
4. The sys_connect_by_path function displays detailed paths and separates them "/".
Example 2
Use connect by to generate a sequence
SQL> SELEC TROWNUM FROM DUAL CONNECT BY ROWNUM <= 10; ROWNUM---------- 1 2 3 4 5 6 7 8 9 10 10 rowsselected
Example 3
Use connect by to convert hexadecimal progress to decimal
Create or replace function f_hex_to_dec (p_strinvarchar2) return varchar2 is convert -- Object Name: f_hex_to_dec -- Object Description: hexadecimal conversion decimal -- input parameter: p_str hexadecimal string -- return result: decimal string -- Test Case: Select f_hex_to_dec ('78a') from dual; v_v_return varchar2 (4000); begin select sum (data) into v_return from (select (caseupper (substr (p_str, rownum, 1 )) when 'a 'then '10' when 'B' then '11' when 'C' then '12' when 'D' then '13' when 'E' then '14' when 'F' then '15' else substr (p_str, rownum, 1) End) * power (16, length (p_str)-rownum) data from dual connect by rownum <= length (p_str); Return v_return; exception when others then return NULL; end;
Note:
1. Connect by rownum <= length (p_str) traverse input strings one by one
2. The case statement is used to parse the 10-hexadecimal value of the A-F in hexadecimal notation.