有如下需求,将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)的写法的不同。