[MySQL] Stored Procedure, Function

1. Stored Procedure

Stored Procedure란 MySQL에서 제공되는 프로그래밍 기능이라고 생각하면 된다. 이것은 일반적인 프로그래밍과는 조금 차이가 있지만 MySQL 내부에서 사용하기 위해서는 아주 적절한 방식을 제공해 준다. Stored Procdedure는 Query문의 집합으로 어떠한 동작을 일괄 처리하기 위한 용도로 사용된다. 자주 사용되는 일반적인 쿼리를 모듈화 시켜서 훨씬 편리하게 MySQL을 운영할 수 있다.

 

형식 :
DELIMITER $$
CREATE PROCEDURE Stored_Procedure_Name ( IN | OUT Parameters )
BEGIN
	SQL Coding ...
END $$
DELIMITER ; ( 띄어쓰기 주의!! )

사용 :
CALL Stored_Procedure_Name();

e.g. SELECT * FROM userTbl을 Call userProc()으로 정의

 -- userProc 프로시저가 있다면 삭제
DROP PROCEDURE IF EXISTS userProc;

 -- userProc 프로시저 생성
DELIMITER $$
CREATE PROCEDURE userProc()
BEGIN
	SELECT * FROM userTbl; -- 스토어드 프로시저 내용
END $$
DELIMITER ;

 -- userProc 프로시저가 있다면 실행
CALL userProc();

Stored Procedure의 특징

• MySQL의 성능을 향상시킬 수 있다.
  긴 코드로 구현된 Query는 Client에서 Server로 모든 텍스트가 전송되어야 하지만 Stored Procedure가 생성되어 있다면 짧은 텍스트만 전송하면 되므로 네트워크 부하를 어느정도 줄일 수 있다.
• 유지관리가 간편하다.
  C#, JAVA 등의 클라이언트 응용 프로그램에서 직접 SQL문을 작성하지 않고 Stored Procedure 이름만 호출하도록 설정함으로써, Database에서 관련된 Stored Procedure의 내용을 일관되게 수정/유지보수 할 수 있다.
• 모듈식 프로그래밍이 가능하다.
  한번 Stored Procedure를 생성해 놓으면 언제든지 실행이 가능하다. Stored Procedure로 저장해 놓은 Query의 수정, 삭제 등의 관리가 수월해진다.
• 보안을 강화할 수 있다.
  사용자 별로 테이블 접근 권한을 주지 않고, Stored Procedure에만 접근 권한을 줌으로써 좀 더 보안을 강화할 수 있다.

2. Stored Function

MySQL이 사용자가 원하는 모든 함수를 제공하지는 않으므로 필요하다면 사용자가 직접 함수를 만들어서 사용할 필요가 있다. 이렇게 사용자가 직접 만들어서 사용하는 함수를 Stored Function이라고 한다. Stored Function은 Stored Procedure와 상당히 유사하지만, 형태와 사용 용도에는 약간의 차이가 있다.

 

DELIMITER $$
CREATE FUNCTION Stored_Function_Name ( Parameter )
	RETURN Return_Type
BEGIN
	Coding ...
    RETURN Return_Value;
END $$
DELIMITER ;

SELECT Stored_Function_Name();

e.g. 2개의 숫자의 합계를 계산하는 Stored Function

-- userFunc 함수가 있다면 삭제
DROP FUNCTION IF EXIST userFunc;

-- userFunc 함수 생성
DELIMITER $$
CREATE FUNTION userFunc(value1 INT, value2 INT)
	RETURN INT
BEGIN
	RETURN value1 + value2;
END $$
DELIMITER ;

-- userFunc 함수 실행
SELECT userFunc(100, 200);

Stored Procedure와 Stored Function의 차이점

• Fuction은 파라메터는 IN, OUT 등을 사용할 수 없으며 모두 입력 파라메터로 사용된다.
• Function은 반환할 값의 데이터 형식을 지정하고 본문에서 하나의 값을 반환해야 한다.
  Procedure는 별도의 반환 구문이 없으며 필요에 따라 여러 개의 OUT 파라메터를 사용하여 반환 가능하다.
• Procedure는 CALL로 호출하고 Function은 SELECT 문장 안에서 호출된다.
• Function 문장안에서는 DML(Insert/Update/Delete) 문을 사용할 수 없다. 
  꼭 필요하다면 BEGIN 상단에 'pragma autonomous_transation;'이라고 추가한다.
  
  
• Procedure는 코딩을 서버(DB)에서 하고 Function은 Client에서 한다.
  처리 속도는 Procedure가 빠르다.
• Procedure는 여러 sQL문이나 숫자 계산 등의 다양한 용도로 사용되지만, Function은 어떤 계산을 통해서 하나의 값을 반환하는데 주로 사용된다.

pragma autonomous_transation : 자율 트랜잭션, 부모 트랜잭션의 승인/롤백 여부에 상관없이 자신읠 작업을 승인/롤백 가능

DELIMITER $$ 
  문장을 구분하는 구분자가 세미콜론(;)인데 프로시저 정의 부분에 세미콜론(;)을 사용해야 하기 때문에 프로시저를 구분하는 구분자를 임시로 $$로 바꿔 준다는 이걸 잠시 $$로 바꿔줘서 중간에 정의되는 세미콜론(;)을 인식하지 않게 하기 위함이다. 그래서 마지막에 DELIMITER ; 를 해서 구분자를 세미콜론(;)으로 되돌려주어야 한다. 
프로시저를 만드는 이유는 MySQL 에디터에서는 프로시저 없이 LOOP를 실행하는 것이 불가능하기 때문이다.