[MySQL] 프로시저 WHILE 사용해서 UPDATE 쿼리 날리기

학교 팀 프로젝트 진행 중 project 테이블에 mockaroo를 이용해서 더미 데이터를 넣는 작업을 진행했는데, project 생성 시 project_team 테이블에 해당 project의 리더 id가 insert 되어야 했다.

1000개의 row에 수작업으로 일일이 넣어주는 건 엄두가 안 나서 MySQL의 저장 프로시저(Stored Procedure)를 사용하기로 했다.

우선 저장 프로시저가 무엇인지 정의부터 알아보고 사용한 프로시저의 내용을 설명하겠다.

저장 프로시저(Stored Procedure)란?

저장 프로시저는 일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합이다. DB에 대한 일련의 작업을 정리한 절차를 RDBMS에 저장한 것으로 영구 저장 모듈이라고도 불린다.

저장 프로시저의 장점

• 하나의 요청으로 여러 SQL문을 실행할 수 있어 네트워크 부하를 줄일 수 있다.
• 미리 구문 분석 및 내부 중간 코드로 변환을 끝내야 하므로 처리 시간이 줄어든다.
• 데이터베이스 트리거와 결합하여 복잡한 규칙에 의한 데이터의 참조무결성 유지가 가능하게 된다.
• JAVA 등의 호스트 언어와 SQL 문장이 확실하게 분리되므로 소스 코드의 가독성이 좋아지며 서비스 운영 중에도 저장 프로시저의 교체에 의한 수정이 가능해 유지보수가 용이해진다.

저장 프로시저의 단점

• 구문 규칙이 SQL / PSM 표준과의 호환성이 낮기 때문에 재사용성이 나쁘다.
• 비즈니스 로직의 일부로 사용하는 경우 업무의 사양 변경 시 응용 프로그램과 함께 저장 프로시저의 정의를 변경할 필요가 있어 불필요한 수고와 변경 실수에 의한 장애를 발생시킬 수 있다.

프로시저 생성

DELIMITER $$ # 문장 종료 구분자를 $$로 변경
CREATE PROCEDURE team_update() # 프로시저 생성(프로시저 이름)
BEGIN # 프로시저 시작
	DECLARE project_id INT DEFAULT 77; # project_id 변수 생성 (타입, 초기값)
	DECLARE team_id INT DEFAULT 174; # team_id 변수 생성 (타입, 초기값)
	WHILE (project_id <= 1075) DO # while문 시작
	UPDATE baro.project_team  SET member_id = (SELECT * FROM (SELECT leader_id FROM project a WHERE id = project_id) AS a) WHERE id = team_id;
	SET project_id = project_id + 1; 
	SET team_id = team_id + 1;
	END WHILE; # while문 종료
END $$ # 프로시저 종료
DELIMITER ; ## 프로시저 종료 후 구분자를 다시 세미콜론으로 변경

프로시저 생성시 첫 번째 줄과 마지막 줄에 DELIMITER라는 명령어를 볼 수 있다.

DELIMITER는 문장의 끝을 구분하는 문자를 지정하는 명령어로, 프로시저와 시작과 끝에 약속처럼 붙여줘야 한다. 해당 명령어가 없으면 프로시저 내에서의 SQL 끝을 나타내는 세미콜론마다 부분이 나눠져 어디부터 어디까지가 프로시저인지 구분할 수가 없다.

 

때문에 MySQL workbench나 dbeaver 같은 도구에서 프로시저 생성 시 자동으로 앞 뒤에 해당 명령어를 붙여준다.

CREATE PROCEDURE 우측에 프로시저의 이름을 지정하고, BEGIN 문으로 프로시저의 시작을 알린다.

프로시저에 매개변수를 지정하고 싶다면 아래와 같이 프로시저 이름 옆에 지정하면 된다.

CREATE PROCEDURE team_update(param1 INT, param2 INT)

DECLARE는 프로시저 내에서 사용할 변수를 생성하는 명령으로 타입과 초기값(더미 데이터의 시작 ID값)을 DEFAULT로 지정해주었다.

더미 데이터로 넣은 project의 id 값의 끝이 1075이기 때문에 while문의 종료 조건을 1075로 걸어주고 project_id와 team_id를 이용해 update 쿼리를 날려 해당 프로젝트에 해당하는 팀의 멤버의 id 값을 업데이트해주었다.

프로시저 실행

저장한 프로시저를 실행하는 것은 매우 간단하게 CALL 프로시저명(파라미터) 의 형식으로 사용한다.

CALL team_update

결과 😲

프로시저를 실행한 뒤 project_team 테이블을 보면 member_id가 잘 들어가 있는 것을 확인할 수 있다.

 

참고

https://ko.wikipedia.org/wiki/%EC%A0%80%EC%9E%A5_%ED%94%84%EB%A1%9C%EC%8B%9C%EC%A0%80