发布时间 : 星期日 文章中国石油大学（华东）数据库原理实验答案（sql语句）更新完毕开始阅读
结算日期 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;