定时任务作业-临时表处理数据

使用临时表处理数据，如编辑，删除等操作

注：使用临时表优于游标

CREATE PROCEDURE [dbo].[sp_rrl_goods_auto_online_or_offline]
AS
BEGIN
declare @goods_id INT;
declare @deletemark DATE;
declare @online_time DATE;
declare @downline_time DATE;
declare @now_time DATE;
declare @temp_goods table (goods_id int primary key, deletemark DATE, online_time DATE, downline_time DATE);

insert into @temp_goods (goods_id, deletemark, online_time, downline_time)
select id,deletemark,online_time,downline_time from rrl_goods WHERE goods_type = 881 AND is_delete = 0;

SET @now_time = GETDATE();

while (select count(*) from @temp_goods) > 0
    begin
        select top 1
            @goods_id = goods_id, 
            @deletemark = deletemark, 
            @online_time = online_time, 
            @downline_time = downline_time from @temp_goods;
            --上架
            if @online_time <= @now_time AND @deletemark is not null
            begin
                UPDATE rrl_goods SET deletemark = null WHERE id = @goods_id;
                insert into wx_temp VALUES (@now_time, 'online', @goods_id);
            end
            --下架
            if @downline_time <= @now_time AND @deletemark is null
            begin
                UPDATE rrl_goods SET deletemark = @now_time WHERE id = @goods_id;
                insert into wx_temp VALUES (@now_time, 'offline', @goods_id);
            end
        -- 从临时表中删除数据
        delete from @temp_goods where goods_id = @goods_id;     
    end
END