고급 SQL 기술: 트리거, 커서, 동적 SQL, 그리고 공통 테이블 표현식(CTE)

트리거: 데이터베이스 이벤트 자동화

트리거는 데이터베이스에서 특정 이벤트가 발생했을 때 자동으로 실행되는 저장 프로시저입니다. 이는 데이터 무결성을 유지하거나 자동화된 작업을 수행하고, 비즈니스 규칙을 강제하는 데 매우 유용합니다. 데이터 삽입, 수정 또는 삭제와 같은 이벤트 발생 시 즉각적으로 작동하여 사용자 입력을 검증하거나 변경 사항을 기록하고, 시스템 간 데이터 동기화를 처리하는 데 활용됩니다. 결과적으로 반복 작업을 줄이고 오류를 방지할 수 있는 강력한 도구입니다.

주요 구성 요소

1. 이벤트: 트리거를 활성화시키는 사건입니다. 주요 이벤트로는 INSERT, UPDATE, DELETE가 있습니다.
2. 타이밍: 트리거가 실행되는 시점으로, BEFORE(이벤트 발생 전) 또는 AFTER(이벤트 발생 후)로 정의됩니다.
3. 대상 테이블: 트리거가 적용되는 테이블입니다.
4. 트리거 본문: 실행할 SQL 명령어로 구성되며, 특정 작업이나 검증 로직을 포함합니다.

사용 사례

• 데이터 무결성 검사:
  • 예를 들어, 직원 급여가 일정 기준 이상이어야만 데이터를 입력할 수 있도록 제한합니다.

CREATE TRIGGER check_salary
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary < 30000 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Salary must be at least 30,000';
    END IF;
END;

이 코드는 직원의 급여가 30,000 미만인 경우 오류 메시지를 출력하여 데이터 무결성을 유지합니다.

• 감사 로그 기록:
  • 데이터 변경 이력을 추적하여 감사 목적으로 사용할 수 있습니다.

CREATE TRIGGER log_employee_changes
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (employee_id, old_salary, new_salary, change_date)
    VALUES (OLD.id, OLD.salary, NEW.salary, NOW());
END;

감사 로그를 통해 누가 데이터를 변경했는지, 어떤 값이 변경되었는지를 기록할 수 있습니다.

• 복잡한 비즈니스 로직 처리:
  • 제품 재고가 감소했을 때 경고를 보내거나 알림을 생성합니다.

CREATE TRIGGER update_inventory_alert
AFTER DELETE ON inventory
FOR EACH ROW
BEGIN
    IF OLD.quantity < 10 THEN
        CALL send_alert(OLD.product_id);
    END IF;
END;

이 코드는 재고 수량이 10 미만일 때 알림을 생성하는 로직을 포함하고 있습니다.

장점

1. 자동화: 데이터 무결성을 자동으로 보장하며 반복 작업을 줄입니다.
2. 일관성 유지: 모든 데이터 조작 작업에서 동일한 규칙을 적용할 수 있습니다.
3. 변경 이력 기록: 데이터를 변경한 사용자와 변경 내용을 추적할 수 있습니다.

단점

1. 성능 문제: 과도한 트리거 사용은 데이터베이스 성능을 저하시킬 수 있습니다.
2. 디버깅 어려움: 트리거의 실행 흐름을 추적하는 것이 복잡할 수 있습니다.

---

커서: 결과 집합의 순차적 처리

커서는 SQL에서 데이터베이스의 결과 집합을 행 단위로 처리할 수 있는 도구입니다. 일반적인 SQL 쿼리는 전체 결과를 한 번에 반환하지만, 커서를 사용하면 각 행을 개별적으로 처리할 수 있습니다. 이는 조건부 작업이 필요한 복잡한 비즈니스 로직 구현에 유용합니다. 하지만 대규모 데이터를 처리할 때는 성능 저하가 발생할 수 있으므로 주의가 필요합니다.

주요 특징

1. 명시적 커서: 개발자가 명시적으로 선언하고 제어하는 커서입니다.
2. 암시적 커서: SQL 문 실행 시 자동으로 생성되는 커서로, 단순한 SELECT 문에서 자주 사용됩니다.

사용 사례

• 데이터 처리 루프 구현:

아래 예제는 특정 부서의 직원 ID와 이름을 출력하는 방법을 보여줍니다:

DECLARE my_cursor CURSOR FOR
SELECT employee_id, first_name FROM employees WHERE department_id = 10;

OPEN my_cursor;
FETCH NEXT FROM my_cursor INTO @employee_id, @first_name;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Employee ID: ' + CAST(@employee_id AS NVARCHAR) + ', Name: ' + @first_name;
    FETCH NEXT FROM my_cursor INTO @employee_id, @first_name;
END;

CLOSE my_cursor;
DEALLOCATE my_cursor;

장점

1. 행 단위 처리: 각 행을 개별적으로 처리할 수 있어 복잡한 로직 구현에 적합합니다.
2. 조건부 처리: 특정 조건에 따라 데이터를 조작하거나 변환할 수 있습니다.

단점

1. 성능 저하: 많은 데이터를 처리할 때 커서를 사용하면 시스템 리소스를 많이 소모할 수 있습니다.
2. 자원 관리: 커서를 닫고 DEALLOCATE하지 않으면 메모리 누수가 발생할 수 있습니다.

---

동적 SQL: 실행 시점의 유연한 쿼리 생성

동적 SQL은 실행 시점에 쿼리를 생성하고 실행할 수 있는 기능으로, 다양한 조건에 따라 유연한 데이터베이스 처리를 가능하게 합니다. 사용자 입력 값이나 특정 조건에 따라 쿼리 구조를 동적으로 변경할 필요가 있을 때 동적 SQL을 활용하면 효과적입니다.

구현 방식

• EXECUTE 명령어 사용:

  DECLARE @sql NVARCHAR(MAX);
  SET @sql = 'SELECT * FROM Employees WHERE DepartmentID = ' + CAST(@DepartmentID AS NVARCHAR(10));
  EXEC sp_executesql @sql;

• sp_executesql 사용:

매개변수를 지원하여 보안과 성능 면에서 더 우수합니다:

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'SELECT * FROM Employees WHERE DepartmentID = @DeptID';
EXEC sp_executesql @sql, N'@DeptID INT', @DeptID;

사용 사례

• 조건부 데이터 필터링:

아래는 사용자 입력 값에 따라 동적으로 조건을 변경하는 예제입니다:

DECLARE @DepartmentID INT = 5;
DECLARE @JobTitle NVARCHAR(50) = 'Manager';
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'SELECT * FROM Employees WHERE DepartmentID = @DeptID';

IF (@JobTitle IS NOT NULL)
BEGIN
    SET @sql += N' AND JobTitle = ''' + REPLACE(@JobTitle, '''', '''''') + '''';
END;

EXEC sp_executesql @sql, N'@DeptID INT', @DepartmentID;

장점

1. 유연성: 사용자 요구에 따라 실시간으로 쿼리를 변경할 수 있습니다.
2. 코드 재사용: 동일한 구조를 기반으로 다양한 조건을 처리할 수 있습니다.

단점

1. 보안 위험: SQL 인젝션 공격에 취약할 수 있으므로 매개변수화된 쿼리를 사용하는 것이 중요합니다.
2. 디버깅 어려움: 동적 쿼리의 실행 흐름을 추적하기가 어렵습니다.

---

공통 테이블 표현식 (CTE): 쿼리의 가독성과 재사용성 개선

공통 테이블 표현식(CTE)은 SQL에서 복잡한 쿼리를 간결하게 작성하고, 재귀적 데이터를 처리하거나 반복적인 논리를 구현할 때 유용한 도구입니다. CTE는 가독성을 높이고 중복 코드를 줄이며, 복잡한 데이터 작업을 쉽게 처리할 수 있도록 합니다.

주요 특징

1. 일시적 결과 집합: CTE는 쿼리 실행 중에만 유지되며, 데이터베이스에 저장되지 않습니다.
2. 재귀적 쿼리 지원: 계층적 데이터 또는 반복 작업을 처리하는 데 유용합니다.

사용 사례

• 기본 CTE 사용:

아래는 특정 부서의 평균 급여보다 높은 직원들을 조회하는 예제입니다:

WITH AverageSalary AS (
    SELECT AVG(salary) AS avg_salary
    FROM employees
    WHERE department_id = 10
)
SELECT employee_id, salary
FROM employees
WHERE salary > (SELECT avg_salary FROM AverageSalary);

• 재귀적 CTE:

조직의 계층 구조를 탐색하는 데 유용합니다:

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT employee_id, manager_id, name
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.employee_id, e.manager_id, e.name
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON eh.employee_id = e.manager_id
)
SELECT * FROM EmployeeHierarchy;

장점

1. 가독성 향상: 복잡한 쿼리를 더 명확하게 표현할 수 있습니다.
2. 재사용성 증가: 동일한 로직을 여러 번 재사용할 수 있습니다.

CTE는 계층적 데이터 처리와 복잡한 비즈니스 로직 구현에서 강력한 도구로 사용되며, 쿼리 작성과 유지보수를 용이하게 만듭니다.