커서와 집합 기반 사고

DECLARE @b int, @c1 int = 0, @c2 int = 0, @c3 int = 0;

DECLARE cur CURSOR FOR SELECT board_num FROM Board.POSTS;   -- 선언
OPEN cur;                                                   -- 열고
FETCH NEXT FROM cur INTO @b;                             -- 한 행 꺼내고

WHILE @@FETCH_STATUS = 0                                  -- 꺼낸 것이 있으면
BEGIN
    IF @b = 1 SET @c1 += 1; ELSE IF @b = 2 SET @c2 += 1; ELSE SET @c3 += 1;
    FETCH NEXT FROM cur INTO @b;
END

CLOSE cur;                                                  -- 닫고
DEALLOCATE cur;                                            -- 버립니다

-- 5만 행짜리 표를 만들어 둡니다. grp 는 500 가지입니다.
-- (가) 커서로 한 행씩 읽으며 갱신합니다.
DECLARE @num int, @grp int, @prev int = -1, @seq int = 0;

DECLARE cur CURSOR FOR SELECT num, grp FROM Board.T_CUR ORDER BY grp, num;
OPEN cur;
FETCH NEXT FROM cur INTO @num, @grp;
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @grp <> @prev BEGIN SET @seq = 1; SET @prev = @grp; END
    ELSE SET @seq += 1;

    UPDATE Board.T_CUR SET seq = @seq WHERE num = @num;
    FETCH NEXT FROM cur INTO @num, @grp;
END
CLOSE cur; DEALLOCATE cur;

-- (나) 윈도 함수로 한 문장에 끝냅니다(2.7).
WITH T AS (
    SELECT seq, ROW_NUMBER() OVER (PARTITION BY grp ORDER BY num) AS rn
    FROM Board.T_CUR
)
UPDATE T SET seq = rn;

DECLARE c1 CURSOR FOR …                          -- 기본
DECLARE c2 CURSOR LOCAL FAST_FORWARD FOR …       -- 앞으로만, 읽기만

SELECT name AS 커서, properties AS 속성, is_open AS 열림
FROM sys.dm_exec_cursors(@@SPID) WHERE name IS NOT NULL;

-- 앞에서 본 것입니다. 덩어리마다 0 부터 다시 셉니다.
WITH T AS (
    SELECT sort_no,
           ROW_NUMBER() OVER (PARTITION BY group_num ORDER BY sort_no, num) - 1 AS rn
    FROM Board.POSTS
)
UPDATE T SET sort_no = rn;

SELECT TOP (5) num, hit_count,
       LAG(hit_count) OVER (ORDER BY num) AS 앞글,
       hit_count - LAG(hit_count) OVER (ORDER BY num) AS 차이
FROM Board.POSTS WHERE board_num = 1 ORDER BY num;

SELECT TOP (5) num, hit_count,
       SUM(hit_count) OVER (ORDER BY num ROWS UNBOUNDED PRECEDING) AS 누적
FROM Board.POSTS WHERE board_num = 1 ORDER BY num;

-- 커서로 하면 변수에 계속 이어 붙여야 합니다.
DECLARE @r nvarchar(max) = N'', @name nvarchar(50);
DECLARE c CURSOR LOCAL FAST_FORWARD FOR SELECT nickname FROM Member.USERS ORDER BY num;
… 여덟 줄 …

-- 한 줄입니다.
SELECT STRING_AGG(nickname, N', ') WITHIN GROUP (ORDER BY num) FROM Member.USERS;