뷰와 저장 프로시저

복잡한 조회를 매번 길게 작성하는 대신 이름을 붙여 재사용하고(VIEW), 여러 문장으로 된 작업을 하나의 호출로 묶고(저장 프로시저), 특정 변경이 일어날 때 자동으로 동작을 거는(트리거) 방법을 배웁니다.

학습 목표

• CREATE VIEW 로 쿼리에 이름을 붙여 재사용합니다.
• 저장 프로시저로 로직을 묶고 파라미터를 전달합니다.
• 변수와 조건문으로 프로시저를 작성합니다.
• 트리거의 개념과 용도를 이해합니다.

뷰 — 저장된 쿼리

뷰는 SELECT 문에 이름을 붙인 가상 테이블입니다. 실제 데이터를 복사하지 않고, 조회할 때마다 정의된 쿼리를 실행합니다.

CREATE VIEW vip_users AS
SELECT u.id, u.name, SUM(o.amount) AS total
FROM users AS u
JOIN orders AS o ON o.user_id = u.id
GROUP BY u.id, u.name
HAVING SUM(o.amount) >= 1000000;

이후로는 일반 테이블처럼 조회할 수 있습니다.

SELECT * FROM vip_users WHERE total >= 2000000;

💡 TIP — 뷰는 복잡한 조인·집계를 감추고 권한을 통제하는 데 유용합니다. 다만 뷰 위에 뷰를 겹겹이 쌓으면 성능을 예측하기 어려워지니 적당한 깊이로 유지하세요.

저장 프로시저 — 묶음 로직

여러 SQL 문을 하나로 묶어 이름으로 호출합니다. 구분자(DELIMITER)를 바꿔 본문 안의 세미콜론과 구분합니다.

DELIMITER //

CREATE PROCEDURE add_point (
  IN  p_user_id INT,
  IN  p_amount  INT
)
BEGIN
  UPDATE users
  SET point = point + p_amount
  WHERE id = p_user_id;
END //

DELIMITER ;

-- 호출
CALL add_point(3, 100);

IN 은 입력 파라미터입니다. 결과를 돌려주려면 OUT 파라미터를 씁니다.

DELIMITER //
CREATE PROCEDURE count_orders (
  IN  p_user_id INT,
  OUT p_cnt     INT
)
BEGIN
  SELECT COUNT(*) INTO p_cnt
  FROM orders WHERE user_id = p_user_id;
END //
DELIMITER ;

CALL count_orders(3, @cnt);
SELECT @cnt;

변수와 조건문

프로시저 안에서는 변수와 IF 분기를 사용할 수 있습니다.

DELIMITER //
CREATE PROCEDURE grade_user (IN p_user_id INT, OUT p_grade VARCHAR(10))
BEGIN
  DECLARE v_total INT;
  SELECT IFNULL(SUM(amount), 0) INTO v_total
  FROM orders WHERE user_id = p_user_id;

  IF v_total >= 1000000 THEN
    SET p_grade = 'VIP';
  ELSE
    SET p_grade = 'NORMAL';
  END IF;
END //
DELIMITER ;

트리거 — 자동 실행

트리거는 특정 테이블에 INSERT/UPDATE/DELETE 가 일어날 때 자동으로 실행되는 코드입니다.

DELIMITER //
CREATE TRIGGER trg_order_log
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
  INSERT INTO order_logs (order_id, logged_at)
  VALUES (NEW.id, NOW());
END //
DELIMITER ;

NEW 는 새로 삽입/수정될 행, OLD 는 변경 전 행을 가리킵니다. 감사 로그 기록이나 집계 컬럼 갱신에 흔히 씁니다.

⚠️ 주의 — 트리거는 눈에 보이지 않는 곳에서 동작하므로 디버깅이 어렵고, 무거운 로직을 넣으면 원래 DML이 느려집니다. 핵심 비즈니스 로직은 애플리케이션 코드에 두고, 트리거는 가벼운 감사·검증 정도로 한정하는 편이 좋습니다.

요약

• 뷰는 SELECT 에 이름을 붙인 가상 테이블로 쿼리를 재사용합니다.
• 저장 프로시저는 여러 문장을 묶고 IN/OUT 파라미터로 값을 주고받습니다.
• 프로시저 안에서 DECLARE 변수와 IF 분기를 사용할 수 있습니다.
• 트리거는 DML 발생 시 자동 실행되며 NEW/OLD 로 행에 접근합니다.
• 트리거는 가벼운 용도로만 쓰고 핵심 로직은 애플리케이션에 둡니다.

연습문제

1. 도시가 'Seoul'인 사용자만 보여 주는 seoul_users 뷰를 만드세요.
2. 사용자 id와 더할 포인트를 받아 포인트를 증가시키는 프로시저를 작성하세요.
3. 위 프로시저로 id 5 사용자에게 200 포인트를 추가하세요.
4. orders 에 INSERT가 일어나면 로그를 남기는 트리거를 작성하세요.

힌트 — 1번 CREATE VIEW, 2번 CREATE PROCEDURE ... IN, 3번 CALL, 4번 AFTER INSERT ... FOR EACH ROW.