Merge 2009 02 15 주말 강의 (Oracle)

insert + update

합치기  없으면

merge into d1
using d2
on (d2.deptno=d1.deptno)
when matched then
update set d1.dname=d2.dname
when not matched then
insert values(d2.deptno, d2.dname)




flash bag 사용


Select loc from dept versions between timestamp
     minvalue  and maxvalue
where deptno=40
order by versions_endtime


// 플래쉬백  표준 질의
select loc,versions_starttime, versions_endtime from
dept versions between timestamp
minvalue and maxvalue
where deptno =40
order by versions_endtime



rollup = 부분합
cube = 차원질의


select ename , deptno , sum(sal) from emp
group by rollup (deptno , ename);// rollup

select deptno,job , count(*) from emp group by cuve(deptno , job);//cube


select empno , e.job,e.comm,r.countx, r.sumx from
emp right outer join
(select empno, deptno,sum(sal) sumx  , count(*) countx from emp group by rollup(deptno,ename) ) r

using (empno);