Oracle PL/SQL/Conversion Functions/TO Number

Материал из SQL эксперт
Версия от 10:01, 26 мая 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

Converting a Character Formatted as Currency to an Integer Value

  
SQL>
SQL> DECLARE
  2       v_Convert_Number VARCHAR2(20) := "$119,252.75";
  3       v_Hold_Number NUMBER ;
  4  BEGIN
  5       v_Hold_Number := TO_Number(v_Convert_Number,"$999,999,999.99");
  6       DBMS_OUTPUT.PUT_LINE("The converted number is: " || v_Hold_Number);
  7       DBMS_OUTPUT.PUT_LINE("Your commission at 6% is: " ||
  8                            (v_Hold_Number*.06));
  9  END;
 10  /
The converted number is: 119252.75
Your commission at 6% is: 7155.165
PL/SQL procedure successfully completed.
SQL>
SQL> --



Converting a Character to an Integer Value

  
SQL>
SQL> DECLARE
  2       v_Convert_Number VARCHAR2(20) := "1999";
  3       v_Hold_Number NUMBER ;
  4  BEGIN
  5       v_Hold_Number := TO_Number(v_Convert_Number,"9999");
  6       DBMS_OUTPUT.PUT_LINE("The converted number is: " || v_Hold_Number);
  7       DBMS_OUTPUT.PUT_LINE("The converted number plus 10 is: " ||
  8                            (v_Hold_Number+10));
  9  END;
 10  /
The converted number is: 1999
The converted number plus 10 is: 2009
PL/SQL procedure successfully completed.
SQL>
SQL> --



Converting VARCHAR2 Percentage Data to a Decimal Equivalent

  
SQL>
SQL> DECLARE
  2       v_Convert_Number VARCHAR2(20) := "33.33";
  3       v_Hold_Number NUMBER ;
  4  BEGIN
  5       v_Hold_Number := TO_Number(v_Convert_Number,"999.999999");
  6       DBMS_OUTPUT.PUT_LINE("The converted number is: " || v_Hold_Number);
  7       DBMS_OUTPUT.PUT_LINE("Your decimal equivalent is: " ||
  8                            (v_Hold_Number/100));
  9  END;
 10  /
The converted number is: 33.33
Your decimal equivalent is: .3333
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> --



Errors with the Format Mask

  
SQL>
SQL> DECLARE
  2       v_Convert_Number VARCHAR2(20) := "$119,252.75";
  3       v_Hold_Number NUMBER ;
  4  BEGIN
  5       v_Hold_Number := TO_Number(v_Convert_Number,"$99,999.99");
  6       DBMS_OUTPUT.PUT_LINE("The converted number is: " || v_Hold_Number);
  7       DBMS_OUTPUT.PUT_LINE("Your commission at 6% is: " ||
  8                            (v_Hold_Number*.06));
  9  END;
 10  /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 5

SQL>
SQL>
SQL> --



to_number("123")

 
SQL>
SQL>
SQL> select 123
  2  ,      to_char(123)
  3  ,      to_char(123,"$09999.99")
  4  ,      to_number("123")
  5  from   dual;
       123 TO_ TO_CHAR(12 TO_NUMBER("123")
---------- --- ---------- ----------------
       123 123  $00123.00              123
SQL>