🎱 실습 목적 및 시나리오

⚽️ 실습 목적

• 테이블 설계
• SP 설계
• 커서 활용 반복 처리
• 조건 분기 기반 포인트 지급 로직
• 로깅을 통한 결과 추적
• SP 호출

⚽️ 시나리오

• 유저 테이블에서 특정 조건을 만족하는 유저에게 포인트 지급
• 지급 대상이 아닌 경우 지급하지 않고 로깅

🎱 실습 내용

⚽️ 로직 설계

• 레벨이 5 이상이라면 50 포인트를 지급
• 레벨이 5 미만이라면 포인트를 지급하지 않음
• 레벨에 따른 포인트 지급 로직을 프로시저로 설계
• 포인트 지급 로직을 모든 레코드에 대해 반복 실행하는 프로시저 설계

⚽️ DB 설정, 테이블 설계

-- DB 생성
-- CREATE DATABASE my_practice_db;
USE my_practice_db;

SHOW DATABASES;

-- 테이블 설계
CREATE TABLE users(
	id INT PRIMARY KEY,
	name VARCHAR(50),
	point INT DEFAULT 0,
	level INT
);

CREATE TABLE point_log(
	id INT AUTO_INCREMENT PRIMARY KEY,
	user_id INT,
	message VARCHAR(100),
	log_time DATETIME DEFAULT CURRENT_TIMESTAMP
);

SHOW TABLES;

⚽️ 샘플 데이터 삽입

-- 1. 샘플 데이터 삽입
INSERT INTO users (id, name, point, level)
VALUES 	(1, 'Peter', 100, 5),
		(2, 'Finn', 150, 3),
		(3, 'Jake', 200, 7);

SELECT * FROM users;

⚽️ 조건 분기로 포인트를 지급하는 프로시저 생성 - GivePoint

-- 2. 조건 분기로 포인트를 지급하는 프로시저 생성
DROP PROCEDURE IF EXISTS GivePoint;
CREATE PROCEDURE GivePoint (
	IN p_id INT
)
BEGIN
	DECLARE v_level INT;
	DECLARE v_msg VARCHAR(100);

	SELECT level INTO v_level
	FROM users
	WHERE id = p_id;
	
	IF v_level >= 5 THEN
		UPDATE users SET point = point + 50
			WHERE id = p_id;
		SET v_msg = '50 포인트가 지급되었습니다.';
	ELSE
		SET v_msg = '레벨이 낮아 포인트 지급 대상이 아닙니다.';
	END IF;
	INSERT INTO point_log (user_id, message) VALUES (p_id, v_msg);
END;

SHOW PROCEDURE STATUS WHERE Db = 'my_practice_db';

⚽️ GivePoint 프로시저 테스트

-- 3. GivePoint 프로시저 테스트
CALL GivePoint(1);
SELECT * FROM point_log;

⚽️ GivePoint를 모든 레코드에 대해 반복 실행하는 프로시저 생성 - GivePointToAll

-- 4. GivePoint를 모든 레코드에 대해 반복 실행하는 프로시저 생성
DROP PROCEDURE IF EXISTS GivePointToAll;
CREATE PROCEDURE GivePointToAll ()
BEGIN
	DECLARE v_id INT;

	DECLARE done INT DEFAULT FALSE;
	DECLARE user_cursor CURSOR FOR
    	SELECT id FROM users ORDER BY id;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
	
	OPEN user_cursor;
	
	read_user: LOOP
		FETCH user_cursor INTO v_id;
		IF done THEN
			LEAVE read_user;
		END IF;
	
		CALL GivePoint(v_id);
	END LOOP;
		
	CLOSE user_cursor;
END;

SHOW PROCEDURE STATUS WHERE Db = 'my_practice_db';

⚽️ GivePointToAll 프로시저 테스트

-- 5. GivePointToAll 프로시저 테스트
CALL GivePointToAll();
SELECT * FROM point_log;

🎱 마무리

• 보완할 점
  • 일치하는 id가 없을 경우 등에 대한 에러 처리 핸들러 추가
  • 예외 로그 테이블 추가
  • 트랜잭션 도입
  • 사용자 조건 다양화
  • 관리 뷰 도입
  • OUT 파라미터에 받은 후 로그 저장 구조로 통일, 모듈화

전반적으로 뜬구름 잡는 듯한 개념을 직접 작성하며 익혀봤다.
확실히 직접 하면 더 잘 와닿는다.
하지만 아직도 보고 작성한 부분도 많고 작성하면서 틀린 부분도 많았어서 멀었구나 싶다.
SQL.. 조회만 할 줄 알았지 정말 아무것도 몰랐구나 싶기도 하고.
공부를 하다보니 또 재밌다.