資源描述:
《sql與oracle函數(shù)對比》由會員上傳分享,免費(fèi)在線閱讀,更多相關(guān)內(nèi)容在工程資料-天天文庫。
1、Oracle和SQLServer常用函數(shù)對比專題一、數(shù)學(xué)函數(shù) 1.絕對值 S:selectabs(-1)value O:selectabs(-1)valuefromdual? 2.取整(大) S:selectceiling(-1.001)value O:selectceil(-1.001)valuefromdual? 3.取整(小) S:selectfloor(-1.001)value O:selectfloor(-1.001)valuefromdual? 4.取整(截?。?/p>
2、 S:selectcast(-1.002asint)value O:selecttrunc(-1.002)valuefromdual? 5.四舍五入 S:selectround(1.23456,4)value1.23460 O:selectround(1.23456,4)valuefromdual1.2346? 6.e為底的冪 S:selectExp(1)value2.7182818284590451 O:selectExp(1)valuefromdual2.71828182?
3、7.取e為底的對數(shù) S:selectlog(2.7182818284590451)value1 O:selectln(2.7182818284590451)valuefromdual;1? 8.取10為底對數(shù) S:selectlog10(10)value1 O:selectlog(10,10)valuefromdual;1? 9.取平方 S:selectSQUARE(4)value16 O:selectpower(4,2)valuefromdual16? 10.取平方根 S:
4、selectSQRT(4)value2 O:selectSQRT(4)valuefromdual2? 11.求任意數(shù)為底的冪 S:selectpower(3,4)value81 O:selectpower(3,4)valuefromdual81? 12.取隨機(jī)數(shù) S:selectrand()value O:selectsys.dbms_random.value(0,1)valuefromdual;? 13.取符號 S:selectsign(-8)value-1 O:selec
5、tsign(-8)valuefromdual-1? 14.圓周率 S:SELECTPI()value3.1415926535897931 O:selectacos(-1)PIfromdual;selectexp(1)efromdual;? 15.sin,cos,tan參數(shù)都以弧度為單位 例如:selectsin(PI()/2)value得到1(SQLServer) 16.Asin,Acos,Atan,Atan2返回弧度 17.弧度角度互換(SQLServer,Oracle未知)
6、DEGREES:弧度-〉角度 RADIANS:角度-〉弧度 二、數(shù)值間比較 18.求集合最大值 S:selectmax(value)valuefrom (select1value union select-2value union select4value union select3value)a O:selectgreatest(1,-2,4,3)valuefromdual? 19.求集合最小值 S:selectmin(value)valuefrom (selec
7、t1value union select-2value union select4value union select3value)a O:selectleast(1,-2,4,3)valuefromdual? 20.如何處理null值(F2中的null以10代替) S:selectF1,IsNull(F2,10)valuefromTbl O:selectF1,nvl(F2,10)valuefromTbl? 21.求字符序號 S:selectascii(''a'')val
8、ue O:selectascii(''a'')valuefromdual? 22.從序號求字符 S:selectchar(97)value O:selectchr(97)valuefromdual? 23.連接 S:select''11''+''22''+''33''value O:selectCONCAT(''11'',''22'')
9、
10、33valuefromdual? 23.子串位置--返回3 S:selectCHARINDEX(''s'',''sdsq'',2)value