下面将为您介绍在SQL中如何使用函数代替游标，供您参考，希望对您学习SQL中函数的使用能够有所帮助。
因为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 @TASKIDTASKID=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
【编辑推荐】
