트랜잭션은 언제 사용할까?
=> 테이블 내용을 변경하는 SQL들이 연달아 실행되며 이것들이 마치 하나의 SQL처럼 다 같이 성공하던지 아니면 실패해야 한다면 이전 상태로 돌아가는 트랜잭션의 사용이 필수!
트랜잭션이란?
- 다수의 SQL문을 실행해도 하나의 SQL처럼 다같이 성공하거나 실패하는 것처럼 실행되는 것
-
레코드를 수정/추가/삭제한 것에만 의미가 있음 → SELECT는 읽기만 하므로 트랜잭션을 사용할 이유가 없음
-
BEGIN과 END(or COMMIT) 사이에 해당 SQL들을 사용
-
ROLLBACK : BEGIN 뒤에 실행된 문장들이 무효화
→ END나 COMMIT이 불리기 전까지는 MySQL사용자에게는 바뀐것 처럼 보이지만 다른 사람들 눈에는 안보임
-
- 은행 계좌 이체가 아주 좋은 예
트랜잭션 커밋 모드: autocommit
- autocommit = True(보통 이렇게 사용)
- 모든 레코드 수정/삭제/추가 작업이 기본적으로 바로 데이터베이스에 쓰여짐
- 특정 작업을 트랜잭션으로 묶고 싶다면 BEGIN과 END(COMMIT)/ROLLBACK으로 처리 → True일 때만 BEGIN과 END 사용 가능!
- autocommit = False
-
모든 레코드 수정/삭제/추가 작업을 명시적으로 커밋을 해야함
-
ROLLBACK : 앞의 과정 무효화
-
BEGIN과 END(COMMIT)/ROLLBACK이 없음 → 트랜잭션이라는 개념이 X
⇒ autocommit 여부는 SQL 클라이언트/라이브러리에 따라 달라짐
⇒ MySQL Workbench 기본은 autocommit이 True -
확인 방법: SHOW VARIABLES LIKE 'AUTOCOMMIT'; →출력이 on이면 true
-
SET autocommit=0 (혹은 1)의 실행으로 변경가능
-
View란?
- 자주 사용하는 SQL 쿼리 (SELECT)에 이름을 주는 것
- View(이름이 있는 쿼리)로 데이터베이스단에 저장됨
▪ SELECT 결과가 테이블로 저장X → View가 사용될 때마다 SELECT가 실행
=> 가상 테이블(Virtual Table) - 사용방법 : CREATE OR REPLACE VIEW 뷰이름 AS SELECT ...
Stored Procedure란?(View와 비슷하지만 더 강력한 것)
- MySQL 서버단에 저장되는 SQL 쿼리들
- CREATE PROCEDURE 사용
- DROP PROCEDURE [IF EXISTS]로 제거
- 프로그래밍 언어의 함수처럼 인자를 넘기는 것이 가능(IN, OUT, INOUT 존재)
- 리턴값 : 레코드들의 집합 (SELECT와 동일)
- 간단한 분기문(if, case)과 루프(loop)를 통한 프로그램이 가능
- 단점 : 디버깅이 힘들고 서버단의 부하를 증가
[정의 문법]
- DELIMITER과 END뒤에 //나 $써야함
DELIMITER //
CREATE PROCEDURE procedure_name(parameter_list)
BEGIN
statements;
END //
DELIMITER ;[정의 예]
- BDGIN과 END사이의 SELECT결과가 리턴
DELIMITER //
CREATE PROCEDURE return_session_details()
BEGIN
SELECT *
FROM test.keeyong_session_details;
END //
DELIMITER;[호출 문법]
CALL stored_procedure_name(argument_list);[호출 예]
CALL return_session_details();Stored Function이란?
- Stored Precedure - 레코드를 리턴, IN/OUT/INOUT 파라미터, CALL로 호출
- Stored Function - 값(Scalar)을 하나 리턴, 모든 함수의 인자는 IN 파라미터 → 읽기 전용, 호출함수 없이 SQL문에서 바로 사용 가능
- 리턴값은 Deterministic(같은 입력에서는 같은 출력) 혹은 Non Deterministic(입력이 같아도 출력이 다름→ 랜덤함수)
- 서버쪽 함수 (특정 데이터베이스 밑에 등록됨)
- CREATE FUNCTION 사용
[정의 예]
DELIMITER $$
CREATE FUNCTION test.Channel_Type(channel varchar(32))
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
DECLARE channel_type VARCHAR(20); --> Stored Procedure나 Stored Function 내에서 변수 선언은 DECLARE
IF channel in ('Facebook', 'Instagram', 'Tiktok') THEN
SET channel_type = 'Social Network';
ELSEIF channel in ('Google', 'Naver') THEN
SET channel_type = 'Search Engine';
ELSE
SET channel_type = channel;
END IF;
-- return the customer level
RETURN (channel_type);
END$$[호출 예]
SELECT channel, test.Channel_Type(channel)
FROM prod.channel;Trigger란?
- INSERT/DELETE/UPDATE 실행 전후에 특정 작업을 자동으로 수행하는 것 → SELECT와 관계X
- 보통 특정 대상 테이블과 언제(전후)실행할 지 정해야함
- CREATE TRIGGER 명령을 사용
- NEW/OLD modifier
- NEW는 INSERT와 UPDATE에서만 사용가능
- OLD는 DELETE와 UPDATE에서만 사용가능
CREATE TRIGGER 트리거이름
{BEFORE | AFTER} {INSERT | UPDATE| DELETE }
ON table_name FOR EACH ROW
trigger_body;