MySQL 데이터베이스 프로그래밍 핵심 개념 정리

MySQL 데이터베이스 프로그래밍에서 주요 개념은 저장 프로시저, 함수, 트리거로 구성됩니다. 이 중 가장 자주 사용되는 저장 프로시저에 대한 심층적인 설명을 중심으로 하며, 함수와 트리거는 간단히 소개합니다.

저장 프로시저는 프로그래밍 언어의 메서드와 유사한 구조를 가진 것으로, 일반적인 처리 로직을 캡슐화한 SQL 문장 집합입니다. 데이터베이스에 컴파일되어 저장되며, 반복 호출 시 네트워크 전송량을 줄이고 성능 향상을 도모합니다.

1. 저장 프로시저 기본 사용법

MySQL의 SQL 문은 일반적으로 세미콜론(;), 하지만 저장 프로시저는 여러 문장을 포함하기 때문에 새로운 종료 기호를 지정해야 합니다. 다음은 기본 구조입니다:

-- 종료 기호 변경
DELIMITER $$

-- 저장 프로시저 정의
CREATE PROCEDURE calcSum(IN val1 INT, IN val2 INT)
BEGIN
	DECLARE total INT DEFAULT 0;
	SET total = val1 + val2;
	SELECT total;
END$$

-- 종료 기호 복원
DELIMITER ;

-- 호출 예시
CALL calcSum(50, 75);

-- 저장 프로시저 목록 확인
SELECT * FROM mysql.proc WHERE db='testdb' AND TYPE='PROCEDURE';

-- 저장 프로시저 삭제
DROP PROCEDURE IF EXISTS calcSum;

2. 변수 선언 및 할당

DELIMITER //

ALTER PROCEDURE showVariables()
BEGIN
	DECLARE defaultVal INT DEFAULT 10;
	DECLARE tempStr VARCHAR(50);
	DECLARE male, female INT;

	-- SET 문 사용
	SET tempStr = '성공';

	-- SELECT INTO 사용
	SELECT AVG(salary) INTO male FROM users WHERE gender = '남';
	SELECT AVG(salary) INTO female FROM users WHERE gender = '여';

	SELECT defaultVal, tempStr, male, female;
END//

DELIMITER ;

3. 조건문과 파라미터 전달

-- 입력 파라미터 예시
DELIMITER $$

CREATE PROCEDURE checkSalary(IN userId INT)
BEGIN
	DECLARE salaryValue INT;
	DECLARE levelMsg VARCHAR(50);

	SELECT salary INTO salaryValue FROM users WHERE user_id = userId;

	IF salaryValue >= 30000 THEN 
		SET levelMsg = '고소득';
	ELSEIF salaryValue >= 20000 THEN 
		SET levelMsg = '중간소득';
	ELSE 
		SET levelMsg = '저소득';
	END IF;

	SELECT salaryValue, levelMsg;
END$$

DELIMITER ;

-- 출력 파라미터 예시
DELIMITER //

CREATE PROCEDURE getSalaryInfo(IN userId INT, OUT resultMsg VARCHAR(50))
BEGIN
	DECLARE salaryValue INT;

	SELECT salary INTO salaryValue FROM users WHERE user_id = userId;

	IF salaryValue >= 30000 THEN
		SET resultMsg = '고소득';
	ELSEIF salaryValue >= 20000 THEN 
		SET resultMsg = '중간소득';
	ELSE 
		SET resultMsg = '저소득';
	END IF;
END//

DELIMITER ;

-- 호출 예시
CALL getSalaryInfo(3, @salaryResult);
SELECT @salaryResult AS '소득분류';

4. CASE 문 사용

-- 표현식 기반 CASE
DELIMITER $$

CREATE PROCEDURE awardCase(IN prizeLevel INT)
BEGIN
	DECLARE awardContent VARCHAR(100);

	CASE prizeLevel
	WHEN 1 THEN
		SET awardContent = '1억 원 차량';
	WHEN 2 THEN
		SET awardContent = '5천만 원 상품권';
	ELSE
		SET awardContent = '20만 원 현금상품권';
	END CASE;

	SELECT awardContent;
END$$

DELIMITER ;

-- 조건 기반 CASE
DELIMITER //

CREATE PROCEDURE salaryCase(IN userId INT)
BEGIN
	DECLARE salaryValue INT;
	DECLARE levelMsg VARCHAR(50);

	SELECT salary INTO salaryValue FROM users WHERE user_id = userId;

	CASE
	WHEN salaryValue >= 30000 THEN 
		SET levelMsg = '고소득';
	WHEN salaryValue >= 20000 THEN 
		SET levelMsg = '중간소득';
	ELSE
		SET levelMsg = '저소득';
	END CASE;

	SELECT salaryValue, levelMsg;
END//

DELIMITER ;

5. 반복문 구현

-- WHILE 반복
DELIMITER $$

CREATE PROCEDURE evenSum()
BEGIN
	DECLARE total INT DEFAULT 0;
	DECLARE current INT DEFAULT 1;

	WHILE current <= 100 DO
		IF current % 2 = 0 THEN
			SET total = total + current;
		END IF;
		SET current = current + 1;
	END WHILE;

	SELECT total;
END$$

DELIMITER ;

-- REPEAT 반복
DELIMITER //

CREATE PROCEDURE totalSum()
BEGIN
	DECLARE total INT DEFAULT 0;
	DECLARE current INT DEFAULT 1;

	REPEAT
		SET total = total + current;
		SET current = current + 1;
	UNTIL current > 100
	END REPEAT;

	SELECT total;
END//

DELIMITER ;

-- LOOP 반복
DELIMITER $$

CREATE PROCEDURE loopSum()
BEGIN
	DECLARE total INT DEFAULT 0;
	DECLARE current INT DEFAULT 1;

	myloop: LOOP
		IF current > 50 THEN
			LEAVE myloop;
		END IF;
		SET total = total + current;
		SET current = current + 1;
	END LOOP myloop;

	SELECT total;
END$$

DELIMITER ;

6. 커서 활용

DELIMITER //

CREATE PROCEDURE updateSalaries()
BEGIN
	DECLARE empId INT;
	DECLARE genderStr VARCHAR(10);
	DECLARE done INT DEFAULT 0;

	DECLARE empCursor CURSOR FOR SELECT user_id, gender FROM users;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

	OPEN empCursor;
	FETCH empCursor INTO empId, genderStr;

	WHILE done = 0 DO
		IF genderStr = '남' THEN
			UPDATE users SET salary = salary + 1000 WHERE user_id = empId;
		ELSEIF genderStr = '여' THEN
			UPDATE users SET salary = salary + 2000 WHERE user_id = empId;
		END IF;
		FETCH empCursor INTO empId, genderStr;
	END WHILE;

	CLOSE empCursor;
END//

DELIMITER ;

7. 함수 사용

DELIMITER $$

CREATE FUNCTION sumNumbers(total INT)
RETURNS INT
BEGIN
	DECLARE result INT DEFAULT 0;
	DECLARE current INT DEFAULT 1;

	WHILE current <= total DO
		SET result = result + current;
		SET current = current + 1;
	END WHILE;

	RETURN result;
END$$

DELIMITER ;

-- 호출 예시
SELECT sumNumbers(100);

8. 트리거 구현

-- INSERT 트리거
DELIMITER //

CREATE TRIGGER logInsert
AFTER INSERT ON employee
FOR EACH ROW
BEGIN
	INSERT INTO employee_log(log_type, employee_id, log_time, log_content)
	VALUES ('INSERT', NEW.id, NOW(), CONCAT('신규 등록: ', NEW.id, ',', NEW.name, ',', NEW.money));
END//

DELIMITER ;

-- UPDATE 트리거
DELIMITER //

CREATE TRIGGER logUpdate
AFTER UPDATE ON employee
FOR EACH ROW
BEGIN
	INSERT INTO employee_log(log_type, employee_id, log_time, log_content)
	VALUES ('UPDATE', NEW.id, NOW(), CONCAT('수정 이전: ', OLD.id, ',', OLD.name, ',', OLD.money, '| 수정 이후: ', NEW.id, ',', NEW.name, ',', NEW.money));
END//

DELIMITER ;

-- DELETE 트리거
DELIMITER //

CREATE TRIGGER logDelete
AFTER DELETE ON employee
FOR EACH ROW
BEGIN
	INSERT INTO employee_log(log_type, employee_id, log_time, log_content)
	VALUES ('DELETE', OLD.id, NOW(), CONCAT('삭제 대상: ', OLD.id, ',', OLD.name, ',', OLD.money));
END//

DELIMITER ;

태그: MySQL Stored Procedure Trigger SQL Programming Database Development

8월 22일 10:36에 게시됨

괴물 클럽