15일차 # 간단한 예제

        drop table t1 purge;

create table t1 as select * from emp;

set serveroutput on

     ------------------------     ------------------------

declare

 v_ret      number;

 v_avg_sal  number;

 v_sum_sal1 number;

 v_sum_sal2 number;

begin

 select avg(sal), sum(sal) into v_avg_sal, v_sum_sal1

 from t1;

 dbms_output.put_line(v_sum_sal1);

 for emp_rec in (select empno from t1 where sal > v_avg_sal) loop

   update t1

   set sal = sal + 50

   where empno = emp_rec.empno;

 end loop;

 for emp_rec in (select empno from t1 where sal <= v_avg_sal) loop

   update t1

   set sal = sal + 100

   where empno = emp_rec.empno;

 end loop;

 select sum(sal) into v_sum_sal2

 from t1;

 dbms_output.put_line(v_sum_sal2);

 dbms_output.put_line(v_sum_sal2 - v_sum_sal1);

 select 1000/0 into v_ret from dual;

exception

 when others then

   null;

end;

/

     ------------------------     ------------------------

begin

 update t1

 set sal = sal+1000

 where rownum = 1;

 commit;

 -- other tx

 delete from t1

 where sal <= 2000;

 -- other tx

 

 commit;

end;

/

  

↓↓

begin

 begin

   update t1

   set sal = sal+1000

   where rownum = 1;

   -- other tx

 end;

 commit;

 begin

   delete from t1

   where sal <= 2000;

   -- other tx

 end;

  

 commit;

end;

/

  

↓↓

create or replace procedure p1

is

begin

 update t1

 set sal = sal+1000

 where rownum = 1;

 -- other tx

end;

/

create or replace procedure p2

is

begin

 delete from t1

 where sal <= 2000;

 -- other tx

end;

/

begin

 p1;

 commit;

 p2;

 commit;

end;

/