Example of the application of a CTE in Oracle 11g

For a CTE application in a CTE in SQL Server, see here: http://www.cnblogs.com/downmoon/archive/2009/10/23/1588405.html

In fact, Oracle's CTE syntax is exactly the same, look at the example:

First, create the sample data table as follows:

declare tableexistedcount number;
      Begin select COUNT (1) into Tableexistedcount from user_tables where table_name = ' demoorganization ';
      If Tableexistedcount =1 then execute immediate ' drop table demoorganization cascade '; 
End If;
    
End                                      /*==============================================================*//* table:demoorganization                * */*==============================================================*/CREATE TABLE demoorganization (OrgID Number (20,0) not NULL, Orgcode VARCHAR2 (MB), OrgName NVARCHAR2 (100) , Orgpath VARCHAR2, parentid Integer, Olevel integer def              Ault 0, OrderID number (10,0), curstate INTEGER default 0, AddUser VARCHAR2 (M), Addtime DATE, constraint pk_demoorganization primary KEY (OrgID));
    
Comment on table demoorganization is ' demo organizational structure ';
    
Comment on column demoorganization.orgid is ' organization ID ';
    
Comment on column demoorganization.orgcode is ' mechanism code ';
    
Comment on column Demoorganization.orgname is ' organization name ';
    
Comment on column demoorganization.orgpath is ' mechanism path ';
    
Comment on column Demoorganization.parentid is ' superior ID ';
    
Comment on column demoorganization.olevel is ' level ';
    
Comment on column Demoorganization.orderid is ' sort ';
    
Comment on column demoorganization.curstate is ' current state ';
    
Comment on column demoorganization.adduser are ' creator '; Comment on column demoorganization.addtime is ' creation time ';