함수, 프로시저, 트리거
함수
- DBMS에서 제공되는 내장함수 외에 사용자가 직접 정의가능
- SQL을 이용하여 로직을 수행하고, 결과를 단일 값으로 반환할 수 있는 모듈
- 보통 함수명에는 sf_~ , f_~ 이런식으로 함수명을 작성한다.(stored function)
# 문법
CREATE FUNCTION 함수명(파라미터명 파라미터_타입,...)
RETURNS 반환타입
BEGIN
#로직작성
#DECLARE 로컬변수명 변수타입; -> 변수 선언
#SELECT ~ INTO 로컬변수명; -> 조회한 내용은 변수에 저장해야 사용 가능
# SET 변수명 = 값 -> 변수에 값을 할당
RETURN 반환타입에 맞는 값 or 변수;
END;
# 예시 - 비밀번호를 마스킹 처리하는 함수
CREATE FUNCTION sf_password(password VARCHAR(255))
RETURNS VARCHAR(255)
BEGIN
RETURN
CASE
WHEN LENGTH(password) > 2 THEN CONCAT(SUBSTR(PASSWORD,1,2),'**')
ELSE '****'
END;
# 예시 - 이름 얻는 함수
CREATE FUNCTION `SP_GET_NAME`(
NAME VARCHAR(20)
, AGE INTEGER -- 파라미터 선언
) RETURNS varchar(20) -- 반환할 데이터타입
BEGIN
DECLARE AGE_TITLE VARCHAR(20);
DECLARE NAME_TITLE VARCHAR(20);
DECLARE RETURN_VALUE VARCHAR(20); -- 변수 선언
SELECT CONCAT(NAME, ' IS ')
INTO NAME_TITLE; -- 조회한 컬럼은 INTO로 변수에 넣어야 함
IF(AGE > 30) THEN
SET AGE_TITLE = 'OLD'; -- 값 할당
ELSE
SET AGE_TITLE = 'YOUNG';
END IF;
SET RETURN_VALUE = CONCAT(NAME_TITLE, AGE_TITLE);
RETURN RETURN_VALUE;
END;
프로시저
- 여러 개의 쿼리를 같이 실행시킬 수 있다
- 같이 실행시켜야하는 쿼리들을 하나로 묶어서 실행시키므로 네트워크 부하를 줄일 수 있다.
- 10개 쿼리를 네트워크로 따로 10번 보내는 것 보다 한번에 보내는게 이득이기 때문
- 미리 저장해놓기 때문에 이미 컴파일이 완료된 상태라 처리시간이 빠르다.
- call로 호출
- sp_ ~, p_~
# 문법
CREATE PROCEDURE 프로시저명()
BEGIN
# 실행시킬 쿼리문 작성...
END;
# 호출 방법
CALL 프로시저명();
# 삭제
DROP PROCEDURE 프로시저명;
트리거
- 특정 조건이 만족하면 자동으로 실행되는 것을 의미
- 보통, 특정 테이블에 INSERT, DELETE, UPDATE 같은 DML 문이 수행되었을 때 자동으로 처리해야할 작업들을 트리거로 설정해놓을 수 있다.
- 트리거는 행 트리거와 문장 트리거로 나눠진다.
- 트리거 자체가 무한루프 빠지거나 그럴 위험이 있어서 잘 생각해보고 조심히 쓰자.
행 트리거
- 조건을 만족하는 여러 개의 행에 대해 트리거를 반복적으로 여러번 수행하는 방법
- FOR EACH ROW 옵션 사용
- 변경 전 또는 변경 후의 행은 old , new라는 변수를 사용하여 읽을 수 있다.
- old : delete로 삭제된 데이터 or update로 바뀌기 전 데이터
- new : insert로 새로 삽입된 데이터 or update로 바뀐 후의 데이터
| 트리거 이벤트 |
OLD |
NEW |
| INSERT |
X |
O |
| UPDATE |
O |
O |
| DELETE |
O |
X |
문장 트리거
- 트리거가 설정된 테이블에 트리거 이벤트가 발생하면 많은 행에 대해 변경 작업이 발생하더라도 오직 한번만 트리거를 발생시키는 방법
- 컬럼값이 변화가 생길때마다 스스로 알아서 실행된다. (FOR EACH ROW 옵션 사용 안함)
기타
- 트리거가 실행되는 시기를 BEFORE , AFTER로 지정한다.
예시
# 예시
# 트리거 생성
CREATE TRIGGER tg_player_update_history
# {BEFORE | AFTER} {INSERT | UPDATE| DELETE } 중 언제 어떤 작업을 할지 정한다
# 여기서는 player 테이블을 업데이트 하기 전에 트리거 실행
BEFORE UPDATE ON player
# 영향받은 로우에 대해 실행(행 트리거로 설정)
FOR EACH ROW
BEGIN
# 변경되기 이전 이름과 포지션을 history에 삽입
INSERT INTO player_history VALUES(OLD.name,OLD.position);
END;
# 트리거 삭제
DROP TRIGGER 트리거명;
댓글