Oracle 11g中CTE应用示例
来源:互联网 发布:淘宝钱夫人雪梨 编辑:程序博客网 时间:2024/06/09 22:33
关于SQL SERVER中的CTE中的CTE应用,请看这里:http://blog.csdn.net/downmoon/archive/2009/10/23/4715814.aspx
其实,ORACLE的CTE语法完全一样,看示例:
一、创建示例数据表如下:
View Code 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 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(10,0), 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 '机构ID'; comment on column DemoOrganization.OrgCode is '机构编码'; comment on column DemoOrganization.OrgName is '机构名称'; comment on column DemoOrganization.OrgPath is '机构路径'; comment on column DemoOrganization.ParentID is '上级ID'; comment on column DemoOrganization.OLevel is '级别'; comment on column DemoOrganization.OrderID is '排序'; comment on column DemoOrganization.CurState is '当前状态'; comment on column DemoOrganization.AddUser is '创建人'; comment on column DemoOrganization.AddTime is '创建时间';
并插入测试数据:
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 '组织机构1',1,'0',0,sysdate,'testUser',13,0 from dual union all select '组织机构2',1,'0',0,sysdate,'testUser',12,0 from dual union all select '组织机构3',1,'0',0,sysdate,'testUser' ,10,0 from dual union all select '组织机构4',2,'1',1,sysdate,'testUser' ,19,0 from dual union all select '组织机构5',2,'2',2,sysdate,'testUser' ,17,0 from dual union all select '组织机构6',3,'1/4',4,sysdate,'testUser' ,16,0 from dual union all select '组织机构7',3,'1/4',4,sysdate,'testUser' ,4,0 from dual union all select '组织机构8',3,'2/5',5,sysdate,'testUser' ,3, 0 from dual union all select '组织机构9',4,'1/4/6',6,sysdate,'testUser' ,5,0 from dual union all select '组织机构10',4,'1/4/6',6,sysdate,'testUser' ,63,0 from dual union all select '组织机构11',4,'1/4/6',6,sysdate,'testUser' ,83,0 from dual union all select '组织机构12',4,'2/5/8',8,sysdate,'testUser' ,3,0 from dual union all select '组织机构13',4,'2/5/8',8,sysdate,'testUser', 1,0 from dual; 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 = 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、--查询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) TWHERE ROWNUM = 1
邀月注:本文版权由邀月和CSDN共同所有,转载请注明出处。
助人等于自助! 3w@live.cn
- Oracle 11g中CTE应用示例
- Oracle 11g中CTE应用示例
- oracle 11g streams 应用进程使用示例
- oracle CTE
- Oracle 11g 新特性 -- SQL Plan Management 示例
- Oracle 11g 新特性 -- SQL Plan Management 示例
- oracle 11g streams rule和rule set 使用示例
- oracle 11g streams 捕获(capture)进程使用示例
- oracle 11g streams 传播进程使用示例
- oracle 11g streams 逻辑修改记录(LCR)示例
- oracle 11g 自动维护任务说明 示例
- Oracle 11g R2数据库示例用户安装说明
- Oracle 11g R2 RAC RMAN备份脚本示例
- 『ORACLE』 PLSQL更新数据示例(11g)
- 『ORACLE』 PLSQL删除数据示例(11g)
- CTE在Oracle和Sqlserver中使用的差异
- CTE 递归应用实例
- oracle CTE 简介
- C++疑问
- Objective-C私有方法和类目
- OpenEvent
- ubuntu config FTP Server
- C++疑问
- Oracle 11g中CTE应用示例
- mysql常用命令
- 用实例谈关于修饰符的经验
- 人生职业生涯的五个黄金阶段
- String常用知识点小结
- Unity3D iTween 使用
- 验收视频的收获
- Dialog例子-工单排产
- SpannableString 记录