这里关于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/23 16:09:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create type ResGpCap as table
(
ResGp varchar(10),
Plant varchar(12),
Dept varchar(12),
workDate date,
WkHrPerDaily int,
DailyAva int,
Remark varchar(250),
CreateDate datetime,
CreateBy char(8),
ChangeDate datetime,
ChangeBy char(8)
)
go
CREATE PROCEDURE [dbo].[USP_uploadResGpCapacity] @ResGpCap ResGpCap readonly,@OperationType nvarchar(3)
AS
BEGIN
SET NOCOUNT ON;
begin try
begin transaction
declare @ResGp varchar(10),
@Plant varchar(12),
@Dept varchar(12),
@workDate date,
@WkHrPerDaily int,
@DailyAva int,
@Remark varchar(250),
@CreateDate datetime,
@CreateBy char(8),
@ChangeDate datetime,
@ChangeBy char(8),
@master_id int
if(@OperationType ='A')
begin
--一、声明游标
declare ResGpCap_cursor cursor for select * from @ResGpCap
--二、打开游标
open ResGpCap_cursor
--三、取第一条数据
fetch next from ResGpCap_cursor
into @ResGp,
@Plant,
@Dept,
@workDate,
@WkHrPerDaily,
@DailyAva,
@Remark,
@CreateDate,
@CreateBy,
@ChangeDate,
@ChangeBy
--四、操作数据
while @@FETCH_STATUS = 0
begin
SELECT @master_id=id FROM [CapacityManagement].[dbo].[ResGp_Master]
where resgp = @ResGp
and plant = @Plant
and dept = @Dept
--update操作
update [CapacityManagement].[dbo].[ResGp_Cap]
set WorkHr = @WkHrPerDaily,
AvaQty = @DailyAva,
Remark = @Remark,
Modified_by = @ChangeBy,
Modified_on = @ChangeDate
where master_id = @master_id
and Valid_Form = @workDate
and Valid_To = @workDate
--insert操作
if not exists(select master_id from [CapacityManagement].[dbo].[ResGp_Cap] where Master_id = @master_id and Valid_Form = @workDate and Valid_To = @workDate)
insert into [CapacityManagement].[dbo].[ResGp_Cap](
Master_id,
AvaQty,
WorkHr,
WorkPer,
Valid_Form,
Valid_To,
Remark,
Created_by,
Created_on,
Modified_by,
Modified_on)
values(
@master_id,
@DailyAva,
@WkHrPerDaily,
'D',
@workDate,
@workDate,
@Remark,
@CreateBy,
@CreateDate,
@ChangeBy,
@ChangeDate)
--五、取下一条数据
fetch next from ResGpCap_cursor
into @ResGp,
@Plant,
@Dept,
@workDate,
@WkHrPerDaily,
@DailyAva,
@Remark,
@CreateDate,
@CreateBy,
@ChangeDate,
@ChangeBy
end
--六、关闭游标
close ResGpCap_cursor
--七、释放游标
deallocate ResGpCap_cursor
end
commit transaction
end try
begin catch
select ERROR_MESSAGE() as errorMessage
rollback transaction
end catch
END
GO
注释也是够详细了,操作数据那个过程,业务需求是对一堆数据进行插入,如果该数据已经存在,就进行更新,如果属于新数据,那么就进行插入(所以博主想到的是先遍历全部根据主键进行update,再根据主键查找是否存在该数据,若没有,则插入新数据),其实读者可以不细看,因为操作过程是根据业务需求,这里主要讲解游标的使用。