remainder

数据库类型与版本

• 源库类型与版本：Oracle所有版本。
• 目标库类型与版本：GaussDB V2.0-3.1之前版本。

转换替代方案：

CREATE OR REPLACE FUNCTION dsc_ora_ext.dsc_fn_remainder
/* This function is used to replicate the behaviour of Oracle REMAINDER */
     ( i_numerator      IN   NUMBER
	 , i_denominator    IN   NUMBER )
RETURN NUMBER 
IMMUTABLE
AS 
    v_remainder      NUMBER;
BEGIN

    IF i_numerator IS NULL OR i_denominator IS NULL
    -- If anyone of the inputs is NULL, return NULL
    THEN 
        RETURN NULL;
    END IF;

    v_remainder := MOD(i_numerator, i_denominator);

    IF ABS(v_remainder/i_denominator) > 0.5 
    -- If the remainder (mod) is more than half of demonitor, do the below
	-- i.e., use ROUND instead of FLOOR used in MOD
    THEN
       v_remainder := i_numerator - (ROUND(i_numerator*1.0 / i_denominator) * i_denominator);
    END IF;

    RETURN v_remainder;
END;