Oracle的Cast的用法

xypincle發表於2017-05-03
經常在前輩們的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;

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/28878983/viewspace-2138362/,如需轉載,請註明出處,否則將追究法律責任。

相關文章