好久没用游标了，为了方便查询，记一下（ZT）

最新推荐文章于 2022-02-25 19:38:17 发布

andyhooo

最新推荐文章于 2022-02-25 19:38:17 发布

阅读量436

点赞数

分类专栏： T-sql(sqlserver2000) 文章标签： sms insert c sql

T-sql(sqlserver2000) 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

alter procedure [dbo].[p_sync_sms_records]
as
declare @type            varchar(5)
declare @send_number    varchar(21)
declare @recv_number    varchar(21)
declare @content        varchar(256)
declare @created_at        datetime
declare @area_id        varchar(5),
        @sql            varchar(5000),
        @row_value        varchar(120),
        @row_a            varchar(20),
        @row_b            varchar(20),
        @row_c            varchar(20),
        @row_d            varchar(20),
        @row_e            varchar(20),
        @row_f            varchar(20)
/*
exec [p_sync_sms_records]
*/

    --初始化游标
    DECLARE cur CURSOR FOR
    SELECT type,send_number,recv_number,content,created_at
    FROM original_sms_records
    WHERE convert(varchar(10),created_at,121)=convert(varchar(10),getdate(),121)

    --打开游标
    OPEN cur
    FETCH next FROM cur INTO @type,@send_number,@recv_number,@content,@created_at

    --置游标于下一条
    WHILE @@FETCH_STATUS = 0
    BEGIN
        select @area_id=dbo.f_region(@recv_number)

        DECLARE row_value CURSOR FOR
        SELECT value FROM f_splitstring(@content,' ')

        OPEN row_value
        FETCH next FROM row_value INTO @row_value

        declare @i int
        set @i=1
        set @row_a=''
        set @row_b=''
        set @row_c=''
        set @row_d=''
        set @row_e=''
        set @row_f=''
        WHILE @@FETCH_STATUS = 0
        BEGIN
            if @i<=6
            begin
                if @i=1
                    set @row_a=@row_value
                else if @i=2
                    set @row_b=@row_value
                else if @i=3
                    set @row_c=@row_value
                else if @i=4
                    set @row_d=@row_value
                else if @i=5
                    set @row_e=@row_value
                else
                    set @row_f=@row_value

                set @i=@i+1
                FETCH NEXT FROM row_value INTO @row_value
            end
            else
            begin
                FETCH NEXT FROM row_value
                break
            end
        END

        select top 1 1 from detail_sms_records
            where send_number=@send_number and recv_number=@recv_number and area_id=area_id
                    and row_a=@row_a and row_b=@row_b and row_c=@row_c and row_d=@row_d and row_e=@row_e and row_f=@row_f

        if @@ROWCOUNT=0
        begin
            insert into detail_sms_records
            (type,send_number,recv_number,area_id,row_a,row_b,row_c,row_d,row_e,row_f,content,created_at)
            values (@type,@send_number,@recv_number,@area_id,@row_a,@row_b,@row_c,@row_d,@row_e,@row_f,@content,@created_at)
        end

        CLOSE row_value
        DEALLOCATE row_value

        FETCH next FROM cur INTO @type,@send_number,@recv_number,@content,@created_at
    END

    --关闭游标
    CLOSE cur
    DEALLOCATE cur