절개와 지조 5차 모각코

Stored Procedure - View보다 강력

MySQL 서버단에 저장되는 SQL 쿼리들

• CREATE PROCEDURE 사용
• DROP PROCEDURE [IF EXISTS]로 제거

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

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

간단한 분기문(if, case)과 루프(loop)를 통한 프로그램이 가능

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

정의 문법

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

호출 문법

CALL stored_procedure_name(argument_list);

IN 파라미터

DROP PROCEDURE IF EXISTS return_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 파라미터

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

값(Scalar)을 하나 리턴해주는 서버쪽 함수(특정 데이터베이스 밑에 등록됨)

• 리턴값은 Deterministic 혹은 Non Deterministic
• 현재 guest 계정으로는 test 데이터베이스 밑에 생성 가능

모든 함수의 인자는 IN 파라미터

SQL 안에서 사용가능:Stored Procedure와 가장 다른 차이점 CALL로 명시적 호출할 필요 없음

CREATE FUNCTION 사용

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$$

Trigger

CREATE TRIGGER 명령을 사용

INSERT/DELETE/UPDATE 실행 전후에 특정 작업을 수행하는 것이 가능

• 대상 테이블 지정이 필요

NEW/OLD modifier

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

CREATE TRIGGER 트리거 이름

{BEFORE | AFTER}{INSERT | UPDATE | DELETE}

ON table_naem FOR EACH ROW

tirgger_body;

중요 테이블의 경우 감사(audit)가 필요

• 레코드에 변경이 생길 때마다 변경전의 레코드를 저장하는 트리거를 만들어보자

CREATE TABLE test.keeyong_naem_gender_audit(
		name varchar(16),
		gender enum('Male','Female'),
		modified timestamp
);
--트리거 정의
CREATE TRIGGER test.before_update_keeyong_name_gender
	BEFORE UPDATE ON test.keeyoung_name_gender
	FOR EACH ROW
INSERT INTO test.keeyong_name_gender_audit
SET name = OLD.name,
	gender = OLD.gender,
	modified = NOW();