24_ DBMS 심화 주제

1. 저장 프로시저 (Stored Procedure)

저장 프로시저는 자주 사용하는 SQL 명령어 집합을 데이터베이스 내부에 저장해두고, 필요할 때마다 호출해 실행하는 기능이다.
복잡한 연산을 서버 내부에서 처리할 수 있어 성능 향상과 코드 재사용성에 유리하다.

-- 학생의 평균 점수를 계산하는 저장 프로시저
CREATE PROCEDURE GetAverageScore(IN student_id INT)
BEGIN
    DECLARE avg_score FLOAT;
    SELECT AVG(score) INTO avg_score
    FROM grades
    WHERE id = student_id;

    SELECT CONCAT('평균 점수: ', avg_score) AS result;
END;

 

2. 사용자 정의 함수 (User-Defined Function)

사용자 정의 함수는 사용자가 직접 정의한 연산이나 변환을 함수 형태로 작성해 SQL 내에서 호출할 수 있도록 하는 기능이다.

-- 학생 점수를 등급으로 변환하는 함수
CREATE FUNCTION GetGrade(score INT)
RETURNS CHAR(1)
DETERMINISTIC
BEGIN
    DECLARE grade CHAR(1);
    IF score >= 90 THEN
        SET grade = 'A';
    ELSEIF score >= 80 THEN
        SET grade = 'B';
    ELSE
        SET grade = 'C';
    END IF;
    RETURN grade;
END;

 

3. 트리거 (Trigger)

트리거는 데이터 변경(INSERT, UPDATE, DELETE)이 발생할 때 자동으로 실행되는 SQL 블록이다.
주로 데이터 무결성을 유지하거나 로그 기록을 남길 때 사용한다.

-- 학생 테이블에 새 데이터가 추가될 때 로그 테이블에 기록
CREATE TRIGGER after_student_insert
AFTER INSERT ON student
FOR EACH ROW
BEGIN
    INSERT INTO student_log(student_id, action_time)
    VALUES (NEW.id, NOW());
END;

 

4. 커서 (Cursor)

커서는 SQL 결과 집합을 한 행씩 처리해야 할 때 사용하는 도구이다.
루프 구조를 통해 개별 레코드를 제어할 수 있다.

-- 모든 학생의 점수를 출력하는 커서 예제
DECLARE done INT DEFAULT 0;
DECLARE s_id INT;
DECLARE s_name VARCHAR(50);
DECLARE cur CURSOR FOR SELECT id, name FROM student;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur;
read_loop: LOOP
    FETCH cur INTO s_id, s_name;
    IF done THEN
        LEAVE read_loop;
    END IF;
    SELECT CONCAT('ID: ', s_id, ', 이름: ', s_name);
END LOOP;
CLOSE cur;

 

5. 트랜잭션 관리 (Transaction Management)

트랜잭션은 하나의 작업 단위를 구성하는 SQL 명령어들의 묶음이다.
모두 성공하거나, 모두 실패해야 한다는 ACID 원칙을 따른다.

-- 트랜잭션 예제: 계좌 이체
START TRANSACTION;

UPDATE account SET balance = balance - 10000 WHERE id = 1;
UPDATE account SET balance = balance + 10000 WHERE id = 2;

IF @@ERROR = 0 THEN
    COMMIT;   -- 모두 성공 시 확정
ELSE
    ROLLBACK; -- 오류 발생 시 되돌림
END IF;

이 다섯 가지는 DBMS의 고급 기능으로, 단순 쿼리 작성 이상으로 프로시저적 로직, 데이터 무결성, 트랜잭션 안정성을 제어할 때 필수적으로 사용된다.