MySQL 프로시저(Procedure)의 의미와 실행해보기

 

 

프로시저(Procedure)

여러 쿼리를 한번에 수행하는 것을 프로시저(Procedure) 라고 한다

 

 

우리가 SQL을 사용할때 SELECT, CREATE, UPDATE, DELETE 등 쿼리문을 사용하여 DB를 조작해

원하는 데이터를 핸들링한다

하지만 이러한 간단한 작업만 있으면 좋겠지만 현실은 그렇지않습니다

여러개의 쿼리가 하나의 로직을 수행하기 위해 묶여 돌아가는 복잡한 작업하는 경우가 흔하게 발생하는데

 

우리는 이것을 Java단에서 해결하기위해 Service 로직들을 만들어서 하나로 묶는 Transaction(트랜잭션) 작업을 하곤합니다

 

 

트랜잭션의 설명은 여기 게시물에서 확인할수있습니다

 

 

 

이외에도 DB에서는 함수(Function) 과 프로시저(Procedure) 라는 기능을 지원하는데

우리가 트랜잭션을이용해서 Java에서 여러개의 쿼리를 묶어 원자성을 보장하듯이 DB에서도 여러개의 쿼리를 묶어 수행할수있습니다

 

서버에서 API를 만들어 작업을 수행하듯이 DB에서도 메서드 비슷하게 만들어서 수행할수있는데

먼저 프로시저(Procedure)를 알아보기전에 함수(Function)란 녀석을 알아보도록 하겠습니다

 

함수(Function)은 프로시저(Procedure)와 매우 비슷한 기능을 수행하는데

절차형 SQL을 이용하여 처리를 수행하고 수행결과를 단일값으로 반환할수있는 기능입니다

해당 함수를 만들어놓게되면 우리가 메서드를 실행하듯 사용할수있습니다

 

 

CREATE FUNCTION 함수이름 
(

)

 

 

가장 처음 CREATE FUNCTION 을 이용해 함수를 만들어줘야 합니다

메서드 이름을 정의하듯이 함수이름을 정의하여 사용할수있습니다

 

 

 

CREATE FUNCTION 함수이름 
(
	파라미터이름 데이터타입,
	파라미터이름 데이터타입,
	...
)


------------------------------------- 예시

CREATE FUNCTION printAge
(
	username VARCHAR(20)
)

 

 

대괄호 안에는 파라미터의 이름과 데이터타입이 파라미터를 선언해줄수 있습니다

유저 이름을 넣으면 나이를 반환하는 Function을 만들어 보도록 하겠습니다

 

해당 파라미터 타입은 메서드와 똑같이 아래와 같이 호출하여 사용하게됩니다

 

 

SELECT printAge('홍길동')

 

 

 

 

 

CREATE FUNCTION printAge
(
	usernaem VARCHAR(20)
) RETURNS INTEGER

 

우리는 나이라는 숫자를 반환할꺼기 때문에 반환타입으로 INTEGER를 잡아주겠습니다

 

 

 

 

 

CREATE FUNCTION printAge
(
	usernaem VARCHAR(20)
) RETURNS INTEGER

BEGIN

END

 

 

그다음 이제 실행할 함수 내용을 작성하면 되는데 BEGIN ~ END 사이에 해당 SQL문들이 들어가게됩니다

여기에서는 우리가 흔히 메서드를 만들듯이 변수를 선언하고 원하는 데이터를 얻기위해 가공하는 작업을 할수있습니다

 

 

 

 

CREATE FUNCTION printAge
(
	username VARCHAR(20)
) RETURNS INTEGER

BEGIN

	SELECT age FROM usertable WHERE name = username

END

 

BEGIN ~ END 사이에는 username으로 받은 파라미터 이름을 가지고 SELECT를 실행하게됩니다

usertable에있는 age라는 칼럼을 검색하게되는데 name = username과 같은값을 조회하게됩니다

 

여기서 조회한 값을 넣어주는 작업이 필요한데 DECLARE를 이용해 변수를 선언해줄수있습니다

 

 

 

 

 

CREATE FUNCTION printAge
(
	username VARCHAR(20)
) RETURNS INTEGER

BEGIN
	
    DECLARE AGE INTEGER;
    
	SELECT age FROM usertable WHERE name = username
		INTO AGE;

END

 

 

DECLARE 변수선언을 통해 AGE라는 이름을 가지고 INTEGER 타입으로 만들어주고

SELECT 아래에 INTO를 통해 변수에 넣어주는 작업을 하게됩니다

 

그러면 AGE라는 값안에는 파라미터로 받은 유저의 나이가 담기게됩니다

 

 

CREATE FUNCTION printAge
(
	username VARCHAR(20)
) RETURNS INTEGER

BEGIN
	
    DECLARE AGE INTEGER;
    
	SELECT age FROM usertable WHERE name = username
		INTO AGE;
        
	RETURN AGE;

END

 

마지막에 RETURN AGE를 하게되면 해당 함수를 실행할시 유저의 나이를 반환하게 됩니다

 

 

 

SELECT printAge('내이름');

 

 

 

여기서만약 조건문을 넣고싶을땐 IF THEN SET을 사용해서 값을 할당해주면됩니다

 

 

 

CREATE FUNCTION printAge
(
	username VARCHAR(20)
) RETURNS INTEGER

BEGIN
	
	DECLARE AGE INTEGER;
	DECLARE AGE_TITLE VARCHAR(10);
    
	SELECT age FROM usertable WHERE name = username
		INTO AGE;
        
	IF (AGE > 50) THEN
		SET AGE_TITLE = 'OLD';
	ELSE
		SET AGE_TITLE = 'YOUNG';
 	END IF;
        
	RETURN AGE;

 

 

SELECT 하여 얻은 유저나이를 IF THEN을 이용하여 50살 이상일경우 OLD, 그외에는 YOUNG이라는 값을 AGE_TITLE에 담게됩니다

 

여기서 Function에 특징이 나오게되는데 바로 하나의 값만 Return할수있습니다

우리는 AGE, AGE_TITLE 2개의 값을 반환받고싶은데 Function으로는 하나의 값만 받을수 있다는 것입니다

 

이때 필요한것이 바로 프로시저(Procedure)로써 하나의 값만 반환하는데 반해 여러개의 값을 반환할수있습니다

사용방법은 함수(Function)과 비슷하면서도 살짝 다릅니다

 

 

 

 

 

DELIMITER $$



DELIMITER ;

 

 

스토어드 프로시저의 형식은 위와같습니다 프로시저를 선언하겠다는 의미로써 이제 위에서 배운 함수와 비슷하게 구조를 가져가면 됩니다

 

CREATE로 만들어주고 BEGIN END를 사용하면됩니다

 

 

 

 

DELIMITER $$

CRTEATE PROCEDURE 프로시저명
(
)

BEGIN

END $$


DELIMITER ;

 

END 뒤에 $$를 붙히는것은 프로시저의 시작과 끝을 명시해주기 위해서 $$붙여 프로시저의 범위를 명시하고 바로아래 DELIMITER ;로 완전하게 종료해줍니다

 

 

 

DELIMITER $$

CRTEATE PROCEDURE 프로시저명
(
	IN username VARCHAR(20),
	OUT age INTEGER,
	OUT region VARCHAR(10),
	OUT gender VARCHAR(5)
)

BEGIN

END $$


DELIMITER ;

 

파라미터 선언부분에 IN, OUT을 통해 받을 파라미터값과 출력할 값을 선언할수있는데

이름을 넣으면 나이, 지역, 성별을 반환하는 프로시저를 만들어보겠습니다

 

함수(Function)와 똑같이 만들면 되지만 반환하는 부분에서는 Return하지않고 Set을 이용해줍니다

 

 

 

DELIMITER $$

CRTEATE PROCEDURE 프로시저명
(
	IN username VARCHAR(20),
	OUT age INTEGER,
	OUT region VARCHAR(10),
	OUT gender VARCHAR(5)
)

BEGIN

	SELECT age FROM usertable WHERE name = username
		SET age;
        
	SELECT region FROM usertable WHERE name = username
		SET region;
        
	SELECT age FROM usertable WHERE name = username
		SET gender;



END $$
DELIMITER ;

 

SELECT 한값을 각각 age, region, gender에 SET해주게 되면 username을 입력시 3개의 값을 반환받을수 있게됩니다

 

CALL 프로시저명('홍길동');

// age = 20, region = seoul, gender = 남

 

 

추가적으로 사용할수 있는 문법은 다음과같습니다

 

 

프로시저 목록 확인

SHOW PROCEDURE STATUS;

 

 

프로시저 내용 확인

SHOW CREATE PROCEDUTE 프로시저이름;

 

 

프로시저 삭제

DROP PROCEDURE 프로시저이름;

 

 

 

마지막으로 Function, Procedure에 특징에 대해서 비교해보도록 하겠습니다

 

	특징
Function	클라이언트에서 처리한다
	반환값이 필수이다
	반환값을 하나만 반환할수있다
Procedure	서버로 보내서 처리한다
	반환값이 필수가 아니다
	여러개의 값을 반환할수있다
공통점	하나의 요청으로 여러개의 SQL문을 실행할수있다
	재사용성 면에서는 좋지않다 (여러개의 담당하는 쿼리가 묶여있기 때문에)
	유지보수에 좋다
	DB관련 처리를 API처럼 사용하여 업무와 구분하여 개발 할수도있다