英文大写金额

来源：互联网发布：结构设计优化编辑：程序博客网时间：2024/05/04 11:07

---因为大写金额，注意字符长度

create or replace FUNCTION CONVERSION1 (p_Number NUMBER
                 )
RETURN VARCHAR2        IS

v_String    VARCHAR2(64);
v_Thousands VARCHAR2(64);
v_Millions VARCHAR2(64);
v_Hmillions VARCHAR2(64);
BEGIN
   IF Length(v_String) > 12 THEN
     RETURN 'Number entered must be between +999999999999 or -999999999999';
   END IF;

   v_String := to_char(abs(p_Number));
   --SRW.MESSAGE(111114,'test P_NUMBER: '||Length(v_String));

   IF Length(v_String) > 11 THEN
     v_Hmillions := replace(conversion1(substr(v_String,1,length(v_String)-11))
                            ,' And',' and')||' Hundred and ';
     v_String := substr(v_String,length(v_String)-10);
   END IF;

   IF length(v_String) > 6 THEN
     v_Millions := replace(conversion1(substr(v_String,1,length(v_String)-6)),' And',' and')||' Million ';
    -- v_String := substr(v_String,2);
     -- v_String := substr(v_String,5);
      v_String := substr(v_String,length(v_String)-5);
   END IF;

   IF length(v_String) = 5 THEN
     v_Thousands := replace(conversion1(substr(v_String,1,2))
                            ,' And',' and')||' Thousand ';
     v_String := substr(v_String,3);
   ELSIF length(v_String) = 6 THEN
     v_Thousands := replace(conversion1(substr(v_String,1,3))
                           ,' And',' and')||' Thousand ';
     v_String := substr(v_String,4);
   END IF;

   IF to_number(v_String) = 0 THEN
     v_String := '';
   ELSE
     v_String := initcap(replace(to_char(to_date(v_String,'YYYY'),'YYYYsp'),'-','ZZ'));
     IF abs(p_Number) > 100 AND mod(abs(p_Number),100) <> 0 THEN
       v_Space := instr(v_String,' ',-1);
       v_string := substr(v_String,1,v_Space)||' and '||substr(v_String,v_Space);
     END IF;
     END IF;
     --- RETURN(Length(v_String));
   RETURN replace(v_Sign||v_Hmillions||v_Millions||v_Thousands||
          replace(v_String,'zz','-'),' ',' ');

   --- when others then

END CONVERSION1;