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')