[MySQL] Trigger, Cursor, Index 이론 및 예제

목차

Trigger

Cursor

Index

---

Trigger 

테이블에 Insert, Update, Delete 작업이 발생하면 자동으로 수행됨

데이터의 무결성을 보장하는 장치의 역할

직접 실행할 수 없음

 

DELIMITER $$
CREATE TRIGGER 트리거명
trigger_time trigger_event
ON 테이블명 FOR EACH ROW
[trigger_order]
BEGIN
~트리거 코딩부;
END $$
DELIMITER ;

 

trigger_time : {BEFORE | AFTER}

trigger_event : {INSERT | UPDATE | DELETE}

trigger_order : {FOLLOW | PRECEDES} other_trigger_name

 

BEFORE trigger

- 테이블에 변경 작업이 일어나기 전에 작동하는 트리거 (변경 작업 전 실행)

 

AFTER trigger

- 테이블에 변경(삽입, 수정, 삭제) 작업이 일어났을 때 작동하는 트리거 (변경 작업 후 실행)

 

NEW 테이블과 OLD 테이블

- 트리거에서 변경 작업이 수행되면 임시로 사용되는 시스템 테이블

- OLD: DELETE 로 삭제된 데이터 | UPDATE 로 바뀌기 전의 데이터

- NEW : INSERT 로 삽입된 데이터 | UPDATE 로 바뀐 후의 데이터

EVENT	OLD	NEW
INSERT	X	O
UPDATE	O	O
DELETE	O	X

 

---

Trigger의 활용 1

변경되기 전 데이터를 저장할 백업 테이블을 생성하고

CREATE TABLE backup_DEPT(
DEPTNO INT(10),
DNAME VARCHAR(14),
LOC VARCHAR(13),
modType CHAR(4), -> 변경 유형 (수정 OR 삭제)
modDate DATE); -> 변경 날짜

 

- 데이터를 수정했을 때 작동하는 dept_up_trg 트리거를 작성해보자

DELIMITER $$
CREATE TRIGGER dept_up_trg
AFTER UPDATE
ON DEPT
FOR EACH ROW
BEGIN
INSERT INTO backup_DEPT VALUES(OLD.DEPTNO, OLD.DNAME. OLD.LOC, '수정', CURDATE());
END $$
DELIMITER ;

 

Trigger의 활용 2

- 데이터를 삭제했을 때 작동하는 dept_del_trg 트리거를 작성해보자

DELIMITER $$
CREATE TRIGGER dept_del_trg
AFTER DELETE
ON DEPT
FOR EACH ROW
BEGIN
INSERT INTO backup_DEPT VALUES (OLD.DEPTNO, OLD.DNAME, OLD.LOC, '삭제', CURDATE());
END $$
DELIMITER ;

 

Trigger의 활용 1,2 확인

UPDATE DEPT
SET DNAME='PLANNING'
WHERE DEPTNO=40;

DELETE FROM DEPT
WHERE DEPTNO=40;

SELECT * FROM DEPT;
SELECT * FROM backup_DEPT;

 

Trigger의 활용 3

- 데이터를 삽입하기 전 작동하는 sal_in_trg 트리거

DELIMITER $$
CREATE TRIGGER sal_in_trg
BEFORE INSERT
ON DEPT
FOR EACH ROW
BEGIN
IF NEW.sal<800 THEN
SET NEW.sal=0;
ELSEIF NEW.sal>4500 THEN
SET NEW.sal=4500;
END IF;
END $$
DELIMITER ;

 

---

생성된 트리거 확인

SHOW TRIGGERS FROM 데이터베이스명;

 

트리거 삭제

DROP TRIGGER 트리거명;

 

---

Cursor (커서)

프로시저 내부에서 복수 행을 처리할 때 사용하는 구성요소

파일 포인터가 열린 파일에서의 현재 위치를 표시하는 것처럼 커서는 결과 집합에서 현재 위치를 표시

- 쿼리의 결과 행 집합에서 한 행씩 옮겨가며 명령을 처리함

- OPEN, FETCH, CLOSE 문을 사용하여 커서를 제어

 

커서의 작동 순서

- 커서 선언 (DELCARE)

- 반복 조건 선언 (DECLARE ... HANDLER)

- 커서 열기 (OPEN0

- 커서에서 데이터 가져오기 (FETCH)

- 데이터 처리

- 커서 닫기 (CLOSE)

 

Cursor 의 활용

DELIMITER $$
CREATE PROCEDURE info_hiredate(IN p_year CHAR(5))
BEGIN
DECLARE cnt INT DEFUALT 0;
DECLARE empName VARCHAR(10);
DECLARE endOfRow BOOLEAN DEFAULT FALSE; #행의 끝 여부

DECLARE userCursor CURSOR FOR
SELECT ename
FROM emp
WHERE YEAR(HIREDATE)=p_year;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET endOfRow=TRUE; #행의 끝일 때 Handler 정의

OPEN userCursor;
cloop: WHILE TRUE DO
FETCH userCursor INTO empName; #읽은 행 empName에 대입
IF endOfRow THEN #더이상 읽을 행이 없으면 반복문 종료
LEAVE cloop;
END IF;

SET cnt=cnt+1;
SELECT CONCAT(cnt,'번','empName);

END WHILE;

CLOSE userCursor;
END $4
DELIMITER ;

CALL info_hiredate('1982');

 

---

Index

SQL 명령문의 처리 속도를 향상시키기 위해 특정 컬럼에 대해 생성하는 개체

- 데이터 검색시 데이터에 빠르게 접근할 수 있도록 함

- 테이블에 저장된 데이터를 액세스하기 위한 목적으로 사용

- MySQL에서는 클러스터형 인덱스와 보조 인덱스로 구분

- INSERT, UPDATE의 속도가 저하되는 단점이 있음

 

인덱스의 구분

(클러스터형 / 비클러스터형)

클러스터형 인덱스	보조 인덱스 (비클러스터형 인덱스)
- 내용 자체가 순서대로 정렬되어 있는 책과 같음 - 테이블당 하나만 생성할 수 있음	- 찾아보기가 별도로 있어 찾아보기로 페이지를 확인 후 내용을 확인하는 책과 같음

 

자동으로 생성되는 인덱스

테이블 생성시 기본키를 설정하면 자동으로 기본키 열에 클러스터형 인덱스가 생성된다

특징

- 테이블을 생성할 때 특정 열을 기본키로 생성하면 클러스터형 인덱스가,

- UNIQUE 제약 조건을 설정하면 그 열에 자동으로 보조 인덱스가 생성된다.

 

보조 INDEX 생성

UNIQUE : 고유 인덱스 지정

ASC | DESC : 인덱스 키를 오름차순 또는 내림차순으로 정렬

CREATE [UNIQUE] INDEX 인덱스명 ON 테이블명(컬럼명 [ASC|DESC],...)

 

고유 인덱스

유일한 값을 가지는 컬럼에 대해 생성하는 인덱스로 모든 인덱스 키는 테이블의 하나의 행과 연결

CREATE UNIQUE INDEX idx_dept_dname ON dept(dname);

 

비고유 인덱스

중복된 값을 가지는 컬럼에 대해 생성하는 인덱스

CREATE INDEX idx_emp_hdate ON emp(hiredate);

 

단일 인덱스

하나의 컬럼으로만 구성된 인덱스

 

결합 인덱스

두 개 이상의 컬럼을 결합하여 생성하는 인덱스

CREATE INDEX emp_job_deptno ON emp(job,deptno);

 

생성된 인덱스 확인

SHOW INDEX FROM 테이블명;

 

인덱스 삭제

DROP INDEX 인덱스명 ON 테이블명;

 

 

---