format number

DECLARE

    FUNCTION FN_FORMAT_SCORE(P_SCORE NUMBER)

    RETURN VARCHAR2

    IS

        V_SCORE     VARCHAR2(20);

    BEGIN

        V_SCORE := NVL(P_SCORE, 0);

        V_SCORE := TRIM(TO_CHAR(V_SCORE, '9,990.9'));

        RETURN V_SCORE;

    EXCEPTION

    WHEN OTHERS

    THEN

        RETURN TO_CHAR(NVL(P_SCORE, 0));

    END FN_FORMAT_SCORE;

BEGIN

    DBMS_OUTPUT.PUT_LINE(FN_FORMAT_SCORE(NULL));

    DBMS_OUTPUT.PUT_LINE(FN_FORMAT_SCORE('0'));

    DBMS_OUTPUT.PUT_LINE(FN_FORMAT_SCORE(0));

    DBMS_OUTPUT.PUT_LINE(FN_FORMAT_SCORE(100));

    DBMS_OUTPUT.PUT_LINE(FN_FORMAT_SCORE(1000));

    DBMS_OUTPUT.PUT_LINE(FN_FORMAT_SCORE(1000.9));

    DBMS_OUTPUT.PUT_LINE(FN_FORMAT_SCORE(1007.9));

END;