Oracle 11g中CTE的应用实例

关于SQL SERVER中的CTE中的CTE应用，其实，ORACLE的CTE语法完全一样，看示例：

一、创建示例数据表如下：

1 declare 
 2       tableExistedCount  number; 
 3 begin 
 4       select count(1) into tableExistedCount  from user_tables where TABLE_NAME ='DemoOrganization';
 5       if   tableExistedCount =1   then 
 6          execute immediate ' drop table DemoOrganization cascade constraints';
 7       end   if; 
 8 end;
 9 
10 /*==============================================================*/
11 /* Table: DemoOrganization                                      */
12 /*==============================================================*/
13 create table DemoOrganization 
14 (
15    OrgID                NUMBER(20,0)         not null,
16    OrgCode              VARCHAR2(100),
17    OrgName              NVARCHAR2(100),
18    OrgPath              VARCHAR2(500),
19    ParentID             INTEGER,
20    OLevel               INTEGER              default 0,
21    OrderID              NUMBER(10,0),
22    CurState             INTEGER              default 0,
23    AddUser              VARCHAR2(50),
24    AddTime              DATE,
25    constraint PK_DEMOORGANIZATION primary key (OrgID)
26 );
27 
28 comment on table DemoOrganization is
29 '演示组织机构';
30 
31 comment on column DemoOrganization.OrgID is
32 '机构ID';
33 
34 comment on column DemoOrganization.OrgCode is
35 '机构编码';
36 
37 comment on column DemoOrganization.OrgName is
38 '机构名称';
39 
40 comment on column DemoOrganization.OrgPath is
41 '机构路径';
42 
43 comment on column DemoOrganization.ParentID is
44 '上级ID';
45 
46 comment on column DemoOrganization.OLevel is
47 '级别';
48 
49 comment on column DemoOrganization.OrderID is
50 '排序';
51 
52 comment on column DemoOrganization.CurState is
53 '当前状态';
54 
55 comment on column DemoOrganization.AddUser is
56 '创建人';
57 
58 comment on column DemoOrganization.AddTime is
59 '创建时间';

并插入测试数据：

1 drop sequence SEQ_DEMOORGANIZATION;
 2 -- Create sequence 
 3 create sequence SEQ_DEMOORGANIZATION
 4 minvalue 1
 5 maxvalue 999999999999999
 6 start with 1
 7 increment by 1
 8 cache 201;
 9 
10 create or replace trigger TRI_SEQ_DEMOORGANIZATION
11 before insert on DEMOORGANIZATION
12 
13 for each row
14 begin
15 select SEQ_DEMOORGANIZATION.NEXTVAL into:new.ORGID from dual;
16 end;
17 
18 truncate table DEMOORGANIZATION;
19 
20 INSERT INTO DEMOORGANIZATION(ORGNAME,OLEVEL,ORGPATH,PARENTID ,ADDTIME ,ADDUSER, ORDERID  ,CURSTATE)
21 select '组织机构1',1,'0',0,sysdate,'testUser',13,0  from dual union all
22 select '组织机构2',1,'0',0,sysdate,'testUser',12,0  from dual union all
23 select '组织机构3',1,'0',0,sysdate,'testUser' ,10,0  from dual union all
24 select '组织机构4',2,'1',1,sysdate,'testUser' ,19,0  from dual union all
25 select '组织机构5',2,'2',2,sysdate,'testUser' ,17,0  from dual union all
26 select '组织机构6',3,'1/4',4,sysdate,'testUser' ,16,0  from dual union all
27 select '组织机构7',3,'1/4',4,sysdate,'testUser' ,4,0  from dual union all
28 select '组织机构8',3,'2/5',5,sysdate,'testUser' ,3, 0 from dual union all
29 select '组织机构9',4,'1/4/6',6,sysdate,'testUser' ,5,0  from dual union all
30 select '组织机构10',4,'1/4/6',6,sysdate,'testUser' ,63,0  from dual union all
31 select '组织机构11',4,'1/4/6',6,sysdate,'testUser' ,83,0  from dual union all
32 select '组织机构12',4,'2/5/8',8,sysdate,'testUser' ,3,0  from dual union all
33 select '组织机构13',4,'2/5/8',8,sysdate,'testUser', 1,0  from dual;
34 
35 select * from DEMOORGANIZATION;

二、示例：

1、--查询ORGID为2的机构包含所有子机构，且级别不大于2

WITH SimpleRecursive(ORGNAME, ORGID, ORGPATH,PARENTID,OLEVEL)
    AS
(SELECT ORGNAME, ORGID, ORGPATH,PARENTID,0  FROM DEMOORGANIZATION WHERE ORGID = 2
UNION ALL
SELECT P.ORGNAME, P.ORGID, P.ORGPATH,P.PARENTID,P.OLEVEL+1
 FROM DEMOORGANIZATION  P  INNER JOIN
 SimpleRecursive A ON A.ORGID = P.PARENTID
)
SELECT sr.ORGNAME as ORGNAME, c.ORGNAME as PARENTIDName,sr.ORGPATH as PARENTIDCode
FROM SimpleRecursive sr inner join DEMOORGANIZATION c
on sr.PARENTID=c.ORGID
where c.OLEVEL<=2

2、--查询ORGID为2的机构包含所有子机构，且级别不大于3

SELECT ORGNAME as ORGNAME,
(Select ORGNAME from DEMOORGANIZATION s where c.PARENTID=s.ORGID) as PARENTNAME,
ORGPATH as ORGPATH,OLEVELfrom DEMOORGANIZATION c where ORGPATH like'2/%' and OLEVEL<=3

3、--查找某个ORGID为12的部门对应的所有树级部门

SELECT ORGID, OLEVEL, ORGNAME, PARENTID
  FROM DEMOORGANIZATION D
 START WITH ORGID IN (SELECT ORGID
                        FROM DEMOORGANIZATION
                       WHERE ORGID = 12
                         AND ROWNUM = 1)
CONNECT BY PRIOR D.PARENTID = ORGID;

4、--查找某个ORGID为12的部门对应的顶级部门             

SELECT *
 FROM (SELECT FIRST_VALUE(ORGNAME) OVER(ORDER BY LEVEL DESC ROWS UNBOUNDED PRECEDING) AS FIRSTID
         FROM DEMOORGANIZATION
        START WITH ORGID = 12
       CONNECT BY PRIOR PARENTID = ORGID) T
WHERE ROWNUM = 1