Neo-Oracle Home

1. 테스트 테이블 생성
orcl@EXPERT> create table orders (order_id number primary key, line_items_count number default 0);

orcl@EXPERT> create table lineitems (order_id references orders, seq_no number, constraint lineitem_pk primary key (order_id,seq_no));

2. 트리거 생성
orcl@EXPERT> create or replace trigger orders_itemcnt_trg
after update or insert or delete on lineitems
for each row
begin
  if updating or inserting then
      update orders set line_items_count=nvl(line_items_count,0)+1
      where order_id=:new.order_id;
  end if;        --여기서 아예 end if하면 아래 if가 안돌아가기에 end if 후 다른 if가 됨
  if deleting or updating then
      update orders set line_items_count=nvl(line_items_count,0)-1
      where order_id=:old.order_id;
  end if;
end;
/

3. Insert 테스트
orcl@EXPERT> insert into orders(order_id) values(10);
orcl@EXPERT> insert into orders(order_id) values(20);

orcl@EXPERT> insert into lineitems values(10,1);

orcl@EXPERT> select * from orders;      --commit을 하지 않아도 트리거에 의해 update되었음

  ORDER_ID LINE_ITEMS_COUNT
---------- ----------------
        10                1
        20                0

orcl@EXPERT> insert into lineitems values (10,2);
--이때 트리거에 의해 update orders set line_item_count=nvl(1,0)+1 where order_id=10; 발생

orcl@EXPERT> select * from orders;

  ORDER_ID LINE_ITEMS_COUNT
---------- ----------------
        10                2
        20                0

4. update 테스트
orcl@EXPERT> update lineitems set order_id=20;
--이때 트리거에 의해 아래 과정을 거친다.
update order set line_item_count=nvl(2,0)-1 where order_id=10;
update order set line_item_count=nvl(0,0)+1 where order_id=20;
update order set line_item_count=nvl(1,0)-1 where order_id=10;
update order set line_item_count=nvl(1,0)+1 where order_id=20;

orcl@EXPERT> select * from orders;

  ORDER_ID LINE_ITEMS_COUNT
---------- ----------------
        10                0
        20                2

5. delete 테스트
orcl@EXPERT> delete from lineitems;
--이때 트리거에 의해 아래 과정을 거친다.
update order set line_item_count=nvl(2,0)-1 where order_id=20;
update order set line_item_count=nvl(1,0)-1 where order_id=20;

orcl@EXPERT> select * from orders;

  ORDER_ID LINE_ITEMS_COUNT
---------- ----------------
        10                0
        20                0

1. 기본문. 예외처리가 없음(20)
declare
        v_department_id em.department_id%type := &deptno;
        dummy number;
begin
        select 1
        into dummy
        from em
        where department_id = v_department_id;
        dbms_output.put_line('ok');
end;
/

2. 사전정의된 예외처리방법(20)
declare
        v_department_id em.department_id%type := &deptno;
        dummy number;
begin
        select 1
        into dummy
        from em
        where department_id = v_department_id;
        dbms_output.put_line('ok');
exception
        when too_many_rows then
          dbms_output.put_line('too many rows return');
        when others then
          dbms_output.put_line(sqlerrm);
end;
/

3. 사전정의안된 오류 예외처리 방법(20). too_many_rows 가 없다고 가정
declare
        v_department_id em.department_id%type := &deptno; --deptno를 받음
        dummy number;   --의미없지만 select가 돌아가게 하기 위해
       
        toomany exception;                     --1. exception 명 선언
        pragma exception_init(toomany, -1422);  --2.
begin
        select 1
        into dummy  --의미없지만 select가 돌아가게 하기 위해
        from em
        where department_id = v_department_id;
        dbms_output.put_line('ok');
exception
        when toomany then
          dbms_output.put_line('too many rows return');
        when others then
          dbms_output.put_line(sqlerrm);
end;
/

4. 유저 정의 예외처리(300) 에러메시지 발생, 중지
declare
        v_department_id em.department_id%type := &deptno;
        dummy number;
begin
        if v_department_id >= 300 then
          raise_application_error(-20000, '300이상의 값을 입력하지 마시옵소서');
        else
          select 1
          into dummy
          from em
          where department_id = v_department_id;
            dbms_output.put_line('ok');
        end if;
exception
        when others then
          dbms_output.put_line(sqlerrm);
end;
/

5. 유저 정의 예외처리(300) 실행 후 에러 메시지 정상 핸들링
declare
        v_department_id em.department_id%type := &deptno;
        dummy number;
        over_dept_err exception;
        pragma exception_init(over_dept_err, -20000);
begin
        if v_department_id >= 300 then
          raise_application_error(-20000, '300이상의 값을 입력하지 마시옵소서');
        else
          select 1
          into dummy
          from em
          where department_id = v_department_id;
            dbms_output.put_line('ok');
        end if;
exception
        when over_dept_err then
          dbms_output.put_line(sqlerrm);
        when others then
          dbms_output.put_line(sqlerrm);
end;
/

이거 말고 더 예제는 있습니다.