저장 프로시저

저장 프로시저는 MySQL 5.0 이상 버전부터 사용가능하다.

 

여러 sql문을 하나의 sql문처럼 정리하여 call 명령으로 실행할 수 있게 만든 것.

=> 데이터베이스 내에 미리 컴파일되어 저장된 sql 코드

=> 사용자 프로그램에서 호출하거나, 다른 저장 프로시저에서 호출가능

=> 결과를 서버에 저장, 컴파일 하고 캐쉬에 저장한뒤 실행함, 

장점

=> 데이터베이스 내에서 sql 명령을 컴파일할때 캐시를 이용할 수 있어 처리가 빠름

=> 어플리케이션마다 복수의 SQL문 기술할 필요 없이 만들어진 저장 프로시저를 사용하면 다른 어플리케이션을 수정하여 컴파일 할 필요 없음.

=> 저장 프로시저는 만들어지는 순간에 구문이 검사됨, 따라서 DBA의 에러를 감소시킬 수 있음(실행전 검사)

=> SQL문을 직접 실행시킬 수 없는 사용자들도 저장 프로시저만 실행시킬 수 있는 권한을 가지게 할 수 있다.

 

단점

=> 접하기 어렵다.

=> DBMS 제품마다 문법이 다르다(비표준화)

=> 저장프로시저를 남발하는 경우 유지보수가 어렵다.

 

함수와의 차이점

저장프로시저 : 일반적으로 return 값이 없는 프로그램, CALL에 의해서 호출

함수 : return 값이 있는 프로그램, MAX(), min()과 같이 SQL문 안에서 사용됨

 

- 저장 프로시저 생성

CREATE PROCEDURE 저장프로시저이름()
BEGIN 
	SQL문 1;
    	SQL문 2;
END

BEGIN ~ END 사이에 원하는 SQL문들을 작성하면 된다.

 

** 구분문자 문제

위와 같이 작성시 BEGIN ~ END안에서 SQL문이 작성이 덜 되었더라도 ;를 만나게되면 CREATE PROCEDURE 을 실행하게되어 완성되지 않은 상태로 저장프로시저가 생성된다.

CREATE PROCEDURE 저장프로시저이름(인수이름 자료형)
BEGIN 
	SQL문 1;
    	SQL문 2
END

와 같이 될 경우 MySQL 콘솔창은 ;이 입력되면 ;이전단계까지의 명령문을 실행하게 되기때문이다.

 

따라서 

저장 프로시저에서 END를 입력하고난 뒤 CREATE PROCEFURE 명령이 실행되게 해야한다.

 DELIMITER를 사용하여 구분문자를 ;대신 다른 문자로 변경한다.

EX) DELIMITER // 혹은 DELIMITER =

와 같이원하는 문자를 적으면 된다.

 

DELIMITER //                  =>구분 문자를 ;에서 //로 변경
CREATE PROCEDURE pr1()
BEGIN
	SELECT * FROM tb;
    	SELECT * FROM tb1;
END
//                            =>//구분문자를 만났으므로 이전까지 입력된 명령을 수행
DELIMITER ;                   => 구분문자를 다시 ;로 변경

 

 

- 저장 프로시저 실행

CALL 저장프로시저이름;

ex) CALL pr1();를 실행하면

자동으로 SELECT * FROM tb;와 SELECT * FROM tb1;가 실행된다.

 

- 저장 프로시저 인수 사용

PROCEDURE 저장프로시저이름(인수이름 자료형);

예를 들어 sales가 200 이상인 값을 보려고 할때

select * from tb where sales>=d;와 같이 설정하면

DELIMITER //                 
CREATE PROCEDURE pr1(d INT)
BEGIN
	SELECT * FROM tb WHERE sales>=d;
END
//                            
DELIMITER ;                  

d를 인자로 받아들여 pr1(200)을 실행하면 

select * from tb where sales>=200;이 된다.

 

 

- 작성된 저장 프로시저 내용 표시

SHOW CREATE PROCEDURE 저장프로시저이름;

ex) SHOW CREATE PROCEDURE pr1;

pr1의 프로시저 내용을 볼 수 있게 된다.

 

- 저장 프로시저 삭제

DROP PROCEDURE 저장프로시저이름;

 

 

- 저장 함수

저장 프로시저와 유사하지만, 실행했을 때 값을 반환한다.

CREATE FUNCTION 저장함수이름(인수이름 자료형) RETURNS 반환값자료형
BEGIN
	SQL문 ....
    	RETURN 반환값식
END

 

* DECLARE

저장 함수에서 변수를 사용하려면 DECLARE로 정의해야한다.

DECLARE 변수이름 자료형;

예시를 보면

CRETAE FUNCTION func() RETURNS DOUBLE          => 함수의 반환형은 double
BEGIN
	DECLARE r DOUBLE;                      => 변수 r 선언
    	SELECT AVG(sales) INTO r FROM tb;      => AVG(sales)값을 r에 저장
       	RETURN 	r;                             => r을 반환
END

 

- 저장함수 삭제

DROP FUNCTION 저장함수이름;

 

- 저장함수 내용 표시

SHOW CREATE FUNCTION 저장함수이름;

저장 함수의 내용을 볼 수 있게된다.

 

 

- 트리거

테이블에 대해 어떤 처리를 실행하면 이에 반응하여 설정한 명령이 자동으로 실행되는 구조

INSERT, UPDATE, DELETE 등 명령이 실행될 때, 트리거로 설정한 명령이 자동으로 실행되게 할 수 있다.

EX) 테이블의 레코드 변경시, 변경한 내용을 다른 테이블에 기록하도록 트리거를 만들 수 있음

따라서 트리거는 처리를 기록하거나, 처리가 실패할 경우를 대비하여 만들어 놓으면 좋다.

 

트리거는 INSERT, UPDATE, DELETE 등 명령이 실행되기 직전(BEFORE) 또는 직후(AFTER)에 호출되어 실행된다.

 

즉, 어떤 데이터를 처리하기 전에 호출되거나 어떤 데이터를 처리한 후에 호출된다.

 

또한 테이블에서 어떤 데이터를 처리하기 전의 값은 OLD.칼럼이름.

처리한 후의 값은 NEW.칼럼이름으로 얻을 수 있다.(추출할 수 있다.)

 

명령에 따라서 칼럼 값을 추출할 수도 있고 없을 수도 있다.

명령	실행전(old.칼럼이름)	실행후(new.칼럼이름)
insert	old.칼럼이름 추출 불가	가능
delete	가능	new.칼럼이름 추출불가
update	가능	가능

 

 

- 트리거 생성

CREATE TRIGGER 트리거이름 BEFORE(또는 AFTER) DELETE(UPDATE,INSERT 등과 같은 명령)
ON 테이블 이름 FOR EACH ROW
BEGIN
	변경전(OLD.칼럼이름)을 이용한 처리                      ====> 또는 변경후(NEW.칼럼이름)
END

 

 

EX) tb1에서 삭제한 레코드를 tb1m에 삽입하는 트리거 작성

DELIMITER //
CREATE TRIGGER tr1 BEFORE DELETE ON tb1 FOR EACH ROW                  
===> delete에 반응, 삭제하기 직전의 값을 넣으므로 before, for each row 각 행에 대해 수행
BEGIN
	INSERT INTO tb1m VALUES(OLD.number, OLD.name, OLD.age);
END
//
DELIMIER ;

tb1에서 레코드가 삭제될때, tb1m에 삭제된 레코드가 입력되게 된다.

DELETE FROM tb1;을 하여 모든 레코드를 삭제하더라도 tb1m에 삭제된 레코드들이 저장되게 된다.

따라서  INSERT INTO tb1 SELECT * FROM tb1m;과 같이 다시 복원을 할 수도 있다.

 

 

- 트리거 확인

SHOW TRIGGERS;

 

- 트리거 삭제

DROP TRIGGER 트리거이름;