Example of CTE application in Oracle11g

For more information about the CTE application in SQL Server, see: blog. csdn. netdownmoonarchive200910242515814.aspx in fact, the CTE Syntax of ORACLE is exactly the same. See example 1. Create a sample data table as follows: ViewCodedeclaretableExistedCountnumber; beginselectcount (1) in

For SQL SERVER CTE application in the CTE, please see here: http://blog.csdn.net/downmoon/archive/2009/10/23/4715814.aspx in fact, the ORACLE CTE syntax is exactly the same, see the example: 1. Create a sample data table as follows: View Code declare tableExistedCount number; begin select count (1) in

For the CTE application in the CTE of SQL SERVER, see here: http://blog.csdn.net/downmoon/archive/2009/10/23/4715814.aspx

In fact, the CTE Syntax of ORACLE is exactly the same. Let's look at the example:

1. Create an example data table as follows:

View Code declare Partition number; begin select count (1) into tableExistedCount from user_tables where TABLE_NAME = 'demoorganization'; if tableExistedCount = 1 then execute immediate 'drop table DemoOrganization cascade constraints '; end if; end; /* ===================================================== =======================================* // * Table: demoOrganization * // * ====================================== ==================== */create table DemoOrganization (OrgID NUMBER (20, 0) not null, OrgCode VARCHAR2 (100), OrgName NVARCHAR2 (100), OrgPath VARCHAR2 (500), ParentID INTEGER, OLevel INTEGER default 0, OrderID NUMBER (), CurState INTEGER default 0, addUser VARCHAR2 (50), AddTime DATE, constraint PK_DEMOORGANIZATION primary key (OrgID); comment on table DemoOrganization is ''; comment on column DemoOrganization. orgID is 'Organization id'; comment on column DemoOrganization. orgCode is 'Organization Code'; comment on column DemoOrganization. orgName is 'Organization name'; comment on column DemoOrganization. orgPath is 'Organization path'; comment on column DemoOrganization. parentID is 'parent id'; comment on column DemoOrganization. OLevel is 'level'; comment on column DemoOrganization. orderID is 'sorted'; comment on column DemoOrganization. curState is 'current state'; comment on column DemoOrganization. addUser is 'creator '; comment on column DemoOrganization. addTime is 'creation time ';

Insert test data:

View Code drop sequence SEQ_DEMOORGANIZATION; -- Create sequence create sequence SEQ_DEMOORGANIZATION minvalue 1 maxvalue 999999999999999 start with 1 increment by 1 cache 201; create or replace trigger TRI_SEQ_DEMOORGANIZATION before insert on DEMOORGANIZATION for each row begin select SEQ_DEMOORGANIZATION.NEXTVAL into: new. ORGID from dual; end; truncate table DEMOORGANIZATION; insert into demoorganization (ORGNAME, OLEVEL, ORGPATH, PARENTID, ADDTIME, ADDUSER, ORDERID, CURSTATE) select 'Organization 1', 1, '0', 0, sysdate, 'testuser', 13, 0 from dual union all select 'Organizational unit 2', 1, '0', 0, sysdate, 'testuser ', from dual union all select 'Organizational unit 3', 1, '0', 0, sysdate, 'testuser', from dual union all select 'Organizational unit 4', 2, '1', 1, sysdate, 'testuser', 19,0 from dual union all select 'Organizational unit 5', 2, '2', 2, sysdate, 'testuser ', 1/4 from dual union all select 'Organizational unit 6', 3, '000000', 4, sysdate, 'testuser', from dual union all select 'Organizational unit 7', 3, '20140901', 4, sysdate, 'testuser', 1/4 from dual union all select 'Organizational unit 8', 3, '20160901', 5, sysdate, 'testuser', 3, 0 from dual union all select 'Organizational unit 9', 4, '2017/6', 6, sysdate, 'testuser', 5, 0 from dual union all select 'Organizational unit 10 ', 4, '2014/3/6', 6, sysdate, 'testuser', 1/4 from dual union all select 'Organization 11', 4, '2014/3/6', 6, sysdate, 'testuser', 2/5 from dual union all select 'Organizational unit 12', 4, '2017/8', 8, sysdate, 'testuser ', 3, 0 from dual union all select 'Organizational unit 13', 4, '2017/8', 8, sysdate, 'testuser', 2/5 from dual; select * from DEMOORGANIZATION;

Ii. Example:

1. -- Query all the sub-organizations whose ORGID is 2 and whose level is not greater than 2

WITH SimpleRecursive(ORGNAME, ORGID, ORGPATH,PARENTID,OLEVEL)    AS(SELECT ORGNAME, ORGID, ORGPATH,PARENTID,0  FROM DEMOORGANIZATION WHERE ORGID = 2UNION ALLSELECT 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 PARENTIDCodeFROM SimpleRecursive sr inner join DEMOORGANIZATION con sr.PARENTID=c.ORGIDwhere c.OLEVEL<=2

2. -- Query all the sub-organizations whose ORGID is 2 and whose level is not greater than 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. -- search for all tree-level departments corresponding to a department with an ORGID of 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. -- find the top-level department corresponding to a department with an ORGID of 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) TWHERE ROWNUM = 1

Helping others is the same as self-help! 3w@live.cn