Procedure

Stored Procedure

• MySQL 서버단에 저장이 되는 SQL 쿼리들
• CREATE PROCEDURE
• DROP PROCEDURE [IF EXISTS]

• 프로그래밍 언어의 함수처럼 인자(IN, OUT, INOUT)를 넘기는 것이 가능

• 리턴되는 값은 레코드들의 집합(SELECT와 동일)

• 간단한 분기분(If, else)과 루프(loop)를 통한 프로그램이 가능

• 디버깅이 힘들고 서버 단의 부하를 증가 시킨다는 단점이 존재

DELIMITER // -- 처음에 DELIMITER 설정해 주고 END 다음에 써주어야 함
CREATE PROCEDURE procedure_name(parameter_list)
BEGIN
	statements;
END //
DELIMITER;

IN 파라미터

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

CALL return_session_details('Facebook');

INOUT 파라미터

DELIMITER //
CREATE PROCEDURE return_session_count(
		IN channelName varchar(64),
		IN totalRecord int)
BEGIN
	SELECT COUNT(1) INTO totalRecord FROM geuno_name_details
	WHERE channel = channelName;
END //
DELIMITER;

SET @facebook_count = 0 -- MySql에서의 변수는 @
CALL return_session_count('Facebook', @facebookc_count);
SELECT @facebook_count;

• 해당 파라미터에 값을 넣어서 리턴해줌

Stored Function

• Stored Procedure는 record들을 반환할 수 있고 인자를 통해서도 값을 return 할 수 있음

• CREATE FUNCTION을 통해서 생성 가능함

Stored Procedure와의 차이점

• Stored Function은 이에 반해 record가 아닌 값을 하나 리턴 해주는 함수임(특정 데이터베이스 밑에 등록됨)
• 리턴 값은 Deterministic 혹은 Non Deterministic

• 모든 함수의 인자는 in 파라미터 (inout, out 지원 x)

• SQL 안에서 사용가능 : Stored Procedure와 가장 다른 차이점임. Procedure는 CALL을 해야 하는데.

DELIMITER $$
CREATE FUNCTION test.Channel_Type(channel varchar(32))
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
	DECLARE channel_type VARCHAR(20);
	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;

• Stored Procedure와 Function 에서 변수 지정할 때는 DECLARE 변수이름 변수타입 이런 식으로 지정함

Trigger

• CREATE TRIGGER 명령을 사용

• INSERT/DELETE/UPDATE 실행 전후에 특정 작업을 수행하는 것이 가능
• 대상 테이블 지정이 필요

• NEW/OLD modifier
• NEW는 INSERT와 UPDATE에서만 사용가능
• OLD는 DELETE와 UPDATE에서만 사용가능

CREATE TRIGGER 트리거이름
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name FOR EACH ROW
trigger_body;

• 중요 테이블의 경우 감사(audit)가 필요
• 레코드에 변경이 생길 때마다 변경 전의 레코드를 저장하는 트리거를 만들어보자

CREATE TABLE geuno_name_gender_audit (
	name varchar(16),
	gender enum('Male', 'Female'),
	modified timestamp
);

CREATE TRIGGER before_update_geuno_name_gender
	BEFORE UPDATE ON geuno_name_gender
	FOR EACH ROW
INSERT INTO geuno_name_gender_audit
SET name = OLD.name; -- UPDATE되기 전의 현재 값
		gender = OLD.gender, -- 새로 쓰여지는 값은 NEW로 접근가능
		modified = NOW();

-- 트리거 사용 예
UPDATE geuno_name_gender
SET name = 'Keeyong'
WHERE name = 'Keeyong2';

SELECT * FROM geuno_name_gender_audit;