[MSSQL] 커서 사용

2007.12.05 15:26

-- 커서를 돌릴 Select 문
declare Inout_Cursor_ItemCode_sub CURSOR
   FOR   select InoutNo, InoutGubun, InoutSerNo,
                CalcInOutQty, InOutPrice, InoutAmnt
         from   mmInoutItem
         where  SiteCode  = @SiteCode
           and  WhCode    = @WhCode
    and  InvStatus = @InvStatus
    and  ItemCode  = @ItemCode
    and  ((InoutDate > @InoutDate) or (InoutDate = @InoutDate and ItemDaySerno > @ItemDaySerno))
        order by InoutDate, ItemDaySerNo

--커서를 열어서 한 Row 씩 값을 받기 위함.
--여기서 받는 변수들은 별도로 선언을 해줘야 함.
   open Inout_Cursor_ItemCode_sub
   fetch next from Inout_Cursor_ItemCode_sub into @InoutNo_c, @InoutGubun_c, @InoutSerNo_c, @CalcInOutQty_c, @InOutPrice_c, @InoutAmnt_c
   
--커서를 돌림.
   WHILE @@FETCH_STATUS = 0
   BEGIN
   set @OhQty_c    = @OhQty_O
   set @OhAmnt_c   = @OhAmnt_O
   set @OhPrice_c  = @OhPrice_O
   
    update mmInoutItem
    set    OhQty   = @OhQty_O,
           OhPrice = @OhPrice_O,
           OhAmnt  = @OhAmnt_O
     where SiteCode   = @SiteCode
       and InoutNo    = @InoutNo_c
       and InoutGubun = @InoutGubun_c
       and InoutSerNo = @InoutSerNo_c

   FETCH NEXT from Inout_Cursor_ItemCode_sub into @InoutNo_c, @InoutGubun_c, @InoutSerNo_c, @CalcInOutQty_c, @InOutPrice_c, @InoutAmnt_c

--커서 닫음  
   END
   close Inout_Cursor_ItemCode_sub
   deallocate Inout_Cursor_ItemCode_sub

Posted by 나비 나비:D

BLOG main image
by 나비:D

공지사항

카테고리

분류 전체보기 (278)
Programming? (0)
----------------------------- (0)
나비의삽질 (5)
Application (177)
SQL (51)
Web (27)
etc. (14)
Omnia (0)
---------------------------.. (0)

글 보관함

달력

«   2018/04   »
1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30          
Total : 925,213
Today : 582 Yesterday : 195