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

講解SQL Server數據庫中函數的使用方法

2013-11-15 14:50:44  來源: SQL Server 

  本文主要主要講解了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


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