[Sql] Chapter 07. 스토어드 프로시저

7장 : 스토어드 프로시저

---

07-1 스토어드 포로시저 사용 방법

1. 스토어드 프로시저 기본

스토어드 프로시저는 데이터베이스에 저장되어 실행 가능한 미리 작성된 SQL 문의 집합입니다. 이를 사용하면 복잡한 데이터베이스 작업을 단일 호출로 실행할 수 있습니다. 스토어드 프로시저는 일련의 SQL 문을 하나의 프로시저로 묶어 관리하며, 데이터의 무결성과 보안을 유지할 수 있습니다!

 

스토어드 프로시저의 기본 구조

DELIMITER //

CREATE PROCEDURE procedure_name(parameter1 datatype, parameter2 datatype)
BEGIN
    -- 프로시저 내부에 실행할 SQL 문 작성
END //

DELIMITER ;

DELIMITER는 기본 SQL 문 구분 기호인 세미콜론(;)과의 충돌을 피하기 위해 사용됩니다.

 

2. 스토어드 프로시저 실습

가정

MySQL 데이터베이스에 "employees"라는 테이블이 있다고 가정합니다. 이 테이블에는 직원의 정보가 저장되어 있습니다.

목표

스토어드 프로시저를 생성하고, "employees" 테이블에서 직원의 정보를 조회하는 기능을 구현해보겠습니다.

실습 단계

1. MySQL에 접속합니다.
2. 새로운 스토어드 프로시저를 생성합니다.

DELIMITER //

CREATE PROCEDURE GetEmployeeInfo(employee_id INT)
BEGIN
    SELECT * FROM employees WHERE id = employee_id;
END //

DELIMITER ;

생성한 스토어드 프로시저를 호출하여 직원 정보를 조회합니다.

CALL GetEmployeeInfo(1);

위의 실습을 통해 간단한 스토어드 프로시저를 만들고 호출하는 과정을 체험할 수 있습니다. 실제로는 더 복잡한 프로시저를 작성하고 다양한 매개변수와 로직을 추가하여 데이터베이스 작업을 처리할 수 있습니다.

 

---

07-2 스토어드 함수와 커서

1. 스토어드 함수

스토어드 함수는 MySQL 데이터베이스에서 결과를 반환하는 유용한 도구입니다. 함수를 사용하여 복잡한 데이터 처리를 수행하고 결과를 손쉽게 얻을 수 있습니다.

 

스토어드 함수 작성 방법

DELIMITER //
CREATE FUNCTION CalculateTax(price DECIMAL(10, 2), tax_rate DECIMAL(4, 2))
RETURNS DECIMAL(10, 2)
BEGIN
    DECLARE tax_amount DECIMAL(10, 2);
    SET tax_amount = price * (tax_rate / 100);
    RETURN tax_amount;
END;
//
DELIMITER ;

-- 스토어드 함수 호출
SELECT CalculateTax(100, 10); -- 10% 세금으로 100에 대한 세금 계산

 

2. 커서로 한 행씩 처리하기

커서는 데이터베이스 결과 집합을 순회하고 처리할 때 사용되는 기능입니다. 보통 반복문과 함께 사용하여 각 행을 차례대로 가져와 작업할 수 있습니다!
아래는 커서를 사용하여 데이터베이스 결과를 한 행씩 처리하는 예제입니다.

DELIMITER //
CREATE PROCEDURE ProcessOrders()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE order_id INT;
    DECLARE order_total DECIMAL(10, 2);
    
    -- 커서 선언
    DECLARE cur CURSOR FOR SELECT id, total FROM orders;
    
    -- NOT FOUND 예외 처리를 위한 핸들러 선언
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO order_id, order_total;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 각 주문에 대한 처리 로직
        -- 예: 주문 ID와 총액 출력
        SELECT CONCAT('Order ID: ', order_id, ', Total: $', order_total);
    END LOOP;
    
    CLOSE cur;
    
END;
//
DELIMITER ;

-- 프로시저 호출
CALL ProcessOrders();

위의 코드는 주문 테이블에서 주문 ID와 총액을 가져와 출력하는 예제입니다.

 

---

07-3 자동 실행되는 트리거

1. 트리거 기본

트리거는 주로 INSERT, UPDATE, DELETE 등의 데이터 조작 이벤트가 발생했을 때 실행되도록 설정됩니다. 각각의 이벤트에 대한 트리거를 정의하고 해당 이벤트가 발생하면 트리거 내에 정의한 작업이 수행됩니다.

 

2. 트리거 활용

예를 들어, 주문 테이블에 새로운 주문이 추가되었을 때 재고 수량을 조정하는 경우를 생각해보겠습니다. 이런 경우 트리거를 사용하여 주문이 추가될 때마다 재고 테이블의 수량을 감소시키는 작업을 자동으로 수행할 수 있습니다.

DELIMITER //
CREATE TRIGGER UpdateInventory AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    UPDATE inventory
    SET quantity = quantity - NEW.quantity_ordered
    WHERE product_id = NEW.product_id;
END;
//
DELIMITER ;

 

3. 트리거가 사용하는 임시 테이블

트리거 내에서는 NEW와 OLD라는 특별한 레코드들을 사용하여 새로운 데이터와 이전 데이터에 접근할 수 있습니다. 이를 통해 이전 상태와 새로운 상태의 데이터를 비교하고 작업을 수행할 수 있습니다.

또한, 트리거 내에서 임시 테이블을 사용할 수도 있습니다. 이 임시 테이블들은 트리거의 실행 도중에만 존재하며, 특정 작업을 수행하기 위해 임시적으로 사용됩니다. 예를 들어, 데이터의 변경을 추적하거나 비즈니스 룰에 따라 조건을 확인하는 데 사용될 수 있습니다.

CREATE TRIGGER AuditChanges AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    DECLARE audit_id INT;
    -- 임시 테이블 생성
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_audit (
        id INT AUTO_INCREMENT PRIMARY KEY,
        employee_id INT,
        changed_column VARCHAR(50),
        old_value VARCHAR(255),
        new_value VARCHAR(255),
        changed_at DATETIME
    );
    
    -- 변경 내용을 임시 테이블에 삽입
    INSERT INTO temp_audit (employee_id, changed_column, old_value, new_value, changed_at)
    VALUES (NEW.id, 'salary', OLD.salary, NEW.salary, NOW());
END;

위의 예제는 임시 테이블을 사용하여 "employees" 테이블의 "salary" 열이 업데이트될 때 변경 내용을 추적하는 간단한 트리거입니다.

트리거를 사용하여 데이터베이스 작업을 자동화하고 변경 사항을 모니터링하면 데이터 일관성과 비즈니스 규칙 준수를 유지할 수 있습니다.