[MSSQL] cursor 사용과 대체 테이블 변수 활용

개발을 하다보면 sql select 하는경우 대규모 update insert 등등 이런건 다 처리가 가능하지만 하나 예외의 경우가 있는데 바로 배열처럼 처리 하고싶은 경우이다.

그럴때 보통 cursor 를 기본적으로 생각을 하게 되는데 리스트를 만들고 그걸 테이블에 순서로 행을 index처럼 사용해서 처리 하고싶을 경우에 사용하는것이다.

일단 기본적으로 cursor로 이용해서 하는걸 먼저 처리 하는걸 작성하겠습니다.

declare @name CHAR(50), @age INT;

/* 커서 선언 */
declare cur 

/* 배열로 사용하기 위해서 만든 테이블 or 조회하고싶은테이블*/
cursor for select name, age from table1

/* 커서 오픈 */
open cur
/* 커서의 행이동 즉 for문 돌리기 다음행으로 이동 */
fetch next from cur into @name,@age
/* while 문 */
while @@FETCH_STATUS = 0
	begin
    
    /* 행을 가지고 처리 할 내용들 처리 insert update 등등 예) 아이템 지급 */
    
	/* 다음 행*/
	fetch next from cur into @name, @age
End
/* 커서 닫기 */
close cur
/* 커서 참조 제거 */
deallocate cur

이렇게 cursor 를 이용해서 mssql 에서 배열 처리하는것 처럼 처리 를 할수있는데 항상 나오는 말이
cursor은 사용을 피해라 성능이 안좋다 등등 이런 글을 많이 볼수가 있다.

실제 라이브 서버가 아닌 라이브에서 db를 떠서 복제 db를 가지고 cursor 사용하는거 성능적인 문제가 일어날 일이 적기때문에 이렇게 처리를 할수가 있다.

하지만 보통 이런 경우는 라이브에서 사용하기 위해서 저장 프로시저안에 달아서 사용을 해야합니다.
그래서 유저 단위로 프로시저로 호출해서 사용을 많이 하기때문에 여기서 좀더 개선된 버전을 코드입니다.
cursor 보단 임시테이블 보단 테이블 변수를 활용한다.

/* 테이블 순서 */
DECLARE @Elements TABLE
(
    Number INT IDENTITY(1,1), /* 인덱스 번호를 IDENTITY 이용해서 부여 만약 판가름 할수있는 연속적인 숫자가 있다면 필요 없다 */ 
    ProductName VARCHAR(300) /* 처리 할꺼 */
)

/* 처음과 끝 번호 */
DECLARE @N INT, @Count INT; 
/* 처리할 배열을 가져오기 */
INSERT INTO @Elements SELECT Name FROM dbo.Products;

/* 시장 번호 끝번호 설정 */
SET @N = 1;
SELECT @Count=max(Number) from @Elements

/* for문 처럼 사용 */
DECLARE @CurrentValue varchar(300);
WHILE @N <= @Count
BEGIN
	/* 테이블 변수 안에서 가져와서 처리*/
    SELECT @CurrentValue = ProductName FROM @Elements WHERE Number = @N
    print @CurrentValue
    SET @N = @N + 1;
END

코드 출처

이렇게 개선된 버전을 사용할수있다.
다시 보게 된다면 차이가 눈에 보일수있다. 실행계획에서는 비슷하겠지만 데이터가 조금 많아지거나 하면 바로 차이가 눈으로 보일것이다.

저장 프로시저에서도 cursor 사용하고 성능은 개선된 코드입니다.