procedure 사용하기

kenu
2001-11-06 2:42오전

실행환경

Oracle 8.1.5, sqlplus

procedure 란 절차란 뜻입니다. 일종의 함수라고 봐도 무방합니다. db 에서 일괄(batch)작업하는데 아주 훌륭하게 사용됩니다. 일단 예제로 oracle 용 kboard 의 게시물을 옮기는 것을 해보겠습니다. kboard 의 bbsid 는 게시판구분코드입니다. 누가 자유게시판에다가 질문을 올려놓으면 운영자로써는 짜증이 나죠. 좀 심한 표현을 하자면, 화장실에서 조준을 잘 못해 옆에 떨어진 것을 보는 느낌입니다.(좀 심했지만 사실입니다.) 이런 게시물을 내용을 그대로 복사해서 Q&A 에 옮겨주는 정말 순진한(바보같은) 짓을 하는 것도 한계가 있겠죠. 대신 이 게시물(일련번호'seq'가 501이라고 하죠)의 구분 코드를 자유게시판'bbs1'에서 Q&A게시판'bbs5'으로 바꿔주는 역할을 하는 페이지를 짜서 운영하면 좋을 것입니다. 실행 절차는 다음과 같을 것입니다.

실행순서

1. 옮겨갈 게시판(BBS5)의 그룹번호의 최대값(max(ref))을 구해서

   NEWREF 변수에 입력합니다.
    SELECT NVL(MAX(REF),0)+1 FROM KBOARD WHERE BBSID = 'bbs5';
2. 게시판(kboard) 에서 해당 게시물번호(seq)줄의 bbsid 를 'bbs5' 로, ref 를 newRef 로 고칩니다.
    UPDATE KBOARD SET BBSID = TBBS, REF = NEWREF WHERE SEQ = 501;
3. 내용테이블(kboard_cnts) 에서 해당 게시물 번호(seq) 의 bbsid 를 'bbs5' 로 고칩니다.
    UPDATE KBOARD_CNTS SET BBSID = 'bbs5' WHERE SEQ = 501;

이것을 jsp 코드로 짜려면 보통 3개의 Statement 를 실행해야 합니다. 머리에 쥐납니다. 하지만 어쩌겠습니까. 짜라는 것은 짜야되니까요. 그것도 짧은 시간 안에 말이죠. 이 기능을 oracle에서 지원하는 procedure 함수를 만들어서 사용하면 CallableStatement 한번만 실행하면 됩니다. 일단 oracle 에서 프로시져를 만들어 보겠습니다.

Procedure Name: PROC_KBOARD_MOVE

CREATE OR REPLACE PROCEDURE  PROC_KBOARD_MOVE 

   ( MSEQ IN NUMBER, TBBS IN VARCHAR2)
   IS
   NEWREF NUMBER;
 BEGIN
    SELECT NVL(MAX(REF),0)+1 INTO NEWREF
      FROM KBOARD WHERE BBSID = TBBS;
 
    UPDATE KBOARD SET BBSID = TBBS, REF = NEWREF 
      WHERE SEQ = MSEQ;
    UPDATE KBOARD_CNTS SET BBSID = TBBS 
      WHERE SEQ = MSEQ;
 END;

Procedure 명은 PROC_KBOARD_MOVE 라고 했습니다. 파라메터는 2개가 들어갑니다. 어디로 갈 것인지 떠날 놈은 누구인지, "과거는 묻지마세요"입니다. ^^; 이동할 게시물의 SEQ 는 MSEQ 변수에 이동한 게시판은 TBBS 라는 변수에 각각 넣었습니다. Type 은 보시듯이 각각 NUMBER 와 VARCHAR2 입니다. 그리고 프로시져 내부에서 사용할 변수 하나 선언했습니다. 이동할 게시판의 마지막 그룹번호를 갖는 것이죠. 구한 값을 NEWREF 에 입력합니다. 그리고 두 개의 UPDATE 쿼리를 실행하면 됩니다.

서브쿼리를 사용하면 NEWREF 를 빼도 됩니다. 소스는 아래와 같겠죠.

Procedure Name: PROC_KBOARD_MOVE

CREATE OR REPLACE PROCEDURE  PROC_KBOARD_MOVE 

   ( MSEQ IN NUMBER, TBBS IN VARCHAR2)
   IS
 BEGIN
    UPDATE KBOARD SET BBSID = TBBS, REF = (SELECT NVL(MAX(REF),0)+1 
      FROM KBOARD WHERE BBSID = TBBS)
      WHERE SEQ = MSEQ;
    UPDATE KBOARD_CNTS SET BBSID = TBBS 
      WHERE SEQ = MSEQ;
 END;

이제 이것을 어떻게 실행할까요. JSP 에서 사용하는 것은 따로 한페이지를 만들어서 소개하겠습니다. QUERY 실행은 다음과 같습니다. 다음 강좌가 기대되시는지 모르겠네요. 아뭏든 배울게 너무 많습니다. 머리 쥐납니다. 가끔 머리식히는 곳에서 열 좀 식히세요. 좋은 하루 되세요.

Procedure 실행

CALL PROC_KBOARD_MOVE(501,'bbs5');

참고문서

about JDBC, 손우상, 영진출판사

관련 사이트

http://okjsp.pe.kr