create PROC SaveColligate
@JLRQ datetime,
@NRJD varchar(50),
@GZQK varchar(500),
@ZBGB varchar(50),
@ZBRS varchar(50),
@ZHBY varchar(50),
@ZHLJ varchar(50),
@GZBY varchar(50),
@GZLJ varchar(50),
@TQSW varchar(50),
@TQXW varchar(50),
@PHCB varchar(50),
@XMMC varchar(50),
@GZNR varchar(500),
@PROJECTNAME varchar(100),
@ZYCB varchar(500),
@ZYZY varchar(50),
@TRRQ datetime,
@JHFYRQ datetime,
@JHGZTS int,
@ZCRS varchar(50),
@HXGZJH varchar(500),
@HY numeric,
@CY numeric,
@CS numeric
as
begin
--对综合报表中第4个表进行操作
select @XMMC=a.XMMC,@GZNR=b.GZNR,@JLRQ=a.RQ
from HYGC_SHIP_DAILY as a , HYGC_SHIP_DAILY_WORK_CONTENT as b
where CONVERT(VARCHAR(10),a.RQ,120) = CONVERT(VARCHAR(10),getdate(),120) and a.BH=b.BH
if exists(select * from T_COLLIGATE3 where XMMC=@XMMC)
begin
update T_COLLIGATE3 set XMMC=@XMMC,GZNR=@GZNR,JLRQ=@JLRQ
end
else
begin
insert into T_COLLIGATE3 (XMMC,GZNR,JLRQ) values(@XMMC,@GZNR,@JLRQ)
end
--对综合报表中第2个表进行操作
declare @bh varchar(50)
declare @xmmc2 varchar(100)
declare @zzyc varchar(50)
declare @gznr2 varchar(500)
declare @zbgb2 varchar(50)
declare @rs2 varchar(50)
declare @cbmc varchar(50)
declare @swflfx varchar(50)
declare @xwflfx varchar(50)
declare @rq datetime
set @rq=getdate()
DECLARE Row_CurSor CURSOR FOR
select d.bh,d.ZZYC,d.GZNR,d.ZBGB,d.rs,e.cbmc,d.xmmc,d.swflfx,d.xwflfx from
(select a.bh,a.ZZYC,b.GZNR,a.ZBGB,c.rs,a.xmmc,a.RQ,a.SWFLFX,a.XWFLFX from HYGC_SHIP_DAILY as a , HYGC_SHIP_DAILY_WORK_CONTENT as b,
HYGC_SHIP_DAILY_PERSONNEL as c
where a.BH=b.BH and a.RQ='2010-1-6' and a.BH=c.BH ) as d,HYGC_SHIP_DAILY_FZCBDT as e
where d.BH=e.BH
OPEN Row_Cursor
FETCH NEXT FROM Row_Cursor INTO @bh,@zzyc,@gznr2,@zbgb2,@rs2,@cbmc,@xmmc2,@swflfx,@xwflfx
WHILE @@Fetch_Status = 0
BEGIN
if exists (select * from T_COLLIGATE2 where JLRQ=@rq and NRJD=@zzyc)
begin
update T_COLLIGATE2 set PHCB=@cbmc
end
else
begin
insert into T_COLLIGATE2 (JLRQ,NRJD,GZQK,ZBGB,ZBRS,PHCB,BH,TQSW,TQXW) values(@rq,@xmmc2+':'+@zzyc,@gznr2,@zbgb2,@rs2,@cbmc,@bh,@swflfx,@xwflfx)
end
FETCH NEXT FROM Row_Cursor INTO @bh,@zzyc,@gznr2,@zbgb2,@rs2,@cbmc,@xmmc2,@swflfx,@xwflfx
end
CLOSE Row_Cursor
DEALLOCATE Row_Cursor
declare @year int
declare @year2 int
set @year=DATEPART(year, GETDATE())
set @year2=@year-1
declare @zhtsby int
declare @zhtslj int
declare @gztsby int
declare @gztslj int
select @zhtsby=cast(b.MTZYLJ AS decimal(5,2))+cast(b.THLJ AS decimal(5,2))+cast(b.ZCSGZYLJ AS decimal(5,2))
+cast(b.YZDJLJ AS decimal(5,2))+cast(b.TQDJLJ AS decimal(5,2))+cast(b.SBDJLJ AS decimal(5,2))
+cast(b.QTDJLJ AS decimal(5,2))+cast(b.DHLJ AS decimal(5,2))
from T_COLLIGATE2 as a ,HYGC_SHIP_DAILY_CBHSTJ as b,HYGC_SHIP_DAILY as c
where a.BH=b.BH and a.JLRQ=c.RQ
select @zhtslj=sum(cast(b.MTZYLJ AS decimal(5,2)))+sum(cast(b.THLJ AS decimal(5,2)))+sum(cast(b.ZCSGZYLJ AS decimal(5,2)))
+sum(cast(b.YZDJLJ AS decimal(5,2)))+sum(cast(b.TQDJLJ AS decimal(5,2)))+sum(cast(b.SBDJLJ AS decimal(5,2)))
+sum(cast(b.QTDJLJ AS decimal(5,2)))+sum(cast(b.DHLJ AS decimal(5,2)))
from T_COLLIGATE2 as a ,HYGC_SHIP_DAILY_CBHSTJ as b,HYGC_SHIP_DAILY as c
where a.BH=b.BH and a.JLRQ=c.RQ and c.RQ <str(@year)+'-'+'12'+'-'+'26' and C.RQ > str(@year2)+'-'+'12'+'-'+'25'
select @gztsby=cast(b.MTZYLJ AS decimal(5,2))+cast(b.THLJ AS decimal(5,2))+cast(b.ZCSGZYLJ AS decimal(5,2))
from T_COLLIGATE2 as a ,HYGC_SHIP_DAILY_CBHSTJ as b,HYGC_SHIP_DAILY as c
where a.BH=b.BH and a.JLRQ=c.RQ
select @gztslj=sum(cast(b.MTZYLJ AS decimal(5,2)))+sum(cast(b.THLJ AS decimal(5,2)))+sum(cast(b.ZCSGZYLJ AS decimal(5,2)))
from T_COLLIGATE2 as a ,HYGC_SHIP_DAILY_CBHSTJ as b,HYGC_SHIP_DAILY as c
where a.BH=b.BH and a.JLRQ=c.RQ and c.RQ <str(@year)+'-'+'12'+'-'+'26' and C.RQ > str(@year2)+'-'+'12'+'-'+'25'
update T_COLLIGATE2 set ZHBY=str(@zhtsby),ZHLJ=str(@zhtslj),GZBY=str(@gztsby),GZLJ=str(@gztslj) where JLRQ=@rq and NRJD=@zzyc
--对综合报表中第1个表进行操作
declare @ID3 varchar(50)
declare @XMMC3 varchar(50)--项目名称
declare @CBMC3 varchar(50)--船舶名称
declare @UseDate3 DateTime--投入日期
declare @ComebackDate3 DateTime--计划复原日期
declare @WorkDays3 int--计划工作天数
declare @FollowWorkPlan3 varchar(500)--后续计划
declare @DRHY3 Decimal--耗油
declare @DRCY3 Decimal--存油
declare @DRCS3 Decimal--存水
declare @GZNR3 varchar(500)--工作内容
declare @GZDT3 varchar(500)--工作动态
declare @rz3 datetime
declare @RS3 varchar(50)
DECLARE Row_CurSor2 CURSOR FOR
select DISTINCT f.XMMC,f.ZZYC,c.UseDate,c.ComebackDate,c.WorkDays,
c.FollowWorkPlan,G.RS,d.DRHY,d.DRCY,d.DRCS,f.BH ,b.GZNR ,f.RQ
from
(select a.XMMC,a.ZZYC,a.BH,a.RQ from HYGC_SHIP_DAILY as a where
CONVERT(VARCHAR(10),a.RQ,120) = CONVERT(VARCHAR(10),'2009-12-29',120)) as f
left join T_Plan as c on f.XMMC=c.ProjectName and f.ZZYC=C.ShipName
left join HYGC_SHIP_DAILY_OIL_WATER as d on f.BH=d.BH
left join HYGC_SHIP_DAILY_WORK_CONTENT as b on f.BH=b.BH
left join HYGC_SHIP_DAILY_PERSONNEL as g on f.bh=g.bh
OPEN Row_Cursor
FETCH NEXT FROM Row_Cursor2 INTO @XMMC3,@CBMC3,@UseDate3,@ComebackDate3,@WorkDays3,@FollowWorkPlan3,@RS3,@DRHY3,@DRCY3,@DRCS3,@ID3,@GZNR,@rz3
WHILE @@Fetch_Status = 0
BEGIN
if exists (select * from T_COLLIGATE1 where JLRQ=@rz3 and PROJECTNAME=@XMMC3 AND ZYCB=@CBMC3)
begin
update T_COLLIGATE1 set PHCB=@cbmc, GZNR=@GZNR3,TRRQ=@UseDate3,JHFYRQ=@ComebackDate3,
JHGZTS=@WorkDays3,ZCRS=@RS3,HXGZJH=@FollowWorkPlan3,HY=@DRHY3,CY=@DRCY3,CS=@DRCS3
end
else
begin
insert into T_COLLIGATE1 (JLRQ,XMMC,ZZYC,GZNR,TRRQ,JHFYRQ,JHGZTS3,ZCRS,HXGZJH,HY,CY,CS,BH)
values
(@rz3,@XMMC3,@CBMC3,@GZNR3,@UseDate3,@ComebackDate3,@WorkDays3,@RS3,@FollowWorkPlan3,@DRHY3,@DRCY3,@DRCS3,@ID3)
end
FETCH NEXT FROM Row_Cursor2 INTO @XMMC3,@CBMC3,@UseDate3,@ComebackDate3,@WorkDays3,@FollowWorkPlan3,@RS3,@DRHY3,@DRCY3,@DRCS3,@ID3,@GZNR3,@rz3
end
CLOSE Row_Cursor2
DEALLOCATE Row_Cursor2
DECLARE Row_CurSor3 CURSOR FOR
select DISTINCT f.XMMC,f.CBMC,f.GZDT,c.UseDate,c.ComebackDate,c.WorkDays,
c.FollowWorkPlan,G.RS,d.DRHY,d.DRCY,d.DRCS,f.BH,f.GZDT,f.RQ
from
(select a.XMMC,i.CBMC,i.BH,a.RQ,i.GZDT from HYGC_SHIP_DAILY as a,HYGC_SHIP_DAILY_FZCBDT as i where
CONVERT(VARCHAR(10),a.RQ,120) = CONVERT(VARCHAR(10),'2009-12-29',120) and a.bh=i.bh) as f
left join T_Plan as c on f.XMMC=c.ProjectName and f.CBMC=C.ShipName
left join HYGC_SHIP_DAILY_OIL_WATER as d on f.BH=d.BH
left join HYGC_SHIP_DAILY_PERSONNEL as g on f.bh=g.bh
OPEN Row_Cursor3
FETCH NEXT FROM Row_Cursor3 INTO @XMMC3,@CBMC3,@GZDT3,@UseDate3,@ComebackDate3,@WorkDays3,@FollowWorkPlan3,@RS3,@DRHY3,@DRCY3,@DRCS3,@ID3,@GZNR,@rz3
WHILE @@Fetch_Status = 0
BEGIN
if exists (select * from T_COLLIGATE1 where JLRQ=@rz3 and XMMC=@XMMC3 AND ZZYC=@CBMC3)
begin
update T_COLLIGATE1 set PHCB=@cbmc, GZNR=@GZNR3,TRRQ=@UseDate3,JHFYRQ=@ComebackDate3,
JHGZTS=@WorkDays3,ZCRS=@RS3,HXGZJH=@FollowWorkPlan3,HY=@DRHY3,CY=@DRCY3,CS=@DRCS3
end
else
begin
insert into T_COLLIGATE1 (JLRQ,XMMC,ZZYC,GZNR,TRRQ,JHFYRQ,JHGZTS3,ZCRS,HXGZJH,HY,CY,CS,BH)
values
(@rz3,@XMMC3,@CBMC3,@GZNR3,@UseDate3,@ComebackDate3,@WorkDays3,@RS3,@FollowWorkPlan3,@DRHY3,@DRCY3,@DRCS3,@ID3)
end
FETCH NEXT FROM Row_Cursor3 INTO @XMMC3,@CBMC3,@GZDT3,@UseDate3,@ComebackDate3,@WorkDays3,@FollowWorkPlan3,@RS3,@DRHY3,@DRCY3,@DRCS3,@ID3,@GZNR,@rz3
end
CLOSE Row_Cursor3
DEALLOCATE Row_Cursor3
DECLARE Row_CurSor4 CURSOR FOR
select DISTINCT f.BH ,b.GZNR ,f.RQ,f.XMMC,f.ZZYC
from
(select a.XMMC,a.ZZYC,a.BH,a.RQ from HYGC_SHIP_DAILY as a where
CONVERT(VARCHAR(10),a.RQ,120) = CONVERT(VARCHAR(10),'2009-12-29',120)) as f
left join HYGC_PROJECT_DAILY_GZNR as b on f.BH=b.BH
OPEN Row_Cursor4
FETCH NEXT FROM Row_Cursor4 INTO @ID3,@GZNR,@rz3,@XMMC3,@CBMC3
WHILE @@Fetch_Status = 0
BEGIN
if exists (select * from T_COLLIGATE1 where JLRQ=@rz3 and XMMC=@XMMC3 AND ZZYC=@CBMC3)
begin
update T_COLLIGATE1 set GZNR=@GZNR3
end
FETCH NEXT FROM Row_Cursor4 INTO @ID3,@GZNR,@rz3,@XMMC3,@CBMC3
end
CLOSE Row_Cursor4
DEALLOCATE Row_Cursor4
end
相关视频
相关阅读 SQL2005新建复制“找不到存储过程 错误：2812”的解决方法通用存储过程.分页存储过程如何在SQLServer 2005中列所有存储过程如何在Access中模拟SqlServer存储过程翻页SQLSERVER存储过程及调用详解mysql存储过程学习笔记用PHP调用Oracle存储过程asp存储过程使用大全
热门文章 没有查询到任何记录。
最新文章
没有查询到任何记录。
Oracle如何提高SQL执行效率Oracle统计信息与子分区为什么对数据库要加DISTINCTOracle SQL 常见问题方案(一)
人气排行 SQL水晶报表存储过程为什么对数据库要加DISTINCTOracle如何提高SQL执行效率MySQL从后门进企业市场常见数据库系统之比较 - Oracle数据库常见数据库系统之比较 - SYBASE 和 SQL SER用wu-ftpd架设FTP服务器常见数据库系统之比较 - DB2数据库
查看所有0条评论>>