[SQL] 8. SQL의 "고급 주제"

SQL 학습: 고급 주제

SQL의 고급 주제를 학습하면 데이터베이스의 복잡한 작업을 더 효과적으로 처리할 수 있습니다. 이번 글에서는 뷰, 저장 프로시저 및 함수, 그리고 트리거에 대해 알아보겠습니다.

---

8.1 뷰

뷰 생성 (CREATE VIEW)

뷰는 하나 이상의 테이블에서 데이터를 추출하여 만든 가상의 테이블입니다. 뷰는 실제 데이터가 저장되지 않으며, 쿼리를 통해 동적으로 생성됩니다.

[sql]

 

CREATE VIEW employee_view AS
SELECT employee_id, first_name, last_name, department_id
FROM employees
WHERE department_id = 10;

 

위의 명령어는 employees 테이블에서 department_id가 10인 직원들의 정보를 포함하는 employee_view 뷰를 생성합니다.

 

 

뷰의 활용 및 제한 사항

 

활용

• 간단한 데이터 조회 : 복잡한 쿼리를 단순화하여 쉽게 데이터를 조회할 수 있습니다.
• 데이터 보안 : 특정 열이나 행만 포함하는 뷰를 생성하여 민감한 데이터를 보호할 수 있습니다.
• 데이터 통합 : 여러 테이블에서 데이터를 결합하여 하나의 뷰로 제공할 수 있습니다.

제한 사항

• 성능 : 뷰는 실제 데이터를 저장하지 않으므로, 뷰를 사용할 때마다 원본 테이블에 접근해야 합니다. 이는 성능에 영향을 미칠 수 있습니다.
• 제한된 데이터 조작 : 모든 뷰가 데이터 삽입, 수정, 삭제 작업을 지원하지 않습니다. 특히, 집계 함수나 조인을 포함하는 뷰는 이러한 작업에 제한이 있을 수 있습니다.

---

8.2 저장 프로시저 및 함수

저장 프로시저의 개념과 생성 (CREATE PROCEDURE)

저장 프로시저는 일련의 SQL 문을 저장하고 재사용할 수 있는 데이터베이스 객체입니다. 저장 프로시저는 복잡한 작업을 자동화하고 일관되게 수행할 수 있습니다.

[sql]

 

CREATE PROCEDURE UpdateEmployeeSalary(
    IN emp_id INT,
    IN new_salary DECIMAL(10, 2)
)
BEGIN
    UPDATE employees
    SET salary = new_salary
    WHERE employee_id = emp_id;
END;

 

위의 명령어는 employee_id가 주어진 값인 직원의 급여를 새로운 값으로 업데이트하는 저장 프로시저를 생성합니다.

 

 

사용자 정의 함수의 개념과 생성 (CREATE FUNCTION)

사용자 정의 함수는 특정 작업을 수행하고 결과를 반환하는 함수입니다. 함수는 복잡한 계산을 수행하고 결과를 반환하는 데 유용합니다.

[sql]

 

CREATE FUNCTION GetEmployeeFullName(emp_id INT)
RETURNS VARCHAR(100)
BEGIN
    DECLARE full_name VARCHAR(100);
    SELECT CONCAT(first_name, ' ', last_name) INTO full_name
    FROM employees
    WHERE employee_id = emp_id;
    RETURN full_name;
END;

 

위의 명령어는 employee_id가 주어진 값인 직원의 전체 이름을 반환하는 사용자 정의 함수를 생성합니다.

 

 

절차형 SQL (PL/SQL, T-SQL)

절차형 SQL은 SQL 문과 프로그래밍 구조(예: 조건문, 반복문 등)를 결합한 프로그래밍 언어입니다. Oracle의 PL/SQL과 Microsoft SQL Server의 T-SQL이 대표적입니다.

 

예제 (PL/SQL)

[sql]

 

BEGIN
    FOR i IN 1..10 LOOP
        INSERT INTO employees (employee_id, first_name, last_name)
        VALUES (i, 'First' || i, 'Last' || i);
    END LOOP;
END;

---

8.3 트리거

트리거의 개념과 생성 (CREATE TRIGGER)

트리거는 특정 이벤트(예: 삽입, 업데이트, 삭제)가 발생할 때 자동으로 실행되는 데이터베이스 객체입니다. 트리거는 데이터 무결성을 유지하고, 자동화된 작업을 수행하는 데 유용합니다.

[sql]

 

CREATE TRIGGER before_employee_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_audit (employee_id, action, action_date)
    VALUES (OLD.employee_id, 'UPDATE', NOW());
END;

 

위의 명령어는 employees 테이블에서 업데이트가 발생하기 전에 employee_audit 테이블에 감사 기록을 삽입하는 트리거를 생성합니다.

 

 

트리거의 활용

• 데이터 무결성 유지 : 데이터 변경 시 자동으로 검증 로직을 실행하여 데이터 무결성을 유지할 수 있습니다.
• 감사 로그 : 데이터베이스 변경 내역을 자동으로 기록하여 추적할 수 있습니다.
• 자동 계산 : 데이터 변경 시 자동으로 관련 데이터를 계산하여 저장할 수 있습니다.

---

이 글에서는 SQL의 고급 주제인 뷰, 저장 프로시저 및 함수, 트리거에 대해 알아보았습니다. 이러한 고급 기능을 활용하면 데이터베이스 작업을 더 효율적이고 효과적으로 수행할 수 있습니다. 다음 단계에서는 이러한 기능을 실습하여 실제 프로젝트에 적용해보세요. Happy Coding!