Oracle PL/SQL Tutorial/Conversion Functions/Cast
Содержание
- 1 cast(12.98 as number(2)) example1
- 2 Cast date to char
- 3 Cast decimal to integer
- 4 CAST examples
- 5 Cast null as timestamp
- 6 Cast number to a char
- 7 Cast string to date type before comparison
- 8 Cast table of numbers
- 9 Cast varchar to char
- 10 select cast(null as date ) example3
- 11 select cast("oak" as char(10) ) example2
- 12 Use Date to cast string to date type
cast(12.98 as number(2)) example1
SQL>
SQL>
SQL> select cast(12.98 as number(2)) example1
2 from dual;
EXAMPLE1
----------
13
SQL>
SQL>
Cast date to char
SQL>
SQL>
SQL> CREATE TABLE book(
2 title_id CHAR(3) NOT NULL,
3 title_name VARCHAR(40) NOT NULL,
4 type VARCHAR(10) NULL ,
5 pub_id CHAR(3) NOT NULL,
6 pages INTEGER NULL ,
7 price DECIMAL(5,2) NULL ,
8 sales INTEGER NULL ,
9 pubdate DATE NULL ,
10 contract SMALLINT NOT NULL
11 );
Table created.
SQL>
SQL>
SQL>
SQL> INSERT INTO book VALUES("T01","Java","history","P01",111,21.99,566,DATE "2000-08-01",1);
1 row created.
SQL> INSERT INTO book VALUES("T02","Oracle","history","P03", 114,19.95,9566,DATE "1998-04-01",1);
1 row created.
SQL> INSERT INTO book VALUES("T03","SQL","computer","P02", 122,39.95,25667,DATE "2000-09-01",1);
1 row created.
SQL> INSERT INTO book VALUES("T04","C++","psychology","P04", 511,12.99,13001,DATE "1999-05-31",1);
1 row created.
SQL> INSERT INTO book VALUES("T05","Python","psychology","P04", 101,6.95,201440,DATE "2001-01-01",1);
1 row created.
SQL> INSERT INTO book VALUES("T06","JavaScript","biography","P01", 173,19.95,11320,DATE "2000-07-31",1);
1 row created.
SQL> INSERT INTO book VALUES("T07","LINQ","biography","P03", 331,23.95,1500200,DATE "1999-10-01",1);
1 row created.
SQL> INSERT INTO book VALUES("T08","C#","children","P04", 861,10.00,4095,DATE "2001-06-01",1);
1 row created.
SQL> INSERT INTO book VALUES("T09","SQL Server","children","P04", 212,13.95,5000,DATE "2002-05-31",1);
1 row created.
SQL> INSERT INTO book VALUES("T10","AJAX","biography","P01", NULL,NULL,NULL,NULL,0);
1 row created.
SQL> INSERT INTO book VALUES("T11","VB","psychology","P04", 821,7.99,94123,DATE "2000-11-30",1);
1 row created.
SQL> INSERT INTO book VALUES("T12","Office","biography","P01", 507,12.99,100001,DATE "2000-08-31",1);
1 row created.
SQL> INSERT INTO book VALUES("T13","VBA","history","P03", 812,29.99,10467,DATE "1999-05-31",1);
1 row created.
SQL>
SQL>
SQL>
SQL> SELECT "Title "
2 || title_id
3 || " published on "
4 || CAST(pubdate AS CHAR(10))
5 AS "Biography publication dates"
6 FROM book
7 WHERE type = "biography"
8 AND pubdate IS NOT NULL
9 ORDER BY pubdate DESC;
Biography publication dates
---------------------------------
Title T12 published on 31-AUG-00
Title T06 published on 31-JUL-00
Title T07 published on 01-OCT-99
SQL>
SQL>
SQL> drop table book;
Table dropped.
SQL>
Cast decimal to integer
SQL>
SQL> CREATE TABLE book(
2 title_id CHAR(3) NOT NULL,
3 title_name VARCHAR(40) NOT NULL,
4 type VARCHAR(10) NULL ,
5 pub_id CHAR(3) NOT NULL,
6 pages INTEGER NULL ,
7 price DECIMAL(5,2) NULL ,
8 sales INTEGER NULL ,
9 pubdate DATE NULL ,
10 contract SMALLINT NOT NULL
11 );
Table created.
SQL>
SQL>
SQL>
SQL> INSERT INTO book VALUES("T01","Java","history","P01",111,21.99,566,DATE "2000-08-01",1);
1 row created.
SQL> INSERT INTO book VALUES("T02","Oracle","history","P03", 114,19.95,9566,DATE "1998-04-01",1);
1 row created.
SQL> INSERT INTO book VALUES("T03","SQL","computer","P02", 122,39.95,25667,DATE "2000-09-01",1);
1 row created.
SQL> INSERT INTO book VALUES("T04","C++","psychology","P04", 511,12.99,13001,DATE "1999-05-31",1);
1 row created.
SQL> INSERT INTO book VALUES("T05","Python","psychology","P04", 101,6.95,201440,DATE "2001-01-01",1);
1 row created.
SQL> INSERT INTO book VALUES("T06","JavaScript","biography","P01", 173,19.95,11320,DATE "2000-07-31",1);
1 row created.
SQL> INSERT INTO book VALUES("T07","LINQ","biography","P03", 331,23.95,1500200,DATE "1999-10-01",1);
1 row created.
SQL> INSERT INTO book VALUES("T08","C#","children","P04", 861,10.00,4095,DATE "2001-06-01",1);
1 row created.
SQL> INSERT INTO book VALUES("T09","SQL Server","children","P04", 212,13.95,5000,DATE "2002-05-31",1);
1 row created.
SQL> INSERT INTO book VALUES("T10","AJAX","biography","P01", NULL,NULL,NULL,NULL,0);
1 row created.
SQL> INSERT INTO book VALUES("T11","VB","psychology","P04", 821,7.99,94123,DATE "2000-11-30",1);
1 row created.
SQL> INSERT INTO book VALUES("T12","Office","biography","P01", 507,12.99,100001,DATE "2000-08-31",1);
1 row created.
SQL> INSERT INTO book VALUES("T13","VBA","history","P03", 812,29.99,10467,DATE "1999-05-31",1);
1 row created.
SQL>
SQL>
SQL>
SQL> SELECT
2 price
3 AS "price(DECIMAL)",
4 CAST(price AS INTEGER)
5 AS "price(INTEGER)",
6 "<" || CAST(price AS CHAR(8)) || ">"
7 AS "price(CHAR(8))"
8 FROM book;
price(DECIMAL) price(INTEGER) price(CHAR
-------------- -------------- ----------
21.99 22 <21.99 >
19.95 20 <19.95 >
39.95 40 <39.95 >
12.99 13 <12.99 >
6.95 7 <6.95 >
19.95 20 <19.95 >
23.95 24 <23.95 >
10 10 <10 >
13.95 14 <13.95 >
<>
7.99 8 <7.99 >
price(DECIMAL) price(INTEGER) price(CHAR
-------------- -------------- ----------
12.99 13 <12.99 >
29.99 30 <29.99 >
13 rows selected.
SQL>
SQL> drop table book;
Table dropped.
SQL>
SQL>
CAST examples
SQL>
SQL> DECLARE
2 a TIMESTAMP WITH TIME ZONE;
3 b VARCHAR2(40);
4 c TIMESTAMP WITH LOCAL TIME ZONE;
5 BEGIN
6 a := CAST ("24-Feb-2002 09.00.00.00 PM US/Eastern" AS TIMESTAMP WITH TIME ZONE);
7 b := CAST (a AS VARCHAR2);
8 c := CAST (a AS TIMESTAMP WITH LOCAL TIME ZONE);
9
10 DBMS_OUTPUT.PUT_LINE(a);
11 DBMS_OUTPUT.PUT_LINE(b);
12 DBMS_OUTPUT.PUT_LINE(c);
13 END;
14 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
Cast null as timestamp
SQL> SELECT CAST(NULL AS TIMESTAMP WITH LOCAL TIME ZONE) from dual;
CAST(NULLASTIMESTAMPWITHLOCALTIMEZONE)
---------------------------------------------------------------------------
Cast number to a char
SQL>
SQL> CREATE TABLE book(
2 title_id CHAR(3) NOT NULL,
3 title_name VARCHAR(40) NOT NULL,
4 type VARCHAR(10) NULL ,
5 pub_id CHAR(3) NOT NULL,
6 pages INTEGER NULL ,
7 price DECIMAL(5,2) NULL ,
8 sales INTEGER NULL ,
9 pubdate DATE NULL ,
10 contract SMALLINT NOT NULL
11 );
Table created.
SQL>
SQL>
SQL>
SQL>
SQL> INSERT INTO book VALUES("T01","Java","history","P01",111,21.99,566,DATE "2000-08-01",1);
1 row created.
SQL> INSERT INTO book VALUES("T02","Oracle","history","P03", 114,19.95,9566,DATE "1998-04-01",1);
1 row created.
SQL> INSERT INTO book VALUES("T03","SQL","computer","P02", 122,39.95,25667,DATE "2000-09-01",1);
1 row created.
SQL> INSERT INTO book VALUES("T04","C++","psychology","P04", 511,12.99,13001,DATE "1999-05-31",1);
1 row created.
SQL> INSERT INTO book VALUES("T05","Python","psychology","P04", 101,6.95,201440,DATE "2001-01-01",1);
1 row created.
SQL> INSERT INTO book VALUES("T06","JavaScript","biography","P01", 173,19.95,11320,DATE "2000-07-31",1);
1 row created.
SQL> INSERT INTO book VALUES("T07","LINQ","biography","P03", 331,23.95,1500200,DATE "1999-10-01",1);
1 row created.
SQL> INSERT INTO book VALUES("T08","C#","children","P04", 861,10.00,4095,DATE "2001-06-01",1);
1 row created.
SQL> INSERT INTO book VALUES("T09","SQL Server","children","P04", 212,13.95,5000,DATE "2002-05-31",1);
1 row created.
SQL> INSERT INTO book VALUES("T10","AJAX","biography","P01", NULL,NULL,NULL,NULL,0);
1 row created.
SQL> INSERT INTO book VALUES("T11","VB","psychology","P04", 821,7.99,94123,DATE "2000-11-30",1);
1 row created.
SQL> INSERT INTO book VALUES("T12","Office","biography","P01", 507,12.99,100001,DATE "2000-08-31",1);
1 row created.
SQL> INSERT INTO book VALUES("T13","VBA","history","P03", 812,29.99,10467,DATE "1999-05-31",1);
1 row created.
SQL>
SQL>
SQL>
SQL>
SQL> SELECT CAST(sales AS CHAR(7))
2 || " copies sold of title "
3 || title_id
4 AS "Biography sales"
5 FROM book
6 WHERE type = "biography"
7 AND sales IS NOT NULL
8 ORDER BY sales DESC;
Biography sales
--------------------------------
1500200 copies sold of title T07
100001 copies sold of title T12
11320 copies sold of title T06
SQL>
SQL> drop table book;
Table dropped.
SQL>
Cast string to date type before comparison
SQL> create table emp
2 ( empno NUMBER(4) constraint E_PK primary key
3 , ename VARCHAR2(8)
4 , init VARCHAR2(5)
5 , job VARCHAR2(8)
6 , mgr NUMBER(4)
7 , bdate DATE
8 , sal NUMBER(6,2)
9 , comm NUMBER(6,2)
10 , deptno NUMBER(2) default 10
11 ) ;
Table created.
SQL> insert into emp values(1,"Tom","N", "Coder", 13,date "1965-12-17", 800 , NULL, 20);
1 row created.
SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20", 1600, 300, 30);
1 row created.
SQL> insert into emp values(3,"Wil","TF" , "Tester",6,date "1962-02-22", 1250, 500, 30);
1 row created.
SQL> insert into emp values(4,"Jane","JM", "Designer", 9,date "1967-04-02", 2975, NULL, 20);
1 row created.
SQL> insert into emp values(5,"Mary","P", "Tester",6,date "1956-09-28", 1250, 1400, 30);
1 row created.
SQL> insert into emp values(6,"Black","R", "Designer", 9,date "1963-11-01", 2850, NULL, 30);
1 row created.
SQL> insert into emp values(7,"Chris","AB", "Designer", 9,date "1965-06-09", 2450, NULL, 10);
1 row created.
SQL> insert into emp values(8,"Smart","SCJ", "Coder", 4,date "1959-11-26", 3000, NULL, 20);
1 row created.
SQL> insert into emp values(9,"Peter","CC", "Designer",NULL,date "1952-11-17", 5000, NULL, 10);
1 row created.
SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28", 1500, 0, 30);
1 row created.
SQL> insert into emp values(11,"Ana","AA", "Coder", 8,date "1966-12-30", 1100, NULL, 20);
1 row created.
SQL> insert into emp values(12,"Jane","R", "Manager", 6,date "1969-12-03", 800 , NULL, 30);
1 row created.
SQL> insert into emp values(13,"Fake","MG", "Coder", 4,date "1959-02-13", 3000, NULL, 20);
1 row created.
SQL> insert into emp values(14,"Mike","TJA","Manager", 7,date "1962-01-23", 1300, NULL, 10);
1 row created.
SQL>
SQL>
SQL> select e.ename as emp
2 , m.ename as Designer
3 from emp m
4 JOIN
5 emp e
6 ON e.mgr = m.empno
7 where e.bdate > date "1965-01-01"
8 order by emp;
EMP DESIGNER
-------- --------
Ana Smart
Chris Peter
Jane Black
Jane Peter
Take Black
Tom Fake
6 rows selected.
SQL>
SQL> drop table emp;
Table dropped.
Cast table of numbers
SQL> create table num_tab (col1 number(10));
Table created.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE numberTableType AS TABLE OF NUMBER(10);
2 /
Type created.
SQL>
SQL> CREATE TABLE address_list (list_id VARCHAR2(6)PRIMARY KEY,
2 home_addresses numberTableType )
3 NESTED TABLE home_addresses STORE AS home_addreses_tab;
SQL>
SQL>
SQL> INSERT INTO address_list VALUES ("H101",numberTableType(1001,1002,1003,1004));
SQL>
SQL>
SQL>
SQL> declare
2 v_numberVarryType numberTableType := numberTableType(NULL,NULL,NULL);
3 begin
4 v_numberVarryType(1):=1001;
5 v_numberVarryType(2):=1002;
6 v_numberVarryType(3):=1003;
7 insert into num_tab select column_value from TABLE(CAST(v_numberVarryType AS numberTableType));
8 end;
9 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from num_tab;
COL1
------
1001
1002
1003
3 rows selected.
SQL>
SQL>
SQL> drop table num_tab;
Table dropped.
SQL>
SQL> drop table address_list;
SQL>
Cast varchar to char
SQL>
SQL>
SQL> CREATE TABLE book(
2 title_id CHAR(3) NOT NULL,
3 title_name VARCHAR(40) NOT NULL,
4 type VARCHAR(10) NULL ,
5 pub_id CHAR(3) NOT NULL,
6 pages INTEGER NULL ,
7 price DECIMAL(5,2) NULL ,
8 sales INTEGER NULL ,
9 pubdate DATE NULL ,
10 contract SMALLINT NOT NULL
11 );
Table created.
SQL>
SQL>
SQL>
SQL> INSERT INTO book VALUES("T01","Java","history","P01",111,21.99,566,DATE "2000-08-01",1);
1 row created.
SQL> INSERT INTO book VALUES("T02","Oracle","history","P03", 114,19.95,9566,DATE "1998-04-01",1);
1 row created.
SQL> INSERT INTO book VALUES("T03","SQL","computer","P02", 122,39.95,25667,DATE "2000-09-01",1);
1 row created.
SQL> INSERT INTO book VALUES("T04","C++","psychology","P04", 511,12.99,13001,DATE "1999-05-31",1);
1 row created.
SQL> INSERT INTO book VALUES("T05","Python","psychology","P04", 101,6.95,201440,DATE "2001-01-01",1);
1 row created.
SQL> INSERT INTO book VALUES("T06","JavaScript","biography","P01", 173,19.95,11320,DATE "2000-07-31",1);
1 row created.
SQL> INSERT INTO book VALUES("T07","LINQ","biography","P03", 331,23.95,1500200,DATE "1999-10-01",1);
1 row created.
SQL> INSERT INTO book VALUES("T08","C#","children","P04", 861,10.00,4095,DATE "2001-06-01",1);
1 row created.
SQL> INSERT INTO book VALUES("T09","SQL Server","children","P04", 212,13.95,5000,DATE "2002-05-31",1);
1 row created.
SQL> INSERT INTO book VALUES("T10","AJAX","biography","P01", NULL,NULL,NULL,NULL,0);
1 row created.
SQL> INSERT INTO book VALUES("T11","VB","psychology","P04", 821,7.99,94123,DATE "2000-11-30",1);
1 row created.
SQL> INSERT INTO book VALUES("T12","Office","biography","P01", 507,12.99,100001,DATE "2000-08-31",1);
1 row created.
SQL> INSERT INTO book VALUES("T13","VBA","history","P03", 812,29.99,10467,DATE "1999-05-31",1);
1 row created.
SQL>
SQL>
SQL>
SQL> SELECT
2 CAST(sales AS CHAR(8))
3 || " copies sold of "
4 || CAST(title_name AS CHAR(20))
5 AS "History and biography sales"
6 FROM book
7 WHERE sales IS NOT NULL
8 AND type IN ("history", "biography")
9 ORDER BY sales DESC;
History and biography sales
--------------------------------------------
1500200 copies sold of LINQ
100001 copies sold of Office
11320 copies sold of JavaScript
10467 copies sold of VBA
9566 copies sold of Oracle
566 copies sold of Java
6 rows selected.
SQL>
SQL> drop table book;
Table dropped.
SQL>
SQL>
select cast(null as date ) example3
SQL>
SQL>
SQL> select cast(null as date ) example3
2 from dual;
EXAMPLE3
---------
SQL>
select cast("oak" as char(10) ) example2
SQL>
SQL>
SQL>
SQL> select cast("oak" as char(10) ) example2
2 from dual;
EXAMPLE2
----------
oak
SQL>
Use Date to cast string to date type
SQL>
SQL> CREATE TABLE price_avg
2 (product VARCHAR(20)
3 ,whn DATE
4 ,price DECIMAL(10,2)
5 ,PRIMARY KEY (product,whn)
6 );
Table created.
SQL> INSERT INTO price_avg VALUES ("Product A",DATE "2006-05-19", 10);
1 row created.
SQL> INSERT INTO price_avg VALUES ("Product A",DATE "2006-05-20", 10);
1 row created.
SQL> INSERT INTO price_avg VALUES ("Product A",DATE "2006-05-21", 10.5);
1 row created.
SQL> INSERT INTO price_avg VALUES ("Product A",DATE "2006-05-22", 10.5);
1 row created.
SQL>
SQL>
SQL> CREATE TABLE price_delta
2 (product VARCHAR(20)
3 ,whn DATE
4 ,price DECIMAL(10,2)
5 ,PRIMARY KEY (product,whn)
6 );
Table created.
SQL>
SQL> INSERT INTO price_delta VALUES ("Product A",DATE "2001-01-01", 10);
1 row created.
SQL> INSERT INTO price_delta VALUES ("Product A",DATE "2006-05-21", 10.5);
1 row created.
SQL> INSERT INTO price_delta VALUES ("Product A",DATE "2005-06-15", 9);
1 row created.
SQL> INSERT INTO price_delta VALUES ("Product B",DATE "2001-01-01", 12);
1 row created.
SQL> INSERT INTO price_delta VALUES ("Product B",DATE "2005-06-15", 13);
1 row created.
SQL>
SQL> SELECT price
2 FROM price_avg
3 WHERE product="Product A" AND whn=DATE "2006-05-20";
PRICE
----------
10
SQL>
SQL> DROP TABLE price_avg;
Table dropped.
SQL> DROP TABLE price_delta;
Table dropped.