39470.htm

来自「一本很基础的SQL讲解」· HTM 代码 · 共 54 行

<link href="./dzs_cs.css" rel="stylesheet" type="text/css" />
<table width="96%" border="0" align="center" cellpadding="0" cellspacing="0">
      <tr>
        <td>&nbsp;</td>
      </tr>
      <tr>
        <td height="24" align="center" valign="bottom" class="d_font3">分析SQL Server里函数的两种用法</td>
      </tr>
      <tr>
        <td height="3" bgcolor="#E3E3E3"></td>
      </tr>
      <tr>
        <td>&nbsp;</td>
      </tr>
      <tr>
        <td class="d_font4"><P>1.因为update里不能用存储过程，然而要根据更新表的某些字段还要进行计算。我们常常采用游标的方法，这里用函数的方法实现。</P>
<P>函数部分：</P>
<P>
<TABLE cellSpacing=0 borderColorDark=#ffffff cellPadding=2 width=400 align=center borderColorLight=black border=1>
<TBODY>
<TR>
<TD class=code bgColor=#e6e6e6><PRE><P>　　CREATE FUNCTION [DBO].[FUN_GETTIME] (@TASKPHASEID INT) <BR>　　RETURNS FLOAT AS <BR>　　BEGIN <BR>　　DECLARE @TASKID INT, <BR>　　@HOUR FLOAT, <BR>　　@PERCENT FLOAT, <BR>　　@RETURN FLOAT <BR>　　IF @TASKPHASEID IS NULL <BR>　　BEGIN <BR>　　RETURN(0.0) <BR>　　END <BR>　　SELECT @TASKID=TASKID,@PERCENT=ISNULL(WORKPERCENT,0)/100 <BR>　　FROM TABLETASKPHASE <BR>　　WHERE <A href="mailto:ID=@TASKPHASEID">ID=@TASKPHASEID</A> <BR>　　SELECT @HOUR=ISNULL(TASKTIME,0) FROM TABLETASK <BR>　　WHERE <A href="mailto:ID=@TASKID">ID=@TASKID</A> <BR>　　SET @RETURN=@HOUR*@PERCENT <BR>　　RETURN (@RETURN) <BR>　　END&nbsp; </P></PRE></TD></TR></TBODY></TABLE></P>
<P>调用函数的存储过程部分：</P>
<P>
<TABLE cellSpacing=0 borderColorDark=#ffffff cellPadding=2 width=400 align=center borderColorLight=black border=1>
<TBODY>
<TR>
<TD class=code bgColor=#e6e6e6><PRE><P>　　CREATE PROCEDURE [DBO].[PROC_CALCCA] <BR>　　@ROID INT <BR>　　AS <BR>　　BEGIN <BR>　　DECLARE @CA FLOAT <BR>　　UPDATE TABLEFMECA <BR>　　SET <BR>　　Cvalue_M= ISNULL(MODERATE,0)*ISNULL(FMERATE,0)</P><P>      *ISNULL(B.BASFAILURERATE,0)*[DBO].[FUN_GETTIME](C.ID) <BR>　　FROM TABLEFMECA ,TABLERELATION B,TABLETASKPHASE C <BR>　　WHERE <A href="mailto:ROID=@ROID">ROID=@ROID</A> AND TASKPHASEID=C.ID AND <A href="mailto:B.ID=@ROID">B.ID=@ROID</A> <BR>　　SELECT @CA=SUM(ISNULL(Cvalue_M,0)) FROM TABLEFMECA WHERE <A href="mailto:ROID=@ROID">ROID=@ROID</A> <BR>　　UPDATE TABLERELATION <BR>　　SET <A href="mailto:CRITICALITY=@CA">CRITICALITY=@CA</A> <BR>　　WHERE <A href="mailto:ID=@ROID">ID=@ROID</A> <BR>　　END <BR>　　GO&nbsp; </P></PRE></TD></TR></TBODY></TABLE></P>
<P>2.我们要根据某表的某些记录，先计算后求和，因为无法存储中间值，平时我们也用游标的方法进行计算。但sqlserver2000里支持：</P>
<P>
<TABLE cellSpacing=0 borderColorDark=#ffffff cellPadding=2 width=400 align=center borderColorLight=black border=1>
<TBODY>
<TR>
<TD class=code bgColor=#e6e6e6><PRE><P>　　SUM ( [ ALL | DISTINCT ] expression )<BR>　　expression</P></PRE></TD></TR></TBODY></TABLE></P>
<P>是常量、列或函数，或者是算术、按位与字符串等运算符的任意组合。因此我们可以利用这一功能。</P>
<P>函数部分：</P>
<P>
<TABLE cellSpacing=0 borderColorDark=#ffffff cellPadding=2 width=400 align=center borderColorLight=black border=1>
<TBODY>
<TR>
<TD class=code bgColor=#e6e6e6><PRE><P>　　CREATE FUNCTION [DBO].[FUN_RATE] (@PARTID INT,@ENID INT,@SOURCEID INT, </P><P>      @QUALITYID INT,@COUNT INT) <BR>　　RETURNS FLOAT AS <BR>　　BEGIN <BR>　　DECLARE @QXS FLOAT, @G FLOAT, @RATE FLOAT <BR>　　IF (@ENID=NULL) OR (@PARTID=NULL) OR (@SOURCEID=NULL) OR (@QUALITYID=NULL) <BR>　　BEGIN <BR>　　RETURN(0.0) <BR>　　END <BR>　　SELECT @QXS= ISNULL(XS,0) FROM TABLEQUALITY WHERE <A href="mailto:ID=@QUALITYID">ID=@QUALITYID</A> <BR>　　SELECT @G=ISNULL(FRATE_G,0) FROM TABLEFAILURERATE <BR>　　WHERE (<A href="mailto:SUBKINDID=@PARTID">SUBKINDID=@PARTID</A>) AND( <A href="mailto:ENID=@ENID">ENID=@ENID</A>) AND ( <A href="mailto:DATASOURCEID=@SOURCEID">DATASOURCEID=@SOURCEID</A>) </P><P>     AND( ( (ISNULL(MINCOUNT,0)&lt;=ISNULL(@COUNT,0)) AND </P><P>     ( ISNULL(MAXCOUNT,0)&gt;=ISNULL(@COUNT,0))) <BR>　　OR(ISNULL(@COUNT,0)&gt;ISNULL(MAXCOUNT,0))) <BR>　　SET @RATE=ISNULL(@QXS*@G,0) <BR>　　RETURN (@RATE) <BR>　　END&nbsp; </P></PRE></TD></TR></TBODY></TABLE></P>
<P>调用函数的存储过程部分：</P>
<P>
<TABLE cellSpacing=0 borderColorDark=#ffffff cellPadding=2 width=400 align=center borderColorLight=black border=1>
<TBODY>
<TR>
<TD class=code bgColor=#e6e6e6><PRE><P>　　CREATE PROC PROC_FAULTRATE <BR>　　@PARTID INTEGER, @QUALITYID INTEGER, @SOURCEID INTEGER, @COUNT INTEGER, </P><P>     @ROID INT, @GRADE INT,@RATE FLOAT=0 OUTPUTAS <BR>　　BEGIN <BR>　　DECLARE <BR>　　@TASKID INT <BR>　　SET @RATE=0.0 <BR>　　SELECT @TASKID=ISNULL(TASKPROID,-1) FROM TABLERELATION WHERE </P><P>     ID=(SELECT PID FROM TABLERELATION WHERE <A href="mailto:ID=@ROID">ID=@ROID</A>) <BR>　　IF (@TASKID=-1) OR(@GRADE=1) BEGIN <BR>　　SET @RATE=0 <BR>　　RETURN <BR>　　END <BR>　　SELECT @RATE=SUM([DBO].[FUN_RATE] (@PARTID,ENID,@SOURCEID, </P><P>     @QUALITYID,@COUNT) *ISNULL(WORKPERCENT,0)/100.0) <BR>　　FROM TABLETASKPHASE <BR>　　WHERE <A href="mailto:TASKID=@TASKID">TASKID=@TASKID</A> <BR>　　END <BR>　　GO&nbsp; </P></PRE></TD></TR></TBODY></TABLE></P>
<P>函数还可以返回表等，希望大家一起讨论sqlserver里函数的妙用。</P>
<P align=right>（责任编辑 火凤凰 <A href="mailto:[e-mail]">[e-mail]</A>&nbsp; TEL：[phone]）</P></td>
      </tr>
      <tr>
        <td class="d_font4">&nbsp;</td>
      </tr>
    </table>