[MySQL] Routines

트랜잭션

하나의 작업 단위이다. 여러 SQL 작업을 하나의 묶음으로 처리해서, 모두 성공하거나 모두 실패하도록 보장함

• START TRANSACTION ➡️ 트랜잭션 시작
• COMMIT ➡️ 트랜잭션 확정 (디스크에 반영)
• ROLLBACK ➡️ 트랜잭션 취소 (변경 내용 원복)

세션

데이터베이스에 연결된 하나의 연결(커넥션) 단위

• 각 세션은 독립적으로 트랜잭션을 관리한다.
  ➡️ 세션마다 별도의 트랜잭션 상태를 가짐.

 

단일 쿼리의 경우

기본적으로 Auto Commit 모드이기 때문에 쿼리가 하나일 경우 START TRANSACTION을 명시할 필요가 없다. 
➡️ 쿼리가 성공하면 자동 커밋, 실패하면 롤백 됨

 

ORM

• ORM(Object Relational Mapping) 프레임워크는 내부적으로 여러개 쿼리를 실행함
• 개발자는 직접 START TRANSACTION을 작성하지 않아도, ORM이 알아서 처리함.

주의

• 보이는 쿼리 외에도, 데이터베이스 안에 설정된 트리거(trigger)들이 실행될 수 있다. 
  (예를 들어 고객이 업데이트 되면 고객의 수를 추가해 와 같은 것들)
• 트리거같은 것을 모두 안전하게 묶으려면 트랜잭션 범위 안에서 처리해야 한다. 
• 예: 한 테이블에 INSERT했더니 다른 테이블에 연쇄적으로 UPDATE 발생 → 트랜잭션으로 전체 묶어야 일관성 보장

---

📌 View

SELECT 쿼리 결과를 가상의 테이블처럼 정의한 객체이다.
물리적으로 데이터를 저장하지 않고, 정의된 쿼리를 매번 실행해서 결과를 보여준다.

• 복잡한 쿼리를 재사용할 수 있다.

📌 DELIMITER

•  
• MySQL에서 여러 문장을 한 번에 실행할 때 명령어 구분자를 의미
  기본은 ; 이다. 하지만 프로시저나 트리거를 만들 때 내부에 ;가 있으면 중간에 실행이 될 수 있기 때문에 잠시동안 구분자를 다른 기호로 변경해주어 사용할 때 이용한다. 

DELIMITER // 
CREATE PROCEDURE sample_proc() BEGIN SELECT NOW(); END // 
DELIMITER ;

 

📌  Procedure

여러 SQL문을 하나의 이름으로 묶어 놓은 저장된 프로그램이다. 매번 작성하지 않고 이름으로 호출해 실행할 수 있다.

DELIMITER //
CREATE PROCEDURE add_user(IN user_name VARCHAR(100))
BEGIN
  INSERT INTO users (name) VALUES (user_name);
END //
DELIMITER ;

CALL add_user('John');

📌  Trigger

테이블에 INSERT, UPDATE, DELETE 이벤트가 발생할 때 자동으로 실행되는 프로그램이다

DELIMITER //
Create Trigger <trigger-name>
  { BEFORE | AFTER } { INSERT | UPDATE | DELETE }
  { PRECEDES | FOLLOWS } other-trigger-name
  on <table-name> FOR EACH ROW
BEGIN
	… OLD.<col>  … NEW.<col>;

END //
DELIMITER ;

 

트리거는 많이 쓰지 않는 것이 좋다. 트리거 == 숨겨진 로직, 그러기 때문에 만약 사용할 경우 트리거 내용을 주석으로 남겨둔다고 한다. 
또한 정규화 원칙과 어긋나게 과도한 동작을 데이터베이스에 내장하지 않도록 주의

 

 

📌  Cursor

쿼리 결과(Result Set)를 한 행씩 순차적으로 처리할 수 있게 해주는 메모리 포인터

메모리 포인터를 위치시켜 놓고 움직이면서 보내준다..?

• 언제 쓰나?
  • 한 쿼리에서 여러 개의 행을 반환했을 때 그걸 하나씩 순서대로 처리할 필요가 있을 때
    (여러개의 resultset이 있을 떄 한개의 Row만 사용하고 싶을 경우)
  • 반복적으로 어떤 계산하거나 로직을 적용하거나 조건 검사를 해야 할 때

-- 커서 템플릿
Declare _done boolean default False;
Declare _cur CURSOR FOR
select x, y, ....;

Declare Continue Handler
For Not Found SET _done := True;

OPEN _cur;

cur_loop: LOOP
Fetch _cur into <var-X>, <var-Y>...;
IF _done THEN
LEAVE cur_loop;
END IF;
...
END LOOP cur_loop;

CLOSE _cur;