27,580
社区成员
发帖
与我相关
我的任务
分享
SET ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Query_Update]
AS
BEGIN
DECLARE @HSGX int , @NDJDW int , @CKSJDW INT, @LSJDW INT, @YHJDW INT,@NDJ NUMERIC(20,8), @CKSJ NUMERIC(20,8),@YHJ NUMERIC(20,8),@LSJ NUMERIC(20,8),@UNI CHAR(10),@DT DATETIME,@ZLDT DATETIME,@CPBH CHAR(16),@TM CHAR(16),@PP CHAR(16),@PM CHAR(50),@GG CHAR(20),@DW2 CHAR(2),@DW CHAR(2),@BZGG CHAR(10)
Select UNI,DT,ZLDT,CPBH,TM,PP,PM,GG,DW2,DW,HSGX,BZGG,NDJ,NDJDW,CKSJ,CKSJDW,YHJ,YHJDW,LSJ,LSJDW
Into #WaitDeal
From CPZL A
Where Not Exists (
Select UNI,DT,ZLDT,CPBH,TM,PP,PM,GG,DW2,DW,HSGX,BZGG,NDJ,NDJDW,CKSJ,CKSJDW,YHJ,YHJDW,LSJ,LSJDW
From CPZL_SANY B
Where A.UNI =B.UNI AND A.CPBH=B.CPBH AND A.ZLDT =B.ZLDT)
-- 定义游标逐条处理
Declare DealCusror Cursor Local For
Select UNI,DT,ZLDT,CPBH,TM,PP,PM,GG,DW2,DW,HSGX,BZGG,NDJ,NDJDW,CKSJ,CKSJDW,YHJ,YHJDW,LSJ,LSJDW
From #WaitDeal
Order By DT
--打开游标
Open DealCusror
--将游标当前行数据传给变量
Fetch Next From DealCusror
Into @UNI,@DT,@ZLDT,@CPBH,@TM,@PP,@PM,@GG,@DW2,@DW,@HSGX,@BZGG,@NDJ,@NDJDW,@CKSJ,@CKSJDW,@YHJ,@YHJDW,@LSJ,@LSJDW
While (@@Fetch_Status = 0)
--如果UNI存在则更新
Begin
If Exists (
Select UNI,DT,ZLDT,CPBH,TM,PP,PM,GG,DW2,DW,HSGX,BZGG,NDJ,NDJDW,CKSJ,CKSJDW,YHJ,YHJDW,LSJ,LSJDW
From CPZL_SANY
Where UNI=@UNI)
Begin
UPdate CPZL_SANY
Set DT=@DT,ZLDT=@ZLDT,CPBH=@CPBH,TM=@TM,PP=@PP,PM=@PM,GG=@GG,DW2=@DW2,DW=@DW,HSGX=@HSGX,BZGG=@BZGG,NDJ=@NDJ
,NDJDW=@NDJDW,CKSJ=@CKSJ,CKSJDW=@CKSJDW,YHJ=@YHJ,YHJDW=@YHJDW,LSJ=@LSJ,LSJDW=@LSJDW
Where UNI=@UNI
END
--否则插入
ELSE
Begin
Insert Into CPZL_SANY(
UNI,DT,ZLDT,CPBH,TM,PP,PM,GG,DW2,DW,HSGX,BZGG,NDJ,NDJDW,CKSJ,CKSJDW,YHJ,YHJDW,LSJ,LSJDW)
Values (
@UNI,@DT,@ZLDT,@CPBH,@TM,@PP,@PM,@GG,@DW2,@DW,@HSGX,@BZGG,@NDJ,@NDJDW,@CKSJ,@CKSJDW,@YHJ,@YHJDW,@LSJ,@LSJDW)
End
Fetch next From DealCusror
Into @UNI,@DT,@ZLDT,@CPBH,@TM,@PP,@PM,@GG,@DW2,@DW,@HSGX,@BZGG,@NDJ,@NDJDW,@CKSJ,@CKSJDW,@YHJ,@YHJDW,@LSJ,@LSJDW
End
Close DealCusror
Deallocate DealCusror
Drop Table #WaitDeal
END