MSSQL 저장 프로시저 CURSOR, FETCH

오랜만에 MSSQL 저장 프로시져 건드렸다가, 머리에 쥐났었는데 다행히 하고자 하는건 다 만들었습니다.

 

코드에 나온건 위의 3개의 테이블을 사용했는데, 사실상 위의 두 테이블중 하나와 아래 테이블로만도 가능합니다.

 

아래 코드에서 다루는 내용들은 CURSOR 사용과 null값, 최대값(MAX), 업데이트방법, 날짜 변형(Format)등등의 복합적인 내용을 담고있어, 살펴보시기 좋은 예가 되지 않을까 싶습니다. 

 

아래는 저장프로시저에서 사용한 전문입니다.

 

*'--' 주석에 설명을 해뒀습니다. 

*주석의 경우 제 스스로 말하는거라 경어가 없더라도 양해바랍니다.

 

USE [서버명]
GO
/****** Object:  StoredProcedure [dbo].[매출총계2]    Script Date: 2022-05-11 오후 12:30:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- 프로시저를 실행시켜서 '0'이 나오면 정상작동임.


ALTER PROCEDURE [dbo].[매출총계2]
AS

--반복문에서 사용할 변수 선언
DECLARE @업체코드 INT, @총매출 INT;

--커서선언 Cur이라는 이름으로
DECLARE Cur CURSOR FOR 

--커서에 담을 결과 Select문
SELECT tbl2거래입력.업체코드, isnull(sum(tbl2거래입력.소계),0)  as 총매출
FROM tbl2거래입력 GROUP BY tbl2거래입력.업체코드;

--커서를 오픈한다.
OPEN Cur;
--위의 결과를 행의 순서대로 커서에 담는다. select문의 쿼리를 보고 컬럼의 순서가 업체코드,총매출이면
--아래 Fetch는 순서대로 @업체코드, @총매출 적어준다. 이게 레코드셋이라고 생각해라.
FETCH NEXT FROM Cur INTO @업체코드,@총매출;

--LOOP RS.EOF 같은 구문 while @@fetch
WHILE @@FETCH_STATUS = 0 

--커서로(레코드셋) 기록(또는 다른 이벤트)를 시작한다. 
BEGIN

UPDATE tbl고객사업자 set tbl고객사업자.총매출 = isnull(@총매출,0) where tbl고객사업자.ID= @업체코드;

--여기서도 커서의 순서는 맞춰라. 위의 순서가 아닌, 원래 레코드셋이 어떤 순서로 구성되어 있는지로 
FETCH NEXT FROM Cur INTO @업체코드,@총매출;

END

CLOSE Cur;
DEALLOCATE Cur;

--//2번째 커서

DECLARE @올해매출 INT, @년도 INT
set @년도 = format(getdate(),'yyyy')

DECLARE Cur CURSOR FOR 

SELECT tbl2거래장.업체코드, isnull(Sum(tbl2거래장.소계의합계),0) AS 올해매출, Format([날짜],'yyyy') AS 년도
FROM tbl2거래장
GROUP BY tbl2거래장.업체코드, Format([날짜],'yyyy')
HAVING not Sum(tbl2거래장.소계의합계) is null AND ((Format([날짜],'yyyy'))=@년도);

OPEN Cur
Fetch Next From Cur into @업체코드, @올해매출, @년도
While(@@FETCH_STATUS = 0 )

Begin

update tbl고객사업자 set 올해매출 = @올해매출 where ID = @업체코드
Fetch Next From Cur into @업체코드, @올해매출, @년도
END
close Cur
deallocate Cur

DECLARE @최종일 datetime
DECLARE Cur CURSOR FOR

SELECT tbl2거래장.업체코드, Max(tbl2거래장.날짜) as 최종일
FROM tbl2거래장
GROUP BY tbl2거래장.업체코드
HAVING Max(tbl2거래장.날짜) Is Not Null;

OPEN Cur
Fetch Next From Cur into @업체코드, @최종일
While(@@FETCH_STATUS = 0 )

Begin

update tbl고객사업자 set 최종거래일 = @최종일 where ID = @업체코드
Fetch Next From Cur into @업체코드, @최종일
end
close Cur
deallocate Cur


go