Oracle tree SQL query example

Use this SQL statement

1. Select*FromTree

View the raw data as follows:

The following figure shows the result of a tree query (including the fields ROOT, LEVEL, IS_LEAF, and PATH ):

Execute the following SQL statement:

1. SelectConnect_by_root (child_col) root,Level, Decode (connect_by_isleaf, 0,'No', 1,'Yes') Is_leaf, sys_connect_by_path (child_col,'/') Path
2. FromTree
3. StartWithParent_colIs Null Connect By PriorChild_col = parent_col;

Tree query focuses on startWith...Connect By Prior... Statement

And connect_by_root, connect_by_isleaf, and sys_connect_by_path functions. decode is a common function.

Other references:

Example of a dynamic query statement:

1. Declare
2. N_rows number;
3. V_ SQL _stmt varchar2 (50 );
4. V_table_name varchar2 (20 );
5. V_name varchar2 (20 );
6. Begin
7. V_table_name: ='Tree';
8. V_ SQL _stmt: ='Select count (*) from'| V_table_name |'Where parent_col =: 1';
9. V_name: ='Air';
10. Dbms_output.put_line (v_ SQL _stmt );
11.  ExecuteImmediate v_ SQL _stmtIntoN_rows using v_name;
12. Dbms_output.put_line ('The number of rows'| V_table_name |'Is'| N_rows );
13. End;

For more information about Oracle, see Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12

1 2 Next Page