大家知道从number、char、date类型之间有to_number、to_char、to_date函数进行类型转换。但是如何进行number、binary_float、binary_double类型之间的转换?或number类型如何转换精度、有效值?这就需要用到cast函数
cast的函数主要有下面两个作用:
1、纯格式转换
number、binary_float、binary_double类型之间的转换:
sys@T>select a from t1;
A
----------------------
123567890.1234567890
sys@T>select cast(a as binary_float) from t1;
CAST(AASBINARY_FLOAT)
---------------------
1.236E+008
sys@T>select cast(a as binary_double) from t1;
CAST(AASBINARY_DOUBLE)
----------------------
1.236E+008
sys@T>select dump(a) from t1;
DUMP(A)
-------------------------------------------------------
Typ=2 Len=11: 197,2,24,57,79,91,13,35,57,79,91
sys@T>select dump(cast(a as binary_float)) from t1;
DUMP(CAST(AASBINARY_FLOAT))
-------------------------------------------------------
Typ=100 Len=4: 204,235,175,226
sys@T>select dump(cast(a as binary_double)) from t1;
DUMP(CAST(AASBINARY_DOUBLE))
-------------------------------------------------------
Typ=101 Len=8: 193,157,117,252,72,126,107,117
number类型精度、有效值的转换:
sys@T>select a from t1;
A
----------------------
123567890.1234567890
sys@T>select cast(a as number(10)) from t1;
CAST(AASNUMBER(10))
-------------------
123567890
sys@T>select dump(a) from t1;
DUMP(A)
-------------------------------------------------------
Typ=2 Len=11: 197,2,24,57,79,91,13,35,57,79,91
sys@T>select dump(cast(a as number(10))) from t1;
DUMP(CAST(AASNUMBER(10)))
-------------------------------------------------------
Typ=2 Len=6: 197,2,24,57,79,91
2、提高性能
Oracle对于number的运算是在软件中模拟的,而对于binary_float、binary_double这种浮点数是使用硬件执行计算的。对于复杂的数学函数,使用number和使用浮点数的性能现比就会有比较大的差距。所以可以在对number计算时,将其转换为浮点数进行运算。
sys@T>insert into t1 select level from dual connect by level<=100000;
在tkprof中可以看到number类型使用的CPU时间是binary_float的10倍:
select ln(a) from t1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 6668 3.85 3.89 0 6823 0 100000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6670 3.85 3.89 0 6824 0 100000
select ln(cast(a as binary_float)) from t1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 6668 0.40 0.37 0 6823 0 100000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6670 0.40 0.39 0 6824 0 100000
但是,使用这种方法一定要小心精度的损失
sys@T>insert into t1 values(123567890.1234567890);
1 row created.
sys@T>select ln(a) from t1;
LN(A)
----------
18.6323013
sys@T>select ln(cast(a as binary_float)) from t1;
LN(CAST(AASBINARY_FLOAT))
-------------------------
1.863E+001
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/498744/viewspace-328471/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/498744/viewspace-328471/