oralce学习笔记之高级查询篇-数据库

高级查询

以下所举例子代码可在oracle9i的scott模式下运行

1、层次化查询：
  create or replace procedure company_listing
  --递归过程实现层次化查询
  (
    p_start_with varchar2,
    p_level number default 0
  ) as
  begin
    dbms_output.put_line(lpad(' ',p_level*2,' ')||p_start_with);
    for c in (select * from emp where mgr in
      (select empno from emp where ename=p_start_with)
      order by ename)
    loop
      company_listing(c.ename,p_level+1);
    end loop;
  end company_listing;
  --确保sql*plus不会剥离我们已经在存储过程中输出的起始空格
  set serveroutput on format wrapped
  --执行
  exec company_listing('KING');
  --以上语句可以用下面语句实现
  select lpad(' ',(level-1)*2,' ')||ename ename
  from emp
  start with ename='KING'
  connect by mgr = prior empno
  输出结果
------------------------------------------
KING
  JONES
    SCOTT
      ADAMS
    FORD
      SMITH
  BLAKE
    ALLEN
    WARD
    MARTIN
    TURNER
    JAMES
  CLARK
    MILLER

  a)start with语句：可以是where子句中的任何有效内同
  b)connect by语句：说明怎么对行进行彼此关联
  c)prior：该关键字区分当前行和备选行
  d)level：同rownum一样，是存在于oracle查询中的伪列，并可放在where条件中使用
    >select ename,level from emp;
    >select lpad(' ',(level-1)*2,' ')||ename ename,level
    from emp
    start with mgr is null --and level<=3
    connect by mgr = prior empno
  e)order siblings by语句：用在层次化查询，对结果集按层排序，order by 则会对整个结果集按行排序
  f)sys_connect_by_path(<column>,<separator character>)函数：输出层次路径

2、使用查询编写生成sql的sql：

3、分析函数：
  function_name(parameter,...) over(<partition-clause><order-by-clause><windowing-clause>)
  a)计算当前总和
    select ename,sal, sum(sal) over
    (order by ename range between unbounded preceding and current row) running_total
    from emp order by ename
    range between unbounded preceding and current row是默认窗口子句，可省
    --根据部门分组计算：
    break on dname skip 1;--在各个dname结果集之间插入空行
    select dname,ename,sal, sum(sal) over
    (partition by dname order by dname,ename range between unbounded preceding and current row) 
      dept_running_total
    from emp,dept
    where emp.deptno=dept.deptno
    order by ename
  b)下一个和上一个值：lead(),lag()
    select deptno,ename,sal,
      lead(ename,1,'N/A') over(partition by deptno order by sal) next_ename,
      lead(sal,1,NULL) over(partition by deptno order by sal) next_sal
    from emp
  c)等级和前n个
    select
      rank() over(order by hiredate) "rank",
      dense_rank() over(order by hiredate) "dense_rank",
      ename,hiredate
    from emp order by hiredate
  d)第一个和最后一个：first_value() last_value()
    select ename,deptno,sal,sal-first_value(sal) over(partition by deptno order by sal) diff
    from emp
    order by deptno,sal

4、merge：
  merge into table_name b
  using (select * from table_name) a
  on(a.id=b.id)
  when matched then update set status='OLD'
  when not matched then insert values(a.id,'NEW')