# Oracle daily # Use of Oracle connect

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.