今天和大家分享一些有关高级Oracle数据库(4)收藏

5.层次查询(Hierarical Retrival)
5.1 基本概念
对于层次查询需要掌握：
1.解释层次查询的基本概念，识别需求中需要用到层次查询的能力。
2.建立和格式化一个树形报表(tree report)。
3.修剪树形结构的节点(node)和枝(branches)。
关键词：tree,root(根),node,leaf(叶子),branch(树枝，分支)
本节例子来源于表s_emp,表结构和数据如下：
看上面的表s_emp,使用层次查询，我们可以获得一张表基于层次关系的数据集合。Oracle是一
种关系型数据库，在表中不可能以层次的关系存放数据。但是我们可以通过一定的规则，使用tree walking(树的遍历或树的查找)来获得层次关系的数据。Hierarical query是一种获得树的层析关系报表的方法。
   树形结构的数据集合，存在于我们日常生活中的很多地方，比如考虑一个家族关系，有长辈，长辈下面有子女，子女下面还可以有子女，这转化为层次或等级关系就是：根节点只有一个，下面有子节点，子节点下面还有子节点，这样组成了一棵树。(有时候，根节点root不一定只有一个，严格意义上说，这种情况不是一个严格的树)。
  当一种层次关系是存在一个表的行中，那么这种层次是可以获得的。例如，我们看s_emp表，对于title:VP，我们知道这些行中都包含manager_id=1,也就是说，这些行属于id=1的雇员的下属雇员，那么有title=vp又可以获得一系列的层次，而这些层次的跟则是id=1这个雇员。由此，得到一棵树形结构数据集合。
    层次树或等级树，在譬如家族关系，育种关系，组织管理，产品装配，人类进化，科学研究等领
广泛应用。
    下面我们就根据s_emp这张表，根据职位大小来描述一个树形结构图。如图：
树形结构的父子关系，你可以控制：
1.    遍历树的方向，是自上而下，还是自下而上。
2.    确定层次的开始点(root)的位置。
层次查询语句正是从这两个方面来确定的,start with确定开始点,connect by确定遍历的方向。
    
5.2 层次查询
5.2.1 语法：
select [level],column,exper,...
  from table
[where condition]
[start with conditions]
[connect by conditions]
层次查询是通过start with和connect by子句标识的。
1.其中level关键字是可选的，表示等级，表示root,2表示root的child,其他相同的规则。
2.From之后可以是table,view但是只能是一个table。
3.Where条件限制了查询返回的行，但是不影响层次关系，属于将节点截断，但是这个被截断的节点的下层child不受影响。
4.Start with是表示开始节点，对于一个真实的层次关系，必须要有这个子句，但是不是必须
的，后面详细介绍。
5.connect by prior是指定父子关系，其中prior的位置不一定要在connect by之后，
对于一个真实的层次关系，这也是必须的。
对于from是视图的，那么这个view不能包含join。
5.2.2 遍历树
    Start with子句：
        首先必须确定startpoint，通过start with子句，后面加条件，这个条件是任何合法的条件表达式。
    Start with确定将哪行作为root，如果没有start with,则每行都当作root，然后查找其后代，这不是一个真实的查询。Start with后面可以使用子查询，如果有where条件，则会截断层次中的相关满足条件的节点，但是不影响整个层次结构。可以带多个条件。
   对于s_emp,从root title=president开始，语句如下：
select level,id,manager_id,last_name,title from s_emp
      start with manager_id is null
      connect by prior id=manager_id;
这个语句与下面的语句等价，将start with 后面的条件换成子查询。
select level,id,manager_id,last_name,title from s_emp
      start with title=(select title from s_emp where manager_id is null)
      connect by prior id=manager_id;
注意：start with 和connect by prior不是ANSI标准sql。
   Connect by子句：
   Connect by与prior确定一个层次查询的条件和遍历的方向(prior确定)。
Connect by prior column_1=column_2;(其中prior表示前一个节点的意思，可以在connect by等号的前后，列之前，也可以放到select中的列之前)。
  Connect by也可以带多个条件，比如 connect by prior id=manager_id and id>10;
   方向：
1.    自顶向下遍历：就是先由根节点，然后遍历子节点。column_1表示父key,column_2表示子key。即这种情况下：connect by prior 父key=子key表示自顶向下，等同于connect by 子key=prior 父key.
例如：
select level,id,manager_id,last_name, title from s_emp
      start with  manager_id=2
      connect by id=prior manager_id;--自下而上遍历
2.    自底向上遍历：就是先由最底层的子节点，遍历一直找到根节点。与上面的相反。
Connect by之后不能有子查询，但是可以加其他条件,比如加上and id !=2等。这句话则会截断树枝，如果id=2的这个节点下面有很多子孙后代，则全部截断不显示。比如下面的句子：
select level,id,manager_id,last_name,title from s_emp
      start with title=(select title from s_emp where manager_id is null)
      connect by prior id=manager_id and id!=2;
不来不加上id!=2，共有25条记录，现在加上这个条件只有9条记录了，因为id=2的后代包括自己共有16条记录，全部被截断。


5.2.3 使用level和lpad格式化报表
   Level是层次查询的一个伪列，如果有level，必须有connect by,start with可以没有。Lpad是在一个string的左边添加一定长度的字符，并且满足中间的参数长度要求，不满足自动添加。例如现在的需求是，输出s_emp等级报表，root节点的last_name不变，比如第2等级，也就是level=2的前面加两个’_’符号，level=3的前面加4个。这样我们可以得到一个公式就是：
   Lpad(last_name,length(last_name)+(level*2)-1,’_’)
可以得出下面的语句：
select level,id,manager_id,lpad(last_name,length(last_name)+(level*2)-2,'_'),title,prior last_name from s_emp
      start with manager_id is null
      connect by prior id=manager_id;
select中的prior last_name是输出其父亲的last_name.这个语句执行的结果如下：
5.2.4 修剪branches
  上面已经提到，where子句会将节点删除，但是其后代不会受到影响，connect by 中加上条件会将满足条件的整个树枝包括后代都删除。要注意，如果是connect by之后加条件正好条件选到根，那么结果和没有加一样，如图所示：

5.2.5 层次查询限制
1.层次查询from 之后如果是table，只能是一个table，不能有join。
2.from之后如果是view，则view不能是带join的。
3.使用order by子句，order子句是在等级层次做完之后开始的，所以对于层次查询来说没有什么意义，除非特别关注level，获得某行在层次中的深度，但是这两种都会破坏层次。见5.3增强特性中的使用siblings排序。
4.在start with中表达式可以有子查询，但是connect by中不能有子查询。

5.2.6 应用
1）查询每个等级上节点的数目
先查看总共有几个等级：
select count(distinct level)
from s_emp
start with manager_id is null
connect by prior id=manager_id;
要查看每个等级上有多少个节点，只要按等级分组，并统计节点的数目即可，可以这样写：
select level,count(last_name)
from s_emp
start with manager_id is null
connect by prior id=manager_id
group by level;
2）格式化报表
见5.2.3.
3）查看等级关系
    有一个常见的需求，比如给定一个具体的emp看是否对某个emp有管理权，也就是从给定的节点寻找，看其子树节点中能否找到这个节点。如果找到，返回，找不到，no rows returned.
    比如对于s_emp表，从根节点，也就是manager_id is null的开始找，看first_name=’ Elena’是否被它管理，语句如下：
select level,a.* from 
s_emp a
where first_name='Elena' –被管理的节点
start with manager_id is null –开始节点
connect by prior id=manager_id;
4）删除子树
        比如有这样的需求，现在要裁员，将某个部门的员工包括经理全部裁掉，那么可以使用树形查询作为子查询实现这个功能。
    将id为2的员工管理的所有员工包括自己删除。因为要全部裁掉了。那么语句如下：
delete from s_emp where id in(
select id from 
s_emp a
start with id=2 –从id=2的员工开始查找其子节点，把整棵树删除
connect by prior id=manager_id);
5）找出每个部门的经理
   这个需求，我们可以从树中查找，也就是对于每个部门选最高等级节点。可以使用connect by后加条件过滤branches的方法。
   select level,a.* from 
s_emp a
start with manager_id is null
connect by prior id=manager_id and dept_id !=prior dept_id;--当前行的dept_id不等于前一行的dept_id，即每个子树中选最高等级节点
6）查询一个组织中最高的几个等级
   用where level条件过滤
select level,a.* from 
s_emp a
  where level <=2 –查找前两个等级
start with manager_id is null
connect by prior id=manager_id and dept_id !=prior dept_id;
7）合计层次
  有两个需求，一是对一个指定的子树subtree做累加计算salary，一是将每行都作为root节点，然后对属于这个节点的所有字节点累加计算salary。
第一种很简单，求下sum就可以了，语句：
select sum(salary) from 
s_emp a
start with id=2—比如从id=2开始
connect by prior id=manager_id;
第2个需求，需要用到第1个，对每个root节点求这个树的累加值，然后内部层次查询的开始节点从外层查询获得。
select last_name,salary,(
  select sum(salary) from 
s_emp
start with id=a.id –让每个节点都成为root
connect by prior id=manager_id) sumsalary
from s_emp a;
8）找出指定层次中的叶子节点
  Leaf(叶子)就是没有子孙的孤立节点。Oracle 10g提供了一个简单的connect_by_isleaf=1,
0表示非叶子节点
select level,id,manager_id,last_name, title from s_emp
    where connect_by_isleaf=1 –表示查询叶子节点
      start with  manager_id=2
      connect by prior id=manager_id;
也可以通过连接查询获得，方法有多种，叶子节点肯定是level最大的节点。
select a.lev,b.id from (   
select max(level) lev from s_emp  --查询最大的level
      start with  manager_id=2
      connect by prior id=manager_id) a,
(select level lev,id 
from s_emp
start with manager_id=2 
connect by prior id=manager_id) b
      where a.lev=b.lev;
注意：level不可以前面加表名
其他：
Connect by 与rownum的联合使用，比如给定两个日期，查询中间所有的日期，按月递增：
SELECT to_date('2008-10-1', 'YYYY-MM-DD') + ROWNUM - 1
  FROM dual
CONNECT BY rownum <= to_date('2008-10-5', 'YYYY-MM-DD') -
           to_date('2008-10-1', 'YYYY-MM-DD') + 1



5.3 增强特性
   5.3.1 SYS_CONNECT_BY_PATH
Oracle 9i提供了sys_connect_by_path(column,char),其中column是字符型或能自动转换成字符型的列名。它的主要目的就是将父节点到当前节点的”path”按照指定的模式展现出现。这个函数只能使用在层次查询中。
例如，要求将s_emp表中的层次关系展现出来，并且将last_name按照’=>’展现。如root,则是=>root_last_name, level=2的就是=>root_last_name=>level_2_last_name,并且利用lpad格式化报表。语句是：
select last_name,
       level,
       id,
       lpad('   ', level * 2 - 1) || sys_connect_by_path(last_name, '=>') –前面按层次加空格，--并且后面加上路径
  from s_emp
start with manager_id is null
connect by prior id = manager_id;
下面的是oracle10g新增特性
5.3.2 CONNECT_BY_ISLEAF
    在oracle9i的时候，查找指定root下的叶子节点，是很复杂的，oracle10g引入了一个新的函数，connect_by_isleaf,如果行的值为0表示不是叶子节点，1表示是叶子节点。
  找出s_emp中找出manager_id=2开始的行为root，表示叶子节点和非叶子节点，那么语句如下：
select level,
       id,
       manager_id,
       last_name,
       title,
        (case --使用case表达式判断是否是叶子节点
         when connect_by_isleaf = 1 then
          '叶子'
         else
          '不是叶子'
       end) isleaf
  from s_emp
start with manager_id = 2
connect by prior id = manager_id;

5.3.3 CONNECT_BY_ISCYCLE和NOCYCLE关键字
    如果从root节点开始找其子孙，找到一行，结果发生和祖先互为子孙的情况，则发生循环，oracle会报ORA-01436: CONNECT BY loop in user data，在9i中只能将发生死循环的不加入到树中或删除，在10g中可以用nocycle关键字加在connect by之后，避免循环的参加查询操作。并且通过connect_by_iscycle得到哪个节点发生循环。0表示未发生循环，1表示发生了循环，如：
create table family1(
fatherid number,
childid number
);
insert into family1 values(null,1);
insert into family1  values(1,2);--父节点为1
insert into family1  values(1,3);
insert into family1  values(2,4);--发生循环
insert into family1  values(4,1);--子节点为1 
insert into family1  values(4,5);
commit;
select connect_by_iscycle, fatherid,childid,sys_connect_by_path(childid,'/')
      from family1
      start with  fatherid is null
      connect by nocycle prior childid=fatherid;
5.3.4 CONNECT_BY_ROOT
    Oracle10g新增connect_by_root,用在列名之前表示此行的根节点的相同列名的值。如：
select connect_by_root last_name root_last_name, connect_by_root id root_id,
       id,last_name,manager_id
       from s_emp
       start with manager_id is null
       connect by prior id=manager_id;
5.3.5 使用SIBLINGS关键字排序
     前面说了，对于层次查询如果用order by排序，比如order by last_name则是先做完层次获得level,然后按last_name排序，这样破坏了层次，比如特别关注某行的深度，按level排序，也是会破坏层次的。
     在oracle10g中，增加了siblings关键字的排序。
语法：order  siblings  by <expre>
     它会保护层次，并且在每个等级中按expre排序。
select level,
       id,last_name,manager_id
       from s_emp
       start with manager_id is null
       connect by prior id=manager_id
       order siblings by last_name;
--------------------------------------------
16.扩展DDL和DML语句
16.1 背景和目标
  本章内容所用的实例表是第5章—层次查询所用到的s_emp表。结构如下：
Name           Type          Nullable Default Comments 
-------------- ------------- -------- ------- -------- 
ID             NUMBER(7)                               
LAST_NAME      VARCHAR2(25)                            
FIRST_NAME     VARCHAR2(25)  Y                         
USERID         VARCHAR2(8)   Y                         
START_DATE     DATE          Y                         
COMMENTS       VARCHAR2(255) Y                         
MANAGER_ID     NUMBER(7)     Y                         
TITLE          VARCHAR2(25)  Y                         
DEPT_ID        NUMBER(7)     Y                         
SALARY         NUMBER(11,2)  Y                         
COMMISSION_PCT NUMBER(4,2)   Y      
本章目标：
1.能够描述多表insert的特性，并能够明确其使用场合。
2.掌握和使用不同类型的多表insert操作：
2-1.无条件的多表insert all
2-2.带条件的多表insert all
2-3.带条件的多表insert first
2-4．Pivoting insert
3.能够建立和使用external tables
关键字：conditional,unconditional,pivote,external,dictionary等。
背景描述：
   本章内容主要讨论，如何使用一条insert语句，向一个目标表插入多行记录，或者向多个目标表中插入多条记录，这些数据源可以来自于一个表或多个表，同过select语句获得数据源记录。常用于一些比较复杂的需求，如数据仓库的ETL中。多表insert在oracle9i中被加入。
16.2 经常使用的扩展
1.一般的insert操作。
   使用语法insert into table_name[(column[,column...])] values (value[,value…])的insert语句，每条insert只能插入到目标表中一条指定的数据。如果有很多行需要插入，而且这些数据来源于别的表或多个表之间提取的数据，那么这就不能满足要求了。因此oracle提供了一个扩展，见下面描述。
2.insert into table_name[(column[,column…])] select_statement
Oracle提供了一个扩展，将通过select语句查询的记录插入到指定表的所有列或者指定列中。每次只能插入到一个表中，但是性能已经比写很多条insert语句要高了。另外oracle还提供了直接create table table_name as select….(as不可以少！)，实现直接创建表并且插入数据，依赖于后面的select语句的结果，比如只复制表结构，约束不赋值，那么可以使用
Create table new_table as select * from source_table where 1=0; --1=0不查询记录，只复制基本结构。
比如有个需求：表mxt新增了一个列time，那么这个新增的列在oracle中会被放在所有列的后面，现在想把这个time列放在第2个位置，那么可以使用这个操作：
create table copy_mxt as select id,time,name from mxt;--按指定顺序copy表到copy_mxt中
drop table mxt;  --删除源表mxt
rename copy_mxt to mxt;--将copy的表命名为目标表
下面具体说明oracle对insert操作的其他扩展，实现目标，一条insert操作，插入到多个表中，每个表插入多条记录。
16.3向多个目标insert
  这里有四种类型：
1.    无条件的insert
2.    带条件的insert all
3.    带条件的insert first
4.    pivoting insert
多表insert使用限制：
1.    只能对table使用多表insert，不能对视图或物化视图使用。
2.    不能对远程表进行这个插入操作。
3.    在做多表insert操作，不能指定一个表的集合表达式。
4.    多表insert中的的into目标表加在一起的列数不能超过999个。
语法：
  
注：如果没有conditional_insert_clause,必须有all，即insert all开头。如果有conditional_insert_clause,则all和first只能取其一。
16.3.1 无条件的insert操作
这里所说的无条件的insert操作有两种，一种就是单表insert into dest_table select..，一种是向多个目标中insert，那么使用insert all into dest_table1,dest table2…select…
目标表只有一个：insert into dest_table[colum(,column…)] select … 
目标表有多个: insert all        --可以替代上面的操作
             Into dest_table1[column(,column…)] [values(…)] 
             Into dest_table2[column(,column…)] [values(…)]
             …
             Select_statement—values中指定的是select结果中的列
   目标表只有一个的，那么插入目标表中后面可以跟指定的列，依赖于查询出来的数目以及类型，没有values指定。如果不指定列，那么表示目标表的列类型和顺序与select语句查询的结果完全一致。
   对于目标表有多个的必须使用insert all,all不能省略。与带条件的不同，带条件的all可以省略，后面讲解。这里的每个目标表可以不指定列，依赖于values中指定的select语句查询出来的结果中的列。如果目标表不指定列，那么表示目标表的列的顺序类型和values中指定的一致，而values中的列则依赖于select语句查询出的结果。当然也可以省略values,那么表示目标表中的列顺序和类型和select语句查询出来的结果完全一致，否则报错。Insert all可以替代目标表只有一个的操作。
建议不要省略目标表中的列和values，增强可读性。
例如：
create table emp_1 as select id,last_name from s_emp where 1=0;
create table emp_2 as select * from s_emp where 1=0;
create table emp_3 as select * from s_emp where 1=0;
--没有条件，向多个目标表全量插入，必须有all
insert all 
--不指定emp_1后面的列，也不指定values,那么emp_1中的所有列类型和顺序与查询的列的类型和顺序一致
--也就是emp_1中只有查询结果中的那几列，而且类型和顺序与其一致
into emp_1 
--指定了emp_2后面的列，没有values,表示emp_2中要插入的列被选择出来，与查询的结果列类型和顺序一致
--emp_2中也可能有很多列，不止这两列
into emp_2(id,last_name)
--指定emp_3后面的列，也指定values，那么values后面的列名必须与查询结果一致，如果
--查询中有别名，必须在values中使用别名。emp_3中指定的列类型和顺序必须与values保持一致
--emp_3中也可能列数大于指定的列数
into emp_3(id,last_name) values(s_id,s_last_name)
select id s_id,last_name s_last_name
  from s_emp;

这里总共插入了75行记录，select语句查询的记录为25行，因为是无条件的插入，那么对每个目标表都插入了25行记录，所以总共插入75行记录。

16.3.2 带条件的insert all
    带条件的insert all与不带条件的insert all相比可以省略all，但是不建议省略。另外的区别就是插入到目标表之前，有条件判断，从查询的结果行中筛选出满足条件的记录，然后向目标表中插入，而不在是无条件的insert all中的全量插入。
语法是：
      Insert all
      When 条件 then
      Into dest[指定列] values(查询中的列)
      When 条件 then
       …
      [else]
      Into dest[指定列] values(查询中的列)
      Select语句.
是否可以省略目标表中的指定列和values那么与无条件的insert all一致。条件中的列那么必须与查询的结果列名保持一致，如果select中使用别名，那么条件中也必须使用别名，和values中的规则一样。Else可以指定，如果不指定else，则按照条件筛选，如果指定else，则表示与前面全不匹配的插入到else后面的目标表中。带条件的insert all与insert first的区别在于只按条件对查询的结果进行筛选，不会不考虑前面已经匹配过的记录，每次都是全量匹配，而insert first是后面的when条件insert操作会对前面已经匹配过的记录不予考虑。
例如：

--conditional insert all
insert all
--将查询结果中为s_id>20的插入,条件中指定的列必须与查询的结果名字一致，如果有别名，用别名
when s_id>20 then
  into emp_1
--s_last_name为M开头的插入,可能插入的行与s_id>20有重复
when s_last_name like 'M%'then
  into emp_2(id,last_name)
--如果指定else，则不满足上面两个条件的插入到emp_3，插入的行不会与上面两个重复
else
  into emp_3(id,last_name) values(s_id,s_last_name)
select id s_id,last_name s_last_name
  from s_emp;

总共插入26行，而查询结果为25行，emp_2中插入了一条与emp_1中重复的记录。

16.3.3 带条件的insert first
Insert first只有带条件的，没有不带条件的。
语法只要将insert all中的all改为first就可以了。这里的first不可以省略。省略那么默认就是all。
它的插入规则是如果前面的when匹配过的，下一个when会自动不考虑上面已经匹配过的行记录。比如将上面16.3.2中的例子改为：
insert first
--将查询结果中为s_id>20的插入,条件中指定的列必须与查询的结果名字一致，如果有别名，用别名
when s_id>20 then
  into emp_1
--s_last_name为M开头的插入,可能插入的行与s_id>20有重复
when s_last_name like 'M%'then
  into emp_2(id,last_name)
--如果指定else，则不满足上面两个条件的插入到emp_3，插入的行不会与上面两个重复
else
  into emp_3(id,last_name) values(s_id,s_last_name)
select id s_id,last_name s_last_name
  from s_emp;

    插入25行，因为when s_last_name like 'M%'不考虑前面已经匹配的行，所以emp_2中就没有那条与插入emp_1中的重复行。

16.3.4 pivoting insert
这节主要讨论使用pivoting insert实现将非关系性表记录转换为关系型表中存储。
比如一个表为sales_source_data,里面的列为
   employee_id number,week_id number,sales_mon number,sales_tue number,sales_wed number,sales_thur number,sales_fri number
   员工id，周id，其他的是工作日的每天的销售记录。
现在要把数据转到sales_info表中，这个表的列为employee_id,week_id,sales。
   
   要实现这个转换可以使用pivoting insert操作。和insert all类似，只不过这里的多表都是同一个表。
   
insert all
  into sales_info values(employee_id,week_id,sales_mon)  --分别按每个工作日插入
  into sales_info values(employee_id,week_id,sales_tue)
  into sales_info values(employee_id,week_id,sales_wed)
  into sales_info values(employee_id,week_id,sales_thur)
  into sales_info values(employee_id,week_id,sales_fri)
select * from sales_source_data;

比如原来的sales_source_data中只有一条记录，那么插入到sales_info中就变成了5条记录。 

 

转自：http://bbs.wmzhe.com/viewthread.php?tid=73158