CURSOR 간단 사용

CURSOR 을 이용한 insert 와 update

트랜잭션도 같이 사용 되었다.


 

begin tran 
 
 declare @order_id int
 declare rs CURSOR for select ID from ORDER_INFO where ORDER_CODE=@order_code and REG_FLAG='Y'
 open rs
 
 FETCH NEXT FROM rs INTO @order_id
    WHILE (@@FETCH_STATUS=0)
    BEGIN               
  insert into ORDER_HISTORY(ORDER_ID, USER_ID, UPDATE_USER_NAME, ORDER_STATE, REG_FLAG, REG_DATE)
  values(@order_id, 0, 'ez_system', '006', 'Y', GETDATE())              
  FETCH NEXT FROM rs INTO @order_id
 END

    CLOSE rs
    DEALLOCATE rs
 
 --상태 변경
    update ORDER_INFO set ORDER_STATE='006', DELIVERY_DATE=@delivery_date where ORDER_CODE=@order_code;
       
    IF @@ERROR <>  0
    begin      
  rollback
  select 0 as rcount;
 end
 else
 begin
  commit   
  select 1 AS rcount;
 end