5-3) MySQL 고급기능(Stored Procedure, Stored Function)

Stored Procedure란?(View와 비슷하지만 더 강력한 것)

• MySQL 서버단에 저장되는 SQL 쿼리들
  • CREATE PROCEDURE 사용
  • DROP PROCEDURE [IF EXISTS]로 제거
• 프로그래밍 언어의 함수처럼 인자를 넘기는 것이 가능(IN, OUT, INOUT 존재)
• 리턴값 : 레코드들의 집합 (SELECT와 동일)
• 간단한 분기문(if, case)과 루프(loop)를 통한 프로그램이 가능
• 단점 : 디버깅이 힘들고 서버단의 부하를 증가

[정의 문법]

• DELIMITER과 END뒤에 //나 $써야함

DELIMITER //
CREATE PROCEDURE procedure_name(parameter_list)
BEGIN
statements;
END //
DELIMITER ;

[정의 예]

• BDGIN과 END사이의 SELECT결과가 리턴

DELIMITER //
CREATE PROCEDURE return_session_details()
BEGIN
SELECT *
FROM test.keeyong_session_details;
END //
DELIMITER;

[호출 문법]

CALL stored_procedure_name(argument_list);

[호출 예]

CALL return_session_details();

[IN 파라미터]

• IN 파라미터 사용 → 입력을 받겠다는 것
• 입력으로 받은 channelName과 같은 channel의 내용만 리턴 → 채널이름이 Facebook인 것만 리턴

DROP PROCEDURE IF EXISTS return_session_details;
DELIMITER //
CREATE PROCEDURE return_session_details(IN channelName varchar(64))
BEGIN
	SELECT *
	FROM test.keeyong_session_details
	WHERE channel = channelName;
END //
DELIMITER ;

CALL return_session_details('Facebook');

[INOUT 파라미터]

• 그냥 리턴하는 것이 아니라 totalRecord 파라미터에 담아서 리턴
• 주어진 채널이름을 가진 레코드들이 몇개인지 리턴

DROP PROCEDURE IF EXISTS return_session_count;
DELIMITER //
CREATE PROCEDURE return_session_count(IN channelName varchar(64), INOUT
totalRecord int)
BEGIN
	SELECT COUNT(1) INTO totalRecord FROM test.keeyong_session_details
	WHERE channel = channelName;
END //
DELIMITER ;

SET @facebook_count = 0
CALL return_session_count('Facebook', @facebook_count);
SELECT @facebook_count;

---

Stored Function이란?

• Stored Precedure - 레코드를 리턴, IN/OUT/INOUT 파라미터, CALL로 호출
• Stored Function - 값(Scalar)을 하나 리턴, 모든 함수의 인자는 IN 파라미터 → 읽기 전용, 호출함수 없이 SQL문에서 바로 사용 가능
  • 리턴값은 Deterministic(같은 입력에서는 같은 출력) 혹은 Non Deterministic(입력이 같아도 출력이 다름→ 랜덤함수)
• 서버쪽 함수 (특정 데이터베이스 밑에 등록됨)
• CREATE FUNCTION 사용

[정의 예]

DELIMITER $$
CREATE FUNCTION test.Channel_Type(channel varchar(32))
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
	DECLARE channel_type VARCHAR(20); --> Stored Procedure나 Stored Function 내에서 변수 선언은 DECLARE
	
	IF channel in ('Facebook', 'Instagram', 'Tiktok') THEN
		SET channel_type = 'Social Network';
	ELSEIF channel in ('Google', 'Naver') THEN
		SET channel_type = 'Search Engine';
	ELSE
		SET channel_type = channel;
	END IF;
	-- return the customer level
	RETURN (channel_type);
END$$

[호출 예]

SELECT channel, test.Channel_Type(channel)
FROM prod.channel;