本文主要主要講解了SQL Server數據庫中函數的兩種用法,具體內容請參考下文:
1. 由於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(0.0)
- END
- SELECT @TASKID=TASKID,@PERCENT=ISNULL(WORKPERCENT,0)/100
- FROM TABLETASKPHASE
- WHERE ID=@TASKPHASEID
- SELECT @HOUR=ISNULL(TASKTIME,0) 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,0)*ISNULL
- (FMERATE,0)*ISNULL(B.BASFAILURERATE,0)*[DBO].[FUN_GETTIME](C.ID)
- FROM TABLEFMECA ,TABLERELATION B,TABLETASKPHASE C
- WHERE ROID=@ROID AND TASKPHASEID=C.ID AND B.ID=@ROID
- SELECT @CA=SUM(ISNULL(Cvalue_M,0)) FROM TABLEFMECA WHERE ROID=@ROID
- UPDATE TABLERELATION
- SET CRITICALITY=@CA
- WHERE ID=@ROID
- END
- GO
2. 我們要根據某表的某些記錄,先計算後求和,因為無法存儲中間值,平時我們也用游標的方法進行計算。但SQL Server 2000裡支持。
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(0.0)
- END
- SELECT @QXS= ISNULL(XS,0) FROM TABLEQUALITY WHERE ID=@QUALITYID
- SELECT @G=ISNULL(FRATE_G,0) FROM TABLEFAILURERATE
- WHERE (SUBKINDID=@PARTID)
- AND( ENID=@ENID)
- AND ( DATASOURCEID=@SOURCEID)
- AND( ( (ISNULL(MINCOUNT,0)<=ISNULL(@COUNT,0))
- AND ( ISNULL(MAXCOUNT,0)>=ISNULL(@COUNT,0)))
- OR(ISNULL(@COUNT,0)>ISNULL(MAXCOUNT,0)))
- SET @RATE=ISNULL(@QXS*@G,0)
- RETURN (@RATE)
- END
調用函數的存儲過程部分: