📌 개념

저장 프로시저는 미리 작성된 SQL 문들의 집합을 데이터베이스에 저장하여 필요할 때마다 호출하여 실행할 수 있는 프로그램입니다. 이를 통해 반복적인 작업을 자동화하고, 코드의 재사용성을 높이며, 데이터베이스 작업의 일관성을 유지할 수 있습니다.

🛠️ 특징 및 장점

• 성능 향상: 한 번 컴파일된 후 재사용되므로 실행 속도가 빠릅니다.
• 보안성: 사용자에게 직접 테이블 접근 권한을 주지 않고, 저장 프로시저를 통해서만 데이터 조작을 허용할 수 있습니다.
• 모듈화: 복잡한 작업을 논리적으로 분리하여 관리할 수 있습니다.
• 코드 재사용성: 공통된 작업을 저장 프로시저로 작성하여 여러 곳에서 호출할 수 있습니다.
• 유지보수성: 비즈니스 로직을 데이터베이스 내부에 캡슐화하여 애플리케이션 코드와 분리할 수 있습니다.

📝 사용법

1. 저장 프로시저 생성

    CREATE PROCEDURE 프로시저명 (매개변수 목록)
    BEGIN
        -- SQL 문장들
    END;
   

2. 저장 프로시저 호출

    CALL 프로시저명 (인자 목록)
   

🔍 예제

예를 들어, 특정 부서의 직원들의 평균 급여를 계산하는 저장 프로시저를 생성하고 호출해보겠습니다.

1. 저장 프로시저 생성

    CREATE PROCEDURE GetAverageSalaryByDept(IN dept_id INT, OUT avg_salary DECIMAL(10,2))
    BEGIN
        SELECT AVG(salary) INTO avg_salary
        FROM employees
        WHERE department_id = dept_id;
    END //
    DELIMITER ;
   

2. 저장 프로시저 호출

    CALL GetAverageSalaryByDept(10, @avg_salary);
    SELECT @avg_salary;
       
   

---

🔄 트리거(Trigger)

🎯 개념

트리거는 특정 테이블에서 INSERT, UPDATE, DELETE 등의 이벤트가 발생할 때 자동으로 실행되는 특별한 종류의 저장 프로시저입니다. 이를 통해 데이터 무결성 유지, 자동 로그 기록, 복잡한 비즈니스 로직 구현 등을 수행할 수 있습니다.

🛠️ 특징 및 장점

• 자동 실행: 사용자 개입 없이 지정된 이벤트 발생 시 자동으로 실행됩니다.
• 데이터 무결성 유지: 데이터 변경 시 자동으로 제약 조건을 확인하거나 보정할 수 있습니다.
• 감사 및 로깅: 데이터 변경 내역을 자동으로 기록하여 추적할 수 있습니다.

📝 사용법

1. 트리거 생성

    CREATE TRIGGER 트리거명
    {BEFORE | AFTER} {INSERT | UPDATE | DELETE}
    ON 테이블명 FOR EACH ROW
    BEGIN
        -- SQL 문장들
    END;
   

🔍 예제

예를 들어, 직원 테이블에서 급여가 변경될 때 변경 이력을 기록하는 트리거를 생성해보겠습니다.

CREATE TRIGGER LogSalaryChange
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary <> OLD.salary THEN
        INSERT INTO salary_changes (employee_id, old_salary, new_salary, change_date)
        VALUES (OLD.employee_id, OLD.salary, NEW.salary, NOW());
    END IF;
END //
DELIMITER ;

---

⏰ 이벤트 스케줄러(Event Scheduler)

🎯 개념

이벤트 스케줄러는 특정 시간이나 주기에 따라 자동으로 SQL 문을 실행하도록 예약하는 기능입니다. 정기적인 데이터 백업, 로그 정리, 통계 업데이트 등의 작업을 자동화하는 데 사용됩니다.

🛠️ 특징 및 장점

• 자동화: 반복적인 작업을 자동으로 수행하여 관리 부담을 줄입니다.
• 유연성: 다양한 주기와 조건으로 이벤트를 자유롭게 설정할 수 있습니다.
• 관리 효율성: 관리자가 수동으로 수행해야 하는 작업을 줄여줍니다.
• 효율성: 시스템 비사용 시간대에 작업을 예약하여 성능 영향을 최소화할 수 있습니다.

📝 사용법

1. 이벤트 스케줄러 활성화

    SET GLOBAL event_scheduler = ON;
   

2. 이벤트 생성

    CREATE EVENT 이벤트명
    ON SCHEDULE 일정
    DO
        -- SQL 문장들
   

🔍 예제

예를 들어, 매일 자정에 특정 테이블의 데이터를 백업하는 이벤트를 생성해보겠습니다.

CREATE EVENT DailyBackup
ON SCHEDULE EVERY 1 DAY
STARTS '2025-03-17 00:00:00'
DO
    INSERT INTO employees_backup
    SELECT * FROM employees;

---

🌀 동적 SQL(Dynamic SQL)

동적 SQL은 프로그램 실행 시점에 SQL 문을 동적으로 생성하고 실행하는 방법입니다. 이를 통해 다양한 조건과 입력값에 따라 유연하게 SQL 문을 구성하고 실행할 수 있습니다.

주요 특징:

• 유연성: 고정된 SQL 문이 아닌, 실행 시점의 조건에 따라 SQL 문을 동적으로 생성할 수 있습니다.
• 복잡한 쿼리 처리: 동적으로 생성된 SQL 문을 통해 복잡한 비즈니스 로직을 구현할 수 있습니다.
• 보안 고려 필요: 사용자 입력을 직접 포함하는 경우 SQL 인젝션 공격에 취약할 수 있으므로, 입력값 검증이 중요합니다.

사용 예시:

SET @table_name = 'Employees';
SET @sql = CONCAT('SELECT * FROM ', @table_name);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

위 예시에서는 테이블 이름을 동적으로 설정하여 해당 테이블의 모든 데이터를 조회합니다.

참고: 동적 SQL을 사용할 때는 SQL 인젝션