저장 프로시저(SP:Stored Procedure)

최근 프로젝트를 진행하면서 우리 팀에서는 프로시저를 사용하지 않지만 다른 팀 중, 쓰는 팀이 있어서 관심을 가지고 찾아보게 되었다. 저장 프로시저는 정보처리기사를 공부하거나 데이터베이스를 공부할 때 이론상으로만 접해보았고, 쿼리문을 써도 되는데 굳이 왜 프로시저를 사용하는지 궁금하여 찾아보게 되었다.

 

 

 

▶ Stored Procedure(저장 프로시저)

DB내부에 저장된 일련의 SQL명령문을들 하나의 함수처럼 실행하기 위한 쿼리들의 집합을 의미.

 

 

<장점>

- 반복적인 작업을 피할 수 있고, 개발언어에 비의존적이므로 개발언어가 바뀌어도 동작하는 데 있어 문제가 없다.

- 쿼리문이 보호가된다.

  =>DB 툴을 사용하여 저장프로시저 항목을 볼 수 있고 프로시저 수정 전까지 프로시저 내부 쿼리를 확인할수 없다.

- 성능 면에서 좋다.

 

 

<프로시저 생성 (기본)>

 

CREATE OR REPLACE PROCEDURE 프로시저이름
IS 
    --프로시저 내에서 사용할 변수
    변수이름 데이터타입;
    변수이름 데이터타입;

 BEGIN
     기능 구현,처리 작성;
 END;

 

 

<프로시저 처리 (조건문)>

   -- <조건이 1개일 경우>
    IF 조건 THEN
       조건 처리;
    END IF;
     
   -- <조건이 2개일 경우>
    IF 조건 THEN
       조건 처리 1;
    ELSE
      조건 처리2;
    END IF;
     
  --  <조건이 n개일 경우>
    IF 조건1 THEN
       조건 처리1;
    ELSIF 조건2 THEN
      조건 처리2;
      ...
    ELSE
       조건 처리n;
    END IF;

 

-예제

    DECLARE
      vn_salary NUMBER := 0;
      vn_department_id NUMBER := 0;
    BEGIN
      vn_department_id := ROUND(DBMS_RANDOM.VALUE (10, 120), -1);

       SELECT salary
         INTO vn_salary
         FROM employees
        WHERE department_id = vn_department_id
          AND ROWNUM = 1;

      DBMS_OUTPUT.PUT_LINE(vn_salary);

      IF vn_salary BETWEEN 1 AND 2000 THEN
         DBMS_OUTPUT.PUT_LINE('낮음');
      ELSIF vn_salary BETWEEN 2001 AND 4000 THEN
         DBMS_OUTPUT.PUT_LINE('중간');
      ELSIF vn_salary BETWEEN 4001 AND 8000 THEN
         DBMS_OUTPUT.PUT_LINE('높음');
      ELSE
         DBMS_OUTPUT.PUT_LINE('최상위');
      END IF;
    END;

→ 부서번호가 10~120(10의 자리 반올림(-1))사이에 랜덤하여 나온 사람의 급여가 1~2000이면 낮음, 2001~4000이면 중간, 4001~8000이면 높음, 그 이상이면 최상위라고 출력을 하는 조건문.

 

 

 

<프로시저 실행>

 

1. EXEC 프로시저 이름 ; -> 인수없는 경우 호출

2. EXEC 프로시저 이름(값, 값,....); -> 인수있는 경우 호출