ORACLE函数-阿拉伯数字转换成英文大写币别(格式化逗号分隔符后传入)

FUNCTION TRANSLATION (
    p_money       IN VARCHAR2,
    p_currency  IN VARCHAR2 default '' --currency
)
    RETURN varchar2
IS
    j INT := 0;
    i INT := 1;
    l int := 0;
    m int := 1;
    len INT := 0;
    len1 INT := 0;
    len2 int :=0;
    str VARCHAR2 (4000);
    s_result varchar2(1000);
    suffix varchar2(1000);
    num number(1);
    dw VARCHAR2(10);
    x_num number(1);
    tail varchar2(1000) := '';
    t_str varchar2(1000);
    p_delimiter varchar2(1) :=',';
BEGIN
    len := LENGTH (p_money);
    len1 := LENGTH (p_delimiter);
    t_str :=p_money; 
    num :=lengthb(regexp_replace(p_money,'[^,]',null));
    x_num := lengthb(regexp_replace(p_money,'[^.]',null));
    s_result := REPLACE(s_result,'HUNDRED_T');
    if x_num > 0 then
      t_str := substr(p_money,1,instr(p_money,'.')-1) ;
      if substr(p_money,instr(p_money,'.')+1) > 0 then
     tail := to_char(to_date(substr(p_money,instr(p_money,'.')+1),'J'),'Jsp')||' CENTS';
      end if;
    END IF; 
    if num = 3 then
      suffix :='BILLION,MILLION,THOUSAND,HUNDRED_T';
         ELSIF num = 2 then
       suffix :='MILLION,THOUSAND,HUNDRED_T';
     ELSIF num = 1 then
       suffix :='THOUSAND,HUNDRED_T';
     else
       suffix :='HUNDRED_T';
       end if;
    len2 := LENGTH (suffix);
    if REPLACE(t_str,',','') > 0 then
    WHILE j < len
    LOOP
        j := INSTR (t_str, p_delimiter, i);
        l := INSTR (suffix, p_delimiter, m);
        IF j = 0 and l = 0
        THEN
             j := len;
             l :=len2;
             str := SUBSTR (t_str, i);
             dw := SUBSTR (suffix, m);
             if str > 0 then
             s_result :=s_result||' '||to_char(to_date(str,'J'),'Jsp')||' '||dw;
             end if;
            IF i >= len
            THEN
                EXIT;
            END IF;
        ELSE
            str := SUBSTR(t_str, i, j - i);
            dw := SUBSTR(suffix, m, l - m);
            m := l + len1;
            i := j + len1;
            if str > 0 then
            s_result := s_result||' '||to_char(to_date(str,'J'),'Jsp')||' '||dw;
            end if;
        END IF;
    END LOOP;
    else
     s_result :=tail; 
    end if;
    s_result := UPPER(replace(s_result,'HUNDRED_T')||' '||p_currency);
    --s_result :=replace(s_result, 'HUNDRED','HUNDRED AND');
    if x_num > 0 and substr(p_money,instr(p_money,'.')+1) > 0 and REPLACE(t_str,',','') > 0 then
     s_result := s_result||' AND '||tail;
     end if;    
    RETURN TRIM(UPPER(REPLACE(s_result,'-',' ')));
END TRANSLATION;