반응형
  1. /**********************************************************************************************
    -- Title : [2k5] 커서의 암시적 형변환
    -- Reference : hanbitbook.co.kr
    -- Key word : cursor 커서 keyset 키셋
    **********************************************************************************************/
    USE sqlDB;
    GO
  2. DROP TABLE keysetTbl;
    GO
  3. CREATE TABLE keysetTbl(id INT, txt CHAR(5));
    GO
  4. INSERT INTO  keysetTbl VALUES(1,'AAA');
    INSERT INTO  keysetTbl VALUES(2,'BBB');
    INSERT INTO  keysetTbl VALUES(3,'CCC');
    GO
  5. DECLARE keysetTbl_cursor CURSOR GLOBAL FORWARD_ONLY  KEYSET
    FOR SELECT id, txt FROM keysetTbl;
  6. --Keyset이 아닌 Static으로 설정됨(model:1)
    --KEYSET에 CL, UQ가 없으면 STATIC으로 암시적 형변환 발생.
    DECLARE @result CURSOR;
  7. EXEC sp_describe_cursor @cursor_return = @result OUTPUT,
            @cursor_source = N'GLOBAL', @cursor_identity = N'keysetTbl_cursor';
  8. FETCH NEXT from @result
    WHILE (@@FETCH_STATUS <> -1)
        FETCH NEXT FROM @result
  9. OPEN keysetTbl_cursor;
  10. FETCH NEXT FROM keysetTbl_cursor;
  11. UPDATE keysetTbl SET txt = 'ZZZ';
  12. FETCH NEXT FROM keysetTbl_cursor;
  13. CLOSE keysetTbl_cursor;
    DEALLOCATE keysetTbl_cursor;
    GO
반응형

+ Recent posts