[영남이공대학교 사이버보안과] MySQL의 저장 프로시저(Stored Procedure), 저장 함수(Stored Function), 트리거(Trigger)

저장 프로시저(Stored Procedure)    MySQL 5.0이상

여러 줄의 SQL 문을 하나의 SQL 문처럼 정리하여 CALL 명령으로 실행할 수 있다.

CREATE PROCEDURE

CREATE PROCEDURE 'name()'

BEGIN

SELECT ...

UPDATE ...

...

END

BEGIN ~ END 까지 저장 프로시저의 본체이다.

저장 프로시저의 본체를 작성하기 전에 구분 문자(기본값 ; )를 변경하는 작업이 필요하다.

구분 문자를 변경하지 않으면 본체에서 에러가 일어나 다시 작성해야하는 무한 루프를...

DELIMITER <구분 문자>

대부분 // 을 많이쓴다. ex) DELIMITER //

프로시저를 입력 후에는 구분문자를 다시 기본값으로 변경하면 된다.

RUN PROCEDURE

PROCEDURE <procedure_name> (<name> <data_type>);

ex) PROCEDURE fox(name char);

SHOW PROCEDURE

SHOW CREATE PROCEDURE <name>;

DROP PROCEDURE

DROP PROCEDURE <name>;

###########################################

저장 함수

프로시저와 비슷한 형태이지만 실행하면 값을 RETURN 한다. 사용자 정의함수 이기도 하다.

CREATE FUNCTION

저장함수 또한 DELIMITER 로 구분문자를 변경해야한다.

CREATE FUNCTION <f_name> (<name> <data_type>) RETURNS <return_type>

BEGIN

SELECT ...

RETURN <return_value_syntax>

END

RUN FUNCTION

SELECT <f_name> (value);

- 변수 선언 "DECLARE <name> <data_type>"

DROP FUNCTION

DROP FUNCTION <f_name>;

SHOW FUNCTION

SHOW CREATE FUNCTION <f_name>;

###########################################

TRIGGER (트리거)

테이블에 어떤 설정을 하면 자동으로 실행된다.

BEFORE  |  처리를 하기 전에 실행된다.

AFTER    |  처리 후에 실행된다.

OLD.COLUMN    |   처리를 하기 전의 column 값

NEW.COLUMN   |   처리 후의 column 값

명령	실행 전	실행 후
INSERT	X	O
DELETE	O	X
UPDATE	O	O

CREATE TRIGGER

DELIMITER //

CREATE TRIGGER <tri_name> <BEFORE or AFTER> <SQL_syntax>

BEGIN

<SQL_syntax  OLD.COLUMN or NEW.COLUMN >

END //

DELIMITER ;

본체 내의 SQL_syntax의 예제는 table1이 삭제되기 전에

INSERT INTO table1_backup VALUES(OLD.id, OLD.name, OLD.email);

DROP TRIGGER

DROP TRIGGER <tri_name>;

SHOW TRIGGER

SHOW TRIGGER <tri_name>;