[大数据]ORACLE用自定义函数实现EXCEL中的NORMSINV与NORMSDIST函数功能

?如下为ORACLE自定义函数：

CREATE OR REPLACE FUNCTION NORMSINV(P IN NUMBER)
  RETURN NUMBER 
AS
  A1     NUMBER(32, 22) := -39.696830286653757;
  A2     NUMBER(32, 22) := 220.9460984245205;
  A3     NUMBER(32, 22) := -275.92851044696869;
  A4     NUMBER(32, 22) := 138.357751867269;
  A5     NUMBER(32, 22) := -30.66479806614716;
  A6     NUMBER(32, 22) := 2.5066282774592392;
  B1     NUMBER(32, 22) := -54.476098798224058;
  B2     NUMBER(32, 22) := 161.58583685804089;
  B3     NUMBER(32, 22) := -155.69897985988661;
  B4     NUMBER(32, 22) := 66.80131188771972;
  B5     NUMBER(32, 22) := -13.280681552885721;
  C1     NUMBER(32, 22) := -0.0077848940024302926;
  C2     NUMBER(32, 22) := -0.32239645804113648;
  C3     NUMBER(32, 22) := -2.4007582771618381;
  C4     NUMBER(32, 22) := -2.5497325393437338;
  C5     NUMBER(32, 22) := 4.3746641414649678;
  C6     NUMBER(32, 22) := 2.9381639826987831;
  D1     NUMBER(32, 22) := 0.0077846957090414622;
  D2     NUMBER(32, 22) := 0.32246712907003983;
  D3     NUMBER(32, 22) := 2.445134137142996;
  D4     NUMBER(32, 22) := 3.7544086619074162;
  q      NUMBER(32, 22);
  r      NUMBER(32, 22);
  P_LOW  NUMBER(32, 22);
  P_HIGH NUMBER(32, 22);

BEGIN
  P_LOW  := 0.02425;
  P_HIGH := 1 - P_LOW;

  IF (p > 0 AND p < P_LOW) THEN
    q := SQRT(-2 * LN(p));
    RETURN(((((C1 * q + C2) * q + C3) * q + C4) * q + C5) * q + C6) /((((D1 * q + D2) * q + D3) * q + D4) * q + 1);
  ELSIF (p >= P_LOW AND p <= P_HIGH) THEN
    q := p - 0.5;
    r := q * q;
    RETURN(((((A1 * r + A2) * r + A3) * r + A4) * r + A5) * r + A6) * q /(((((B1 * r + B2) * r + B3) * r + B4) * r + B5) * r + 1);
  ELSIF (p > P_HIGH AND p < 1) THEN
    q := SQRT(-2 * LN(1 - p));
    RETURN -(((((C1 * q + C2) * q + C3) * q + C4) * q + C5) * q + C6) /((((D1 * q + D2) * q + D3) * q + D4) * q + 1);
  ELSE
    RETURN 0;
  END IF;
END;

CREATE OR REPLACE FUNCTION NORMSDIST(Z IN NUMBER) RETURN NUMBER AS
  A1    NUMBER(32, 22) := 0.319381530;
  A2    NUMBER(32, 22) := -0.356563782;
  A3    NUMBER(32, 22) := 1.781477973;
  A4    NUMBER(32, 22) := -1.821255978;
  A5    NUMBER(32, 22) := 1.330274429;
  GAMMA NUMBER(32, 22) := 0.231641900;
  X     NUMBER(32, 22);
  T     NUMBER(32, 22);
  N     NUMBER(32, 22);

BEGIN
  X := ABS(Z);
  T := 1 / (1 + GAMMA * X);
  N := 1 - (1 / (SQRT(2 * ACOS(-1))) * EXP(-Z * Z / 2)) * (A1 * T + A2 * POWER(T, 2) + A3 * POWER(T, 3) + A4 * POWER(T, 4) + A5 * POWER(T, 5));

  IF (Z > 6) THEN
    RETURN 1;
  ELSIF (Z < -6) THEN
    RETURN 0;
  ELSIF (Z < 0) THEN
    RETURN 1.0 - N;
  ELSE
    RETURN N;
  END IF;

END;