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;
=> 모든 트랜잭션 완료
- 강좌 URL : http://www.gurubee.net/lecture/3515
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.