此存储过程给出的结果与Calc.需要传递的参数是x、均值、标准差和累积。累积参数提供了在x（0）处获得正态分布值或值<=x（1）的累积概率的选择。

create or replace FUNCTION NORMDIST(x_value number,mean_value number,stddev_value number, cumulative NUMBER DEFAULT 0)
RETURN NUMBER IS

        x number;
        t number;
        z number;
        ans number;

BEGIN
  IF (stddev_value = 0) THEN
  RETURN 1;
  END IF;

  x := (x_value-mean_value)/stddev_value;

  IF cumulative = 1 THEN
    z := abs(x)/SQRT(2);
    t := 1/(1+0.5*z);
    ans := t*exp(-z*z-1.26551223+t*(1.00002368+t*(0.37409196+t*(0.09678418+t*(-0.18628806+t*(0.27886807+t*(-1.13520398+t*(1.48851587+t*(-0.82215223+t*0.17087277)))))))))/2;
    If (x <= 0)
     Then RETURN ans;
     Else return  1-ans;
    End if;
  ELSE

   RETURN 1/(sqrt(2*3.14159265358979)*stddev_value)*Exp(-(Power(x_value-mean_value,2)/(2*Power(stddev_value,2)) ));
  END IF;

END;
/

字符串

--I wrote this function in PL/SQL and it works. I compared results with the NORMDIST 
 --Function in excel and the results match very closely. You will need to pass the  
  --following --parameters to the function.

-- 1. Value of X
-- 2. Value of Mean
-- 3. Value of Standard Deviation

--This function returns the same result  when you pass cumulative=TRUE in excel.

create or replace FUNCTION NORMSDIST(x_value number,mean_value number,stddev_value number)
RETURN NUMBER IS

        x number;
        t number;
        z number;
        ans number;

BEGIN
  IF (stddev_value = 0) THEN
  RETURN 1;
  END IF;

  x := (x_value-mean_value)/stddev_value;

  z := abs(x)/SQRT(2);

  t := 1.0/(1.0+0.5*z);

  ans := t*exp(-z*z-1.26551223+t*(1.00002368+t*(0.37409196+t*(0.09678418+t*(-0.18628806+t*(0.27886807+t*(-1.13520398+t*(1.48851587+t*(-0.82215223+t*0.17087277)))))))))/2.0;

     If (x <= 0)   
     Then RETURN ans;
     Else return  1-ans;
    End if; 

END NORMSDIST;

字符串

这是一个快速的解决方案，我没有试图获得最大的精度或性能。根据您请求，您可能需要调整数字格式、精度、计算逻辑等。

create or replace function calc_sn_pdf(x in number) return number
is
 pi      CONSTANT NUMBER := 3.14159265358979;
begin
  return 1/sqrt(2*pi) * exp(-x*x/2);
end;
/

字符串
cdf必须被近似（因为它是没有简单数学公式的az积分函数），一种可能的近似如下实现。许多其他近似可以在维基百科上找到。

create or replace function calc_sn_cdf(x in number) return number
is
 b0 CONSTANT NUMBER :=  0.2316419; 
 b1 CONSTANT NUMBER :=  0.319381530;
 b2 CONSTANT NUMBER := -0.356563782; 
 b3 CONSTANT NUMBER :=  1.781477937; 
 b4 CONSTANT NUMBER := -1.821255978; 
 b5 CONSTANT number :=  1.330274429;
 v_t  number;
begin
  --see 26.2.17 at http://people.math.sfu.ca/~cbm/aands/page_932.htm
  --see https://en.wikipedia.org/wiki/Normal_distribution#Numerical_approximations_for_the_normal_CDF
  --Zelen & Severo (1964) approximation
  if x < 0 then
    --this approximation works for x>0, but cdf is symmetric for x=0:
    return 1 - calc_sn_cdf(-x);
  else
    v_t := 1 / (1 + b0*x);
    return 1 - calc_sn_pdf(x)*(b1*v_t + b2*v_t*v_t + b3*v_t*v_t*v_t + b4*v_t*v_t*v_t*v_t + b5*v_t*v_t*v_t*v_t*v_t);
  end if;    
end;
/

型
顺便说一句，如果你需要运行这些函数很多时间，这将是有益的打开原生pl/sql编译。

以下工作为我得到z值（相当于标准化在Excel中）的分级/重新分级学生的分数，并获得新的分数的基础上，新的平均值和标准差。

create or replace FUNCTION STANDARDIZE(x_value number,mean_value number,stddev_value number)
RETURN NUMBER IS
BEGIN
   IF (stddev_value = 0) THEN
  RETURN 1;
   END IF;
 RETURN  (x_value-mean_value)/stddev_value;
END STANDARDIZE;

字符串
一旦你得到了z值（标准化），下一步就是决定新的标准差和新的平均差。计算新值的公式如下

create or replace FUNCTION f_get_new_marks(zvalue  number,p_new_mean number,p_new_std_dev number) RETURN NUMBER IS
BEGIN
  IF   ROUND(((zvalue * p_new_std_dev) + p_new_mean),0) >100 then 
    return 100;
    else 
return ROUND(( (zvalue * p_new_std_dev) + p_new_mean),0);
end if;    
END f_get_new_marks;

型