熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> SQL Server >> 正文

SQL Server裡函數的兩種用法(可以代替游標)

2022-06-13   來源: 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

   我們要根據某表的某些記錄先計算後求和因為無法存儲中間值平時我們也用游標的方法進行計算但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裡函數的妙用


From:http://tw.wingwit.com/Article/program/SQLServer/201311/22538.html
    推薦文章
    Copyright © 2005-2022 電腦知識網 Computer Knowledge   All rights reserved.