SQL - 저장 프로시저(Stored Procedure, SP)란 무엇인가? 프로시저의 활용 방법

저장 프로시저(Stored Procedure)는 데이터베이스에 저장된 SQL 코드의 집합으로, 재사용 가능한 쿼리와 로직을 효율적으로 실행할 수 있도록 설계된 기능이다.

저장 프로시저는 데이터베이스 성능을 최적화하고, 복잡한 비즈니스 로직을 관리하는데 유용하다.

 

 

이번 글에서는 저장 프로시저의 개념과 장점, 생성 방법, 그리고 실제로 활용하는 방법에 대해서 알아보자!

 

---

1. 저장 프로시저(Stored Procedure)란? 

 

저장 프로시저는 이름이 지정된 SQL 코드의 블록으로, 데이터베이스에 저장되어 필요할 때 호출된다.

- 저장 프로시저는 입력 파라미터를 받아서 실행할 수 있다.

- 복잡한 작업을 캡슐화하여 코드 중복을 줄이고 유지보수를 간편하게 만든다.

 

이렇게만 보면 function과 비슷한 것 같지만, SP와 function은 기능과 사용 목적에서 몇 가지 차이점이 있다.

 

SP / Function 의 차이

구분	Stored Procedure(SP)	Function
반환 값	반환 값이 없거나 여러 개일 수 있다. OUT 또는 INOUT 파라미터를 통해 값을 반환할 수 있다.	반드시 하나의 값을 반환해야 하며, RETURN 문을 통해 값을 반환한다.
사용 위치	CALL 또는 EXECUTE 문을 통해 호출되며, SQL 쿼리 내에서 직접 사용할 수 없습니다.	SQL 쿼리 내에서 직접 호출할 수 있으며, SELECT 문 등과 함께 사용될 수 있다.
목적과 기능	여러 SQL 문을 실행하거나 복잡한 로직을 처리하는 데 사용된다. 데이터 조작, 트랜잭션 처리 등 다양한 작업을 수행할 수 있다.	특정 값을 계산하거나 변환하여 결과를 반환하는 데 중점을 둔다. 데이터 변환, 계산 등의 용도로 사용되며, 다른 SQL 쿼리 내에서 쉽게 호출하여 사용할 수 있다.
파라미터 사용	IN, OUT, INOUT 파라미터를 모두 지원하여 입력 값을 받고, 결과 값을 반환하거나, 입력 값을 변경할 수 있다.	IN 파라미터만 지원하며, 값을 입력받아 계산 후 결과를 반환하지만, 입력 값을 변경할 수는 없습니다.

 

---

2. 저장 프로시저의 장점

 

2-1. 유지보수 및 재사용성

- 여러 애플리케이션에서 동일한 프로시저를 재사용할 수 있다. 

- 비즈니스 로직이 변경되었을 때 애플리케이션 코드를 수정하지 않고, 저장프로시저만 업데이트하면 되므로 유지보수가 용이하다.

 

2-2. 성능 최적화

- 저장 프로시저는 데이터베이스 서버에서 컴파일되고 실행되어 네트워크 트래픽을 줄인다. 특히 복잡한 쿼리나 다수의 SQL 문을 실행할 때 저장 프로시저를 활용하면 네트워크 부하를 줄일 수 있다.

 

2-3. 보안 강화

- 프로시저를 통해 민감한 데이터 접근 권한을 세밀하게 제어할 수 있다. 즉, 직접 테이블 접근 권한을 부여하는 대신, 저장 프로시저를 통해 데이터에 접근하도록 하여 데이터 무결성을 유지하고, 민감한 데이터에 대한 접근을 제어한다.

 

2-4. 비즈니스 로직 캡슐화

- 데이터베이스 내부에서 비즈니스 로직을 실행하므로 애플리케이션 코드가 단순해진다. 이는 애플리케이션 계층과 데이터 계층 간의 역할 분담이 명확해져 개발 업무를 구분하여 효율적으로 진행할 수 있다.

 

---

3. 저장 프로시저의 기본 구조

 

DBMS에 따라 저장 프로시저의 문법과 구조가 약간씩 다를 수 있다.

 

 

3-1. MSSQL(SQL Server)

# 기본 구조 ===============================================
CREATE PROCEDURE 프로시저명
    @매개변수1 데이터형식,
    @매개변수2 데이터형식 = 기본값
AS
BEGIN
    -- SQL 문장들
    SELECT 컬럼명 FROM 테이블명 WHERE 조건;
    -- 추가적인 SQL 로직
END;

# 예시 ====================================================
CREATE PROCEDURE GetEmployeeByID
    @EmployeeID INT
AS
BEGIN
    SELECT FirstName, LastName, Position
    FROM Employees
    WHERE EmployeeID = @EmployeeID;
END;

 

 

3-2. MySQL

# 기본 구조 ==============================================
DELIMITER //

CREATE PROCEDURE 프로시저명 (
    IN 매개변수1 데이터형식,
    OUT 매개변수2 데이터형식
)
BEGIN
    -- SQL 문장들
    SELECT 컬럼명 INTO 매개변수2 FROM 테이블명 WHERE 조건;
    -- 추가적인 SQL 로직
END //

DELIMITER ;

# 예시 ====================================================
DELIMITER //

CREATE PROCEDURE GetEmployeeName(
    IN emp_id INT,
    OUT emp_name VARCHAR(100)
)
BEGIN
    SELECT CONCAT(FirstName, ' ', LastName) INTO emp_name
    FROM Employees
    WHERE EmployeeID = emp_id;
END //

DELIMITER ;

 

 

3-3. Oracle

# 기본 구조 ===============================================
CREATE OR REPLACE PROCEDURE 프로시저명 (
    매개변수1 IN 데이터형식,
    매개변수2 OUT 데이터형식
) IS
BEGIN
    -- SQL 문장들
    SELECT 컬럼명 INTO 매개변수2 FROM 테이블명 WHERE 조건;
    -- 추가적인 SQL 로직
END 프로시저명;

# 예시 ====================================================
CREATE OR REPLACE PROCEDURE GetEmployeeSalary(
    emp_id IN NUMBER,
    emp_salary OUT NUMBER
) IS
BEGIN
    SELECT Salary INTO emp_salary
    FROM Employees
    WHERE EmployeeID = emp_id;
END GetEmployeeSalary;

 

주요 구성 요소

- 프로시저명 : 저장 프로시저의 이름으로, 프로시저를 호출할 때 사용된다.

- 매개변수 : 프로시저에 전달되는 입력(IN), 출력(OUT), 입출력(INOUT) 값 들로, 데이터 형식과 함께 정의된다.

- SQL : 프로시저가 수행할 작업을 정의하는 SQL 구문

- BEGIN ... END : 프로시저의 시작과 끝을 나타내며, 그 안에 SQL 문장들이 위치한다.

---

4. 저장 프로시저의 활용

 

4-1. 데이터 검색

- 특정 부서의 직원 목록 조회

DELIMITER $$

CREATE PROCEDURE GetEmployeesByDepartment (IN dept_id INT)
BEGIN
    SELECT employee_id, name, salary
    FROM employees
    WHERE department_id = dept_id;
END$$

DELIMITER ;


# 호출
CALL GetEmployeesByDepartment(10);

설명 : 해당 부서 키에 속한 직원들의 목록을 조회

 

 

4-2. 데이터 삽입

- 신규 주문 추가

DELIMITER $$

CREATE PROCEDURE AddOrder (IN customer_id INT, IN order_date DATE, IN total_amount DECIMAL(10, 2))
BEGIN
    INSERT INTO orders (customer_id, order_date, total_amount)
    VALUES (customer_id, order_date, total_amount);
END$$

DELIMITER ;

# 호출
CALL AddOrder(101, '2025-01-12', 150.00);

설명 : 고객이 주문 시 고객 아이디, 날짜, 총금액을 주문 테이블에 INSERT 해준다.

 

 

4-3. 데이터 업데이트

- 급여 인상

DELIMITER $$

CREATE PROCEDURE UpdateSalary (IN emp_id INT, IN salary_increment DECIMAL(10, 2))
BEGIN
    UPDATE employees
    SET salary = salary + salary_increment
    WHERE employee_id = emp_id;
END$$

DELIMITER ;

# 호출
CALL UpdateSalary(201, 500.00);

설명 : 직원아이디, 인상 금액을 통해 해당 직원의 급여에서 인상된 금액을 더해서 UPDATE 해준다.

 

4-4. 조건 처리

- 재고 확인 후 주문 처리

DELIMITER $$

CREATE PROCEDURE ProcessOrder (IN in_product_id INT, IN quantity INT, OUT result VARCHAR(50))
BEGIN
    DECLARE stock INT;

    SELECT stock_level INTO stock
    FROM inventory
    WHERE product_id = in_product_id;

    IF stock >= quantity THEN
        UPDATE inventory
        SET stock_level = stock_level - quantity
        WHERE product_id = in_product_id;

        SET result = 'Order processed';
    ELSE
        SET result = 'Insufficient stock';
    END IF;
END$$

DELIMITER ;

# 호출
CALL ProcessOrder(101, 5, @result);
SELECT @result;

설명 : Inventory 테이블에서 해당 상품 아이디에 대한 현재 재고를 조회하고, 조회된 재고 수량을 stock 변수에 저장한다.

만약 현재 재고(stock)가 주문 수량(quantity) 이상이라면 재고에서 주문 수량만큼 차감하고, 성공 메시지(Order processed)를 출력한다.

하지만 재고가 부족하다면 재고가 부족하다는 메시지(Insufficient stock)를 출력한다.

 

기대 : 주문 시 재고 수량을 자동으로 업데이트하여 수작업 없이 재고를 실시간으로 관리할 수 있으며, 재고가 부족한 경우 즉시 알림으로 불필요한 주문 처리 과정을 방지한다.

 

 

---

5. 저장 프로시저 관리 (각 DBMS별 프로시저 관리)

 

5-1. 프로시저 목록 조회

5-1-1. MSSQL

# INFORMATION_SCHEMA.ROUTINES 뷰를 활용하여 조회(알파벳 순으로 조회)
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
ORDER BY ROUTINE_NAME;

# sys.procedures를 활용하여 조회
SELECT name
FROM sys.procedures
ORDER BY name;

 

 

5-1-2. MySQL

# INFORMATION_SCHEMA.ROUTINES 뷰를 사용하여 저장 프로시저 목록을 조회
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
  AND ROUTINE_SCHEMA = 'your_database_name'
ORDER BY ROUTINE_NAME;

 

 

5-1-3. Oracle

# ALL_PROCEDURES 뷰를 사용하여 저장 프로시저 목록을 조회
SELECT OBJECT_NAME
FROM ALL_PROCEDURES
WHERE OBJECT_TYPE = 'PROCEDURE'
  AND OWNER = 'YOUR_SCHEMA_NAME'
ORDER BY OBJECT_NAME;

 

 

 

5-2. 프로시저 삭제

5-2-1. MSSQL

# 기본 문법
DROP PROCEDURE [스키마명].[프로시저명];

# 예제
DROP PROCEDURE dbo.MyProcedure;

 

5-2-2. MySQL

# 기본 문법
DROP PROCEDURE [IF EXISTS] 프로시저명;

# 예시
DROP PROCEDURE IF EXISTS MyProcedure;

 

5-2-3. Oracle

# 기본 문법
DROP PROCEDURE 프로시저명;

# 예시
DROP PROCEDURE MyProcedure;

 

---

6. 저장 프로시저 사용 시 주의사항

 

6-1. 디버깅의 어려움

- 저장 프로시저는 디버깅이 까다로울 수 있으므로, 로그를 활용하여 문제를 추적해야 한다.

 

6-2. 복잡성 관리

- 지나치게 복잡한 로직은 성능 문제를 초래할 수 있다. 간결하고 모듈화 된 코드를 작성해야 한다.

- 특히, 매개변수 스니핑(Parameter Sniffing) 문제로 인해 특정 매개변수 값에 최적화된 실행 계획이 고정되어 다른 값에 대해 비효율적인 실행이 이루어질 수 있다.

* Parameter Sniffing : 프로시저의 통계 정보를 이용하여 실행 계획을 만드는데, 이러한 매개 변수의 값을 이용함을 말한다.

 

6-3. 데이터베이스별 호환성

- 저장 프로시저의 문법과 기능은 데이터베이스마다 다를 수 있다. 즉, 다른 DBMS로의 이식이 어렵다.

- 여러 DBMS를 지원해야 하는 경우에는 표준 SQL을 사용하는 것이 바람직하다.

 

6-4. 과도한 의존성 방지

- 너무 많은 비즈니스 로직을 저장 프로시저에 포함하면 데이터베이스 의존성이 증가할 수 있다.

 

 

---

정리

 

SQL 저장 프로시저는 데이터베이스 성능 최적화, 작업 자동화, 그리고 비즈니스 로직 캡슐화에 유용한 도구이다.

저장 프로시저를 잘 활용하면 데이터베이스 중심의 (성능이 좋은) 애플리케이션을 구축할 수 있다.

 

끝!