中国石油大学（华东）数据库原理实验答案（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;