中国石油大学（华东）数据库原理实验答案（sql语句）(4)

FETCHFIRSTFROM COST_CRU

INTO @单据号,@预算单位,@井号,@预算金额,@预算人,@预算日期, @开工日期,@完工日期,@施工单位,@施工内容,@材料费,@人工费,

@设备费,@其他费用,@结算金额,@结算人,@结算日期,@入账金额,@入账人,@入账日期

WHILE(@@FETCH_STATUS=0) BEGIN

PRINT+@单据号+@预算单位 +@井号 +@预算金额 +' '+@预算人+' '+@预算日期 +' '+@开工日期 +' '+@完工日期 +' '+@施工单位 +' '+@施工内容 +@材料费+@人工费 +@设备费

+@其他费用 +@结算金额 +' '+@结算人 +' '+@结算日期 +@入账金额 +' '+@入账人 +@入账日期

FETCHNEXTFROM COST_CRU INTO

@单据号,@预算单位,@井号,@预算金额,@预算人,@预算日期,

@开工日期,@完工日期,@施工单位,@施工内容,@材料费,@人工费,

@设备费,@其他费用,@结算金额,@结算人,@结算日期,@入账金额,@入账人,@入账日期 END ;

ifOBJECT_ID('YUNXING_SITU','p')isnotnull dropprocedure YUNXING_SITU; go

CREATEPROCEDURE YUNXING_SITU

@DANWEIDAIMA VARCHAR(30),@STARTTIME DATE,@ENDTIME DATE AS

DECLARE @DANWEIMING VARCHAR(30);

DECLARE @YSMONEY money,@JSMONEY money,@RZMONEY money, @NOJSMONEY money,@NORUZHANGMONEY money;

SELECT @DANWEIMING='单位名称' FROM UNITNO

WHERE @DANWEIDAIMA='单位代码' SET

@YSMONEY=(SELECTSUM(预算金额) FROM COST

WHERE 预算单位 LIKE @DANWEIDAIMA+'%'AND

预算日期 BETWEEN @STARTTIME AND @ENDTIME and 预算金额 isnotnull) SET

@JSMONEY=(SELECTSUM(结算金额)FROM COST WHERE 预算单位 LIKE @DANWEIDAIMA+'%'AND

结算日期 BETWEEN @STARTTIME AND @ENDTIME and 结算金额 isnotnull) SET

@RZMONEY=(SELECTSUM(入账金额)FROM COST WHERE 预算单位 LIKE @DANWEIDAIMA+'%'AND 入账日期 BETWEEN @STARTTIME AND @ENDTIME AND 入账人 ISNOTNULL) SET

@NOJSMONEY=(SELECTSUM(预算金额)FROM COST WHERE 预算日期 BETWEEN @STARTTIME AND @ENDTIME AND 结算金额 ISNULL) SET

@NORUZHANGMONEY=(SELECTSUM(结算金额)FROM COST WHERE 结算日期 BETWEEN @STARTTIME AND @ENDTIME AND 入账人 ISNULL)

IF @YSMONEY ISNULLSET @YSMONEY=0 IF @JSMONEY ISNULLSET @JSMONEY=0 IF @RZMONEY ISNULLSET @RZMONEY=0 IF @NOJSMONEY ISNULLSET @NOJSMONEY=0

IF @NORUZHANGMONEY ISNULLSET @NORUZHANGMONEY=0 SET @NOJSMONEY=@YSMONEY-@JSMONEY

SET @NORUZHANGMONEY=@JSMONEY-@RZMONEY

/*DECLARE @RESULT VARCHAR(100)

SET @RESULT=CONVERT(VARCHAR,@YSMONEY)+' '+CONVERT(VARCHAR,@JSMONEY)+' '+CONVERT(VARCHAR,@RZMONEY)

+' '+CONVERT(VARCHAR,@NOJSMONEY)+' '+CONVERT(VARCHAR,@NORUZHANGMONEY)

PRINT @DANWEIMING+'单位

'+CONVERT(VARCHAR,@STARTTIME,102)+'--'+CONVERT(VARCHAR,@ENDTIME,102)+'成本运行情况'

PRINT '预算金额 结算金额 入账金额 未结算金额 未入账金额' PRINT @RESULT*/ print

@DANWEIMING+convert(varchar,@STARTTIME,102)+'--'+convert(varchar,@ENDTIME,102)+' 成本运营情况'

print'预算金额结算金额入账金额未结算金额未入账金额'

printconvert(varchar,@YSMONEY)+' '+convert(varchar,@JSMONEY)+' '+convert(varchar,@RZMONEY)

+' '+convert(varchar,@NOJSMONEY)+' '+convert(varchar,@NORUZHANGMONEY); GO

EXECUTE

YUNXING_SITU@DANWEIDAIMA='1122',@STARTTIME='2016-5-1',@ENDTIME='2016-5-29'; EXECUTE

YUNXING_SITU@DANWEIDAIMA='112201',@STARTTIME='2016-5-1',@ENDTIME='2016-5-29'; EXECUTE

YUNXING_SITU@DANWEIDAIMA='112201001',@STARTTIME='2016-5-1',@ENDTIME='2016-5-29'; GO

实验七

ifOBJECT_ID('TRR','TR')isnotnull droptrigger TRR GO

CREATETRIGGER TRR ON COST AFTERINSERT AS

DECLARE

@JS_MONEY MONEY; DECLARE

@MA_MONEY MONEY, @PE_MONEY MONEY, @EQ_MONEY MONEY, @EL_MONEY MONEY;

SELECT @MA_MONEY=材料费,@PE_MONEY=人工费,@EQ_MONEY=设备费,@EL_MONEY=其他费用 FROM COST;

SET @JS_MONEY=@MA_MONEY+@PE_MONEY+@EQ_MONEY+@EL_MONEY;