Procedure; 프로시저

프로시저란?

• SQL문 및 변수, 조건문, 반복문 등으로 구성된 미리 작성하는 일련의 데이터베이스 객체이다.
• 특정 작업을 수행할 때, 혹은 복잡한 비즈니스 로직을 처리할 때 쓰인다.

프로시저의 특징

• 여러 동작을 하나로 만들어두는 것이기 때문에 연동된 서비스에서 DB를 여러번 호출할 필요가 없다.
  • 예시로, 회원가입 시 User Table과 UserSec Table에 두 번 Insert를 해야한다고 할 때,
  • 이 로직을 Procedure로 만들어두면 한 번의 호출만으로 해당 작업을 모두 완료할 수 있다.
• 자주 필요로 하는 쿼리문을 여러 명이 지속적으로 재사용할 수 있고, 해당 쿼리문을 성능이 높아지도록 발전시킬 수도 있다.
• 여러 동작을 하나로 합치는 것이다보니 다양한 요소를 다루고 여러 Table 및 로직이 섞이며 복잡해질 수가 있다.
• Procedure가 많아질 수록 관리하기가 어렵다.

실습

1. 프로시저 생성

CREATE [OR REPLACE] PROCEDURE get_employee_info(
    --IN: 입력 받을 매개변수
    p_emp_id IN NUMBER,
    --OUT: 반환할 매개변수
    p_first_name OUT VARCHAR,
    p_last_name OUT VARCHAR,
    p_dept_name OUT VARCHAR
)
IS
BEGIN
    --사원 정보 및 부서 정보를 조회하는 프로시저 내용 작성
    SELECT e.first_name, e.last_name, e.dept_name
    INTO p_first_name, p_last_name, p_dept_name --INTO: SELECT된 값을 반환할 매개변수에 삽입한다
    FROM employee e
    JOIN department d ON e.dept_id = d.id
    WHERE e.id = p_emp_id
EXCEPTION
    --발생한 예외 처리
    WHEN OTHERS THEN
        -- DBMS 출력
        DBMS_OUTPUT.PUT_LINE('An unexpected error occurred');
END;
/ --프로시저 컴파일 요청

• 매개변수에 입력된 IN, OUT은 MODE라고 불리는데, 이 외에도 INOUT이라는 MODE가 존재한다.
  • INOUT은 말 그대로 IN과 OUT의 기능을 모두 수행한다고 보면 된다.
• local 변수는 IS절과 BEGIN절 사이에 입력한다.

2. 프로시저 호출

DECLARE
    --사원 정보를 저장할 변수 선언
    v_first_name OUT VARCHAR(20);
    v_last_name OUT VARCHAR(20);
    v_dept_name OUT VARCHAR(20);
BEGIN
    --사원 번호가 100인 사원의 이름 및 부서를 찾는다
    --익명 블록에서 프로시저 호출 시 EXEC나 EXECUTE 붙이지 X
    get_emp_info(100, v_first_name, v_last_name, v_dept_name);
END;