[MySQL] Stored Procedure/Function/Trigger

Stored Procedure

Stored Procedure
DROP PROCEDURE IF EXISTS return_ryu;
DELIMITER //
CREATE PROCEDURE return_ryu
BEGIN
	SELECT *
    FROM ryu;
END //
DELIMITER ;

-- 띄어쓰기 중요하고 //는 $$도 상관없음

 

IN 파라미터 (입력 전용)

DELIMITER //
CREATE PROCEDURE return_ryu(IN channelName varchar(64))
BEGIN
	SELECT *
    FROM ryu
	WHERE channel = channelName;
END //
DELIMITER ;
CALL return_ryu('Facebook');

-- 데이터를 직접 집어넣어서 프로시저를 돌릴 수 있음

 

INOUT 파라미터 ( INTO 앞의 값을 변수에 대입해서 리턴)

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

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

-- 즉 count(1)의 값을 totalRecord에 적용

 

 

 

Stored Function (값을 하나 리턴해주는 함수)

• 리턴 값은 Deterministic or Non Deterministic
• 모든 함수의 인자는 IN 파라미터
• SQL 안에서 사용가능

DELIMITER $$

CREATE FUNCTION ryu(
	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, ryu(channel)
FROM prod.channel;
*/

 

 

Trigger

• INSERT/DELETE/UPDATE 실행 전 후에 특정 작업을 수행
• NEW / OLD modifier
  • NEW : INSERT와 UPDATE에서만 사용가능
  • OLD : DELETE와 UPDATE에서만 사용가능

CREATE TABLE trigger_test (
	name varchar(16),
	gender enum('Male', 'Female'),
	modified timestamp
);
CREATE TRIGGER ryu
	BEFORE UPDATE ON trigger_test
	FOR EACH ROW
	INSERT INTO trigger_test
	SET name = OLD.name,
		gender = OLD.gender,
		modified = NOW();

UPDATE trigger_test
SET name = 'ryu'
WHERE name = 'ryu2';

SELECT * FROM trigger_test;