Oracle PL/SQL/PL SQL/Data Type Convert
Содержание
- 1 Convert char to date: TO_DATE("January","Month DD, YYYY")
- 2 Convert char to number
- 3 Convert char to number and number to char with various formats
- 4 Convert date to char, and char to date with various formats
- 5 Converting number to character formatted as a numeric string
- 6 Converting VARCHAR2 percentage data to a decimal equivalent
- 7 Convert number to char
- 8 Data implicit conversion examples: from char to date
- 9 Data implicit conversion examples: from char to number
- 10 Expressing your work in scientific notation with TO_CHAR()
Convert char to date: TO_DATE("January","Month DD, YYYY")
<source lang="sql">
SQL> SQL> -- Datatype conversion SQL> SQL> DECLARE
2 myDate DATE;
3 BEGIN
4 myDate := TO_DATE("January 01, 2000","Month DD, YYYY");
5
6 DBMS_OUTPUT.PUT_LINE(myDate);
7 END;
8
9
10
11 /
01-JAN-00 PL/SQL procedure successfully completed. SQL>
</source>
Convert char to number
<source lang="sql">
SQL> SQL> DECLARE
2 myChar VARCHAR2(20) := "$119,252.75";
3 myNumber NUMBER ;
4 BEGIN
5 myNumber := TO_Number(myChar,"$999,999.99");
6 DBMS_OUTPUT.PUT_LINE("The converted number is: " || myNumber);
7 DBMS_OUTPUT.PUT_LINE("Your commission at 6% is: " || (myNumber*.06));
8 END;
9 /
The converted number is: 119252.75 Your commission at 6% is: 7155.165 PL/SQL procedure successfully completed. SQL>
</source>
Convert char to number and number to char with various formats
<source lang="sql">
SQL> SQL> -- Examples of the conversion functions. SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE
2 n1 NUMBER;
3 n2 NUMBER;
4 n3 NUMBER;
5 BEGIN
6 -- here are some examples of numeric conversions.
7 n1 := TO_NUMBER ("123.99","999D99");
8 n2 := TO_NUMBER ("$1,235.95","$9G999D99");
9 DBMS_OUTPUT.PUT_LINE("n1 = " || TO_CHAR(n1,"999D99"));
10 DBMS_OUTPUT.PUT_LINE("n2 = " || TO_CHAR(n2,"$9G999D99"));
11 END;
12 /
n1 = 123.99 n2 = $1,235.95 PL/SQL procedure successfully completed. SQL> SQL> SQL>
</source>
Convert date to char, and char to date with various formats
<source lang="sql">
SQL> SQL> -- Examples of the conversion functions. SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE
2 d1 DATE;
3 d2 DATE;
4 d3 DATE;
5 d4 DATE;
6 BEGIN
7 d1 := TO_DATE("1/1/02","mm/dd/yy");
8 d2 := TO_DATE("1-1-1998","mm-dd-yyyy");
9 d3 := TO_DATE("Jan 1, 2000","mon dd, yyyy");
10 d4 := TO_DATE("1/1/02","mm/dd/rr");
11 DBMS_OUTPUT.PUT_LINE("d1 = " || TO_CHAR(d1,"dd-Mon-yyyy"));
12 DBMS_OUTPUT.PUT_LINE("d2 = " || TO_CHAR(d2,"mm/dd/yyyy"));
13 DBMS_OUTPUT.PUT_LINE("d3 = " || TO_CHAR(d3,"Day, Month dd, yyyy"));
14 DBMS_OUTPUT.PUT_LINE("d4 = " || TO_CHAR(d4,"Dy, Mon dd, yyyy"));
15 END;
16 /
d1 = 01-Jan-2002 d2 = 01/01/1998 d3 = Saturday , January 01, 2000 d4 = Tue, Jan 01, 2002 PL/SQL procedure successfully completed. SQL> SQL>
</source>
Converting number to character formatted as a numeric string
<source lang="sql">
SQL> -- Converting number to character formatted as a numeric string. SQL> DECLARE
2 myNumber NUMBER := 90210;
3 myChar VARCHAR2(21) ;
4 BEGIN
5 myChar := TO_CHAR(myNumber,"0000000000");
6 DBMS_OUTPUT.PUT_LINE("The employee ID is: " || myChar);
7 END;
8 /
The employee ID is: 0000090210 PL/SQL procedure successfully completed. SQL> SQL>
</source>
Converting VARCHAR2 percentage data to a decimal equivalent
<source lang="sql">
SQL> -- Converting VARCHAR2 percentage data to a decimal equivalent. SQL> DECLARE
2 myChar VARCHAR2(20) := "33.33";
3 myNumber NUMBER ;
4 BEGIN
5 myNumber := TO_Number(myChar,"999.999999");
6 DBMS_OUTPUT.PUT_LINE("The converted number is: " || myNumber);
7 DBMS_OUTPUT.PUT_LINE("Your decimal equivalent is: " || (myNumber/100));
8 END;
9 /
The converted number is: 33.33 Your decimal equivalent is: .3333 PL/SQL procedure successfully completed. SQL> SQL>
</source>
Convert number to char
<source lang="sql">
SQL> SQL> --WHILE loop SQL> SQL> SET SERVEROUTPUT ON SQL> DECLARE
2 x NUMBER := 1;
3 BEGIN
4 WHILE x <= 5 LOOP
5 DBMS_OUTPUT.PUT_LINE("This has executed" ||TO_CHAR(x)||" time(s)");
6 x := x +1;
7 END LOOP;
8 END;
9 /
This has executed1 time(s) This has executed2 time(s) This has executed3 time(s) This has executed4 time(s) This has executed5 time(s) PL/SQL procedure successfully completed. SQL> SQL>
</source>
Data implicit conversion examples: from char to date
<source lang="sql">
SQL> SQL> -- Implicit conversion examples. SQL> SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE
2 d1 DATE;
3 cd1 VARCHAR2(10);
4 cd2 VARCHAR2(10);
5 BEGIN
6 cd1 := "15-Nov-61";
7 -- Now assign the string to a date variable. The conversion is implicit.
8 d1 := cd1;
9 -- Now assign that date variable to another string.
10 -- Again the conversion is implicit, but this time the conversion is
11 -- from a date to a string.
12 cd2 := d1;
13 -- Display the two character strings to show that they are the same.
14 DBMS_OUTPUT.PUT_LINE("CD1 = " || cd1);
15 DBMS_OUTPUT.PUT_LINE("CD2 = " || cd2);
16
17 END;
18 /
CD1 = 15-Nov-61 CD2 = 15-NOV-61 PL/SQL procedure successfully completed. SQL>
</source>
Data implicit conversion examples: from char to number
<source lang="sql">
SQL> SQL> -- Implicit conversion examples. SQL> SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE
2 n1 NUMBER;
3 cn1 VARCHAR2(10);
4 cn2 VARCHAR2(10);
5 BEGIN
6 -- Repeat the same example as above, but with numbers.
7 cn1 := "995";
8 n1 := cn1 + .99 ;
9 cn2 := n1;
10 DBMS_OUTPUT.PUT_LINE("CN1 = " || cn1);
11 DBMS_OUTPUT.PUT_LINE("CN2 = " || cn2);
12 END;
13 /
CN1 = 995 CN2 = 995.99 PL/SQL procedure successfully completed. SQL> SQL>
</source>
Expressing your work in scientific notation with TO_CHAR()
<source lang="sql">
SQL> -- Expressing your work in scientific notation. SQL> DECLARE
2 myNumber NUMBER := 90210;
3 myChar VARCHAR2(21) ;
4 BEGIN
5 myChar := TO_CHAR(myNumber,"9.99EEEE");
6 DBMS_OUTPUT.PUT_LINE("The Scientific Notation is: " || myChar);
7 END;
8 /
The Scientific Notation is: 9.02E+04 PL/SQL procedure successfully completed. SQL>
</source>
