[MySQL] 프로시저를 사용한 cursor loop fetch 적용(sql의 for문)

DB와 서버를 만지다 보면

DB에서 가져온 데이터 목록에 대해서 일괄처리, 그 중 반복문을 돌려 처리를 해야 하는 상황이 생긴다.

 

그런 상황은 단순한 상황과 복잡한 상황으로 나눠서 보자

 

예를 들어보자

먼저 단순한 상황을 생각해 보면

 

쿼리를 통해 db에서 가져온 목록들에 대해

for문을 돌려

특정 컬럼에 대해 분류를 해서

응답을 하는 경우가 있겠다.

 

이 같이 단순한 경우는

1. 쿼리문(DB)안에서 ORDER BY를 이용해 분류하고 서버에서 어느정도 분류된 데이터를 for문으로 돌려 나누는 방법
2. 쿼리문(DB) 단계에서는 다른 분류 없이 단순 select문을 통해 서버로 가져온 다음 서버단에서 for문을 통해 분류하는 방법

으로 나눌 수 있을 것이다.

 

 

이번엔 복잡한 상황을 생각해보자

 

예를 들어

스타벅스 어플에서 자정에 스탬프가 12개이상을 가진 유저들에 대해 일괄적으로 쿠폰(바코드)이 하나 생기는 구조를 구현한다고 해보자.

이 단순해 보이는 구조를 프로시저를 사용하지 않았을 때와 사용했을 때로 구분해서 보자

 

먼저 프로시저를 사용하지 않으면 굉장히 복잡해진다.

1. 스탬프가 12개 이상인 유저 select

2. 해당 유저의 수 만큼 for문을 돌려 쿠폰 바코드를 생성하는데

3. 하나씩 생성할 때마다 그 코드가 중복되지 않는지 select로 확인해야 한다

4. 생성된 코드들을 아까 select된 유저에 하나씩 매칭을 하고

5. 그 매칭된 제한되지 않은 레코드들을 쿠폰 테이블에 insert

 

말로만 했는데도 이렇게 복잡한데 실제로 코딩하면

엄청난 불필요한 코드 수는 물론

유저가 많아짐에 따라 몇배에 해당하는 어마어마한 네트워킹 비용이 든다.(위의 3번의 중복체크에서 이미 넋 다운이 되어있을 수 있다)

 

저 1~5의 과정을 한 번의 DB와의 네트워킹을 통해 이룰 수 있다면 엄청난 네트워킹 비용낭비를 아낄 수 있는 것이다.

 

따라서 필자는 하나의 프로시저를 만들어 한 번의 DB와의 네트워킹을 통해 수행하려고 한다.

 

DROP PROCEDURE IF EXISTS InsertFreeDrinkCoupon;	//겹치면 업데이트
DELIMITER $$	// ';' 으로 문장을 나눌 수 있게

CREATE PROCEDURE InsertFreeDrinkCoupon()	//여기서는 생성 인자를 따로 사용하지 않았다.
	BEGIN
 	    DECLARE now DATETIME;	//insert할 변수를 먼저 선언해준다(발급 일자)
        DECLARE deadline DATETIME;	//쿠폰 마감일자

        DECLARE done INTEGER DEFAULT 0;		//루프의 끝을 정해주는 변수 done 선언(처음엔 0: false)
        DECLARE v_user INT(11);		//loop의 주체가 user에서 한 번의 루프 당 할당된 user를 담는 변수(for문의 i)
        DECLARE i INT(11) default 1;	//바코드가 중복이 아닐때까지 생성하게 하기 위해 while에 붙는 조건 i

        DECLARE openCursor CURSOR FOR SELECT user_idx FROM user WHERE user_stamp_count>11;
        							// 루프를 돌릴 데이터배열 선택 => 커서를 올림

        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
        							// 루프를 돌리고 있는 데이터배열에 더이상 커서를 올릴 데이터가 없다면 done = true

        SET now = date_add(now(), interval 9 hour);		//처음에 선언한 변수 중 now 초기화(현재)
        SET deadline = date_add(now, interval 2 week);		//처음에 선언한 변수 초기화(쿠폰 마감일자)

        open openCursor;	//루프 시작

        convert_to_coupon: LOOP		//**** 루프 이름(이 아래로 루프가 실행) ****

          set i = 1;	//맨 위에서 선언했던 변수 i 초기화

          FETCH openCursor INTO v_user;		//현재 cursor pointer가 가리키는 record 를 fetch 하여 v_user 에 넣는다
          					//여기서 fetch 할 row가 없을 때 NOT FOUND condition 이 되고 위에 선언된 not found handler 의 set done := TRUE 가 실행된다.
                            //루프마다 실행되기 때문에 done의 값을 제대로 할당하기 위해서 위의 루프 선언문 바로 아래에 위치시켜야 좋다.
                            //(안 그러면 마지막 커서가 두번 실행되는 상황이 생긴다.)

                   IF done THEN		//변수 done을 체크해보고 true면 루프 탈출(종료)
          		   		LEAVE convert_to_coupon;
                   END IF;
                   
//한 루프에 해당하는 유저마다 바코드 생성, 중복 체크
          while i = 1 do	//i = 생성한 바코드와 일치하는 코드 개수 => 0(개)일때까지만 바코드 생성, 변수담기
            select @barcode:=cast(lpad(floor(Rand()*10000000000000000),16,0) as char);	//16자리 바코드 생성
            select @same_code_count:=count(*) from coupon where coupon_code = @barcode;		//바코드 중복 개수
            set i = @same_code_count;	// 중복 여부 i에 담기 => 0이면 while 탈출, 1이면 다시 while 시작
          end while;

//한 루프의 유저당 유일한 바코드가 생성되었으면 한 객체로 묶고 쿠폰목록에 추가 (맨 위에서 선언했던 변수들, select에서 변수로 임시 지정했던 변수(@barcode) 사용)
          INSERT INTO coupon (user_idx, coupon_title, coupon_detail, date, coupon_date_deadline, coupon_code)
          values (v_user, '프리드링크', '아메리카노 1잔', now, deadline, @barcode); //전역변수, 로컬변수 이용
     
// 쿠폰을 만드는데 사용되었던 12개의 스탬프 차감 내역 추가
          INSERT INTO stamp (user_idx, date, stamp_count)
          values (v_user, now, -12);		//v_user에 각 루프에 해당하는 user가 들어있다
          update user set user_stamp_count=user_stamp_count-12 where user_idx=v_user;

// done의 여부를 통해 루프 종료
          IF done THEN
              LEAVE convert_to_coupon;
          END IF;

        END LOOP convert_to_coupon;		// 해당 이름 루프 종료

    END $$		// $$를 꼭 붙여줘야 한다
DELIMITER ;

 

 

위의 쿼리문을 stored procedure에 저장해 놓은 다음

 

서버에서는

CALL InsertFreeDrinkCoupon()

 

의 간단한 쿼리문을 통해 위의 복잡한 과정의 결과물을 효율적으로 가져올 수 있다.