[DB] 스토어드 함수, Trigger

« 2025/02 »
일	월	화	수	목	금	토
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28

[목차]

01. 스토어드 함수

💡

사용자가 직접 함수를 만들어서 사용한다.

내장 함수(Built-in Function)가 사용자를 만족하는 모든 함수를 제공하지 않는다. (필요에 의해 사용자가 직접 함수를 만들어 사용한다.)

형태와 사용 용도에서 스토어드 프로시저와 차이가 있다.

단점
👉🏻
유지보수 복잡성 증가

➡️ 각 기능을 담당하는 프로그램 코드가 자바와 MySQL 스토어드 프로그램으로 분산되어 관리되기 때문에 애플리케이션의 설치나 배포가 복잡해진다.

프로시저와 함수의 차이
👉🏻
함수는 OUT 파라미터를 사용할 수 없다.
함수의 파라미터는 모두 입력 파라미터로 사용된다.
함수는 RETURNS 예약어를 통해 반환할 값의 데이터 형식을 지정하고, 본문 안에서 RETURN문으로 하나의 값을 반환해야 한다.
함수는 어떤 계산을 통해 하나의 값을 반환하는데 주로 사용한다.

프로시저 ➡️ CALL로 호출하는 독립적인 프로그램
함수 ➡️ SELECT문 안에서 호출. (값을 제공하는 용도로 사용.)

프로시저 ➡️ SELECT문을 사용
함수 ➡️ 집합 결과를 반환하는 SELECT문을 사용할 수 없다. (단, 하나의 결과만 반환하는 SELECT는 사용 가능.)

정의 및 호출
👉🏻
CREATE FUNCTION 스토어드_함수_이름(매개변수)
RETURNS문으로 반환할 값의 데이터 형식 지정

BEGIN
프로그래밍 코드
RETURN 반환값;
END $$

SELECT 스토어드_함수_이름();
➡️ 스토어드 함수는 SELECT문 안에서 호출.

삭제
DROP FUNCTION 스토어드_함수_이름;

생성 권한 허용
SET GLOBAL log_bin_trust_function_creators = 1;

예시

DELIMITER $$
CREATE FUNCTION sf_sum(num1 INT, num2 INT) -- 2개의 정수형 매개변수
-- 함수가 반환할 데이터의 형식 지정
RETURNS INT

BEGIN

	DECLARE total INT;
	SET tatal := num1 + num2;
	RETURN tatal; -- return문으로 결과 반환

END $$
DELIMITER ;

-- 함수 호출
SELECT sf_sum(10, 30);

만 나이를 구하는 함수

DROP FUNCTION IF EXISTS sf_get_mem_age;

DELIMITER $$
CREATE FUNCTION sf_get_mem_age(bYear INT)
RETURNS INT

BEGIN

	DECLARE age INT;
	SET age := YEAR(CURDATE()) - bYear;
	-- CURDATE는 yyyy-mm-dd로 출력
	-- YEAR은 날짜에서 연도만 추출하는 함수
	RETURN age;

END $$
DELIMITER ;

-- 함수 실행
SELECT sf_get_mem_age(1990) AS '만나이';

-- 출생 연도 데이터는 있고, 회원 테이블 회원 나이는 없을 때 함수 사용
SELECT
	u.u_id,
	u.u_name,
	sf_get_mem_age(u.u_birth) AS '나이'
FROM
	tb_user AS u;

02. Trigger

💡

해당 테이블에 어떤 이벤트(데이터 변경문)가 발생하면 자동으로 실행된다.

트리거 ➡️ 테이블에 부착한다.

이벤트 ➡️ 테이블에 데이터 삽입, 수정, 삭제가 발생하면 해당 테이블에 부착되어 있는 트리거가 자동으로 실행. (INSERT, UPDATE, DELETE)

트리거 타임(발동 시점) ➡️ AFTER, BEFORE

특징
👉🏻
1) 프로시저와 다르게 직접 실행할 수 없다.
2) 해당 테이블에 이벤트(DML)가 발생하면, 자동 실행된다.
3) IN, OUT 매개변수를 사용할 수 없다.
4) 이벤트 발생 후, ROLLBACK할 수 없다.

사용 권한 허용
SET GLOBAL log_bin_trust_function_creators = 1;

형식
👉🏻
CREATE TRIGGER 트리거_이름 ➡️ 트리거 이름
트리거_타임 ➡️ AFTER, BEFORE
트리거_이벤트 ➡️ INSERT, UPDATE, DELETE
(AFTER INSERT ➡️ INSERT 후 발동)
ON 테이블_이름 ➡️ 트리거를 부착할 테이블
FOR EACH ROW ➡️ 각 행마다 적용. 트리거 사용하면 항상 작성.

BEGIN
트리거 명령문
(SET @msg = ‘테이블에 회원 정보가 등록됨.’)
END

삭제
DROP TRIGGER 트리거_이름;

예시

➡️ AFTER DELETE TRIGGER

member table 컬럼 3개만 가지고 테이블 복사

백업 → 탈퇴 시 데이터가 저장되는 테이블 생성

트리거 생성

복사한 테이블 DELETE

백업 테이블 데이터 조회

-- 1. 컬럼 3개 테이블 복사
-- CREATE ~ (SELECT)
-- 테이블을 복사해서 새로운 테이블을 만드는데, 기본키에 대한 정의는 복사 되지 않는다.
CREATE TABLE tb_member2
(
	SELECT
		m.m_id,
		m.m_name,
		m.m_addr
	FROM
		tb_member AS m
);

-- 2. 백업 테이블 생성
CREATE TABLE member2_backup
(
	mem_id VARCHAR(50) NOT NULL,
	mem_name VARCHAR(50) NOT NULL,
	mem_addr VARCHAR(50) NOT NULL,
	mod_type CHAR(2), -- 변경 타입 '수정' 또는 '삭제'
	mod_date DATE, -- 변경 일자
	mod_user VARCHAR(50) -- 변경한 사용자
);

-- 3. AFTER DELETE TRIGGER 생성
DROP TRIGGER IF EXISTS member_delete_trigger;

DELIMITER $$
CREATE TRIGGER member_delete_trigger -- 트리거 이름
AFTER DELETE -- 트리거 타임, 트리거 이벤트
ON tb_member2 -- 트리거를 부착할 테이블
FOR EACH ROW

BEGIN
	-- 기존 member2 테이블의 데이터를 입력
	INSERT INTO member2_backup
	VALUES (OLD.m_id, OLD.m_name, OLD.m_addr, '삭제', CURDATE(), CURRENT_USER());

END $$
DELIMITER ;

-- 4. 데이터 삭제
DELETE
FROM
	tb_member AS m
WHERE
	m.m_id = 'id009';

--5. 백업 테이블 조회
SELECT
	*
FROM
	member_backup;

📖

OLD, NEW

테이블에 INSERT, UPDATE, DELETE 작업이 수행되면, 임시로 사용되는 테이블

OLD.m_id → 사용자가 직접 생성하는 테이블이 아닌 MySQL에서 알아서 생성하고 관리해주는 테이블.

NEW 테이블

➡️ 테이블에 새로운 값이 들어가는 경우(INSERT, UPDATE)

OLD 테이블

➡️ 예전 값 DELETE 작동 (또는 UPDATE)

➡️ 삭제 전 예전 값이 OLD테이블에 잠시 저장.

tag : #데이터베이스 #DB #스토어드함수 #트리거 #생성권한허용

Uploaded by N2T

[DB] 트랜잭션 (0)	2023.06.18
[DB] SELECT ~ INTO, CASE ~ END, WHILE (0)	2023.06.11
[DB] Stored Program(프로시저) (1)	2023.06.11
[DB] 데이터베이스 설계, 정규화 (0)	2023.06.06
[DB] 서브쿼리(스칼라, 인라인뷰, 중첩질의), UNION (0)	2023.06.06

멋쟁이v의 개발일지

멋쟁이v의 개발일지