Mysql - CREATE PROCEDURE

 

 Mysql  에서는 CREATE PROCEDURE  혹은 CREATE FUNCTION 을 저장 루틴(stored routine)이라고 표현합니다.

 

Mysql 문서를 보면 많이 복잡하지만 많이 사용하는 방식은 아래와 같습니다.

 

 

USE database_name;
DROP PROCEDURE IF EXISTS procedure_name;

DELIMITER $$
CREATE PROCEDURE procedure_name (
	IN	i_param_1	bigint,
	IN	i_param_2	varchar(10),
	IN	i_param_3	datetime
)
COMMENT 'procedure_comment'
BEGIN

	PROCEDURE_BODY:
	BEGIN
		
		SELECT '처리할 명령';
	
	END PROCEDURE_BODY;
END$$
DELIMITER ;

 

DELIMITER $$  는 문장의 끝을 세미콜론(;)으로 정의하는데 프로시저 안에서도 문장의 끝을 세미콜론으로 사용하기 때문에 $$ 로 임시로 바꾸고 프로시저를 만든 다음에 다시 세미콜론으로 바꿔주게 됩니다.

 

db_work 라는 DB 에  procedre 를 생성하고 호출을 해봅니다.

 

USE db_work;
DROP PROCEDURE IF EXISTS sp_procedure_test;

DELIMITER $$
CREATE PROCEDURE sp_procedure_test (
	IN	i_param_1	bigint,
	IN	i_param_2	varchar(10),
	IN	i_param_3	datetime
)
COMMENT 'procedure_comment'
BEGIN

	PROCEDURE_BODY:
	BEGIN
		
		SELECT CONCAT(i_param_1, ' 은 bigint,', i_param_2, ' 는 varchar(10), ', i_param_3, '는 datetime 입니다.') AS select_string;
	
	END PROCEDURE_BODY;
END$$
DELIMITER ;

 

실행은  CALL 에 인풋 파라미터 3개를 넣어 줍니다.

 

CALL sp_procedure_test(123,'abc','2000-01-01 00:00:00');

 

결과가 이쁘게 나옵니다.

 

 

소스의 SELECT CONCAT 자리에 반복문 혹은 계산식등 처리될 일련의 명령어가 들어가면 인풋 파라미터만 변경하면 쉽게 처리 됩니다.

 

SELECT  하지 않고 OUTPUT  변수로 리턴을 받아 재사용을 해야 하면 아래와 같이 변경하면 됩니다.

 

USE db_work;
DROP PROCEDURE IF EXISTS sp_procedure_test;

DELIMITER $$
CREATE PROCEDURE sp_procedure_test (
	IN	i_param_1	bigint,
	IN	i_param_2	varchar(10),
	IN	i_param_3	datetime,
	OUT o_output	varchar(100)
)
COMMENT 'procedure_comment'
BEGIN

	PROCEDURE_BODY:
	BEGIN
		
		SET o_output = CONCAT(i_param_1, ' 은 bigint,', i_param_2, ' 는 varchar(10), ', i_param_3, '는 datetime 입니다.');
	
	END PROCEDURE_BODY;
END$$
DELIMITER;

 

다른점은 OUT 이라는 output 파라미터가 추가됐고 SELECT 대신 SET 을 사용합니다.

AS select_string 도 삭제해야 에러가 없습니다.

 

SELECT 를 하지 않고 OUTPUT 파라미터로 변경해서 리턴하기 때문에 프로시저 호출 후 SELECT 를 해야 합니다.

 

CALL sp_procedure_test(123,'abc','2000-01-01 00:00:00',@o_output);
SELECT @o_output;

 

그러면 @o_output 으로 똑같은 결과가 리턴됩니다.

 

 

사용하는 방식에 따라서 필요하게 만들면 됩니다.

 

 

https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html

MySQL :: MySQL 8.0 Reference Manual :: 13.1.17 CREATE PROCEDURE and CREATE FUNCTION Statements