本文主要主要讲解了SQL Server数据库中函数的两种用法具体内容请参考下文
◆ 由于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
◆ 我们要根据某表的某些记录先计算后求和因为无法存储中间值平时我们也用游标的方法进行计算但SQL Server 里支持
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
