Cursor 사용 예제
SET NOCOUNT ON
Declare curGet_ViewSO1_PaperLog_Query CURSOR
FOR
select
count(*) as count_all,
convert(varchar(10), regdate, 121) as q_date,
datepart(hh, regdate) as q_time,
isnull(sum(cost),0) as countpay,
mrd
from
SO1_Portal_Log
where
convert(varchar(10),regdate,121) = convert(varchar(10), dateadd(d,-1,getdate()), 121)
and mrd in ('3800', '3802', '480', '4800', '6800', '390', '3806')
and phone not in (select phone_mdn from kocn_phone)
group by mrd, convert(varchar(10), regdate, 121), datepart(hh, regdate)
order by convert(varchar(10), regdate, 121), datepart(hh, regdate)
FOR READ ONLY
OPEN curGet_ViewSO1_PaperLog_Query
Declare @count_all int
, @q_date varchar(20)
, @q_time varchar(20)
, @countpay int
, @mrd varchar(20)
Fetch next From curGet_ViewSO1_PaperLog_Query into @count_all, @q_date, @q_time, @countpay, @mrd
While @@Fetch_status=0
Begin
insert into So1_ALL_Paper_Times_Log (wdate, wtime, cost, cnt, regdate, winc)
values ( replace(@q_date, '-', '') , right('0'+@q_time,2), @countpay, @count_all, getdate(), @mrd)
Fetch next From curGet_ViewSO1_PaperLog_Query into @count_all, @q_date, @q_time, @countpay, @mrd
end
close curGet_ViewSO1_PaperLog_Query
deallocate curGet_ViewSO1_PaperLog_Query
SET NOCOUNT OFF
go