SQL Stored Procedures
SQL Stored Procedures: 저장된 절차 (프로시저)
SQL Stored Procedures는 미리 작성된 SQL 쿼리 블록을 데이터베이스에 저장해두고, 필요할 때 호출하여 실행할 수 있는 기능입니다. 저장 프로시저는 복잡한 작업을 미리 정의하고, 이를 반복적으로 사용할 수 있어 코드 재사용성과 유지보수성을 높이는 데 유용합니다. 데이터베이스 내부에 저장되기 때문에, 데이터베이스 작업을 자동화하거나 일관되게 처리할 수 있습니다.
1. Stored Procedures의 주요 특징
- 복잡한 쿼리 관리: 여러 SQL 문을 하나의 프로시저로 묶어 복잡한 작업을 자동화합니다.
- 재사용성: 프로시저를 여러 번 호출하여 동일한 작업을 쉽게 수행할 수 있습니다.
- 매개변수 지원: 입력 매개변수를 받아 다양한 조건에 맞는 작업을 수행할 수 있습니다.
- 보안: 민감한 SQL 로직을 외부에 노출하지 않고 실행할 수 있어 보안성이 향상됩니다.
2. Stored Procedure 기본 문법
CREATE PROCEDURE 프로시저_이름 (IN/OUT/INOUT 매개변수1 데이터_타입, 매개변수2 데이터_타입, ...)
BEGIN
-- SQL 문장들
SELECT ...;
INSERT ...;
UPDATE ...;
END;
- CREATE PROCEDURE: 프로시저를 생성하는 키워드.
- 프로시저_이름: 프로시저의 이름.
- IN, OUT, INOUT: 매개변수의 방향을 나타냅니다.
- IN: 호출 시 값을 입력받고, 프로시저 내부에서만 사용.
- OUT: 프로시저가 종료될 때 값을 반환.
- INOUT: 호출 시 값을 입력받고, 종료 시 값을 반환.
- BEGIN ... END: 프로시저 본문을 나타냅니다.
3. Stored Procedure 예제
3.1 간단한 Stored Procedure 생성
다음은 employees 테이블에서 모든 직원 정보를 조회하는 간단한 저장 프로시저입니다.
CREATE PROCEDURE GetAllEmployees()
BEGIN
SELECT * FROM employees;
END;
이 프로시저는 GetAllEmployees라는 이름으로 생성되며, 호출 시 employees 테이블의 모든 데이터를 조회합니다.
프로시저 호출:
CALL GetAllEmployees();
3.2 매개변수가 있는 Stored Procedure
매개변수를 사용하여 특정 부서의 직원을 조회하는 저장 프로시저를 생성할 수 있습니다.
CREATE PROCEDURE GetEmployeesByDepartment(IN dept_name VARCHAR(50))
BEGIN
SELECT * FROM employees
WHERE department = dept_name;
END;
이 프로시저는 부서 이름을 입력받아 해당 부서의 직원만 조회합니다.
프로시저 호출:
CALL GetEmployeesByDepartment('Sales');
3.3 OUT 매개변수를 사용하는 Stored Procedure
다음은 OUT 매개변수를 사용하여 직원의 급여 합계를 반환하는 프로시저입니다.
CREATE PROCEDURE GetTotalSalaryByDepartment(IN dept_name VARCHAR(50), OUT total_salary DECIMAL(10,2))
BEGIN
SELECT SUM(salary)
INTO total_salary
FROM employees
WHERE department = dept_name;
END;
이 프로시저는 dept_name을 입력받아 해당 부서의 급여 합계를 반환합니다.
프로시저 호출:
CALL GetTotalSalaryByDepartment('HR', @total_salary);
SELECT @total_salary;
이 호출은 HR 부서의 급여 합계를 @total_salary 변수에 저장한 후, 이를 조회합니다.
4. INOUT 매개변수를 사용하는 Stored Procedure
INOUT 매개변수는 값을 입력받아 내부에서 변경한 후 반환할 수 있습니다.
예제: 보너스 계산 프로시저
CREATE PROCEDURE CalculateBonus(INOUT base_salary DECIMAL(10,2), IN bonus_rate DECIMAL(5,2))
BEGIN
SET base_salary = base_salary + (base_salary * bonus_rate);
END;
이 프로시저는 base_salary를 입력받아 **보너스 비율(bonus_rate)**에 따라 급여에 보너스를 추가한 후 반환합니다.
프로시저 호출:
SET @salary = 5000;
CALL CalculateBonus(@salary, 0.10);
SELECT @salary;
이 호출은 급여에 10%의 보너스를 추가한 후, 업데이트된 급여를 반환합니다.
5. Stored Procedure에서 조건문 사용
IF, CASE 등의 조건문을 사용하여 프로시저에서 논리적 분기를 처리할 수 있습니다.
5.1 IF 조건문을 사용하는 예제
다음은 부서에 따라 다른 메시지를 반환하는 저장 프로시저입니다.
CREATE PROCEDURE GetDepartmentMessage(IN dept_name VARCHAR(50), OUT message VARCHAR(100))
BEGIN
IF dept_name = 'Sales' THEN
SET message = 'This is the Sales department.';
ELSEIF dept_name = 'HR' THEN
SET message = 'Welcome to the HR department.';
ELSE
SET message = 'Other department.';
END IF;
END;
이 프로시저는 부서 이름에 따라 다른 메시지를 반환합니다.
프로시저 호출:
CALL GetDepartmentMessage('Sales', @message);
SELECT @message;
6. Stored Procedure에서 반복문 사용
LOOP, WHILE, REPEAT를 사용하여 반복 작업을 수행할 수 있습니다.
6.1 WHILE 반복문을 사용하는 예제
다음은 지정된 횟수만큼 반복하여 메시지를 출력하는 저장 프로시저입니다.
CREATE PROCEDURE RepeatMessage(IN num_times INT)
BEGIN
DECLARE counter INT DEFAULT 0;
WHILE counter < num_times DO
SELECT 'This is a repeated message.' AS message;
SET counter = counter + 1;
END WHILE;
END;
이 프로시저는 num_times만큼 메시지를 반복 출력합니다.
프로시저 호출:
CALL RepeatMessage(3);
7. Stored Procedure에서 트랜잭션 관리
저장 프로시저 내에서 트랜잭션을 처리하여 데이터의 일관성을 유지할 수 있습니다. COMMIT과 ROLLBACK을 사용하여 트랜잭션을 제어합니다.
7.1 트랜잭션 처리 예제
CREATE PROCEDURE TransferFunds(IN from_account INT, IN to_account INT, IN amount DECIMAL(10,2))
BEGIN
DECLARE exit handler FOR SQLEXCEPTION
BEGIN
ROLLBACK;
END;
START TRANSACTION;
UPDATE accounts
SET balance = balance - amount
WHERE account_id = from_account;
UPDATE accounts
SET balance = balance + amount
WHERE account_id = to_account;
COMMIT;
END;
이 프로시저는 from_account에서 to_account로 금액을 이체하는 작업을 수행하며, 오류가 발생하면 ROLLBACK으로 트랜잭션을 취소합니다.
프로시저 호출:
CALL TransferFunds(1, 2, 500);
8. Stored Procedure 관리
- 프로시저 삭제: 프로시저를 더 이상 사용하지 않으면 DROP PROCEDURE를 사용하여 삭제할 수 있습니다.
DROP PROCEDURE IF EXISTS GetAllEmployees;
- 프로시저 수정: 저장 프로시저는 직접 수정할 수 없으며, 기존 프로시저를 삭제하고 새로 작성해야 합니다.
9. Stored Procedure의 장점
- 성능 최적화: 미리 컴파일된 상태로 저장되므로 성능이 향상될 수 있습니다.
- 보안 강화: 복잡한 SQL 논리를 저장 프로시저 내부에 숨길 수 있어 외부로부터 SQL 쿼리를 보호합니다.
- 유지 보수성: 여러 SQL 문을 하나로 관리하여 유지 보수가 쉬워집니다.
- 재사용성: 동일한 로직을 여러 곳에서 재사용할 수 있습니다.
10. 요약
- Stored Procedure는 SQL 쿼리 블록을 저장하고 재사용할 수 있게 해주는 기능으로, 복잡한 로직을 처리하는 데 유용합니다.
- 매개변수를 통해 유연하게 동작하며, IN, OUT, INOUT 매개변수를 사용해 데이터를 전달하거나 반환할 수 있습니다.
- 조건문, 반복문, 트랜잭션 등을 사용할 수 있으며, 이를 통해 복잡한 작업을 처리할 수 있습니다.
- 성능 최적화, 보안 강화, 재사용성 등의 장점을
제공하며, 여러 데이터베이스 시스템에서 지원됩니다.