DB2 到 Oracle 函数迁移

有如下需求，将DB2的自定义函数迁移到Oracle，现做一下记录。
DB2函数：

--DB2
CREATE FUNCTION "QUAL"."CHECKINDEXSTATUS"
 ("CRNT_VAL" DECIMAL(20, 2),
  "PERCENT_VAL" DECIMAL(20, 2),
  "WRCAP" DECIMAL(20, 2),
  "WRFLOOR" DECIMAL(20, 2),
  "WPCAP" DECIMAL(20, 2),
  "WPFLOOR" DECIMAL(20, 2),
  "ERCAP" DECIMAL(20, 2),
  "ERFLOOR" DECIMAL(20, 2),
  "EPCAP" DECIMAL(20, 2),
  "EPFLOOR" DECIMAL(20, 2)
 ) 
  RETURNS INTEGER
  SPECIFIC "QUAL"."SQL140402151923000"
  LANGUAGE SQL
  NOT DETERMINISTIC
  READS SQL DATA
  STATIC DISPATCH
  CALLED ON NULL INPUT
  EXTERNAL ACTION
  INHERIT SPECIAL REGISTERS
  BEGIN AtOMIC
    if CRNT_VAL=-444444444
      and PERCENT_VAL=-444444444 then
      return -3;
    elseIF CRNT_VAL=-444444444 THEN
      IF PERCENT_VAL <=EPFLOOR
        or PERCENT_VAL>=EPCAP THEN
        RETURN -2;
      ELSEIF PERCENT_VAL>= WPCAP
        or PERCENT_VAL<= WPFLOOR THEN
        RETURN -1;
      ELSE
        RETURN 0;
      END IF;
    ELSEIF PERCENT_VAL=-444444444 THEN
      IF CRNT_VAL>= ERCAP
        or CRNT_VAL<= ERFLOOR THEN
        RETURN -2;
      ELSEIF CRNT_VAL>= WRCAP
        or CRNT_VAL<= WRFLOOR THEN
        RETURN -1;
      ELSE
        RETURN 0;
      END IF;
    ELSE
      IF (PERCENT_VAL>= EPCAP
        or PERCENT_VAL<= EPFLOOR )
        OR ( CRNT_VAL>= ERCAP
        or CRNT_VAL<= ERFLOOR ) THEN
        RETURN -2;
      ELSEIF ( PERCENT_VAL>= WPCAP
        or PERCENT_VAL<= WPFLOOR )
        OR ( CRNT_VAL>= WRCAP
        or CRNT_VAL<= WRFLOOR ) THEN
        RETURN -1;
      ELSE
        RETURN 0;
      END IF;
    END IF;
  END;

迁移到Oracle，如下：

--Oracle
create or replace function CHECKINDEXSTATUS
(CRNT_VAL number,
 PERCENT_VAL number,
 WRCAP number,
 WRFLOOR number,
 WPCAP number,
 WPFLOOR number,
 ERCAP number,
 ERFLOOR number,
 EPCAP number,
 EPFLOOR number
)
return number
is
begin
    if (CRNT_VAL=-444444444
      and PERCENT_VAL=-444444444) then
      return -3;
    elsif (CRNT_VAL=-444444444) THEN
      IF (PERCENT_VAL <=EPFLOOR
        or PERCENT_VAL>=EPCAP) THEN
        RETURN -2;
      elsif (PERCENT_VAL>= WPCAP
        or PERCENT_VAL<= WPFLOOR) THEN
        RETURN -1;
      ELSE
        RETURN 0;
      END IF;
    elsif (PERCENT_VAL=-444444444) THEN
      IF (CRNT_VAL>= ERCAP
        or CRNT_VAL<= ERFLOOR) THEN
        RETURN -2;
      elsif (CRNT_VAL>= WRCAP
        or CRNT_VAL<= WRFLOOR) THEN
        RETURN -1;
      ELSE
        RETURN 0;
      END IF;
    ELSE
      IF ((PERCENT_VAL>= EPCAP
        or PERCENT_VAL<= EPFLOOR )
        OR ( CRNT_VAL>= ERCAP
        or CRNT_VAL<= ERFLOOR )) THEN
        RETURN -2;
      elsif (( PERCENT_VAL>= WPCAP
        or PERCENT_VAL<= WPFLOOR )
        OR ( CRNT_VAL>= WRCAP
        or CRNT_VAL<= WRFLOOR )) THEN
        RETURN -1;
      ELSE
        RETURN 0;
      END IF;
    END IF;
end;

小结：

• 注意数据类型不同
• 注意if函数的括号，elsif（ELSEIF）的写法的不同。