update里不能用存储过程然而要根据更新表的某些字段还要进行计算我们常常采用游标的方法这里用函数的方法实现
函数部分
CREATE FUNCTION [DBO][FUN_GETTIME] (@TASKPHASEID INT)
RETURNS FLOAT AS
BEGIN
DECLARE @TASKID INT
@HOUR FLOAT
@PERCENT FLOAT
@RETURN FLOAT
IF @TASKPHASEID IS NULL
BEGIN
RETURN()
END
SELECT @TASKID=TASKID@PERCENT=ISNULL(WORKPERCENT)/
FROM TABLETASKPHASE
WHERE ID=@TASKPHASEID
SELECT @HOUR=ISNULL(TASKTIME) FROM TABLETASK
WHERE ID=@TASKID
SET @RETURN=@HOUR*@PERCENT
RETURN (@RETURN)
END
调用函数的存储过程部分
CREATE PROCEDURE [DBO][PROC_CALCCA]
@ROID INT
AS
BEGIN
DECLARE @CA FLOAT
UPDATE TABLEFMECA
SET
Cvalue_M= ISNULL(MODERATE)*ISNULL(FMERATE)*ISNULL(BBASFAILURERATE)*[DBO][FUN_GETTIME](CID)
FROM TABLEFMECA TABLERELATION BTABLETASKPHASE C
WHERE ROID=@ROID AND TASKPHASEID=CID AND BID=@ROID
SELECT @CA=SUM(ISNULL(Cvalue_M)) FROM TABLEFMECA WHERE ROID=@ROID
UPDATE TABLERELATION
SET CRITICALITY=@CA
WHERE ID=@ROID
END
GO
我们要根据某表的某些记录先计算后求和因为无法存储中间值平时我们也用游标的方法进行计算但sqlserver里支持
SUM ( [ ALL | DISTINCT ] expression )
expression
是常量列或函数或者是算术按位与字符串等运算符的任意组合因此我们可以利用这一功能
函数部分
CREATE FUNCTION [DBO][FUN_RATE] (@PARTID INT@ENID INT@SOURCEID INT @QUALITYID INT@COUNT INT)
RETURNS FLOAT AS
BEGIN
DECLARE @QXS FLOAT @G FLOAT @RATE FLOAT
IF (@ENID=NULL) OR (@PARTID=NULL) OR (@SOURCEID=NULL) OR (@QUALITYID=NULL)
BEGIN
RETURN()
END
SELECT @QXS= ISNULL(XS) FROM TABLEQUALITY WHERE ID=@QUALITYID
SELECT @G=ISNULL(FRATE_G) FROM TABLEFAILURERATE
WHERE (SUBKINDID=@PARTID) AND( ENID=@ENID) AND ( DATASOURCEID=@SOURCEID) AND( ( (ISNULL(MINCOUNT)<=ISNULL(@COUNT)) AND ( ISNULL(MAXCOUNT)>=ISNULL(@COUNT)))
OR(ISNULL(@COUNT)>ISNULL(MAXCOUNT)))
SET @RATE=ISNULL(@QXS*@G)
RETURN (@RATE)
END
调用函数的存储过程部分
CREATE PROC PROC_FAULTRATE
@PARTID INTEGER @QUALITYID INTEGER @SOURCEID INTEGER @COUNT INTEGER @ROID INT @GRADE INT@RATE FLOAT= OUTPUTAS
BEGIN
DECLARE
@TASKID INT
SET @RATE=
SELECT @TASKID=ISNULL(TASKPROID) FROM TABLERELATION WHERE ID=(SELECT PID FROM TABLERELATION WHERE ID=@ROID)
IF (@TASKID=) OR(@GRADE=) BEGIN
SET @RATE=
RETURN
END
SELECT @RATE=SUM([DBO][FUN_RATE] (@PARTIDENID@SOURCEID @QUALITYID@COUNT) *ISNULL(WORKPERCENT)/)
FROM TABLETASKPHASE
WHERE TASKID=@TASKID
END
GO
函数还可以返回表等希望大家一起讨论sqlserver里函数的妙用
