您的位置主页 > MYSQL

分析MS SQL Server里函数的两种用法

  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