데이터베이스의 프로시저

023·2024년 7월 5일

database

목록 보기

7/8

데이터베이스의 프로시저(Procedure)

프로시저

데이터베이스 프로시저(Procedure)는 데이터베이스 내에서 일련의 SQL 문들을 하나의 단위로 묶어 놓은 코드 블록이다. 이는 특정 작업을 수행하기 위해 작성되며, 데이터베이스 서버에 저장되어 필요할 때 호출하여 실행할 수 있다. 프로시저를 사용하면 비즈니스 로직을 데이터베이스 레이어로 옮겨 실행 효율성을 높일 수 있다.

프로시저의 특징

저장 위치: 프로시저는 데이터베이스 서버에 저장되며, 클라이언트가 서버에 요청을 보낼 때 서버에서 직접 실행된다.
호출 방식: 프로시저는 클라이언트 애플리케이션에서 호출할 수 있으며, 파라미터를 전달하여 다양한 작업을 수행할 수 있다.
컴파일 방식: 대부분의 RDBMS는 프로시저를 미리 컴파일하여 저장하므로, 실행 시 컴파일 오버헤드가 적다.
제어 구조 지원: 조건문, 반복문 등의 제어 구조를 포함할 수 있어 복잡한 비즈니스 로직을 구현할 수 있다.
트랜잭션 관리: 프로시저 내에서 트랜잭션을 시작하고 종료할 수 있어 일관성 있고 원자적인 데이터 변경 작업을 수행할 수 있다.

장점

성능 향상:
- 네트워크 트래픽 감소: 클라이언트-서버 간의 통신 횟수를 줄여 네트워크 트래픽을 줄인다. 예를 들어, 여러 개의 SQL 문을 클라이언트에서 서버로 여러 번 보내는 대신, 하나의 프로시저 호출로 처리할 수 있다.
- 컴파일된 상태로 저장: 프로시저는 컴파일된 상태로 데이터베이스에 저장되므로, 실행 시 컴파일 오버헤드가 적고 빠르게 실행된다.
재사용성 및 유지보수성:
- 코드 재사용: 동일한 프로시저를 여러 애플리케이션이나 여러 부분에서 재사용할 수 있어 코드 중복을 줄인다.
- 중앙 집중식 관리: 비즈니스 로직이 데이터베이스 내에 중앙 집중화되어 있어, 수정 시 프로시저만 변경하면 되어 유지보수가 용이하다.
보안 강화:
- 세밀한 권한 관리: 데이터베이스 사용자에게 프로시저 실행 권한만 부여하여, 직접적인 테이블 접근을 제한할 수 있다.
- 데이터 보호: 프로시저 내부에서 데이터 접근 및 조작을 수행하므로, 민감한 데이터의 직접 노출을 막을 수 있다.
비즈니스 로직 캡슐화:
- 일관성 있는 비즈니스 로직: 비즈니스 로직을 프로시저로 캡슐화하여, 모든 데이터베이스 작업에서 일관된 로직이 적용되도록 할 수 있다.
- 애플리케이션 분리: 비즈니스 로직이 데이터베이스에 존재하므로, 클라이언트 애플리케이션과의 의존성을 줄여 개발을 분리할 수 있다.

단점

디버깅 어려움:
- 복잡한 디버깅: SQL 기반 언어로 작성된 프로시저는 디버깅 도구가 제한적이어서, 디버깅이 어렵고 복잡할 수 있다.
- 제한된 오류 메시지: 프로시저 오류 시 제공되는 메시지가 제한적이어서, 문제를 정확히 파악하고 수정하기 어려울 수 있다.
이식성 부족:
- DBMS 종속성: 프로시저 작성 문법은 RDBMS마다 다르므로, 특정 DBMS에 종속적인 코드는 다른 DBMS로 이식하기 어렵다.
- 표준 준수 어려움: SQL 표준을 따르지 않는 DBMS 특정 기능을 사용하면, 이식성이 더욱 떨어진다.
성능 이슈:
- 복잡한 로직의 성능: 매우 복잡한 비즈니스 로직을 프로시저로 작성할 경우, 서버의 부하가 증가하여 성능 문제가 발생할 수 있다.
- 병목 현상: 많은 사용자가 동시에 프로시저를 호출하면, 데이터베이스 서버에 병목 현상이 발생할 수 있다.
관리 복잡성 증가:
- 프로시저 관리: 많은 프로시저를 관리하는 것은 복잡하고 어려울 수 있으며, 프로시저 간의 의존성을 관리하는 것도 추가적인 노력을 필요로 한다.
- 버전 관리 어려움: 소스 코드 관리 시스템에서 프로시저를 효과적으로 버전 관리하는 것이 어려울 수 있다.

RDBMS별 프로시저 작성 방법

1. Oracle

CREATE OR REPLACE PROCEDURE IncreaseSalary (
    emp_id IN NUMBER,
    increment IN NUMBER
) IS
BEGIN
    UPDATE Employees
    SET Salary = Salary + increment
    WHERE EmployeeID = emp_id;
END;
/

2. MySQL

DELIMITER //

CREATE PROCEDURE IncreaseSalary (
    IN emp_id INT,
    IN increment DECIMAL(10,2)
)
BEGIN
    UPDATE Employees
    SET Salary = Salary + increment
    WHERE EmployeeID = emp_id;
END //

DELIMITER ;

3. Microsoft SQL Server (MSSQL)

CREATE PROCEDURE IncreaseSalary
    @emp_id INT,
    @increment DECIMAL(10,2)
AS
BEGIN
    UPDATE Employees
    SET Salary = Salary + @increment
    WHERE EmployeeID = @emp_id;
END;

4. PostgreSQL

CREATE OR REPLACE FUNCTION IncreaseSalary(
    emp_id INTEGER,
    increment NUMERIC
) RETURNS VOID AS $$
BEGIN
    UPDATE Employees
    SET Salary = Salary + increment
    WHERE EmployeeID = emp_id;
END;
$$ LANGUAGE plpgsql;

결론

데이터베이스 프로시저는 비즈니스 로직을 데이터베이스에 캡슐화하여 성능을 향상시키고, 보안을 강화하며, 유지보수를 용이하게 한다. 그러나 이식성 문제와 디버깅의 어려움, 복잡한 관리 문제를 고려하여 적절히 사용해야 한다. 다양한 RDBMS에서 프로시저를 작성하는 방법을 이해하고, 필요에 따라 활용함으로써 데이터베이스 관리 및 운영의 효율성을 높일 수 있다.