英文大写金额

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

 

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;