文章导航PC6首页软件下载单机游戏安卓资源苹果资源

pc软件新闻网络操作系统办公工具编程服务器软件评测

安卓新闻资讯应用教程刷机教程安卓游戏攻略tv资讯深度阅读综合安卓评测

苹果ios资讯苹果手机越狱备份教程美化教程ios软件教程mac教程

单机游戏角色扮演即时战略动作射击棋牌游戏体育竞技模拟经营其它游戏游戏工具

网游cf活动dnf活动lol周免英雄lol礼包

手游最新动态手游评测手游活动新游预告手游问答

您的位置:首页技术开发数据库文摘 → SQL水晶报表存储过程

SQL水晶报表存储过程

时间:2010/1/28 12:02:00来源:本站整理作者:我要评论(0)

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数据库