Oracle PL/SQL Tutorial/PL SQL Data Types/CHAR Type Convert
Содержание
Converting a Character Formatted as Currency to an Integer Value
<source lang="sql">
SQL> SQL> set serveroutput on SQL> set echo on 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.</source>
Converting a Character to an Integer Value
<source lang="sql">
SQL> SQL> SQL> set serveroutput on SQL> set echo on 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.</source>
Converting VARCHAR2 Percentage Data to a Decimal Equivalent
<source lang="sql">
SQL> SQL> set serveroutput on SQL> set echo on 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></source>
Convert Number to VARCHAR implicitly
<source lang="sql">
SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> DECLARE
2 d1 DATE;
3 cd1 VARCHAR2(10);
4 cd2 VARCHAR2(10);
5 n1 NUMBER;
6 cn1 VARCHAR2(10);
7 cn2 VARCHAR2(10);
8 BEGIN
9 cn1 := "995";
10 n1 := cn1 + .99 ;
11 cn2 := n1;
12
13 DBMS_OUTPUT.PUT_LINE("CN1 = " || cn1);
14 DBMS_OUTPUT.PUT_LINE("N2 = " || n1);
15 DBMS_OUTPUT.PUT_LINE("CN2 = " || cn2);
16 END;
17 /
CN1 = 995 N2 = 995.99 CN2 = 995.99 PL/SQL procedure successfully completed. SQL> SQL></source>
Convert VARCHAR to Number implicitly
<source lang="sql">
SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> DECLARE
2 d1 DATE;
3 cd1 VARCHAR2(10);
4 cd2 VARCHAR2(10);
5 n1 NUMBER;
6 cn1 VARCHAR2(10);
7 cn2 VARCHAR2(10);
8 BEGIN
9 cn1 := "995";
10 n1 := cn1 + .99 ;
11
12 DBMS_OUTPUT.PUT_LINE("CN1 = " || cn1);
13 DBMS_OUTPUT.PUT_LINE("N2 = " || n1);
14 END;
15 /
CN1 = 995 N2 = 995.99 PL/SQL procedure successfully completed.</source>
