SQL 저장 프로시저란?
SQL 저장 프로시저(Stored Procedure)는 데이터베이스 내에 저장된 SQL 쿼리와 로직의 집합으로, 특정 작업을 수행하기 위해 작성된 미리 컴파일된 코드 블록이다.
저장 프로시저는 재사용 가능한 코드를 제공하며, 데이터베이스 서버에서 실행되어 성능을 향상시키고 클라이언트-서버 간 통신 비용을 줄이는 데 유용하다. 이를 통해 애플리케이션의 데이터 처리 로직을 데이터베이스 계층으로 옮겨 유지보수성과 확장성을 높일 수 있다.
저장 프로시저는 파라미터를 받아 조건에 따라 동작을 다르게 수행할 수 있으며, 복잡한 비즈니스 로직도 처리할 수 있다. 이를 통해 반복 작업을 줄이고 코드의 일관성을 보장한다. 저장 프로시저는 보통 데이터 삽입, 업데이트, 삭제, 검색 작업에 사용되며, 트랜잭션 처리를 통해 데이터 무결성을 유지한다.
저장 프로시저의 주요 특징
- 성능 최적화
저장 프로시저는 서버 측에서 실행되므로 클라이언트와 서버 간의 데이터 전송을 최소화한다. 또한 미리 컴파일되어 실행 계획이 저장되므로 실행 속도가 빠르다. - 재사용 가능
한 번 작성한 저장 프로시저는 여러 애플리케이션과 사용자 간에 재사용할 수 있어 개발 생산성을 높인다. - 보안 강화
데이터베이스에 직접 접근하는 SQL 문 대신 저장 프로시저를 호출하도록 설정하면, 데이터베이스의 구조를 숨기고 보안을 강화할 수 있다. - 유지보수 용이
데이터베이스 로직이 중앙화되어 있어, 코드 변경 시 저장 프로시저만 수정하면 된다.
저장 프로시저 작성 예제
아래는 직원 데이터를 관리하는 간단한 저장 프로시저 작성 예제이다.
1. 직원 데이터 삽입용 저장 프로시저
DELIMITER //
CREATE PROCEDURE InsertEmployee(
IN empName VARCHAR(50),
IN empPosition VARCHAR(50),
IN empSalary DECIMAL(10,2)
)
BEGIN
INSERT INTO employees (name, position, salary)
VALUES (empName, empPosition, empSalary);
END //
DELIMITER ;
2. 저장 프로시저 호출 예제
CALL InsertEmployee('홍길동', '개발자', 5000000.00);
이 저장 프로시저는 InsertEmployee라는 이름으로 정의되며, 이름, 직책, 급여 데이터를 받아 employees 테이블에 삽입하는 코드다.
저장 프로시저로 데이터 조회하기
1. 직원 목록 조회용 저장 프로시저
DELIMITER //
CREATE PROCEDURE GetAllEmployees()
BEGIN
SELECT * FROM employees;
END //
DELIMITER ;
2. 저장 프로시저 호출 예제
CALL GetAllEmployees();이 저장 프로시저는 employees 테이블의 모든 데이터를 조회한다.
위의 코드처럼 이미 insert된값을 get 할 수 있는것이다.
결과적으로 클라이언트에서 반복적인 쿼리를 작성할 필요 없이 간단히 호출만 하면 된다.
트랜잭션 처리 저장 프로시저 예제
저장 프로시저는 트랜잭션 처리를 통해 데이터 무결성을 유지할 수도 있다.
DELIMITER //
CREATE PROCEDURE TransferSalary(
IN fromEmpId INT,
IN toEmpId INT,
IN amount DECIMAL(10,2)
)
BEGIN
DECLARE insufficient_balance BOOLEAN DEFAULT FALSE;
-- 트랜잭션 시작
START TRANSACTION;
-- 잔액 확인
IF (SELECT salary FROM employees WHERE id = fromEmpId) < amount THEN
SET insufficient_balance = TRUE;
END IF;
-- 잔액 부족 시 롤백
IF insufficient_balance THEN
ROLLBACK;
ELSE
-- 송금자 급여 차감
UPDATE employees
SET salary = salary - amount
WHERE id = fromEmpId;
-- 수금자 급여 추가
UPDATE employees
SET salary = salary + amount
WHERE id = toEmpId;
-- 트랜잭션 커밋
COMMIT;
END IF;
END //
DELIMITER ;
이 저장 프로시저는 송금 처리 관련된 프로시저다.
위 코드는 한 직원의 급여를 다른 직원에게 송금하는 작업을 처리한다.
잔액 부족 시 ROLLBACK을 통해 작업을 취소하고, 성공적으로 처리되면 COMMIT을 통해 트랜잭션을 완료한다.
호출 예제
CALL TransferSalary(1, 2, 100000.00);
총 정리
1. SQL 저장 프로시저는 데이터베이스 작업을 최적화하고 재사용 가능한 로직을 제공하여 개발 생산성을 높인다.
2. 이를 활용하면 데이터베이스 중심의 애플리케이션 설계를 효율적으로 구현할 수 있다.
3. 성능 향상, 보안 강화, 유지보수성 개선 등 다양한 장점을 제공하므로, 대규모 시스템 설계 시 저장 프로시저의 활용을 적극 고려해야 한다.
댓글