熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

Oracle中將數字轉化成字符串

2013-11-13 15:46:50  來源: Oracle 

  CREATE OR REPLACE FUNCTION to_words (
    m_amount_in IN NUMBER
    m_first_para IN VARCHAR := Dollars
    m_second_para IN VARCHAR := Cents
    )
    RETURN VARCHAR
  AS
    m_amount   NUMBER := m_amount_in;
    word_string VARCHAR();
    lower_number NUMBER;
    lower_str  VARCHAR();
    loop_var   NUMBER := ;
    /* Function declarartion to convert into string */
    FUNCTION to_string (m_val_in NUMBER)
     RETURN CHAR
    IS
    BEGIN
     IF m_val_in >
     THEN
       RETURN (INITCAP (TO_CHAR (TO_DATE (m_val_in SSSSS) SSSSSSP)));
     ELSE
       RETURN ();
     END IF;
    END;
    /*     End of Function   */
  BEGIN
  
  /* place the parameters at required places */
    lower_number := (m_amount MOD ) * ;
      /* Check for lower denominations not exceeding two digits */
    IF length(lower_number) <
    THEN
    IF m_amount >= and lower_number >
     THEN  lower_str :=
       ||
        m_first_para ||
       & ||
        LOWER (to_string (lower_number)) ||
       ||
        m_second_para;
     ELSIF m_amount < and lower_number >
     THEN  lower_str :=
       LOWER (to_string (lower_number)) ||
        ||
       m_second_para;
     ELSIF m_amount >= and lower_number =
     THEN  lower_str := || m_first_para;
     END IF;
    ELSE
        word_string := Invalid lower denomination !! ;
     RETURN (word_string);
    END IF;
  /* End of placing paramters */
  
    WHILE loop_var =
    LOOP
     IF   m_amount >
       AND m_amount <=
     THEN
       word_string :=
        word_string ||
         (INITCAP (to_string (FLOOR (m_amount))) || lower_str);
       loop_var := ;
     ELSIF   m_amount >
        AND m_amount <
     THEN
       word_string :=
        word_string ||
         (INITCAP (to_string (FLOOR (m_amount / ))) ||
         Thousand ||
        to_string (FLOOR (m_amount MOD )) ||
        lower_str
          );
       loop_var := ;
     ELSIF   m_amount >=
        AND m_amount <
     THEN
       word_string :=
        word_string ||
        INITCAP (to_string (FLOOR (m_amount / ))) || Million ;
       m_amount := FLOOR (m_amount MOD );
       IF m_amount =
       THEN
        loop_var := ;
        word_string := word_string || lower_str;
       END IF;
     ELSIF   m_amount >=
        AND m_amount <=
     THEN
       word_string :=
        INITCAP (
          to_string (FLOOR (m_amount / ))) || Billion ;
       m_amount := FLOOR (m_amount MOD );
       IF m_amount =
       THEN
        loop_var := ;
        word_string := word_string || lower_str;
       END IF;
     ELSIF m_amount =
     THEN
       word_string := (Zero || lower_str);
       loop_var := ;
     ELSE
       word_string := This is beyond my scope !!;
       loop_var := ;
     END IF;
    END LOOP;
    RETURN (word_string);
  END to_words;
  /
  
  

From:http://tw.wingwit.com/Article/program/Oracle/201311/17291.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.