use STDB
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EM_CARDS_UPDATES]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[EM_CARDS_UPDATES]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ST_PERSONS_UPDATES]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ST_PERSONS_UPDATES]
GO
create procedure EM_CARDS_UPDATES
as
begin
begin tran
Declare @v_person_no varchar(10),@v_sf_card varchar(10),@v_type_no varchar(10),@v_Note varchar(10),@v_type varchar(10)
DECLARE Em_Cards_Cursor CURSOR FOR
SELECT person_no,sf_card,type_no,Note,type FROM EM_CARDS_UPDATE
OPEN Em_Cards_Cursor
FETCH NEXT FROM Em_Cards_Cursor
Into @v_person_no,@v_sf_card,@v_type_no,@v_Note,@v_type
WHILE @@FETCH_STATUS = 0
begin
--处理insert
if @v_type='insert'
begin
delete from em_cards where person_no=@v_person_no
insert into em_cards (person_no,sf_card,type_no,Note)values(@v_person_no,@v_sf_card,@v_type_no,@v_Note)
end
--处理update
if @v_type='update'
begin
delete from em_cards where person_no=@v_person_no
insert into em_cards (person_no,sf_card,type_no,Note)values(@v_person_no,@v_sf_card,@v_type_no,@v_Note)
end
--处理delete
if @v_type='delete'
begin
delete from em_cards where person_no=@v_person_no
end
FETCH NEXT FROM Em_Cards_Cursor into @v_person_no,@v_sf_card,@v_type_no,@v_Note,@v_type
end
CLOSE Em_Cards_Cursor
DEALLOCATE Em_Cards_Cursor
--
commit tran
end
GO
create procedure ST_PERSONS_UPDATES
as
begin
begin tran
Declare @v_person_no varchar(10),@v_Person_Name varchar(10),@v_Dept_No varchar(10),@v_Note varchar(10),@v_type varchar(10)
DECLARE ST_PERSONS_Cursor CURSOR FOR
SELECT person_no,Person_Name,Dept_No,Note,Type FROM ST_PERSONS_UPDATE
OPEN ST_PERSONS_Cursor
FETCH NEXT FROM ST_PERSONS_Cursor into @v_person_no,@v_Person_Name,@v_Dept_No,@v_Note,@v_type
WHILE @@FETCH_STATUS = 0
begin
--处理insert
if @v_type='insert'
begin
delete from ST_PERSONS where person_no=@v_person_no
insert into ST_PERSONS (person_no,Person_Name,Dept_No,Note)values(@v_person_no,@v_Person_Name,@v_Dept_No,@v_Note)
end
--处理update
if @v_type='update'
begin
delete from ST_PERSONS where person_no=@v_person_no
insert into ST_PERSONS (person_no,Person_Name,Dept_No,Note)values(@v_person_no,@v_Person_Name,@v_Dept_No,@v_Note)
end
--处理delete
if @v_type='delete'
begin
delete from ST_PERSONS where person_no=@v_person_no
end
FETCH NEXT FROM ST_PERSONS_Cursor into @v_person_no,@v_Person_Name,@v_Dept_No,@v_Note,@v_type
end
CLOSE ST_PERSONS_Cursor
DEALLOCATE ST_PERSONS_Cursor
--
commit tran
end
GO
