Oracle的Cast的用法
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)
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, VARCHAR2 | To NUMBER | To DATETIME, INTERVAL | To NCHAR, NVARCHAR2 |
---|---|---|---|---|---|
BINARY_FLOAT, BINARY_DOUBLE |
yes |
yes |
yes |
no |
yes |
CHAR, VARCHAR2 |
yes |
yes |
yes |
yes |
no |
NUMBER |
yes |
yes |
yes |
no |
yes |
DATETIME, INTERVAL |
no |
yes |
no |
yes |
yes |
NCHAR, NVARCHAR2 |
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;
评论暂时关闭