數(shù)據(jù)庫(kù)中cast的用法
數(shù)據(jù)庫(kù)中cast的用法的用法你知道嗎?下面小編就跟你們?cè)敿?xì)介紹下數(shù)據(jù)庫(kù)中cast的用法的用法,希望對(duì)你們有用。
數(shù)據(jù)庫(kù)中cast的用法的用法如下:
經(jīng)常在前輩們的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遞歸語(yǔ)句,但是CAST因?yàn)橛玫谋容^少,今天就先給自己掃個(gè)盲了,引用Oracle的官方文檔中對(duì)于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.
通過(guò)上面的描述,我們就可以知道CAST可以將一種類(lèi)型轉(zhuǎn)換為另外一種類(lèi)型。
比如,將字符串類(lèi)型轉(zhuǎn)換為NUMBER(10,2)類(lèi)型,而不僅僅是限于使用用to_number、to_char()以及to_date()類(lèi)型,上述表Table 3-1代表CAST是否能夠用于該類(lèi)型的相互轉(zhuǎn)換。
例子調(diào)用:
ChenZw> SELECT CAST('123.4567' AS NUMBER(10,2)) AS NUM FROM DUAL;