Sql Server在存储过程里面使用游标遍历一个表

这里关于SqlServer有两个知识点:一个是使用游标遍历表,另一个是使用if not exists的sql语句进行插入。

一、使用游标遍历表

  这个表可以是数据库的表,也可以是外面DataTable类型的参数传进去,使用游标可以概括为以下步骤:声明游标、打开游标、读取数据、操作数据、读取数据、关闭游标、释放游标。

二、在insert语句使用if not exits

  使用了if not exists的语句的insert操作,意思是,在找不到相关数据时才进行insert操作。不同数据库,有不同的使用语法。

下面是创建存储过程:

USE [CapacityManagement]
GO

/****** Object:  StoredProcedure [dbo].[USP_uploadResGpMaster]    Script Date: 2018/10/24 10:09:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--创建一张表,用来接收C#程序传过来的DataTable对象
--注意,这张表的字段必须跟传过来的DataTable对象的属性一致,名字可以不同
create type GS_Master as table
(
ResGp varchar(10),
Plant varchar(12),
Dept varchar(12),
Descrip nvarchar(50),
Step_mapping varchar(10),
CreateDate datetime,
CreateBy char(8),
ChangeDate datetime,
ChangeBy char(8)
)

go

CREATE PROCEDURE [dbo].[USP_uploadMaster] @Master GS_Master readonly,@OperationType nvarchar(3) 
AS
BEGIN    
    
    SET NOCOUNT ON;
    begin try
    begin transaction
    if(@OperationType ='A')
--声明一些local变量,用于接收查询表得到的数据,以便操作
    declare @ResGp varchar(10),
            @Plant varchar(12),
            @Dept varchar(12),
            @Descrip nvarchar(50),
            @Step_mapping varchar(10),
            @CreateDate datetime,
            @CreateBy char(8),
            @ChangeDate datetime,
            @ChangeBy char(8),
            @master_id int
      begin
       --一、声明游标
       declare master_cursor cursor for select * from @Master
       --二、打开游标
       open master_cursor
       --三、取第一条数据
       fetch next from master_cursor 
                  into @ResGp,
                        @Plant,
                        @Dept,
                        @Descrip,
                        @Step_mapping,
                        @CreateDate,
                        @CreateBy,
                        @ChangeDate,
                        @ChangeBy 
       while @@FETCH_STATUS = 0
       begin
        --四、操作数据,先操作[ResGp_Master],后操作[Step_ResGp_Maping]
            --1、操作[ResGp_Master]
        SELECT @master_id=id FROM [CapacityManagement].[dbo].[ResGp_Master] 
                                where resgp = @ResGp
                                and    plant = @Plant
                                and    dept = @Dept

        update [CapacityManagement].[dbo].[ResGp_Master]
        set ChaDate = @ChangeDate,
            ChaBy = @ChangeBy,
            ResGpDesc = @Descrip
        where id = @master_id
        
        IF NOT EXISTS (SELECT id FROM [CapacityManagement].[dbo].[ResGp_Master] WHERE id = @master_id)  
        INSERT INTO [CapacityManagement].[dbo].[ResGp_Master] (ResGp, Dept, CreDate, CreBy, ChaDate, ChaBy, ResGpDesc, plant)
        values(@ResGp, @Dept, @CreateDate, @CreateBy, @ChangeDate, @ChangeBy, @Descrip, @Plant)

            --2、操作[Step_ResGp_Maping]
        update [CapacityManagement].[dbo].[Step_ResGp_Maping]
        set ChaDate = @ChangeDate,
            ChaBy = @ChangeBy
        where Master_id = @master_id
        and step = @Step_mapping

        if not exists(select master_id from [CapacityManagement].[dbo].[Step_ResGp_Maping] where Master_id = @master_id    and step = @Step_mapping)
        insert into [CapacityManagement].[dbo].[Step_ResGp_Maping](step, resgp, CreDate, CreBy, ChaDate, ChaBy, Master_id)
        values(@Step_mapping, @ResGp, @CreateDate, @CreateBy, @ChangeDate, @ChangeBy, @master_id)

        --五、取下一条数据
        fetch next from master_cursor 
                  into @ResGp,
                        @Plant,
                        @Dept,
                        @Descrip,
                        @Step_mapping,
                        @CreateDate,
                        @CreateBy,
                        @ChangeDate,
                        @ChangeBy 
       end      
       --六、关闭游标
       close master_cursor
       --七、释放游标
       deallocate master_cursor 
      end
      
     commit transaction
    end try 
    begin catch
    select ERROR_MESSAGE() as errorMessage
    rollback transaction
    end catch

END
GO

注释也是够详细了,操作数据那个过程,业务需求是对一堆数据进行插入,如果该数据已经存在,就进行更新,如果属于新数据,那么就进行插入(所以博主想到的是先遍历全部根据主键进行update,再根据主键查找是否存在该数据,若没有,则插入新数据),其实读者可以不细看,因为操作过程是根据业务需求,这里主要讲解游标的使用。

值得留意的一点:存储过程创建一个table类型的变量是用来存储程序传过来的DataTable对象,不仅字段个数要与DataTable的列数一致,类型也要特别注意,假设如果将DataTable的非数字列传给table类型里面的int类型字段,则该存储过程无法执行,会抛出异常。

注意:使用游标时,代码里使用一些局部变量存放查找的值(如@Master_id),如果第一次循环,@Master_id可以通过select查找到值,到了第二次select时却没有查找到值,那么@Master_id这个局部变量在第二次的循环里面的值不是null,也不是0或"",反而是第一次循环的值(即上一次循环的值

posted @ 2018-10-24 10:39  KamShing  阅读(6361)  评论(0编辑  收藏  举报