▷ Mssql cursor use case (MSSQL 커서 사용 예제)

[ Mssql cursor sample ]

-- 저장프로시져 사용
-- CREATE PROC TEST
-- AS

DECLARE @DATE INT
DECLARE @VALUE INT
DECLARE @HOSTNAME VARCHAR(10)

-- (1개 행 적용됨) 와 같은 메세지를 보이지 않게 하기 위함
SET NOCOUNT ON

-- 임시테이블 사용
CREATE TABLE #TEST (COL1 INT, COL2 INT, COL3 VARCHAR(10), COL4 INT, COL5 VARCHAR(10))

DECLARE CUR_TEST CURSOR FOR SELECT * FROM USM_TEMP WHERE HOSTNAME='A'

OPEN CUR_TEST

FETCH NEXT FROM CUR_TEST INTO @DATE, @VALUE,  @HOSTNAME

WHILE @@FETCH_STATUS = 0
BEGIN
-- SELECT @DATE, @VALUE, @HOSTNAME, TIME_STAMP, HOSTNAME  FROM USM_TEMP WHERE HOSTNAME='B'
INSERT INTO #TEST SELECT @DATE, @VALUE, @HOSTNAME, TIME_STAMP, HOSTNAME  FROM USM_TEMP WHERE HOSTNAME='B'
FETCH NEXT FROM CUR_TEST INTO @DATE, @VALUE,  @HOSTNAME
END

SET NOCOUNT OFF

SELECT * FROM #TEST

CLOSE CUR_TEST
DEALLOCATE CUR_TEST

-- 임시테이블 삭제
-- DROP TABLE #TEST

-- 저장프로시져 호출
-- EXEC TEST