[혼공단11기] 혼자 공부하는 SQL 6주차(Chapter 07 ~ 08)

 

6주차(24.2.5 - 2.13) Chapter 07. 스토어드 프로시저

스토어드 프로시저 stored procedure

스토어드 프로시저(저장 프로시저) : MySQL에서 제공하는 프로그래밍 기능.

                                                       SQL에 프로그래밍 기능을 추가해서 일반 프로그래밍 언어와 비슷한 효과 도출.

                                                       (데이터베이스 개체 중 한 가지로, 각 데이터베이스 내부에 저장 됨) 

 

-- 스토어드 프로시저 기본 형식

DELIMITER $$
CREATE PROCEDURE 스토어드_프로시저_이름 (IN 또는 OUT 매개변수)
BEGIN
	/* SQL QUERY */
END $$
DELIMITER;

 

DELIMITER ~ DELIMITER : 필수 항목, 스토어드 프로시저를 묶어주는 기능

$$ : $ 1개만 사용해도 되지만, 명확한 표시를 위해 2개를 사용. ##, %%, &&, // 등으로 변경 가능

 

 

입력 & 출력 매개변수 활용(IN & OUT)

-- 입력 매개변수 사용 프로시저 예시

USE market_db;
DROP PROCEDURE IF EXISTS user_proc1;
DELIMITER $$
CREATE PROCEDURE user_proc1(IN userName VARCHAR(10))
BEGIN
  SELECT * FROM member WHERE mem_name = userName; 
END $$
DELIMITER ;

CALL user_proc1('에이핑크');

 

 

-- 출력 매개변수 사용 프로시저 예시

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 ;

 

 

스토어드 프로시저 + SQL 프로그래밍

DROP PROCEDURE IF EXISTS ifelse_proc;
DELIMITER $$
CREATE PROCEDURE ifelse_proc(
    IN memName VARCHAR(10)
)
BEGIN
    DECLARE debutYear INT; -- 변수 선언
    SELECT YEAR(debut_date) into debutYear FROM member
        WHERE mem_name = memName;
    IF (debutYear >= 2015) THEN
            SELECT '신인 가수네요. 화이팅 하세요.' AS '메시지';
    ELSE
            SELECT '고참 가수네요. 그동안 수고하셨어요.'AS '메시지';
    END IF;
END $$
DELIMITER ;

CALL ifelse_proc ('오마이걸');

 

트리거 trigger

트리거(trigger) : INSERT, UPDATE, DELETE 문이 작동할 때 자동으로 실행되는 프로그래밍 기능.

                          데이터에 오류 발생 방지(데이터의 무결성)

                          EX) 트리거를 활용해 데이터 삭제 시 해당 데이터를 다른 곳에 자동으로 백업 가능

-- 트리거 예시 코드

DROP TRIGGER IF EXISTS myTrigger;
DELIMITER $$ 
CREATE TRIGGER myTrigger  -- 트리거 이름
    AFTER  DELETE -- 삭제후에 작동하도록 지정
    ON trigger_table -- 트리거를 부착할 테이블
    FOR EACH ROW -- 각 행마다 적용시킴
BEGIN
    SET @msg = '가수 그룹이 삭제됨' ; -- 트리거 실행시 작동되는 코드들
END $$ 
DELIMITER ;

 

 

기본미션

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

1. BACK-UP 테이블 생성

-- BACKUP 테이블 생성

DROP TABLE IF EXISTS backup_member;
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,
  modType  CHAR(2), -- 변경된 타입. '수정' 또는 '삭제'
  modDate  DATE, -- 변경된 날짜
  modUser  VARCHAR(30) -- 변경한 사용자
);

 

2. update trigger 생성

-- update trigger

DROP TRIGGER IF EXISTS member_updateTrg;
DELIMITER $$
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, '수정', CURDATE(), CURRENT_USER() );
END $$ 
DELIMITER ;

 

3. delete trigger 생성

-- delete trigger

DROP TRIGGER IF EXISTS member_deleteTrg;
DELIMITER $$
CREATE TRIGGER member_deleteTrg  -- 트리거 이름
    AFTER DELETE -- 삭제 후에 작동하도록 지정
    ON member -- 트리거를 부착할 테이블
    FOR EACH ROW 
BEGIN
    INSERT INTO backup_member VALUES( OLD.mem_id, OLD.mem_name, OLD.mem_number, 
        OLD.addr, '삭제', CURDATE(), CURRENT_USER() );
END $$ 
DELIMITER ;

 

데이터 DELETE 실행시 MEMBER 테이블의 FK 오류 발생.

PK 및 FK 제거 후 재 실행

 

-- FK 삭제
ALTER TABLE buy
	DROP FOREIGN KEY buy_ibfk_1;

-- PK 삭제
ALTER TABLE member
	DROP PRIMARY KEY;
    
-- 재 실행을 위한 BACKUP_TABLE 데이터 삭제
truncate backup_member;

 

 

 

 

전체 SQL QUERY

-- 1. backup 테이블 생성
DROP TABLE IF EXISTS backup_member;
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,
  modType  CHAR(2), -- 변경된 타입. '수정' 또는 '삭제'
  modDate  DATE, -- 변경된 날짜
  modUser  VARCHAR(30) -- 변경한 사용자
);


-- 2. 트리거 생성
DROP TRIGGER IF EXISTS member_updateTrg;
DELIMITER $$
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, '수정', CURDATE(), CURRENT_USER() );
END $$ 
DELIMITER ;

DROP TRIGGER IF EXISTS member_deleteTrg;
DELIMITER $$
CREATE TRIGGER member_deleteTrg  -- 트리거 이름
    AFTER DELETE -- 삭제 후에 작동하도록 지정
    ON member -- 트리거를 부착할 테이블
    FOR EACH ROW 
BEGIN
    INSERT INTO backup_member VALUES( OLD.mem_id, OLD.mem_name, OLD.mem_number, 
        OLD.addr, '삭제', CURDATE(), CURRENT_USER() );
END $$ 
DELIMITER ;


-- 3. 데이터 업데이트 및 조회
update member SET addr = '영국' where mem_id = 'BLK';
delete from member where mem_number >= 7;
select * from backup_member;

 

 

 

혼자 공부하는 SQL | 우재남 - 교보문고

https://product.kyobobook.co.kr/detail/S000001810432

 

 

 

+ chat GPT를 활용해 프로시저 예시 코드 생성하기