뷰(View)와 저장 프로시저(Stored Procedure): 데이터베이스 관리의 최적화 도구

1. 뷰(View): 가상의 테이블

뷰는 데이터베이스 테이블에서 유도된 가상의 테이블입니다. 실제 데이터를 저장하지 않고, 특정 SQL 쿼리를 통해 정의되며, 이를 통해 데이터 접근을 간소화하고 보안을 강화할 수 있습니다.

1.1 뷰의 주요 특징과 이점

• 데이터 접근 단순화: 복잡한 SQL 쿼리를 한 번 작성하여 재사용함으로써 사용자의 편의성을 높입니다.
• 보안 강화: 민감한 데이터에 대한 접근을 제한하여 데이터 보호 수준을 향상시킵니다.
• 재사용 가능성: 여러 애플리케이션에서 동일한 쿼리를 재사용할 수 있어 데이터 일관성을 유지합니다.
• 가독성 향상: 명확하고 간결한 쿼리로 데이터베이스 구조를 더 쉽게 이해할 수 있습니다.

1.2 뷰 생성하기

뷰를 생성하려면 CREATE VIEW 문을 사용합니다. 다음은 직원 테이블에서 이름, 부서, 급여 정보를 포함하는 간단한 뷰 생성 예제입니다:

CREATE VIEW 직원_정보 AS
SELECT 이름, 부서, 급여
FROM 직원;

이 예제는 직원 테이블의 특정 컬럼만 포함된 뷰를 생성하여, 사용자가 필요로 하는 데이터에만 접근할 수 있도록 제한합니다.

1.3 뷰 조회 및 활용

생성된 뷰는 일반적인 SELECT 문으로 데이터를 조회할 수 있습니다:

SELECT * FROM 직원_정보;

뷰는 마치 실제 테이블처럼 동작하며, 쿼리를 단순화하고 가독성을 높이는 데 도움을 줍니다.

1.4 뷰 수정하기

뷰는 수정할 수 없으므로 기존 뷰를 삭제한 후 재생성해야 합니다:

DROP VIEW IF EXISTS 직원_정보;

CREATE VIEW 직원_정보 AS
SELECT 이름, 부서, 급여, 입사일
FROM 직원;

이 코드에서는 새로운 컬럼 입사일을 포함하도록 뷰를 재정의하였습니다.

1.5 뷰 삭제하기

뷰가 더 이상 필요하지 않을 경우, 다음 명령어를 사용하여 삭제할 수 있습니다:

DROP VIEW IF EXISTS 직원_정보;

1.6 활용 예제: 데이터 통합

뷰를 사용하여 다중 테이블 데이터를 통합할 수 있습니다. 예를 들어, 직원 테이블과 부서 테이블을 조인하여 각 직원의 이름과 부서를 표시하는 뷰를 생성할 수 있습니다:

CREATE VIEW 직원_부서 AS
SELECT 직원.이름, 부서.부서명
FROM 직원
JOIN 부서 ON 직원.부서_id = 부서.id;

이 뷰는 직원의 이름과 소속 부서를 쉽게 조회할 수 있는 간결한 인터페이스를 제공합니다.

---

2. 저장 프로시저(Stored Procedure): 반복 작업의 자동화

저장 프로시저는 미리 작성된 SQL 문장의 집합으로, 특정 작업을 자동화하고 데이터베이스 작업을 최적화하는 데 사용됩니다. 이는 복잡한 비즈니스 로직을 데이터베이스 수준에서 처리할 수 있도록 해줍니다.

2.1 저장 프로시저의 이점

• 자동화된 작업: 반복적으로 실행해야 하는 쿼리를 자동으로 처리합니다.
• 성능 향상: 서버 측에서 실행되므로 네트워크 트래픽을 줄이고 응답 속도를 높입니다.
• 보안 강화: 데이터베이스 테이블에 직접 접근하지 않고 작업을 수행하므로 민감한 데이터 보호에 유리합니다.
• 코드 재사용성: 동일한 작업을 여러 곳에서 호출하여 사용할 수 있어 효율적입니다.

2.2 저장 프로시저 작성하기

다음은 특정 부서의 직원을 조회하는 저장 프로시저 예제입니다:

CREATE PROCEDURE GetEmployeeByDepartment(IN dept_name VARCHAR(50))
BEGIN
    SELECT * FROM 직원 WHERE 부서 = dept_name;
END;

2.3 저장 프로시저 호출하기

작성된 저장 프로시저는 다음과 같이 호출할 수 있습니다:

CALL GetEmployeeByDepartment('개발팀');

2.4 데이터 집계 프로시저 작성하기

특정 부서의 평균 급여를 계산하는 저장 프로시저를 작성할 수 있습니다:

DELIMITER //

CREATE PROCEDURE GetAverageSalaryByDepartment(IN dept_name VARCHAR(50))
BEGIN
    SELECT AVG(salary) AS average_salary
    FROM employees
    WHERE department = dept_name;
END //

DELIMITER ;

이 프로시저를 호출하면, 입력한 부서의 평균 급여가 반환됩니다:

CALL GetAverageSalaryByDepartment('Sales');

2.5 고급 활용: 트랜잭션 관리

저장 프로시저를 사용하여 데이터 수정 및 트랜잭션 관리를 수행할 수 있습니다:

DELIMITER //

CREATE PROCEDURE UpdateEmployeeSalary(IN emp_id INT, IN new_salary DECIMAL(10,2))
BEGIN
    START TRANSACTION;
    UPDATE employees
    SET salary = new_salary
    WHERE id = emp_id;
    COMMIT;
END //

DELIMITER ;

이 코드는 특정 직원의 급여를 업데이트하면서 트랜잭션을 활용하여 데이터의 무결성을 보장합니다.

---

3. 뷰와 저장 프로시저의 결합: 강력한 데이터베이스 관리

뷰와 저장 프로시저는 결합하여 사용할 때 더욱 강력한 도구가 됩니다. 뷰는 데이터를 간단하게 조회하는 데 유용하며, 저장 프로시저는 이 데이터를 기반으로 복잡한 작업을 수행할 수 있도록 합니다.

예제: 뷰와 저장 프로시저를 활용한 부서별 데이터 분석

1. 먼저, 직원과 부서 데이터를 통합하는 뷰를 생성합니다:

CREATE VIEW 직원_부서_정보 AS
SELECT 직원.이름, 부서.부서명, 직원.급여
FROM 직원
JOIN 부서 ON 직원.부서_id = 부서.id;

2. 그런 다음, 특정 부서의 평균 급여와 직원 수를 반환하는 저장 프로시저를 작성합니다:

DELIMITER //

CREATE PROCEDURE GetDepartmentStats(IN dept_name VARCHAR(50))
BEGIN
    SELECT 부서명, AVG(급여) AS 평균_급여, COUNT(*) AS 직원_수
    FROM 직원_부서_정보
    WHERE 부서명 = dept_name
    GROUP BY 부서명;
END //

DELIMITER ;

3. 이를 호출하여 데이터를 조회합니다:

CALL GetDepartmentStats('개발팀');

이 조합은 데이터베이스 관리와 분석을 더욱 간단하고 효율적으로 만들어 줍니다.

---

4. 결론

뷰와 저장 프로시저는 데이터베이스 관리에서 각각 중요한 역할을 합니다. 뷰는 데이터 접근성을 향상시키고 복잡성을 줄이며, 저장 프로시저는 작업의 자동화와 성능 최적화를 제공합니다. 두 도구를 조합하면 데이터 관리의 유연성과 효율성을 극대화할 수 있습니다.

더 나아가, 뷰와 저장 프로시저의 조합은 대규모 데이터베이스 환경에서도 체계적이고 확장 가능한 솔루션을 제공합니다. 데이터베이스 성능과 개발 생산성을 향상시키고 싶다면, 뷰와 저장 프로시저를 적극 활용해 보세요.