Oracle PL/SQL/Conversion Functions/TO Number
Содержание
Converting a Character Formatted as Currency to an Integer Value
<source lang="sql">
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> --
</source>
Converting a Character to an Integer Value
<source lang="sql">
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> --
</source>
Converting VARCHAR2 Percentage Data to a Decimal Equivalent
<source lang="sql">
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> --
</source>
Errors with the Format Mask
<source lang="sql">
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> --
</source>
to_number("123")
<source lang="sql">
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>
</source>