저장 프로시저(Stored Procedure) - Oracle, MySQL 예시

저장 프로시저란?

저장 프로시저는 데이터베이스에서 반복적으로 수행되는 작업을 하나의 단위로 모아 저장해 놓은 일련의 SQL문들이다.

특정 작업을 수행할 때마다 동일한 SQL문을 다시 작성할 필요 없이, 미리 정의된 절차를 호출함으로써 실행할 수 있다.

 

SELECT, INSERT, UPDATE, DELETE 등의 DQL, DML을 포함할 수 있다.

IF문, DECLARE, SET 등의 프로그래밍 문법을 사용할 수 있어 복잡한 로직 구현이 가능하다.

CALL, EXEC 명령어를 통해 함수처럼 호출하여 실행할 수 있다.

 

 

 

---

저장 프로시저의 장단점

장점

 

• 성능 향상
  저장 프로시저는 컴파일되고 캐싱되기 때문에 동일한 작업을 반복적으로 수행할 때 성능이 향상다.
• 보안 강화
  데이터베이스 접근을 저장 프로시저를 통해 제어하면, 프로시저 단위로 실행 권한을 부여할 수 있어 보안 관리가 용이하다.
• 코드 일관성
  복잡한 비즈니스 로직을 저장 프로시저로 작성하면, 데이터 조작의 일관성을 유지할 수 있다.
• 관리 용이
  모든 데이터베이스 로직이 중앙 집중화되어 있어 관리가 용이하다.
• 재사용성
  동일한 로직을 여러 곳에서 사용할 때 효율적이다
• 네트워크 부하 감소
  한번의 호출로 여러 쿼리를 실행할 수 있어 네트워크 트래픽을 줄일 수 있다.

단점

• 디버깅 어려움
  오류 추적이 어려울 수 있어 별도의 에러 테이블 사용이 필요할 수도 있다.
  즉, 일반 응용 프로그램보다 오류 관리가 더 복잡하다
• 이식성 문제
  특정 DBMS에 종속적인 저장 프로시저는 다른 DBMS로 이식이 어려울 수 있다.
• 변경된 데이터 환경에 대한 최적화가 어려움
  데이터 분포, 데이터의 양, 인덱스 변경, 통계정보 변경, 하드웨어 리소스 등의 변화가 일어나도 초기에 캐시된 실행 계획을 계속 사용하여 문제가 발생할 수 있음.
  
  • 최초 실행 : 쿼리 구문을 분석하고 최적화하여 실행 계획을 생성한다.
  • 이후 실행 : 생성된 실행 계획은 캐시되어 다시 사용된다.

 

 

---

Oracle 예시

저장 프로시저 생성

-- 기본적인 저장 프로시저 예시
CREATE OR REPLACE PROCEDURE GetEmployeeByID(emp_id IN NUMBER) IS
BEGIN
    SELECT * FROM employees WHERE employee_id = emp_id;
END;


-- 파라미터가 여러 개인 저장 프로시저 생성 예시
CREATE OR REPLACE PROCEDURE AddEmployee(emp_name IN VARCHAR2, emp_salary IN NUMBER) IS
BEGIN
    INSERT INTO employees (name, salary) VALUES (emp_name, emp_salary);
END;


-- 저장 프로시저에 출력 파라미터 사용 예시
-- 출력 파라미터를 사용하여 저장 프로시저에서 값을 반환할 수 있다.
CREATE OR REPLACE PROCEDURE GetEmployeeSalary(emp_id IN NUMBER, emp_salary OUT NUMBER) IS
BEGIN
    SELECT salary INTO emp_salary FROM employees WHERE employee_id = emp_id;
END;

• CREATE OR REPLACE PROCEDURE: 저장 프로시저를 새로 생성하거나, 이미 존재하는 프로시저를 덮어쓰는 명령어
• emp_id IN NUMBER: 입력 파라미터를 정의하는 부분으로, emp_id는 숫자형임을 명시.
• emp_salary OUT NUMBER: 출력 파라미터를 정의하는 부분으로, emp_salary는 숫자형임을 명시.
• IS: 저장 프로시저의 본문이 시작됨을 나타냄.
• BEGIN ... END: 저장 프로시저의 실행할 SQL 구문을 정의하는 블록.

 

저장 프로시저 실행

BEGIN
    GetEmployeeByID(1);  -- emp_id가 1인 직원 정보 조회
END;

• BEGIN ... END: 저장 프로시저를 실행하려면 BEGIN과 END 사이에 프로시저 호출을 포함해야 함.

 

저장 프로시저 삭제

DROP PROCEDURE GetEmployeeByID;

• DROP PROCEDURE: 지정된 저장 프로시저를 삭제.

 

 

---

MySQL 예시

저장 프로시저 생성

-- 기본적인 저장 프로시저 생성 예시
DELIMITER //

CREATE PROCEDURE GetEmployeeByID(IN emp_id INT)
BEGIN
    SELECT * FROM employees WHERE employee_id = emp_id;
END //

DELIMITER ;



-- 파라미터가 여러 개인 저장 프로시저 생성 예시
DELIMITER //

CREATE PROCEDURE AddEmployee(IN emp_name VARCHAR(50), IN emp_salary DECIMAL(10,2))
BEGIN
    INSERT INTO employees (name, salary) VALUES (emp_name, emp_salary);
END //

DELIMITER ;


-- 저장 프로시저에 출력 파라미터 사용 예시
-- 출력 파라미터를 사용하여 저장 프로시저에서 값을 반환
DELIMITER //

CREATE PROCEDURE GetEmployeeSalary(IN emp_id INT, OUT emp_salary DECIMAL(10,2))
BEGIN
    SELECT salary INTO emp_salary FROM employees WHERE employee_id = emp_id;
END //

DELIMITER ;

 

• DELIMITER //: MySQL에서는 저장 프로시저를 작성할 때 기본 구분자인 ; 대신 다른 구분자(//)를 사용하여 여러 줄의 SQL 문을 구분함.
• CREATE PROCEDURE: 저장 프로시저를 생성하는 구문.
• IN emp_id INT: 입력 파라미터를 정의하는 부분.
• OUT emp_salary DECIMAL(10,2): 출력 파라미터를 정의하는 부분
• BEGIN ... END: 저장 프로시저의 실행할 SQL 구문을 정의하는 블록.

 

 

저장 프로시저 실행

CALL GetEmployeeByID(1);  -- emp_id가 1인 직원 정보 조회

• CALL: CALL 키워드를 사용하여 저장 프로시저를 실행.

 

저장 프로시저 삭제

DROP PROCEDURE GetEmployeeByID;

• DROP PROCEDURE: 지정된 저장 프로시저를 삭제.