資源描述:
《oracle與sql函數(shù)對(duì)比》由會(huì)員上傳分享,免費(fèi)在線閱讀,更多相關(guān)內(nèi)容在教育資源-天天文庫(kù)。
1、SQLServer和Oracle的常用函數(shù)對(duì)比 1.絕對(duì)值S:selectabs(-1)valueO:selectabs(-1)valuefromdual2.取整(大)S:selectceiling(-1.001)valueO:selectceil(-1.001)valuefromdual3.取整(?。㏒:selectfloor(-1.001)valueO:selectfloor(-1.001)valuefromdual4.取整(截?。㏒:selectcast(-1.002asint)valueO:selecttrunc(-1.002)valuefro
2、mdual5.四舍五入S:selectround(1.23456,4)value1.23460O:selectround(1.23456,4)valuefromdual1.23466.e為底的冪S:selectExp(1)value2.7182818284590451O:selectExp(1)valuefromdual2.718281827.取e為底的對(duì)數(shù)S:selectlog(2.7182818284590451)value1O:selectln(2.7182818284590451)valuefromdual;18.取10為底對(duì)數(shù)S:selectl
3、og10(10)value1O:selectlog(10,10)valuefromdual;19.取平方S:selectSQUARE(4)value16O:selectpower(4,2)valuefromdual1610.取平方根S:selectSQRT(4)value2O:selectSQRT(4)valuefromdual211.求任意數(shù)為底的冪S:selectpower(3,4)value81O:selectpower(3,4)valuefromdual8112.取隨機(jī)數(shù)S:selectrand()valueO:selectsys.dbms_ra
4、ndom.value(0,1)valuefromdual;13.取符號(hào)S:selectsign(-8)value-1O:selectsign(-8)valuefromdual-114.圓周率S:SELECTPI()value3.1415926535897931O:不知道15.sin,cos,tan參數(shù)都以弧度為單位例如:selectsin(PI()/2)value得到1(SQLServer)16.Asin,Acos,Atan,Atan2返回弧度17.弧度角度互換(SQLServer,Oracle不知道) DEGREES:弧度-〉角度RADIANS:角度
5、-〉弧度 ---------數(shù)值間比較 18.求集合最大值 S:selectmax(value)valuefrom(select1value union select-2value union select4value unionselect3value)a O:selectgreatest(1,-2,4,3)valuefromdual 19.求集合最小值 S:selectmin(value)valuefrom (select1valueunionselect-2valueunionselect4valueunionselect3value
6、)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 --------數(shù)值間比較 21.求字符序號(hào) S:selectascii('a')value O:selectascii('a')valuefromdual 22.從序號(hào)求字符 S:selectchar(97)value O:selectchr(97)valu
7、efromdual 23.連接 S:select'11'+'22'+'33'value O:selectCONCAT('11','22')
8、
9、33valuefromdual 23.子串位置--返回3 S:selectCHARINDEX('s','sdsq',2)value O:selectINSTR('sdsq','s',2)valuefromdual 23.模糊子串的位置--返回2,參數(shù)去掉中間%則返回7 S:selectpatindex('%d%q%','sdsfasdqe')value O:oracle沒(méi)發(fā)現(xiàn),但是instr可以通
10、過(guò)第四 selectINSTR('sdsfasdqe','sd',1,2)val