select rpad('*',2*level,'*') || ename ename
from emp
start with mgr is null
connect by prior empno = mgr
/
select deptno, ename, sal,
sum(sal) over (partition by deptno order by sal, ename) cum_sal,
round(100*ratio_to_report(sal) over(partition by deptno),1) pct_dept,
round(100*ratio_to_report(sal) over(), 1) pct_overall
from emp
order by deptno, sal
/
create table dept
( deptno int primary key,
sum_of_salary number
);
create table emp
( empno int primary key,
deptno references dept,
salary number
);
insert into dept (deptno) values (1);
insert into dept (deptno) values (2);
insert into emp (empno, deptno, salary) values (100, 1, 55);
insert into emp (empno, deptno, salary) values (101, 1, 50);
update dept
set sum_of_salary = ( select sum(salary)
from emp
where emp.deptno = dept.deptno )
where dept.deptno = 1;
commit;
select * from emp;
select * from dept;
세션1> insert into emp (empno, deptno, salary) values (102, 2, 60);
세션2> update emp
set deptno = 2
where empno = 100;
세션2> update dept
set sum_of_salary = ( select sum(salary)
from emp
where emp.deptno = dept.deptno)
where dept.deptno in(1, 2);
세션1> update dept
set sum_of_salary = ( select sum(salary)
from emp
where emp.deptno = dept.deptno)
where dept.deptno =2;
=> LOCK 발생
세션2> commit;
=> LOCK 해제
세션1> commit;
=> 모든 트랜잭션 완료