ANSI-SQL/프로시저/트리거

요구사항

회원 테이블과 게시판 테이블을 만들어 포인트 제도를 적용시킨다. 회원이 글을 작성하면 포인트를 100점 얻고, 글을 삭제하면 포인트를 50점 잃는다. > 회원은 글을 쓰거나 삭제할 수 있고, 포인트를 누적시킬 수 있다.

이를 ANSI-SQL, 프로시저, 트리거로 작성하며 비교해본다.

create table tblUser (
    id varchar2(30) primary key,
    point number default 10000 not null
);

create table tblBoard (
    seq number primary key,
    subject varchar2(1000) not null,
    id varchar2(30) not null references tblUser(id)
);

create sequence seqBoard;

insert into tblUser values ('hong', default);
select * from tblUser;

회원의 점수제도를 가시화 하면, 회원 > 글쓰기 > + 100 / 회원 > 글삭제 > - 50 두가지가 있고, 고려해야할 두가지 사항이 있다.
- A. 글을 쓴다.(삭제한다.)
- B. 포인트를 누적시킨다.

 

 

ANSI-SQL

-- 1.1 글쓰기
insert into tblBoard values (seqBoard.nextVal, '게시판입니다.', 'hong');

-- 1.2 포인트 누적하기
update tblUser set point = point + 100 where id = 'hong';

-- 1.3 글삭제
delete from tblBoard where seq = 1;

-- 1.4 포인트 누적하기
update tblUser set point = point - 50 where id = 'hong';

- 절차를 개발자가 직접 제어해야한다.
- 실수하면 일부의 일부가 누락될 수 있다.

 

ANSI-SQL은 확실히 까다롭고 어렵다.

 

 

프로시저

create or replace procedure procAddBoard (
    psubject varchar2,
    pid varchar2
)
is
begin

    -- 2.1 글쓰기
    insert into tblBoard values (seqBoard.nextVal, psubject, pid);

    -- 2.2 포인트 누적하기
    update tblUser set point = point + 100 where id = pid;
    
    commit;

exception
    when others then
        rollback;    

end;



create or replace procedure procDeleteBoard (
    pseq number
)
is
    vid varchar2(30);
begin

    --삭제글의 작성자
    select id into vid from tblBoard where seq = pseq;
    
    -- 2.3 글삭제
    delete from tblBoard where seq = pseq;
    
    -- 2.4 포인트 누적하기
    update tblUser set point = point - 50 where id = vid;
    
    
    commit;

exception
    when others then
        rollback;    

end;


begin
    --procAddBoard('다시 글을 씁니다.', 'hong');
    procDeleteBoard(2);
end;

 

 

트리거

create or replace trigger trgBoard
    after
    insert or delete
    on tblBoard
    for each row
begin

    if inserting then
        update tblUser set point = point + 100 where id = :new.id;
    elsif deleting then
        update tblUser set point = point - 50 where id = :old.id;
    end if;

end;


insert into tblBoard values (seqBoard.nextVal, '또 다시 글을 씁니다.', 'hong');
delete from tblBoard where seq = 3;