[혼공S] 6주차 - 프로시저, 트리거

기본 미션: p.363 market_db의 고객 테이블(member)에 입력된 회원의 정보가 변경될 때 변경한 사용자, 시간, 변경 전의 데이터 등을 기록하는 트리거 작성하기

USE market_db;

CREATE TABLE backup_member
( mem_id        CHAR(8) NOT NULL,
  mem_name        VARCHAR(10) NOT NULL,
  mem_number    INT NOT NULL,
  addr            CHAR(2) NOT NULL,
  modUser        VARCHAR(30),
  modDate        DATE,
);

DELIMITER S$
CREATE TRIGGER member_updateTrg
    AFTER UPDATE
    ON member
    FOR EACH ROW
BEGIN
    INSERT INTO backup_member VALUES( OLD.mem_id, OLD.mem_name,
        OLD.mem_number, OLD.addr, CURRENT_USER(), CURDATE()  );
END $$
DELIMITER ;

SELECT * FROM backup_member;

스토어드 프로시저

개념과 형식

• MySQL에서 제공하는 프로그래밍 기능
• 쿼리 문의 집합, 어떠한 동작을 일괄 처리하기 위한 용도로 사용
• 필요할 때마다 간단히 호출하면 편리하게 MySQL을 운영할 수 있다
• 형식

DELIMITER $$     -- 구분자를 $$로 변경 
CREATE PROCEDURE 스토어드_프로시저_이름( IN 또는 OUT 매개변수 ) 
BEGIN 
    -- 이 부분에 SQL 프로그래밍을 코드를 작성 
END $$ 
DELIMITER ; -- 구분자를 원래대로(;) 변경

매개변수의 사용

DROP PROCEDURE IF EXISTS user_proc3;  
DELIMITER $$  
CREATE PROCEDURE user_proc3(  
    IN txtValue CHAR(10),  
    OUT outValue INT )  
BEGIN  
    INSERT INTO noTable VALUES (NULL, txtValue);  
    SELECT MAX(id) INTO outValue FROM noTable;  
END $$  
DELIMITER ;

CALL user_proc3 ('테스트1', @myValue);  
SELECT CONCAT('입력된 ID 값 ==> ', @myValue);

• IN, OUT를 사용한다
• OUT의 경우 @변수명 형태로 변수를 전달해주면 그 변수에 결과가 저장된다

스토어드 함수

개념과 형식

• 사용자가 직접 만들어서 사용하는 함수
• RETURNS 문으로 반환할 값의 데이터 형식을 지정하고, 하나의 값을 반환해야한다
• 매개변수는 모두 입력 매개변수, IN을 붙이지 않음
• 스토어드 프로시저는 CALL로 호출하지만, 스토어드 함수는 SELECT문 안에서 호출됨
• 스토어드 함수 안에서는 SELECT를 사용할 수 없음
• 형식

-- MySQL에서 스토어드 함수 생성 권한 허용  
SET GLOBAL log_bin_trust_function_creators = 1;

DELIMITER $$  
CREATE FUNCITON 스토어드_함수_이름(매개변수)  
    RETURNS 반환형식  
BEGIN

-- 이 부분에 프로그래밍 코딩  
RETURN 반환값;

END $$  
DELIMITER ;  
SELECT 스토어드_함수_이름();

트리거

개요와 형식

• INSERT, UPDATE, DELETE 작업이 발생하면 실행되는 코드
• 형식

DELIMITER $$  
CREATE TRIGGER singer_updateTrg -- 트리거 이름  
    AFTER UPDATE -- 변경 후에 작동하도록 지정  
    ON singer -- 트리거를 부착할 테이블  
    FOR EACH ROW  
BEGIN  
    INSERT INTO backup_singer VALUES( OLD.mem_id, OLD.mem_name, OLD.mem_number, 
    OLD.addr, '수정', CURDATE(), CURRENT_USER() );  
END $$  
DELIMITER ;