Oracle的Cast的⽤法
经常在前辈们的SQL中可以看到CAST，例如:
WITH T AS(SELECT LEVEL||' ' L FROM DUAL CONNECT BY LEVEL<=5),
A(A,V) AS(SELECT CAST(L AS VARCHAR(30)),1 FROM T
UNION ALL
SELECT A||L,V+1 FROM A, T WHERE INSTR(A,L)=0 AND V<5
)
SELECT * FROM A;
例如上⾯的with递归语句，但是CAST因为⽤的⽐较少，今天就先给⾃⼰扫个盲了，引⽤Oracle的官⽅⽂档中对于CAST的描述如下：CAST
CAST converts values from one data type to another.
Return Value
The data type specified by type_name.
Syntax
CAST(expr AS type_name)oracle decimal类型
Arguments
expr can be an expression in one of the data types.
type_name is one of the data types listed in Table1-2，"Dimensional Data Types".
Table 3-1 shows which data types can be cast into which other built-in data types. NUMBER includes NUMBER, DECIMAL,
and INTEGER. DATETIME includes DATE,TIMESTAMP, TIMESTAMP WITH TIMEZONE, and TIMESTAMP WITH LOCAL TIMEZONE. INTERVAL includes INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH.
Table 3-1 Compatible Data Types
From To BINARY_FLOAT,
BINARY_DOUBLE
To CHAR,
VARCHAR2To NUMBER
To DATETIME,
INTERVAL
To NCHAR,
NVARCHAR2
BINARY_FLOAT, BI
NARY_DOUBLE
yes yes yes no yes
CHAR, VARCHAR2yes yes yes yes no
NUMBER yes yes yes no yes
DATETIME, INTERV
AL
no yes no yes yes
NCHAR, NVARCHA
R2
yes no yes no yes
Example
CAST('123.4567' AS NUMBER(10,2)) returns the value 123.46.
通过上⾯的描述，我们就可以知道CAST可以将⼀种类型转换为另外⼀种类型。
⽐如，将字符串类型转换为NUMBER(10,2)类型，⽽不仅仅是限于使⽤⽤to_number、to_char（）以及to_date（）类型，上述表Table 3-1代表CAST是否能够⽤于该类型的相互转换。
例⼦调⽤：
ChenZw> SELECT CAST('123.4567' AS NUMBER(10,2))  AS NUM FROM DUAL;