Oracle PL/SQL/PL SQL/Number to Char
Содержание
Converting a character formatted as currency to an integer value
<source lang="sql">
SQL> SQL> SQL> -- Converting a character formatted as currency to an integer value. SQL> DECLARE
2 myString VARCHAR2(20) := "$123,456.78"; 3 myNumber NUMBER ; 4 BEGIN 5 myNumber := TO_Number(myString,"$999,999,999.99"); 6 DBMS_OUTPUT.PUT_LINE("The converted number is: " || myString); 7 DBMS_OUTPUT.PUT_LINE("Your commission at 6% is: " || (myNumber*.06)); 8 END; 9 /
The converted number is: $123,456.78 Your commission at 6% is: 7407.4068 PL/SQL procedure successfully completed. SQL> SQL>
</source>
Converting Numbers to Characters Formatted as a Numeric String
<source lang="sql">
SQL> SQL> DECLARE
2 v_Convert_Number NUMBER := 90210; 3 v_Hold_Char VARCHAR2(21) ; 4 BEGIN 5 v_Hold_Char := TO_CHAR(v_Convert_Number,"0000000000"); 6 DBMS_OUTPUT.PUT_LINE("The employee ID is: " || v_Hold_Char); 7 END; 8 /
The employee ID is: 0000090210 PL/SQL procedure successfully completed. SQL> SQL> SQL> --
</source>
Expressing Your Work in Scientific Notation
<source lang="sql">
SQL> SQL> DECLARE
2 v_Convert_Number NUMBER := 90210; 3 v_Hold_Char VARCHAR2(21) ; 4 BEGIN 5 v_Hold_Char := TO_CHAR(v_Convert_Number,"9.99EEEE"); 6 DBMS_OUTPUT.PUT_LINE("The Scientific Notation is: " || v_Hold_Char); 7 END; 8 /
The Scientific Notation is: 9.02E+04 PL/SQL procedure successfully completed. SQL> SQL> SQL> --
</source>
Illegal conversion between character and number
<source lang="sql">
SQL> SQL> DECLARE
2 v_TempVar NUMBER; 3 BEGIN 4 v_TempVar := "xyz"; 5 END; 6 /
DECLARE
ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at line 4
SQL> SQL>
</source>